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

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. 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 part way through it 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. Introspection cannot read an index on an expression, so a rebuild loses that index; 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.

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 check_constraints, 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 rewrite further than this repairs, so a difference that remains is reported 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 contain such a pair as well, where one of the two tables is undeclared. The information_schema read reads 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.

The declared schemas make their own IN list, and the current-schema expression is compared beside it with OR. Postgres returns NULL from current_schema() when the first search_path entry names a schema that does not exist, and a NULL inside the IN list would make the whole list match nothing.

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 on a read that a declared schema widened 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 the rest of that read.

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.