Dialect differences

Most of a definition compiles the same everywhere. This page covers what does not, and what to expect before you rely on it.

Support matrix

Operation	SQLite	MySQL	PostgreSQL	SQL Server
Create, drop, rename a table	yes	yes	yes	yes
Add, drop, rename a column	yes	yes	yes	yes
Add, drop an index	yes	yes	yes	yes
Add a unique constraint later	as an index	yes	yes	yes
Add a primary or foreign key later	refused	yes	yes	yes
Change a column	refused	yes	yes	yes
Change a column and its default at once	refused	yes	yes	refused
Add a NOT NULL column with no default	refused	yes	yes	yes
unsigned()	ignored	applied	ignored	ignored

A refusal is an UnsupportedDriverException carrying the driver, the operation and the subject — never a silently different statement.

SQLite

The most constrained dialect, and the one most likely to surprise if you develop against it and deploy on something else.

An auto-incrementing key is declared on the column. AUTOINCREMENT is only legal on INTEGER PRIMARY KEY, so a single-column auto-incrementing key becomes "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT and no table constraint is emitted. A composite or non-incrementing key is a normal PRIMARY KEY clause.

Uniqueness is an index. ALTER TABLE cannot touch a constraint, so adding a unique constraint to an existing table compiles CREATE UNIQUE INDEX — which is how SQLite stores one anyway. dropConstraint() compiles DROP INDEX to match.

What it refuses, because SQLite has no statement for it:

• changing a column
• adding a primary or foreign key to a table that already exists
• adding a NOT NULL column without a default
• auto-incrementing a column that is not the primary key

SQLite documents a table rebuild for the first two. This package does not do it, because a rebuild silently loses the triggers and views attached to the table.

caution

dropConstraint() on SQLite compiles DROP INDEX, so pointing it at a foreign key's name fails at run time with "no such index". A name alone does not say which kind of constraint it is.

MySQL

Indexes are declared inside CREATE TABLE. MySQL has no CREATE INDEX IF NOT EXISTS, so keeping the index in the create statement is what makes createIfNotExists() safe to run twice.

Backslashes are escaped in defaults. MySQL treats \ as an escape character, so doubling quotes is not enough on its own.

AUTO_INCREMENT sits on the column with the primary key declared after it, and unsigned() is the one place that modifier does anything.

PostgreSQL

Auto-increment is an identity column, GENERATED BY DEFAULT AS IDENTITY, rather than the older SERIAL.

Boolean defaults are TRUE and FALSE. DEFAULT 1 on a BOOLEAN is a type error in PostgreSQL.

A change is several actions in one statement, because there is no MODIFY COLUMN:

ALTER TABLE "users" ALTER COLUMN "email" TYPE VARCHAR(320),
  ALTER COLUMN "email" SET NOT NULL, ALTER COLUMN "email" DROP DEFAULT

caution

That last action is why a change() resets anything you did not restate. A column with a default that you change without calling default() again comes out with no default.

DROP INDEX takes no ON clause; an index is a schema object rather than something a table owns.

SQL Server

CREATE TABLE IF NOT EXISTS does not exist, so a conditional create is guarded instead, with the index inside it:

IF OBJECT_ID(N'[users]', N'U') IS NULL CREATE TABLE [users] (...)

A column is added without the COLUMN keyword. ALTER TABLE … ADD is correct and ADD COLUMN is a syntax error — though DROP COLUMN does take it.

Renames go through sp_rename, for both tables and columns, because there is no ALTER TABLE … RENAME.

String literals are prefixed with N so a unicode default is not mangled on its way into an NVARCHAR column.

A column and its default cannot change together. SQL Server keeps a default in its own auto-named constraint, so altering both would mean discovering that generated name first. It refuses rather than dropping the default silently.