Skip to content

MariaDB

This page describes how ts-sql-query integrates with MariaDB, including dialect-specific behavior, configuration options, and available features. It covers the proper setup of a MariaDB connection, guidelines for connection management, and advanced behaviors such as sequences and UUID handling.

Info

To configure the database dialect, extend the appropriate database connection class when defining your connection. You must choose the correct database type to ensure that the generated SQL queries follow the dialect expected by that database.

Do not share connections between requests

A ts-sql-query connection object — along with the query runner instances passed to its constructor — represents a dedicated connection to the database.

Therefore, you must not share the same connection object between concurrent HTTP requests. Instead, create a new connection object for each request, along with its own query runners.

Even if the query runner internally uses a connection pool, the ts-sql-query connection still represents a single active connection, acquired from the pool. It must be treated as such and never reused across requests.

Usage Example

import { MariaDBConnection } from "ts-sql-query/connections/MariaDBConnection";

class DBConnection extends MariaDBConnection<'DBConnection'> { }

Compatibility version

The compatibilityVersion property declares the minimum MariaDB version the generated SQL must support, encoded as the integer major * 1_000_000 + minor * 1_000 + patch — e.g. 10_005_000 for MariaDB 10.5.0, 10_003_003 for MariaDB 10.3.3. The numeric separator _ is for readability only (10_005_000 === 10005000). The default is Number.POSITIVE_INFINITY (latest), so every supported feature is emitted.

You can set this to your real database version (whatever it is) regardless of whether ts-sql-query currently uses it — extra granularity is harmless and future-proof.

Recognised breakpoints (with the default Number.POSITIVE_INFINITY every breakpoint below is enabled — the list reads as the bar you need to clear to keep each feature):

  • >= 13_000_001: target MariaDB 13.0.1+. Column references on a table-or-view returned by .oldValues() are emitted as OLD_VALUE(col) so that UPDATE ... RETURNING can return both pre- and post-update values in a single statement (added in MariaDB 13.0.1 via MDEV-5092). Bare column references on the updated table continue to mean the post-update value inside RETURNING. Note: MariaDB only supports RETURNING on single-table UPDATE statements; old values of joined tables can't be returned.
  • >= 10_005_000: target MariaDB 10.5+. The RETURNING clause (supported on INSERT and DELETE since MariaDB 10.5) is emitted on INSERT to retrieve the last inserted ID directly from the statement. Below this breakpoint the last inserted ID reported by the underlying connector after the query execution is used instead.
  • >= 10_004_000: target MariaDB 10.4+. DOUBLE is used as a cast target (added in MariaDB 10.4.0) whenever a value must be turned into a floating point number — .asDouble(), both operands of .divide(...). Below this breakpoint the value is multiplied by the approximate literal 1.0e0 instead, which promotes it to DOUBLE on any version.
  • >= 10_003_003: target MariaDB 10.3.3+. The VALUE(col) function — added in MariaDB 10.3.3 (MDEV-12172) as a rename of VALUES(col), to avoid the name clash with the standard Table Value Constructors syntax introduced in the same release — is emitted inside ON DUPLICATE KEY UPDATE to reference the values being inserted. The 10.3 line went GA at 10.3.7, so every stable 10.3 release is past this breakpoint. Below it the legacy VALUES(col) name is emitted instead — it remains accepted by every modern MariaDB version, so generated SQL stays runnable.
import { MariaDBConnection } from "ts-sql-query/connections/MariaDBConnection";

class DBConnection extends MariaDBConnection<'DBConnection'> {
    protected override compatibilityVersion = 10_004_000
}

stringConcat truncates long results

stringConcat is emitted as GROUP_CONCAT(), which silently truncates its result at the session's group_concat_max_len — 1024 bytes by default. It raises a warning, never an error, so the value arrives as a clean but incomplete string:

LENGTH(GROUP_CONCAT(6 x 200 chars)) = 1024      -- expected 1205
SHOW WARNINGS -> Warning 1260 "Row 6 was cut by GROUP_CONCAT()"

There is no alternative function on this engine (STRING_AGG does not exist), so the limit is inherent to the aggregate and the library cannot work around it. It is session configuration your application owns — raise it on the connection if you aggregate long strings:

pool.on('connection', c => c.query('SET SESSION group_concat_max_len = 1000000'))

Rounding behavior

MariaDB's native ROUND function applies different tie-breaking rules depending on whether its argument is an exact-value or an approximate-value number. Per the MariaDB knowledge base:

For exact-value numbers (DECIMAL), ROUND() uses the "round half away from zero" rule (so round(0.5) → 1 and round(2.5) → 3).

For approximate-value numbers (DOUBLE), the result depends on the C library, which rounds half to even on the usual platforms (so round(0.5) → 0 and round(2.5) → 2).

ts-sql-query breaks ties away from zero on every dialect, matching JavaScript's Math.round for positive .5 values. Expressions such as .divide(...) and .asDouble() produce a DOUBLE, so without care .round() would silently switch tie-breaking rules depending on what came before it in the chain. To keep .round() predictable and portable, the MariaDB connection casts the operand back to an exact type before applying round, so value.round() always rounds ties away from zero regardless of the operand's type.

For example, tIssue.priority.divide(2).round() (where priority = 1) yields round(0.5) = 1 on every dialect, including MariaDB.

If you prefer MariaDB's native round(<double>) semantics — typically because your application is single-dialect and you want the IEEE 754 round-to-even tie-breaking common on modern systems, or because existing queries depend on that result — set usePlatformDependentRound = true on your connection:

import { MariaDBConnection } from "ts-sql-query/connections/MariaDBConnection";

class DBConnection extends MariaDBConnection<'DBConnection'> {
    protected override usePlatformDependentRound = true
}

With the flag on, value.round() and value.roundn(n) emit round(x) directly: when x is an exact expression you still get away-from-zero, but when x is a DOUBLE expression (the type produced by .divide(...), .asDouble(), and many other arithmetic chains) the tie-breaking follows the C library's rules.

Sequences

MariaDB exposes server-side SEQUENCE objects (added in MariaDB 10.3.0 via MDEV-10139), and ts-sql-query lets you reference them from a MariaDBConnection. The sequence(...) builder and autogeneratedPrimaryKeyBySequence(...) emit NEXTVAL(seq) / LASTVAL(seq), matching the function form documented in the MariaDB knowledge base. LASTVAL(seq) is scoped to the current connection, so it returns the value most recently produced by NEXTVAL(seq) within the same session (or NULL if none).

class DBConnection extends MariaDBConnection<'DBConnection'> {
    customerSeq = this.sequence('customer_seq', 'int')
}

const tCustomer = new class TCustomer extends Table<DBConnection, 'TCustomer'> {
    id = this.autogeneratedPrimaryKeyBySequence('id', 'customer_seq', 'int')
    /* ... */

    constructor() {
        super('company'); // table name in the database
    }
}()

Set compatibilityVersion to your MariaDB version (or leave the default) so the rest of the SQL is emitted accordingly. There is no fallback below 10.3 — the server simply does not recognise the SEQUENCE syntax, so don't expose sequences when targeting older versions.

Collations & case sensitivity

MariaDB's default collation (utf8mb4_uca1400_ai_ci) is case- and accent-insensitive — so equals / contains / like fold both case and accents out of the box, where PostgreSQL, Oracle and SQLite would not. The plain string operations follow that configured collation; the *Insensitive operations force case-insensitivity over it. To force the case-sensitive direction, use a binary collation (utf8mb4_bin) with .collate('<name>') per value or on the column — the SET collation_connection session variable does not retarget a column comparison. REPLACE ignores collation on MariaDB, so replaceAll is byte-wise case-sensitive with nothing to configure; for a case-insensitive replace use .replaceAllInsensitive(...). See the dedicated Collations & case sensitivity page and the MySQL / MariaDB tab.

Validate the case sensitivity, then configure the connection

The case- and accent-insensitive (_ai_ci) default is common but not guaranteed — a database or column can be created with a binary (utf8mb4_bin) or case-sensitive collation, and a deployment you don't control may differ. Confirm it rather than assuming: SELECT @@collation_database for the database default, or SHOW FULL COLUMNS FROM your_table per column (the Collation column).

If it is case-insensitive, tell ts-sql-query so it generates the leanest SQL: set insensitiveCollation = '' on the connection. The *Insensitive operations then trust the column's collation and drop the redundant lower(a) = lower(b) — which also defeats indexes — emitting the bare comparison the already-CI column folds correctly:

class DBConnection extends MariaDBConnection<'DBConnection'> {
    override insensitiveCollation = '' // the database is already case-insensitive — trust it
}

Note too that the plain operations already fold case (and accents) here — .equals(...) behaves like .equalsInsensitive(...). Where a query needs a case-sensitive comparison, force it with .collate('utf8mb4_bin').

UUID strategies

ts-sql-query provides different strategies to handle UUID values in MariaDB. These strategies control how UUID values are represented in JavaScript and stored in the database.

  • 'uuid' (default strategy): UUIDs are treated as strings and stored using the native UUID column type. This requires MariaDB version 10.7 or higher.
  • 'string': UUIDs are treated as strings and stored in character-based columns such as CHAR(36), VARCHAR(36), or TEXT. This option can be used with older MariaDB versions or when avoiding the UUID type.

You can configure the strategy by overriding the uuidStrategy field in your connection class:

import { MariaDBConnection } from "ts-sql-query/connections/MariaDBConnection";

class DBConnection extends MariaDBConnection<'DBConnection'> {
    protected override uuidStrategy = 'string' as const
}

Generating UUIDs

Prefer UUID v7 over UUID v4. MariaDB's native UUID type stores UUIDs with version ≥ 6 in canonical byte order (MDEV-29959, since MariaDB 10.10.6 / 10.11.5 — predates the RFC 9562 publication), so v7 keeps its chronological ordering on the primary-key index. MariaDB 11.7+ also exposes a server-side UUID_v7() function. As a general rule, generating the UUID in the database as a column DEFAULT is preferred over generating it in application code; the latter is the fallback when the value must be known before the INSERT. See the column types page for more context.