Writing database migration SQL scripts. Use when creating or modifying migration files under db/migration/ directories, adding tables, indexes, or columns.
Every migration must be written for both PostgreSQL and SQLite. Migration files live in parallel directories:
db/migration/postgres/NNN_description.sqldb/migration/sqlite/NNN_description.sqlBoth files must have the same numbered prefix and name. Use the appropriate SQL dialect for each (e.g., BIGSERIAL vs INTEGER PRIMARY KEY AUTOINCREMENT, UUID vs TEXT, TIMESTAMPTZ vs TEXT).
Migration files are numbered sequentially with zero-padded three-digit prefixes:
001_init.sql
002_code_first_routes.sql
003_wasi_config.sql
Check existing files to determine the next number.
Index names follow the format: <table>_<column(s)>_<idx|uk>
_idx for regular indexes_uk for unique indexesExamples:
CREATE INDEX accounts_deleted_at_idx ON accounts (deleted_at);
CREATE UNIQUE INDEX accounts_email_uk ON accounts (email) WHERE deleted_at IS NULL;
CREATE UNIQUE INDEX plugins_name_version_uk ON plugins (account_id, name, version);
<table>_pk:
CONSTRAINT accounts_pk PRIMARY KEY (account_id)
Use the same column types in PostgreSQL and SQLite whenever possible, to make it easier to write queries that work on both databases. Only use database-specific types (e.g., BIGSERIAL vs INTEGER PRIMARY KEY AUTOINCREMENT, UUID vs TEXT, TIMESTAMPTZ vs TEXT, TEXT[] vs TEXT) when there is no common alternative.
CREATE TABLE, NOT NULL, PRIMARY KEY)