Migration Safety
Catch migrations that lock or rewrite tables or break deployed code before they run, with analyzeMigration, MigrationRunner#check() and safety modes.
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:
- Each migration's
up()runs against a recording driver. Every query is captured, nothing is executed, and every query returns no rows. - The captured SQL is split into statements and analyzed for the dialect of the runner's connection (
postgres,mysqlorsqlite). - Every risky statement becomes a finding with a severity, the lock it takes and a suggested fix.
| Severity | Meaning |
|---|---|
error | The statement fails, rewrites the table under a blocking lock, or breaks running instances. safety: 'block' refuses to run it |
warning | The statement blocks reads or writes for a time that grows with the table, or loses data |
info | Advice, for example a missing lock_timeout |
Check pending migrations
runner.check() analyzes every pending migration and returns one result per file:
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}| Call | Description |
|---|---|
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:
| Field | Description |
|---|---|
name | Migration name (file name without extension) |
file | Absolute path of the migration file |
statements | The SQL captured from up() |
findings | MigrationFinding[] |
Each MigrationFinding has:
| Field | Description |
|---|---|
rule | Stable rule id, for example create-index-non-concurrent |
severity | 'error', 'warning' or 'info' |
statement | The offending statement, comments removed and whitespace collapsed |
statementIndex | Position of the statement in the analyzed SQL (0-based) |
table | Affected table, when known |
lock | Lock the statement takes, for example ACCESS EXCLUSIVE |
message | What goes wrong |
fix | The 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:
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 findingsafety | Behaviour |
|---|---|
'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>.
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
| Option | Default | Description |
|---|---|---|
safety | 'off' | 'off', 'warn' or 'block' |
tableSizes | none | Approximate row counts by table (users or public.users). Size-sensitive findings escalate on large tables |
largeTableThreshold | 1_000_000 | Row count above which size-sensitive findings escalate one level |
columnTypes | none | Current column types, { 'users.name': 'varchar(50)' }, used to recognise safe type widenings |
ignore | none | Rule 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:
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:
| Option | Default | Description |
|---|---|---|
dialect | required | 'postgres', 'mysql' or 'sqlite' |
tableSizes | none | Approximate row counts by table |
largeTableThreshold | 1_000_000 | Escalation threshold |
inTransaction | false | The statements run inside a transaction. The runner sets this to true unless the migration exports transaction = false |
columnTypes | none | Current column types (table.column to type) |
ignore | none | Rule 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.
| Field | Description |
|---|---|
sql | Statement text without comments, whitespace collapsed |
masked | Same, with string literals and dollar-quoted bodies blanked out |
comments | Comments 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.
| Rule | Dialect | Severity | Lock | Safe alternative |
|---|---|---|---|---|
create-index-non-concurrent | PostgreSQL | warning* | SHARE: writes blocked for the whole build | CREATE INDEX CONCURRENTLY in a migration with transaction = false |
drop-index-non-concurrent | PostgreSQL | warning | ACCESS EXCLUSIVE on the table | DROP INDEX CONCURRENTLY in a migration with transaction = false |
reindex-non-concurrent | PostgreSQL | warning* | SHARE on the table, ACCESS EXCLUSIVE on each index | REINDEX ... CONCURRENTLY (PostgreSQL 12+) with transaction = false |
concurrently-in-transaction | PostgreSQL | error | none (PostgreSQL rejects the statement) | export const transaction = false, with the concurrent statement in a migration of its own |
add-column-not-null-without-default | all | error; warning on MySQL | ACCESS EXCLUSIVE (PostgreSQL) | Add the column nullable or with a constant default, backfill, then enforce NOT NULL |
add-column-volatile-default | PostgreSQL | warning* | ACCESS EXCLUSIVE, full table rewrite | Add without a default, backfill in batches, then ALTER COLUMN ... SET DEFAULT |
sqlite-add-column-unsupported | SQLite | error | none (the statement fails) | A plain column plus a unique index, or rebuild the table in one transaction |
alter-column-type | PostgreSQL | error* | ACCESS EXCLUSIVE, table and index rewrite | New column, dual write, backfill, switch reads, drop the old column |
set-not-null | PostgreSQL | warning* | ACCESS EXCLUSIVE, full scan | CHECK (col IS NOT NULL) NOT VALID, then VALIDATE CONSTRAINT, then SET NOT NULL (PostgreSQL 12+) |
add-constraint-without-not-valid | PostgreSQL | warning* | Foreign key: SHARE ROW EXCLUSIVE on both tables. CHECK: ACCESS EXCLUSIVE | Add NOT VALID, then VALIDATE CONSTRAINT in a later migration |
validate-in-same-transaction | PostgreSQL | warning* | ACCESS EXCLUSIVE, still held from ADD CONSTRAINT | Validate in a separate, later migration |
add-unique-constraint | PostgreSQL | warning* | ACCESS EXCLUSIVE while the index is built | CREATE UNIQUE INDEX CONCURRENTLY, then ADD CONSTRAINT ... USING INDEX |
drop-column | all | warning | ACCESS EXCLUSIVE, brief, metadata only (PostgreSQL) | Expand/contract: stop using the column, deploy, then drop it |
drop-table | all | warning | ACCESS EXCLUSIVE (PostgreSQL) | Expand/contract: stop using the table, deploy, back up, then drop it |
rename-column | all | error | none reported | Expand/contract: new column, dual write and backfill, switch reads, drop the old one |
rename-table | all | error | none reported | Rename, and create a view under the old name in the same migration |
truncate | all | warning | ACCESS EXCLUSIVE (PostgreSQL); metadata lock (MySQL) | Confirm intent; delete in batches from a background job |
delete-without-where | all | warning* | ROW EXCLUSIVE plus a lock on every row | Add a WHERE clause, or delete in batches from a job |
update-without-where | all | warning* | ROW EXCLUSIVE plus a lock on every row | Backfill in batches (1,000 to 10,000 rows per statement) from a job |
vacuum-full | PostgreSQL | error* | ACCESS EXCLUSIVE, table rewrite | pg_repack, pg_squeeze or plain VACUUM, outside migrations |
cluster | PostgreSQL | error* | ACCESS EXCLUSIVE, table rewrite | pg_repack --order-by, outside migrations |
lock-table | PostgreSQL, MySQL | warning | The requested mode: ACCESS EXCLUSIVE by default on PostgreSQL, READ or WRITE on MySQL | Avoid; otherwise the weakest mode, a short transaction and lock_timeout |
missing-lock-timeout | PostgreSQL | info* | none (advice) | SET lock_timeout = '5s' first (SET LOCAL inside a transaction) |
mysql-copy-algorithm | MySQL | warning* | Table copy (ALGORITHM=COPY), concurrent writes blocked | Append ALGORITHM=INPLACE, LOCK=NONE so MySQL refuses instead of copying; otherwise gh-ost or pt-online-schema-change |
dynamic-analysis-failed | runner only | info | none | Guard 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 andAUTO_INCREMENTcolumns are not reported.add-column-volatile-default: reported forrandom(),gen_random_uuid(), theuuid_generate_*functions,clock_timestamp(),nextval()and similar volatile defaults, serial types, identity columns and stored generated columns. Constant defaults,now()andCURRENT_TIMESTAMPare metadata-only on PostgreSQL 11+ and are not reported.sqlite-add-column-unsupported: SQLite'sALTER TABLE ADD COLUMNcannot add aPRIMARY KEYorUNIQUEcolumn, or a column whose default isCURRENT_TIMESTAMP(orCURRENT_DATE/CURRENT_TIME) or a parenthesized expression.alter-column-type: withoutUSING, a change thatisSafeTypeChange()accepts for the type given incolumnTypesis not reported. When the current type is unknown and the new type istext,varcharornumeric, the finding isinfoinstead oferror, because the change may be a safe widening; passcolumnTypesto decide.set-not-null: not reported when a validatedCHECK (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 byMODIFYorCHANGEof a column definition,CONVERT TO CHARACTER SET,ADD FOREIGN KEYwhileforeign_key_checksis enabled, adding anAUTO_INCREMENTorPRIMARY KEYcolumn, and dropping the primary key without adding a new one. It is not reported when the statement already setsALGORITHM=INPLACEorALGORITHM=INSTANT.CHANGEthat renames a column is reported asrename-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 aSET lock_timeout(other than0) 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:
-- axil:allow drop-column
ALTER TABLE users DROP COLUMN legacy_id;-- axil:allow rule-a, rule-blists several rules.-- axil:allow allsilences 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 toanalyzeMigration().
In a TypeScript migration, put the comment inside the SQL string:
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:
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
tableSizesandcolumnTypes. - 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 MySQLDELIMITERblocks contain unquoted semicolons and are not split correctly; use dollar quoting in PostgreSQL. - Only the
postgres,mysqlandsqlitedrivers are analyzed.
Related
Was this page helpful?