Types & Engine Support

Types

Each column uses a portable type, which PolySchema maps to the closest native type on each engine:

Type	PostgreSQL	MySQL / MariaDB	SQLite
smallint	SMALLINT	SMALLINT	INTEGER
integer	INTEGER	INTEGER	INTEGER
bigint	BIGINT	BIGINT	INTEGER
integer + autoIncrement	GENERATED BY DEFAULT AS IDENTITY	AUTO_INCREMENT	INTEGER PRIMARY KEY AUTOINCREMENT
string	VARCHAR(n)	VARCHAR(n)	TEXT
text	TEXT	TEXT	TEXT
boolean	BOOLEAN	TINYINT(1)	INTEGER
decimal	NUMERIC(p,s)	DECIMAL(p,s)	NUMERIC
float	REAL	FLOAT	REAL
double	DOUBLE PRECISION	DOUBLE	REAL
date	DATE	DATE	TEXT
time	TIME	TIME	TEXT
timestamp	TIMESTAMP	DATETIME	TEXT
timestamptz	TIMESTAMPTZ	DATETIME ⚠	TEXT
json	JSONB	JSON	TEXT
uuid	UUID	CHAR(36)	TEXT
binary	BYTEA	BLOB	BLOB

string defaults to a length of 255. decimal needs a precision and takes an optional scale.

⚠ MySQL and MariaDB have no time-zone-aware timestamp type, so timestamptz becomes DATETIME with a warning. Store UTC values in it.

What each engine can't do

PolySchema doesn't emulate features an engine lacks. It reports them instead, as one of two severities:

• ⚠ Warning: the migration still runs, but the result is weaker than on other engines.
• ✗ Error: the migration is refused on that engine. The migrator checks every file before it touches the database, so nothing runs.

Run polyschema -check (or CheckFS in a test) to see these ahead of time.

PostgreSQL supports everything in the file format, so it isn't in the table.

	MySQL	MariaDB	SQLite
DDL inside a transaction	⚠ commits implicitly	⚠ commits implicitly	✓
dropTable with cascade	⚠ ignored	⚠ ignored	⚠ ignored
createIndex ifNotExists, dropIndex ifExists	✗	✓	✓
dropColumn ifExists	✗	✓	✗
addColumn ifNotExists	✗	✓	✗
Partial index (where)	✗	✗	✓
timestamptz	⚠ DATETIME	⚠ DATETIME	✓ (TEXT)
Literal default on text / json / binary	✗ use defaultExpr	✓	✓
alterColumn	✓	✓	✗ needs a table rebuild
addForeignKey, dropForeignKey	✓	✓	✗ needs a table rebuild
addCheck, dropCheck	✓	✓	✗ needs a table rebuild
addUnique, dropUnique	✓	✓	⚠ done with a unique index
addColumn that is unique, NOT NULL without a default, or has a defaultExpr	✓	✓	✗
sql with no entry for the engine and no default	✗	✗ (a mysql entry counts)	✗

The same rule applies to Postgres: a sql operation with no postgres entry and no default is refused there.

Working around the gaps

MySQL IF [NOT] EXISTS. Drop the flag. Migrations only run once, so you rarely need it. createTable ifNotExists does work on MySQL, and indexes declared inside createTable are created along with the table.

SQLite table changes. SQLite can't alter columns, or add foreign keys and checks to an existing table. The usual fix is to rebuild the table. PolySchema deliberately doesn't do this for you. Use a sql operation instead, with the rebuild as its sqlite entry and the equivalent ALTER for the other engines:

# Equivalent to: alterColumn { table: users, name: handle, type: string, length: 100, nullable: false, default: "" }
- sql:
    postgres: "ALTER TABLE users ALTER COLUMN handle TYPE VARCHAR(100), ALTER COLUMN handle SET NOT NULL, ALTER COLUMN handle SET DEFAULT ''"
    mysql:    "ALTER TABLE users MODIFY COLUMN handle VARCHAR(100) NOT NULL DEFAULT ''"
    sqlite: |
      CREATE TABLE users_new (id INTEGER PRIMARY KEY AUTOINCREMENT, handle TEXT NOT NULL DEFAULT '');
      INSERT INTO users_new (id, handle) SELECT id, COALESCE(handle, '') FROM users;
      DROP TABLE users;
      ALTER TABLE users_new RENAME TO users;

MariaDB uses the mysql entry. Whether one entry can hold several statements depends on the driver. The SQLite driver used by the CLI accepts them. For MySQL, go-sql-driver needs multiStatements=true in the DSN, so it's simpler to use one sql operation per statement.

SQLite foreign keys. SQLite only enforces foreign keys when you run PRAGMA foreign_keys = ON on the connection. Many drivers can set this in the DSN.

timestamptz on MySQL. Store UTC and convert in your application.

Engine-specific notes

Identifiers are always quoted: "name" on Postgres and SQLite, and `name` on MySQL and MariaDB. Table and column names are therefore case-sensitive on Postgres. Write them in lowercase if you'll ever query them unquoted.

MySQL string defaults escape backslashes, which assumes the default sql_mode. If your server runs with NO_BACKSLASH_ESCAPES, avoid backslashes in literal defaults.

MySQL indexes declared in createTable go inside the CREATE TABLE statement. On the other engines they become separate CREATE INDEX statements.

Postgres alterColumn becomes one ALTER TABLE ... ALTER COLUMN statement that sets the type, nullability and default together, which matches what MySQL's MODIFY COLUMN does.