> Every UQL docs page, as Markdown: https://uql-orm.dev/llms.txt
> The same docs over MCP: https://uql-orm.dev/mcp
> Before writing UQL code, read the skill: https://uql-orm.dev/.well-known/agent-skills/uql-orm/SKILL.md

# MySQL & MariaDB

> Run UQL on MySQL with mysql2 or on MariaDB with its own driver, and the differences that matter.

Source: https://uql-orm.dev/mysql

MySQL and MariaDB share a dialect base but are two entry points with two drivers. Pick the one that matches the server you run: the generated SQL differs, and the MySQL dialect emits JSON operators MariaDB does not have.

| Server | Entry point | Driver |
| - | - | - |
| MySQL 8.0.19+ | `uql-orm/mysql` | `mysql2` |
| MariaDB 10.5+ | `uql-orm/maria` | `mariadb` |
| Either, under Bun | [`uql-orm/bunSql`](https://uql-orm.dev/bun-sql.md) | built in |

## Connect

```sh
npm install uql-orm mysql2   # or: mariadb
```

```ts
import { MySql2QuerierPool } from 'uql-orm/mysql';
// MariaDB: import { MariadbQuerierPool } from 'uql-orm/maria';

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

Both take their driver’s own pool config verbatim. Sizing, lifetime and shutdown are the same on every driver: see [Pool](https://uql-orm.dev/pool.md).

## Generated ids after a multi-row insert

The one behavioral difference worth knowing before choosing. MariaDB 10.5+ has `INSERT ... RETURNING`, so ids come back exact. MySQL has none: the driver reports only the first id and UQL infers the rest by incrementing it.

```ts
const ids = await pool.insertMany(User, [
  { email: 'a@example.com' },
  { email: 'b@example.com' },
]);
```

That inference holds under `innodb_autoinc_lock_mode` 0 or 1. MySQL 8 defaults to mode 2 (`interleaved`), where a concurrent insert into the same table can interleave with your statement’s allocation and leave the block non-contiguous. On a hot table, set lock mode 1 or insert row by row.

## What the dialect does

- **Tables** are `ENGINE=InnoDB DEFAULT CHARSET=utf8mb4`; the surrogate key is `BIGINT AUTO_INCREMENT`, spelled from the type the `@Id` declares so a foreign key column can match it.
- **Transactions** use `START TRANSACTION` with the isolation level set on the session just before. All four levels work; the default is `repeatable read`.
- **Upserts** compile to `INSERT ... ON DUPLICATE KEY UPDATE`, or `INSERT IGNORE` when every non-conflict column is a conflict key. Neither takes a conflict target: the server picks the unique index.
- **Arrays** are not native: a `string[]` field is stored as JSON and queried with the [JSON operators](https://uql-orm.dev/querying/json.md).
- **JSON paths** differ: MySQL uses `->` / `->>`, MariaDB uses `JSON_VALUE()` / `JSON_EXTRACT()`, which every version it supports has. Emitting the right one is the main reason the entry points are separate.
- **A JSON number** compares as a `DOUBLE` on both sides, fractions included. On MySQL that is also what lets a [`jsonPath` index](https://uql-orm.dev/entities/indexes.md#json-indexes) serve it; MariaDB has no expression indexes.
- **`$elemMatch`** explodes the array with `JSON_TABLE`, one `JSON` column per element whose fields are read as paths. One value, or one of several, compiles to `JSON_CONTAINS()` or, on MySQL, `JSON_OVERLAPS()` instead, which a `jsonArray` index serves. On MySQL the exploded form carries a `NO_SEMIJOIN()` hint: without it the planner can answer every row with one row’s elements.
- **Sorting by a JSON path** orders a number by its value: MySQL sorts the JSON value, MariaDB the number and then the text.
- **Bound parameters** cap at 65,535, so large `insertMany` payloads are chunked for you.
- **A populated to-many** is aggregated in the parent’s statement: an ordered `GROUP_CONCAT` on MySQL (8.0.14+), `JSON_ARRAYAGG` on MariaDB. The statement lifts `group_concat_max_len` for itself.

## Search

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

```ts
import { Index } from 'uql-orm';

@Index((post) => [post.title, post.body], { type: 'fulltext' })
```

[Migrations](https://uql-orm.dev/migrations.md) create it, and follow one added to a table with rows by `OPTIMIZE TABLE`, without which InnoDB scores the index 0 or fails the search. Without it the server answers “Can’t find FULLTEXT index matching the column list”.

A weighted index (`{ column: post.title, weight: 3 }`) also gets a `FULLTEXT` index over each column heavier than the lightest, since `MATCH` scores a column through an index of its own. See [column weights](https://uql-orm.dev/querying/full-text.md#column-weights).

MariaDB 11.7+ has a native `VECTOR(n)` type and a `VECTOR INDEX` of its own, so [semantic search](https://uql-orm.dev/querying/semantic-search.md) works with no extension. A vector reads back from its packed bytes, every float32 digit kept. MySQL has the `VECTOR` column but no distance function outside HeatWave, so it has nothing to search on, and UQL stores a vector field there as JSON.

Both drivers stream natively: `mysql2` through its result-set `.stream()`, `mariadb` through `queryStream`.