What each dialect supports, and what it refuses. Every refusal raises DialectError while the statement builds, never in the database.

Guide: SQL Dialects.

Dialects

The Dialects enum lives in sustained.dialects.

Member	Engine
Dialects.DEFAULT	ANSI SQL, and SQLite in practice
Dialects.POSTGRES	PostgreSQL
Dialects.MSSQL	Microsoft SQL Server
Dialects.MYSQL	MySQL, MariaDB
Dialects.PRESTO	Presto, Trino
Dialects.ATHENA	AWS Athena
Dialects.DUCKDB	DuckDB

Dialects.get_compiler(dialect) returns the compiler instance. Set a dialect with Model.set_dialect(), or pass one to Migrator(dialect=...).

Drivers and placeholders

The connection’s parameter style has to match the dialect’s placeholder, or execution fails at the driver.

Dialect	Driver	Placeholder	Identifier quoting
DEFAULT	sqlite3	?	none
POSTGRES	psycopg, psycopg2	%s	"name"
MSSQL	pyodbc	?	[name]
MYSQL	PyMySQL, mysqlclient	%s	`name`
PRESTO	trino	?	"name"
ATHENA	pyathena	?	"name"
DUCKDB	duckdb	?	"name"

Query features

A blank cell means the feature works.

Feature	Default	Postgres	MySQL	DuckDB	MSSQL	Presto	Athena
LIMIT / OFFSET			a bare OFFSET takes a maximum LIMIT		needs orderBy(), compiles to OFFSET ... FETCH	OFFSET before LIMIT	OFFSET before LIMIT
top()	raises	raises	raises	raises		raises	raises
distinctOn()	raises		raises		raises	raises	raises
qualify()	raises	raises	raises		raises	raises	raises
for_update()	raises			raises	raises	raises	raises
explain()					raises
ILIKE	emulated	native	emulated	native	emulated	emulated	emulated
WITH RECURSIVE					plain WITH
Booleans	TRUE / FALSE				1 / 0

Emulated ILIKE compiles to LOWER(col) LIKE LOWER(pattern), so ILIKE never raises.

Write features

Feature	Default	Postgres	MySQL	DuckDB	MSSQL	Presto	Athena
Upserts	ON CONFLICT	ON CONFLICT	ON DUPLICATE KEY UPDATE	ON CONFLICT	MERGE	raises	MERGE, Iceberg only
returning()			raises		raises	raises	raises
create_table_as()					raises		raises when temporary=True

The MSSQL messages name the alternative: OUTPUT for RETURNING, and SELECT ... INTO for CREATE TABLE AS. The MySQL message names a second query, or LAST_INSERT_ID(). MariaDB does have RETURNING, but the shared MySQL dialect refuses it, so one builder cannot emit SQL the other server rejects.

ON DUPLICATE KEY UPDATE fires on any unique key the row collides with, not only on the columns named in onConflict(). The MySQL statement therefore covers more collisions than the same upsert does elsewhere.

Schema features

Feature	Default	Postgres	MySQL	DuckDB	MSSQL	Presto	Athena
autoincrement	rowid alias	GENERATED BY DEFAULT AS IDENTITY	AUTO_INCREMENT	raises	IDENTITY(1,1)	raises	raises
Constraints			no unique key or DEFAULT on a whole Text() or Json() column				none, raises
tableConstraints	inline at CREATE, then rebuilds	ADD CONSTRAINT	ADD CONSTRAINT	ADD CONSTRAINT	ADD CONSTRAINT	raises	raises
Enum columns	VARCHAR + CHECK	named type	inline ENUM(...)	named type	NVARCHAR + CHECK	raises	raises
Enum value append	re-created CHECK	ALTER TYPE ... ADD VALUE	MODIFY COLUMN	raises	re-created CHECK	raises	raises
references	beside the column	beside the column	table constraint, or ADD CONSTRAINT	beside the column	beside the column	beside the column	raises
ALTER COLUMN type	no, rebuilds		MODIFY COLUMN			no	CHANGE COLUMN, widening only
ALTER COLUMN nullability	raises		MODIFY COLUMN			raises	raises
USING cast hints	raises		ignored	ignored	ignored	raises	raises
Rename column			MySQL 8.0, MariaDB 10.5		sp_rename		raises
Rename table					sp_rename		raises
Indexes							raises
Table options	raises	raises	raises	raises	raises	raises

MySQL and SQL Server restate a whole column to change it, and give a text column the table’s or the database’s collation unless the statement names one. Their reads take COLLATION_NAME, and a generated type or nullability change on a text column restates the collation the catalog reports. MySQL also restates the ON UPDATE clause it reports in EXTRA. Presto and Trino can neither alter a column nor rebuild a table, so migration generation raises DialectError for a column change there. The default dialect cannot alter a column, so migration generation rebuilds the table instead: it creates the new table, copies the rows over, and replaces the old table. SQLite also refuses some new columns in ADD COLUMN: a UNIQUE column, a NOT NULL column with no default, a column whose default is not a constant, such as CURRENT_TIMESTAMP, and a column with both references and a default. Such a column comes in through a rebuild as well. A rebuild does not reverse, so a migration that contains one has no down step. Dropping the old table needs foreign key enforcement off while another table points at it, and SQLite ignores PRAGMA foreign_keys inside a transaction, so such a migration is generated with transactional=False. The migrator turns the driver’s own transaction control off for that run and puts it back at the end. A failure partway through the run leaves the statements that already ran, the copy table, and enforcement off on that connection. The rebuild keeps the columns and indexes the models do not declare, unless you pass allow_drops=True. A model has no way to declare a collation, a CHECK without a CONSTRAINT name, a UNIQUE constraint over more than one column, or a trigger, so the rebuild reads each one off the old table and writes it back whatever allow_drops says. A carried check or UNIQUE constraint that names a column the rebuild drops is dropped with that column. SQLite checks every view and trigger when it renames a table, and one that names the rebuilt table fails that check while the old table is gone. When the schema has a view or a trigger, the rebuild therefore renames its copy into place between PRAGMA legacy_alter_table = ON and OFF, and a failure of that rename leaves the setting on for the connection. Introspection cannot read an index on an expression, so a rebuild loses that index and you have to create it again by hand.

Migration behaviour

Behaviour	Default	Postgres	MySQL	DuckDB	MSSQL	Presto	Athena
Transactions			rows only, DDL commits as it runs				none
rehearse()			refuses		refuses	refuses	refuses
Advisory lock	none	pg_advisory_lock	GET_LOCK	none	sp_getapplock	none	none

Only a dialect whose schema changes roll back may rehearse against the real database: the default dialect, Postgres, and DuckDB. On every other dialect, rehearse with scratch=True against a throwaway database.

Athena has no transactions, and MySQL has transactions for rows but not for schema changes. Both dialects run each migration without a surrounding transaction, so a multi-step migration that fails leaves the steps before the failure applied. Both write a failed-attempt row, which makes the interrupted run visible and blocks the next run until repair() clears the row.

SQLite and DuckDB have no advisory lock, because they serialize writers themselves. Athena has no lock to take, so run one migrator at a time there. MySQL’s GET_LOCK is scoped to the session, is reentrant, and releases on disconnect, the same way the Postgres and MSSQL locks do.

Introspection

The default dialect reads SQLite’s PRAGMA tables. Every other dialect reads information_schema, including check_constraints for CHECK expressions on MySQL, MariaDB, MSSQL, and DuckDB. Presto and Athena skip that read, because their tables have no CHECK constraints. When a view is unavailable, introspection falls back to the column data alone rather than failing.

A snapshot keys every table, column, index, and constraint by its name in lower case, so a model’s spelling matches the catalog’s on every engine. Each object also keeps the name as the catalog spells it, and a generated statement that drops or restores an object the models do not declare writes that spelling. Postgres takes a quoted name as written, and MySQL on Linux does the same for a table name, so a drop of "Legacy" written as "legacy" would name a table that is not there.

Postgres has a dedicated read. It takes udt_name, varchar lengths, and numeric precision from information_schema.columns, every index from pg_index, foreign key names, columns, targets, and referential actions from pg_constraint, CHECK expressions from pg_constraint as well, keyed on the table each check belongs to, and enum values from pg_enum in sort order. Expression indexes are skipped, as on SQLite.

A check expression is compared after the engine’s own rewriting comes off: identifier quoting, the spacing around operators, and parentheses around a single word. A function call keeps its parentheses, so LENGTH(name) > 5 stays a call. MySQL and MariaDB report `price` > 0 for price > 0, and MSSQL reports ([price]>(0)), and all three compare equal to the expression the model declares. String literals keep their spelling. Engines can rewrite an expression in ways this comparison does not undo, so a difference that remains appears as a note and never as a drop.

SQLite reports constraints only inside the stored CREATE TABLE text, so introspection recovers named foreign keys, and the CHECK constraints Sustained itself generates (names starting ck_), from sqlite_master. Any other CHECK stays a note. MySQL recovers an enum column’s values from its inline enum('a','b') type spelling.

MySQL introspection differs from the rest. It reads column_type rather than data_type, so a column arrives as varchar(120) and compares against the compiler’s own spelling. It scopes every query to DATABASE(), because a MySQL schema is a database. The column read, the index read from information_schema.statistics, and the MariaDB json_valid recovery read all use the same scope, so a snapshot never takes its tables from one schema and its indexes from another.

Schema scope

A snapshot keys its tables on the bare table name, so a read that covered two schemas would merge app.users into public.users and the diff would never converge. Every read is scoped instead.

Postgres reads current_schema(), MSSQL reads SCHEMA_NAME(), DuckDB reads current_schema(), MySQL reads DATABASE(), and Athena reads current_schema. A model that sets tableSchema widens the read to that schema as well, so a model outside the connection’s own schema still diffs. Two models that declare the same table name in different schemas are refused, because the read cannot tell the two tables apart. Diff them in separate calls. The database can also contain such a pair when one of the two tables is undeclared. For that case, the information_schema read fetches the schema name with every column and raises ValueError when one table name arrives from two schemas, rather than merging the columns of both into one table.

Presto and Trino have no expression for the schema the connection is on. Their read covers every schema but the system ones, and a declared tableSchema leaves it that wide, because narrowing the read to the declared schema would drop the tables in the connection’s own schema.

A table the read finds in a declared schema keeps that schema in the snapshot, as IntrospectedTable.schema. A table in the connection’s own schema keeps None. Every statement a generated migration writes for a table in a declared schema names the schema in front of the table: the drops of a column, an index, a key, a check, or a whole undeclared table, and a table or column rename. The new name in a table rename stays bare on every engine but MySQL, because the renamed table keeps its schema there, while MySQL moves a table named without a schema to the connection’s database. Postgres, DuckDB, and SQLite name no table in DROP INDEX, so the index takes the table’s schema in front of its own name, as in DROP INDEX "app"."ix_note". Without it, Postgres looks for the index in the schemas on the search_path.

A foreign key reads its target table by the bare name, and Postgres, MySQL, MariaDB, and SQL Server add the target’s schema as IntrospectedForeignKey.target_schema when it is not the schema the connection is on. A declared target such as app.parents.id matches a key whose target is parents in app, and also one whose target the read reports with no schema, because a schema the read leaves out is the connection’s own. A bare declared target such as parents.id means the connection’s schema, so it differs from a key into app. A key that a generated migration drops is restored on the way down with its target’s schema in front. DuckDB refuses a foreign key across schemas, so its read reports none.

The declared schemas make their own IN list, and the read compares the current-schema expression beside it with OR. Postgres returns NULL from current_schema() when the first search_path entry names a schema that does not exist, and in that case the OR branch matches no rows while the declared schemas still match.

The constraint join matches schema names as well as constraint names, because a constraint name is only unique within its schema. An engine whose key_column_usage has no table_schema column falls back to the plain join, but only when no model declares a tableSchema. The plain join cannot keep two schemas apart, so when a declared schema widens the read, the constraints stay unread instead.

Postgres refuses every later statement in a transaction once one has failed. A read tries a catalog and falls back when it is not there, so on Postgres each query runs inside the savepoint sustained_read and a failure rolls back to it. The read then releases the savepoint, whether the query worked or failed, because ROLLBACK TO SAVEPOINT leaves the savepoint in place and one per failed query would pile up. A driver that refuses RELEASE SAVEPOINT does not stop the read: the rows are already read. A connection with no transaction open takes no savepoint, and the read stops asking for one for its remaining queries.

Athena scopes every introspection query to the schema the connection was opened on. Its catalog spans every Glue database in the account, so an unscoped read would be slow and would fail outright when any other database contains a table with broken metadata. Models on an Athena connection must live in that schema for a diff to see them.

MariaDB stores a Json() column as longtext with a json_valid CHECK constraint, and reports the storage type. Introspection looks those constraints up and restores the JSON type, so the column does not report as drift that no migration can close. MariaDB before 10.2.22 has no check_constraints view, so on those versions the column does report as drift.