Database

Migration Safety

Catch migrations that lock or rewrite tables or break deployed code before they run, with analyzeMigration, MigrationRunner#check() and safety modes.

14 min readUpdated DocumentationEdit this page
On this page11

Some schema changes are instant on an empty development database and take an application down in production. They hold a lock that blocks reads or writes while the table is scanned or rewritten, or they remove a name that the currently deployed code still uses. The migration safety check in @axiljs/orm reports these statements before they run and explains the zero-downtime alternative for each one.

Added in 0.3.5. This page covers MigrationRunner#check(), the safety option of the runner, analyzeMigration() for raw SQL, the full rule list, acknowledging intended changes, and migrations that run outside a transaction. For the migration file format and the axil migrate command, see Migrations.

How the check works

The check is static. Nothing is executed against your database:

  1. Each migration's up() runs against a recording driver. Every query is captured, nothing is executed, and every query returns no rows.
  2. The captured SQL is split into statements and analyzed for the dialect of the runner's connection (postgres, mysql or sqlite).
  3. Every risky statement becomes a finding with a severity, the lock it takes and a suggested fix.
SeverityMeaning
errorThe statement fails, rewrites the table under a blocking lock, or breaks running instances. safety: 'block' refuses to run it
warningThe statement blocks reads or writes for a time that grows with the table, or loses data
infoAdvice, for example a missing lock_timeout

Check pending migrations

runner.check() analyzes every pending migration and returns one result per file:

TypeScript
TS
1import { join } from 'node:path'
2import { createConnection, MigrationRunner } from '@axiljs/orm'
3
4const db = await createConnection({ driver: 'postgres', url: process.env.DATABASE_URL })
5
6const runner = new MigrationRunner(db.getDriver(), join(process.cwd(), 'dist', 'migrations'), {
7  tableSizes: { users: 4_200_000, orders: 18_000_000 }   // optional: escalates findings on large tables
8})
9
10for (const { name, findings } of await runner.check()) {
11  for (const f of findings) {
12    console.log(`${f.severity} ${name} [${f.rule}] ${f.message}\n  fix: ${f.fix}`)
13  }
14}
CallDescription
runner.check()Analyze pending migrations. When the _migrations table does not exist yet, every migration counts as pending
runner.check({ all: true })Analyze every migration file, including ones that already ran
checkMigrations(runner, options?)Standalone form of runner.check(options)

Each result is a MigrationCheckResult:

FieldDescription
nameMigration name (file name without extension)
fileAbsolute path of the migration file
statementsThe SQL captured from up()
findingsMigrationFinding[]

Each MigrationFinding has:

FieldDescription
ruleStable rule id, for example create-index-non-concurrent
severity'error', 'warning' or 'info'
statementThe offending statement, comments removed and whitespace collapsed
statementIndexPosition of the statement in the analyzed SQL (0-based)
tableAffected table, when known
lockLock the statement takes, for example ACCESS EXCLUSIVE
messageWhat goes wrong
fixThe safe alternative

Note

A migration whose up() throws against the empty results, for example because it reads rows[0].id, is reported as an info finding with the rule dynamic-analysis-failed. The statements captured before the error are still analyzed. Guard such code (rows[0]?.id) or review the migration by hand.

Check before up()

Pass safety to the runner to check pending migrations every time up() runs:

TypeScript
TS
1const runner = new MigrationRunner(driver, dir, { safety: 'block' })
2
3await runner.up()   // throws MigrationSafetyError before running anything
4                    // if a pending migration has an error finding
safetyBehaviour
'off' (default)No check; up() behaves exactly as before
'warn'Prints all findings, then runs the migrations
'block'Prints all findings and throws MigrationSafetyError if any finding is an error. No migration in the batch runs

MigrationSafetyError has a results property with the MigrationCheckResult of every blocked migration. Its message lists each error finding and ends with a hint to fix the statement or acknowledge it with -- axil:allow <rule>.

TypeScript
TS
1import { MigrationSafetyError } from '@axiljs/orm'
2
3try {
4  await runner.up()
5} catch (err) {
6  if (err instanceof MigrationSafetyError) {
7    for (const r of err.results) console.error(r.name, r.findings.map(f => f.rule))
8    process.exit(1)
9  }
10  throw err
11}

Warning

axil migrate creates its MigrationRunner without options, so the safety check is off there. To check migrations in CI or before a deploy, run runner.check() or safety: 'block' from your own script.

Runner options

OptionDefaultDescription
safety'off''off', 'warn' or 'block'
tableSizesnoneApproximate row counts by table (users or public.users). Size-sensitive findings escalate on large tables
largeTableThreshold1_000_000Row count above which size-sensitive findings escalate one level
columnTypesnoneCurrent column types, { 'users.name': 'varchar(50)' }, used to recognise safe type widenings
ignorenoneRule ids to skip everywhere

DEFAULT_LARGE_TABLE_THRESHOLD (1_000_000) is exported.

Analyze SQL directly

analyzeMigration() checks SQL without a runner or a connection:

TypeScript
TS
1import { analyzeMigration } from '@axiljs/orm'
2
3const findings = analyzeMigration(sql, { dialect: 'postgres', tableSizes: { users: 2_000_000 } })
4// [{ rule, severity, statement, table, lock, message, fix }, ...]

sql may be a string or an array of strings. The options are:

OptionDefaultDescription
dialectrequired'postgres', 'mysql' or 'sqlite'
tableSizesnoneApproximate row counts by table
largeTableThreshold1_000_000Escalation threshold
inTransactionfalseThe statements run inside a transaction. The runner sets this to true unless the migration exports transaction = false
columnTypesnoneCurrent column types (table.column to type)
ignorenoneRule ids to skip

BEGIN / START TRANSACTION and COMMIT / ROLLBACK statements in the SQL also switch the transaction state for the statements that follow.

Statement splitting

splitStatements(sql, dialect = 'postgres') splits SQL on top-level semicolons and returns { sql, masked, comments } for each statement. It respects single-quoted strings (including E'' strings and MySQL backslash escapes), quoted identifiers, PostgreSQL dollar quoting, -- comments, # comments on MySQL, and nested block comments on PostgreSQL. SQL inside a function body or a string literal is never reported.

FieldDescription
sqlStatement text without comments, whitespace collapsed
maskedSame, with string literals and dollar-quoted bodies blanked out
commentsComments that belong to the statement: the leading ones, and a trailing -- comment on the same line after the semicolon

isSafeTypeChange(from, to) is also exported. It returns true for the PostgreSQL type changes that need neither a rewrite nor a scan: varchar(n) to varchar(m >= n), varchar or text; text to varchar; numeric(p,s) to numeric(q >= p,s) or numeric; and no-op changes.

Rules

Severities marked with * are size-sensitive: when tableSizes puts the table above largeTableThreshold, warning becomes error and info becomes warning. Breaking-change rules do not depend on size.

RuleDialectSeverityLockSafe alternative
create-index-non-concurrentPostgreSQLwarning*SHARE: writes blocked for the whole buildCREATE INDEX CONCURRENTLY in a migration with transaction = false
drop-index-non-concurrentPostgreSQLwarningACCESS EXCLUSIVE on the tableDROP INDEX CONCURRENTLY in a migration with transaction = false
reindex-non-concurrentPostgreSQLwarning*SHARE on the table, ACCESS EXCLUSIVE on each indexREINDEX ... CONCURRENTLY (PostgreSQL 12+) with transaction = false
concurrently-in-transactionPostgreSQLerrornone (PostgreSQL rejects the statement)export const transaction = false, with the concurrent statement in a migration of its own
add-column-not-null-without-defaultallerror; warning on MySQLACCESS EXCLUSIVE (PostgreSQL)Add the column nullable or with a constant default, backfill, then enforce NOT NULL
add-column-volatile-defaultPostgreSQLwarning*ACCESS EXCLUSIVE, full table rewriteAdd without a default, backfill in batches, then ALTER COLUMN ... SET DEFAULT
sqlite-add-column-unsupportedSQLiteerrornone (the statement fails)A plain column plus a unique index, or rebuild the table in one transaction
alter-column-typePostgreSQLerror*ACCESS EXCLUSIVE, table and index rewriteNew column, dual write, backfill, switch reads, drop the old column
set-not-nullPostgreSQLwarning*ACCESS EXCLUSIVE, full scanCHECK (col IS NOT NULL) NOT VALID, then VALIDATE CONSTRAINT, then SET NOT NULL (PostgreSQL 12+)
add-constraint-without-not-validPostgreSQLwarning*Foreign key: SHARE ROW EXCLUSIVE on both tables. CHECK: ACCESS EXCLUSIVEAdd NOT VALID, then VALIDATE CONSTRAINT in a later migration
validate-in-same-transactionPostgreSQLwarning*ACCESS EXCLUSIVE, still held from ADD CONSTRAINTValidate in a separate, later migration
add-unique-constraintPostgreSQLwarning*ACCESS EXCLUSIVE while the index is builtCREATE UNIQUE INDEX CONCURRENTLY, then ADD CONSTRAINT ... USING INDEX
drop-columnallwarningACCESS EXCLUSIVE, brief, metadata only (PostgreSQL)Expand/contract: stop using the column, deploy, then drop it
drop-tableallwarningACCESS EXCLUSIVE (PostgreSQL)Expand/contract: stop using the table, deploy, back up, then drop it
rename-columnallerrornone reportedExpand/contract: new column, dual write and backfill, switch reads, drop the old one
rename-tableallerrornone reportedRename, and create a view under the old name in the same migration
truncateallwarningACCESS EXCLUSIVE (PostgreSQL); metadata lock (MySQL)Confirm intent; delete in batches from a background job
delete-without-whereallwarning*ROW EXCLUSIVE plus a lock on every rowAdd a WHERE clause, or delete in batches from a job
update-without-whereallwarning*ROW EXCLUSIVE plus a lock on every rowBackfill in batches (1,000 to 10,000 rows per statement) from a job
vacuum-fullPostgreSQLerror*ACCESS EXCLUSIVE, table rewritepg_repack, pg_squeeze or plain VACUUM, outside migrations
clusterPostgreSQLerror*ACCESS EXCLUSIVE, table rewritepg_repack --order-by, outside migrations
lock-tablePostgreSQL, MySQLwarningThe requested mode: ACCESS EXCLUSIVE by default on PostgreSQL, READ or WRITE on MySQLAvoid; otherwise the weakest mode, a short transaction and lock_timeout
missing-lock-timeoutPostgreSQLinfo*none (advice)SET lock_timeout = '5s' first (SET LOCAL inside a transaction)
mysql-copy-algorithmMySQLwarning*Table copy (ALGORITHM=COPY), concurrent writes blockedAppend ALGORITHM=INPLACE, LOCK=NONE so MySQL refuses instead of copying; otherwise gh-ost or pt-online-schema-change
dynamic-analysis-failedrunner onlyinfononeGuard code that reads query results, or review the migration by hand

Details per rule:

  • add-column-not-null-without-default: on PostgreSQL the statement fails as soon as the table has rows; on SQLite it always fails; MySQL silently fills existing rows with the type's implicit default (0, '', a zero date), which is why it is only a warning there. Serial, identity, generated and AUTO_INCREMENT columns are not reported.
  • add-column-volatile-default: reported for random(), gen_random_uuid(), the uuid_generate_* functions, clock_timestamp(), nextval() and similar volatile defaults, serial types, identity columns and stored generated columns. Constant defaults, now() and CURRENT_TIMESTAMP are metadata-only on PostgreSQL 11+ and are not reported.
  • sqlite-add-column-unsupported: SQLite's ALTER TABLE ADD COLUMN cannot add a PRIMARY KEY or UNIQUE column, or a column whose default is CURRENT_TIMESTAMP (or CURRENT_DATE / CURRENT_TIME) or a parenthesized expression.
  • alter-column-type: without USING, a change that isSafeTypeChange() accepts for the type given in columnTypes is not reported. When the current type is unknown and the new type is text, varchar or numeric, the finding is info instead of error, because the change may be a safe widening; pass columnTypes to decide.
  • set-not-null: not reported when a validated CHECK (col IS NOT NULL) earlier in the migration proves the column, or when the migration validates a constraint on the same table before it.
  • mysql-copy-algorithm: triggered by MODIFY or CHANGE of a column definition, CONVERT TO CHARACTER SET, ADD FOREIGN KEY while foreign_key_checks is enabled, adding an AUTO_INCREMENT or PRIMARY KEY column, and dropping the primary key without adding a new one. It is not reported when the statement already sets ALGORITHM=INPLACE or ALGORITHM=INSTANT. CHANGE that renames a column is reported as rename-column.
  • missing-lock-timeout: reported at most once per migration, on the first lock-taking statement (ALTER TABLE, an index build or drop, TRUNCATE, DROP TABLE, LOCK TABLE, VACUUM FULL, CLUSTER) on an existing PostgreSQL table, unless a SET lock_timeout (other than 0) came before it. A DDL statement waiting for its lock queues every later query on the table behind it, even when the DDL itself is instant.

Statements on a table created earlier in the same migration are not reported: the table is new, empty and unused. The SQLite ADD COLUMN rules are the exception, because those statements fail on any table.

Acknowledge an intended change

Once a change is intended, for example the contract phase of a column removal after the code that used it has been deployed, mark the statement with a comment. The check, including block mode, then accepts it:

SQL
SQL
-- axil:allow drop-column
ALTER TABLE users DROP COLUMN legacy_id;
  • -- axil:allow rule-a, rule-b lists several rules.
  • -- axil:allow all silences every rule for that statement.
  • A comment on the same line after the semicolon also applies to that statement.
  • To skip a rule everywhere, pass ignore: ['rule-id'] to the runner or to analyzeMigration().

In a TypeScript migration, put the comment inside the SQL string:

TypeScript
TS
1export async function up(db) {
2  await db.query(`
3    -- axil:allow drop-column
4    ALTER TABLE users DROP COLUMN legacy_id
5  `)
6}

Non-transactional migrations

MigrationRunner wraps each migration in a transaction. PostgreSQL refuses CREATE INDEX CONCURRENTLY, DROP INDEX CONCURRENTLY and REINDEX CONCURRENTLY inside one, so a migration can opt out:

007_index_users_email.ts
TS
1export const transaction = false
2
3export async function up(db) {
4  await db.query('CREATE INDEX CONCURRENTLY idx_users_email ON users (email)')
5}
6
7export async function down(db) {
8  await db.query('DROP INDEX CONCURRENTLY IF EXISTS idx_users_email')
9}

The setting applies to both up() and down(), and the safety check analyzes such a migration as running outside a transaction.

Note

The runner reads transaction from the same object as up(). With named exports, as above, use export const transaction = false. With a default export, put transaction: false inside the default object, next to up and down.

Warning

Keep a non-transactional migration to the concurrent statement alone. If it fails partway there is no rollback, and a failed concurrent build leaves an INVALID index that must be dropped before retrying. Without a transaction, queries may run on different pooled connections, so a session SET lock_timeout is not guaranteed to apply to the next statement; set it per role (ALTER ROLE migrator SET lock_timeout = '5s') when that matters.

Limitations

  • The analysis is static and pattern based. It recognises the statements listed above, not every possible form, and does not know the live schema beyond tableSizes and columnTypes.
  • The dynamic check runs up() with empty results. Migrations that branch on query results are analyzed only along the path taken with no rows.
  • Statements built by other means than db.query() on the migration's connection are not seen.
  • SQL-standard function bodies (BEGIN ATOMIC ... END) and MySQL DELIMITER blocks contain unquoted semicolons and are not split correctly; use dollar quoting in PostgreSQL.
  • Only the postgres, mysql and sqlite drivers are analyzed.

Was this page helpful?

Help improve the documentation

AxilJS is open source and documentation improvements are welcome.

AxilJS DocumentationMIT License · Built by SyntaxilitY