MariaDB Dialect

MariaDB Dialect The MariaDB dialect provides first-class support for MariaDB servers. It shares most of its SQL surface with the MySQL dialect (backtick identifiers, the mariadb npm driver's pooled connections, ON DUPLICATE KEY UPDATE upserts) but diverges where MariaDB has grown its own features: native RETURNING clauses, sequences, virtual columns, system-versioned tables, and the CONNECT storage engine. Source: mariadb. Overview Dialect name: mariadb Driver: mariadb (the official MariaDB Connector/Node.js). Install it alongside the ORM. Identifier quoting: backticks ( column ), same as MySQL. Default storage engine: InnoDB. createTable emits ENGINE=InnoDB unless you override it via the engine table option. Status: Stable. MariaDB started as a MySQL fork, so anything you know from the MySQL dialect generally applies. The sections below focus on where MariaDB is either a strict superset of MySQL or behaves differently. Connection Pass dialect: 'mariadb' to the Prorm constructor along with standard connection fields. The dialect defaults host to localhost and port to 3306, and creates a connection pool via mariadb.createPool under the hood. Connection options The dialect accepts these options (see MariaDBDialectOptions): Option | Default | Notes | ------------------- | ---------- | ------------------------------------------------------------ | host | localhost| Server hostname. | port | 3306 | Server port. | database | — | Default schema. | username | — | Mapped to the driver's user field. | password | — | Password. | charset | utf8mb4 | Connection charset. | timezone | Z | Pool normalizes to UTC; date binding uses UTC accordingly. | ssl | — | Passed straight to the driver. | connectionLimit | 10 | Max pooled connections. | waitForConnections| true | Queue requests when the pool is saturated. | queueLimit | 0 | 0 means an unbounded queue. | insertIdAsNumber | true | Return insertId as a JS number. | decimalAsFloat | false | Keep DECIMAL precise instead of coercing to float. | debug / trace | false | Driver-level diagnostics. | Because the pool is configured with supportBigNumbers: true and timezone: 'Z', BIGINT values are preserved and Date values are stored and read as UTC. If you supply Date objects, the dialect formats them with UTC getters so what you insert matches what the server stores. How MariaDB differs from MySQL Upsert: ON DUPLICATE KEY UPDATE with VALUES(col) Like MySQL, MariaDB implements upserts with INSERT ... ON DUPLICATE KEY UPDATE. buildInsertQuery (with upsert: true) and buildUpsertQuery both emit the VALUES(col) form to reference the would-be-inserted value in the update clause: Note: VALUES(col) in the ON DUPLICATE KEY UPDATE clause is the classic MySQL and MariaDB syntax. MariaDB has never adopted the MySQL 8.0.19 AS new row-alias replacement, so VALUES(col) remains the portable, correct form for MariaDB — which is exactly what this dialect generates. At the model layer, use upsert. It returns [instance, created]: If you omit updateOnDuplicate, every supplied column is written into the ON DUPLICATE KEY UPDATE clause. RETURNING support This is the headline difference from MySQL. MySQL has no RETURNING clause; MariaDB does. The dialect emits RETURNING for INSERT, UPDATE, and DELETE when you pass a returning option: returning: true renders RETURNING on INSERT/UPDATE/DELETE (upserts expand it to the explicit column list). returning: ['id', 'name'] renders RETURNING \id\, \name\. Version caveats (be honest about the server you run against): INSERT ... RETURNING requires MariaDB 10.5+. DELETE ... RETURNING has been available since MariaDB 10.0.5. UPDATE ... RETURNING is not supported by MariaDB at all. The dialect's buildUpdateQuery will still append the clause if you pass returning, but the server rejects it. Treat returning on updates as unsupported and read the row back with a follow-up SELECT instead. The dialect always emits the clause when you ask for it; it does not gate on the server version. Only rely on it for INSERT (10.5+) and DELETE (10.0.5+). Sequences MariaDB 10.0+ has real CREATE SEQUENCE objects (MySQL does not). The dialect's createSequence / dropSequence emit standard sequence DDL, and supports CREATE OR REPLACE SEQUENCE, IF NOT EXISTS, and TEMPORARY sequences. OFFSET without LIMIT Like MySQL, MariaDB cannot use OFFSET without a LIMIT. When only an offset is given, the dialect supplies the documented max unsigned BIGINT as the limit so all remaining rows are returned: Partitions MariaDB supports RANGE, LIST, HASH, KEY, and LINEAR partitioning. Partitions are created inline in CREATE TABLE or added later with ALTER TABLE ... ADD PARTITION. Note that MariaDB has no DETACH PARTITION operation — detachPartition throws; use dropPartition (which emits ALTER TABLE ... DROP PARTITION) instead. Data types getDataTypeSql maps the ORM's DataTypes to MariaDB column types: DataType | MariaDB SQL | -------------------- | ---------------------------------------------- | STRING(n) | VARCHAR(n) (default VARCHAR(255)) | CHAR(n) | CHAR(n) | TEXT | TEXT / TINYTEXT / MEDIUMTEXT / LONGTEXT by length | INTEGER | TINYINT / SMALLINT / MEDIUMINT / INT / BIGINT by length, UNSIGNED when set | BIGINT | BIGINT (UNSIGNED when set) | FLOAT / DOUBLE | FLOAT / DOUBLE (optionally (len,decimals))| DECIMAL | DECIMAL(precision, scale) | BOOLEAN | BOOLEAN (native) | DATE | DATETIME (or DATETIME(precision)) | DATEONLY | DATE | TIME | TIME (or TIME(precision)) | BLOB | BLOB / TINYBLOB / MEDIUMBLOB / LONGBLOB | ENUM(...) | ENUM('a','b',...) | JSON / JSONB | JSON (MariaDB 10.2+; stored as LONGTEXT with a JSON check under the hood) | UUID | CHAR(36) | GEOMETRY | GEOMETRY | JSON MariaDB exposes JSON functions rather than a distinct binary JSON storage type. The dialect builds them for you: buildJsonExtract → JSONUNQUOTE(JSONEXTRACT(col, '$.path')) for text, or JSONEXTRACT(...) when asText is false. buildJsonContains → JSONCONTAINS(col, ?, '$.path'). buildJsonHasKey → JSONCONTAINSPATH(col, 'one'|'all', '$.key'). buildJsonSet / buildJsonReplace / buildJsonInsert / buildJsonRemove → the matching JSONSET / JSONREPLACE / JSONINSERT / JSONREMOVE functions (MariaDB 10.2.3+). buildJsonMergePatch → JSONMERGEPATCH (RFC 7396) and buildJsonMergePreserve → JSONMERGEPRESERVE (MariaDB 10.2.4+; the dialect always emits the explicit name rather than the deprecated JSONMERGE alias). MariaDB-specific table & column options createTable accepts MariaDBTableOptions and columns accept MariaDBColumnDefinition. Highlights: engine — storage engine (InnoDB, Aria, MyISAM, MEMORY, CONNECT, ...). Constants live in MariaDBStorageEngines. autoIncrement — table-level AUTOINCREMENT= seed; per-column autoIncrementInit seeds an individual AUTOINCREMENT column. rowFormat, keyBlockSize, tableComment, check. systemVersioning: true — appends WITH SYSTEM VERSIONING for MariaDB 10.3+ temporal (system-versioned) tables. connectType / connectOptions — switch the table to the CONNECT engine and set TYPE= plus OPTIONS(...) for external data sources (MYSQL, ODBC, CSV, JSON, ...); see MariaDBConnectTypes. Virtual columns — set generated: 'VIRTUAL' | 'STORED' with a generationExpression to emit ... AS (expr) VIRTUAL|STORED (MariaDB 5.2+). invisible: true — MariaDB 10.3+ invisible columns. CRUD examples Standard model operations work the same as any other dialect: Transactions Raw queries prorm.query runs raw SQL through the same pool. Statements starting with SELECT, SHOW, DESCRIBE, EXPLAIN, or WITH are treated as row-returning: The query method also supports retry options for transient failures (Deadlock, Lock wait timeout, Too many connections, connection refusals, etc.) via query(sql, { retry: { max: 3, timeout: 1000 } }). Introspection helpers getDatabaseVersion() → SELECT VERSION(). getMariaDBVersionInfo() → parsed { version, versionNumber, storageEngine }, where versionNumber encodes major10000 + minor100 + patch (e.g. 10.11.4 → 110004) so you can gate feature use on the server version yourself. showTables(), showViews(), describeTable(), showIndexes(), showConstraints(), getCreateTable(), getTableStatus(). resetAutoIncrement(table, value?) → ALTER TABLE ... AUTOINCREMENT[= value]. Limitations & honest caveats RETURNING is INSERT/DELETE only. INSERT ... RETURNING needs MariaDB 10.5+, DELETE ... RETURNING needs 10.0.5+, and MariaDB has no UPDATE ... RETURNING at all. The dialect emits the clause unconditionally whenever you pass returning, so unsupported combinations fail at the server, not in the ORM. No DETACH PARTITION. detachPartition throws by design; use dropPartition. dropTrigger does not support CASCADE — passing cascade: true throws, because MariaDB's DROP TRIGGER has no such option. OFFSET requires a LIMIT; the dialect injects a max-BIGINT limit for offset-only queries. JSON is function-based, not a separate binary storage type. Everything goes through JSON functions; there is no JSONB-style operator set as in PostgreSQL — JSONB maps to JSON. CONNECT / external engines depend on the target server actually having the CONNECT plugin installed; the dialect only generates the DDL. See also MySQL dialect — MariaDB shares most of its behavior. mariadb — the authoritative implementation. Related reading Running mariadb in Docker — get one going locally All dialects — what each engine supports Data types — how each type maps to this engine Querying — how a FindOptions becomes SQL