MySQL & MariaDB

MySQL and MariaDB have separate entry points, because the SQL each needs differs. Pick the one that matches your server.

Server	Entry point	Driver
MySQL 8.0.19+	uql-orm/mysql	mysql2
MariaDB 10.5+	uql-orm/mariadb	mariadb
Either, under Bun	uql-orm/bun-sql	built in

Install and connect

npm install uql-orm mysql2   # or: mariadb

import { MySql2QuerierPool } from 'uql-orm/mysql';
// MariaDB: import { MariadbQuerierPool } from 'uql-orm/mariadb';

export const pool = new MySql2QuerierPool({
  host: 'localhost',
  user: 'app',
  password: process.env.DB_PASSWORD,
  database: 'app',
  connectionLimit: 10,
});

Both pools take their driver’s own config and stream natively. UQL sets the session time zone to UTC, so dates read back as the instant they hold.

Ids after a multi-row insert

MariaDB has INSERT ... RETURNING, so insertMany returns exact ids. MySQL reports only the first id, and UQL infers the rest from auto_increment_increment.

Inferred ids on MySQL

The inference holds under innodb_autoinc_lock_mode 0 or 1. MySQL 8 defaults to 2, where a concurrent insert into the same table can leave gaps in your ids. On a hot table, set lock mode 1 or insert row by row.

Schema and writes

All four isolation levels work; the default is repeatable read. Upserts compile to INSERT ... ON DUPLICATE KEY UPDATE (or INSERT IGNORE when there is nothing to update). The server matches on any unique index, not only the conflict keys you passed, and an upsert’s $where guards each column the update sets, so it may read only one of them.

A query binds at most 65,535 values, so UQL splits a large insertMany into chunks. A populated to-many is aggregated in the parent’s statement, which raises group_concat_max_len for itself so long relations are not cut off.

JSON and arrays

Arrays are stored as JSON and queried with the JSON operators. Paths compile to -> / ->> on MySQL and JSON_VALUE() / JSON_EXTRACT() on MariaDB.

A JSON number compares as a DOUBLE. On MySQL a JSON path index can serve that; MariaDB has no expression indexes. Sorting by a JSON path orders numbers by value on both, MariaDB breaking ties by text.

$elemMatch on one value, or one of several, compiles to JSON_CONTAINS() (or JSON_OVERLAPS() on MySQL), which an index over the array’s elements, type: [String], serves. Anything more uses JSON_TABLE.

Full-text search

$text compiles to MATCH(...) AGAINST(...), which needs a FULLTEXT index over exactly the searched columns, in order:

import { f, Entity, Field } from 'uql-orm';

@Entity({
  indexes: (post, index) => [
    index([post.title, post.body], { type: 'fulltext' }),
  ],
})
export class Post {
  @Field(f.number().autoIncrement()) id?: number;
  @Field(f.string().nullable()) title?: string | null;
  @Field(f.string().nullable()) body?: string | null;
}

Migrations create it, running OPTIMIZE TABLE on a table that has rows. A weighted index also gets one FULLTEXT index per heavier column, since MATCH scores a column only through its own index.

Vector search

MariaDB 11.7+ has native VECTOR(n) columns and VECTOR INDEX, so semantic search works with no extension. MySQL has no distance function outside HeatWave, so UQL stores vectors there as JSON and cannot search them.