Dialect support

Postgres, SQLite and MySQL 8 run the same schemas, changesets, queries and repo calls. This page lists where they differ. It is generated from each adapter's capability table (adapter.capabilities), so it says what the adapters do.

A query or write that needs a feature the database lacks throws QueryError, naming the feature and the database, when it is compiled: nothing is sent, and nothing is emulated behind your back. The conformance suites run on every database; a case that needs a capability the database lacks is skipped, and the skip names the capability.

Queries

Feature	Capability	Postgres	SQLite	MySQL 8
fullJoin	fullJoin	yes	yes	no
LATERAL joins (query trees only; no builder method yet)	lateralJoin	yes	no	yes
distinct(callback) (DISTINCT ON)	distinctOn	yes	no	no
ilike	ilike	yes	as LIKE: case-insensitive for ASCII letters only	as LIKE: the column's collation decides
nullsFirst() / nullsLast()	nullsOrder	yes	yes	no
Array columns and array operators	arrays	yes	no	no
in(list) as one array parameter	arrayParams	yes	expanded to in (?, ?, …)	expanded to in (?, ?, …)
jsonb and its containment operators	jsonb	yes	no; JSON is text, read with the JSON functions	no; json columns, read with the JSON functions
filter(...) on an aggregate (FILTER (WHERE …))	aggregateFilter	yes	yes	no
withCte(..., { materialized })	cteMaterialized	yes	yes	no
A CTE that writes (updateAll / deleteAll in withCte)	writingCtes	yes	no	no
intersectAll / exceptAll	intersectExceptAll	yes	no	yes
limit / offset from an expression (not only a value or parameter)	limitExpressions	yes	yes	no
repo.explain options (analyze, format, …)	explainOptions	yes	no	yes, except analyze with format: "json"

Without the feature:

• fullJoin (MySQL 8): Write a union of a leftJoin and a rightJoin.
• distinct(callback) (DISTINCT ON) (SQLite, MySQL 8): Rank the rows with rowNumber().over(...) in a subquery and keep rank 1.
• nullsFirst() / nullsLast() (MySQL 8): Order by x.isNull() first, then by the column.
• Array columns and array operators (SQLite, MySQL 8): Arrays are stored as JSON text (an array of the elements) and read back as arrays; the array operators and in(arrayExpression) are refused. Use t.json() and JSON functions to query inside them.
• filter(...) on an aggregate (FILTER (WHERE …)) (MySQL 8): Aggregate a caseWhen(...) that is null for the rows to leave out.
• A CTE that writes (updateAll / deleteAll in withCte) (SQLite, MySQL 8): Run the write first, in the same transaction.
• limit / offset from an expression (not only a value or parameter) (MySQL 8): Compute the number first and pass it as a value.

Writes

Feature	Capability	Postgres	SQLite	MySQL 8
Rows returned from a write; .returning(...) on bulk writes	returning	yes	yes	no: insert and update read the row back by its key; bulk writes return counts
onConflict (upserts)	onConflict	yes	yes	yes
onConflict.target	conflictTarget	yes	yes	no: any unique key conflicts (ON DUPLICATE KEY UPDATE); leave target out
onConflict.target: { constraint }	conflictConstraint	yes	no	no
Inserting a row of defaults only	insertDefaultValues	yes	yes	yes
insertAll rows with different fields (DEFAULT in VALUES)	defaultInValues	yes	no	yes
Joins in updateAll / deleteAll	updateJoin	yes	no	yes

Without the feature:

• insertAll rows with different fields (DEFAULT in VALUES) (SQLite): Give every row the same fields, or insert the rows in groups that share them.
• Joins in updateAll / deleteAll (SQLite): Filter with where(... in(subquery)) instead of a join.

Transactions and connections

Feature	Capability	Postgres	SQLite	MySQL 8
Nested transactions (savepoints)	savepoints	yes	yes	yes
transaction(..., { readOnly: true })	readOnlyTransactions	yes	no	yes
deferrable: true (serializable, read-only)	deferrableTransactions	yes	no	no
Serializable transactions fail with a serialization error	serializationFailures	yes	no	no: a conflict surfaces as a deadlock (code: "deadlock"), also retryable
DDL inside a transaction (migrations roll back as a whole)	ddlTransactions	yes	yes	no: each DDL statement commits; a migration that fails midway is left half-applied
More than one connection	multipleConnections	yes	no: one handle, used by one caller at a time; transactions run one after the other	yes
Cancelling a running statement (timeoutMs, signal)	cancel	yes	no: a statement runs to its end; timeoutMs and signal bound the wait for the handle	yes
repo.stream	stream	yes	yes	yes

Values and errors

Feature	Capability	Postgres	SQLite	MySQL 8
NaN and infinities in float columns	nonFiniteFloats	yes	no	no
t.decimal() reads back as written and compares by value (1.0 = 1.00)	exactDecimals	yes	no: stored as the text given, compared as text	no: the column's scale is fixed (1.0 reads back as 1.00)
Foreign-key violations name the constraint and the table	foreignKeyNames	yes	no: ConstraintError has type: "foreignKey" and no name	yes
Identifiers longer than 64 bytes	longIdentifiers	yes, shortened to 63 bytes	yes	no: refused; name a long constraint yourself with { name }

Without the feature:

• NaN and infinities in float columns (SQLite, MySQL 8): Writing one throws CastError; store it as null or as text.

Locks, isolation levels and limits

	Postgres	SQLite	MySQL 8
Row locks (lock(...))	FOR UPDATE, FOR UPDATE SKIP LOCKED, FOR UPDATE NOWAIT, FOR NO KEY UPDATE, FOR NO KEY UPDATE SKIP LOCKED, FOR NO KEY UPDATE NOWAIT, FOR SHARE, FOR SHARE SKIP LOCKED, FOR SHARE NOWAIT, FOR KEY SHARE, FOR KEY SHARE SKIP LOCKED, FOR KEY SHARE NOWAIT	accepted and not written (one writer at a time)	FOR UPDATE, FOR UPDATE SKIP LOCKED, FOR UPDATE NOWAIT, FOR SHARE, FOR SHARE SKIP LOCKED, FOR SHARE NOWAIT
Isolation levels	read committed, repeatable read, serializable	none: one writer at a time, and asking for a level is refused	read committed, repeatable read, serializable
Parameters per statement	65,535	32,766	65,535

insertAll splits its rows into statements under the parameter limit. A statement that needs more parameters than the limit (a very long in list, say) is refused with QueryError before it is sent.

Migrations

From the migration table of each dialect (features). A migration that uses a feature the database lacks throws MigrationError before anything runs.

Feature	Feature flag	Postgres	SQLite	MySQL 8
Tables in a database schema (createSchema, schema option)	schemas	yes	no	yes
createExtension	extensions	yes	no	no
concurrently index builds and drops	concurrentIndexes	yes	no	no
Partial indexes (where)	partialIndexes	yes	yes	no
Covering indexes (include)	includeColumns	yes	no	no
Exclusion constraints	exclusionConstraints	yes	no	no
cascade on drops	dropCascade	yes	no	no
DDL inside a transaction	ddlTransactions (capability)	yes	yes	no
The runner's lock	lock	a lock of the session	a write transaction for the whole run	a lock of the session

Constraint names

SQLite reports a unique violation by its columns, not by an index name. The adapter turns that into the default constraint name (users_email_key, or users_pkey for the primary key), so uniqueConstraint("email") matches; a custom name cannot be matched there, except for a unique index on an expression, which SQLite reports by its name.

MySQL names the primary key PRIMARY; the adapter reports it as <table>_pkey. A unique index created without a name is named after its first column (email), not users_email_key: the migrations of this library always name it, and for one created otherwise, pass its name to uniqueConstraint(…, { name }).