Docs menu

Deploy and run

Safe database migrations

CLI checks a change to your database for anything that loses data, breaks the running app or locks a busy table, and shows the safer way.

Explain it

A migration changes your database's structure: adding a column, renaming a table, adding an index. Some changes are risky in ways that aren't obvious. Dropping a column deletes its data. Renaming one breaks the version of your app that's running right now. Adding an index the usual way can stop your app saving anything until it finishes.

CLI checks every migration before it runs and tells you, in plain words, what each risky part does and the safer way to do it. It can also try the migration on an empty copy of your database's structure, to catch mistakes without touching your data.

How to do it: open your database, go to its Data tab, and under Run a migration paste the SQL and press Check it. If it's safe, press Back up and run. If it's risky, you have to tick I know what this does first.

CLI always takes a backup just before, runs Postgres migrations all-or-nothing, and stops any statement that waits more than 10 seconds for a lock, so a migration can't freeze your app.

"Add a phone column to users, safely."

Check a migration

API
POST /v1/services/:id/migrations/check {"sql": "ALTER TABLE users DROP COLUMN fax", "rehearse": true}

Developers and up. Works with Postgres and MySQL.

JSON
{
  "verdict": "risky",
  "statements": 1,
  "needsNoTransaction": false,
  "findings": [
    {
      "severity": "danger",
      "code": "DROP_COLUMN",
      "statement": "ALTER TABLE users DROP COLUMN fax",
      "table": "users",
      "rows": 2500000,
      "message": "This deletes a column and its data (fax). Code that still reads it breaks.",
      "safer": "Deploy code that no longer uses the column first. Drop it in a later migration."
    }
  ],
  "rehearsal": { "ok": true, "error": null, "ms": 412 }
}

verdict is safe, careful (only warnings) or risky (anything danger). Table sizes are estimates read from the database first (pg_class.reltuples, or information_schema.tables on MySQL). A lock warning on a table under 10,000 rows becomes info; on one over a million rows the message says how big it is.

What it flags

CodeSeveritySafer way
DROP_TABLE, TRUNCATE, DELETE_ALL, UPDATE_ALLdangerrename first and drop later; add a WHERE; batch big updates
DROP_COLUMN, RENAME_COLUMN, RENAME_TABLEdangerstop using it in code first; add new, dual-write, backfill, switch, drop
NOT_NULL_NO_DEFAULTdangeradd it nullable or with a default, backfill, then NOT NULL
TYPE_REWRITEwarnnew column, backfill in batches, switch, drop the old one
INDEX_LOCKS_WRITES, DROP_INDEX_LOCKS (Postgres)warnCONCURRENTLY, outside a transaction
VOLATILE_DEFAULT (Postgres)warnadd without a default, set it after, backfill
SET_NOT_NULL_SCAN, FK_LOCKS, CHECK_LOCKS, UNIQUE_LOCKS (Postgres)warnNOT VALID then VALIDATE CONSTRAINT; unique index concurrently, then USING INDEX

Statements are split properly: semicolons inside quotes, comments and $$ bodies don't count.

The rehearsal

With rehearse: true, CLI copies the schema only (no rows) into a scratch database on the same server (pg_dump --schema-only or mysqldump --no-data), runs the migration there exactly as it would be sent, and drops the scratch database whatever happens. It catches syntax errors, missing tables and columns, and type mistakes. It can't catch problems that depend on the data (a NOT NULL that fails on existing rows); the check above covers those. For running your app's release command against a full copy of the data, see Previews and safe releases.

Run it

API
POST /v1/services/:id/migrations {"name": "drop fax", "sql": "…", "acceptRisk": true}
FieldMeaning
acceptRiskrequired when the check is risky; without it the answer is 409 with error.details.check and needsAcceptRisk: true
noTransactionPostgres: run outside a transaction. Required for CREATE INDEX CONCURRENTLY (otherwise 400)
lockTimeoutSecondshow long a statement may wait for a lock, default 10, 0 for no limit. Sent as SET LOCAL lock_timeout (Postgres) or lock_wait_timeout (MySQL)

A backup is taken first; if it fails nothing runs. A migration stopped by the lock timeout fails with a message saying so, and on Postgres nothing changed. Migrations are for admins, need a recent second sign-in, and an agent's needs approval. The answer includes the check. Agents use cli_db_tools.