Migrations

Turbine migrations are plain SQL files with -- UP and -- DOWN sections, applied in order and tracked in a _turbine_migrations table.

The migration CLI drives PostgreSQL only. On other engines, define the schema with defineSchema and manage DDL yourself; see Database Engines.

Commands#

npx turbine migrate create add_users_table              # blank file
npx turbine migrate create add_email_index --auto       # SQL from the schema diff
npx turbine migrate up                                  # apply pending (interactive)
npx turbine migrate deploy                              # apply pending (CI, no prompts)
npx turbine migrate down                                # roll back the last one
npx turbine migrate status                              # applied, pending and drifted

--auto and --from-diff refuse while unapplied migration files exist, because the diff would repeat them. Run migrate up first, or pass --allow-pending when the pending files do not touch the schema.

Migration files#

-- 20260409143022_add_users_table.sql
-- UP
CREATE TABLE "users" (
  "id" SERIAL PRIMARY KEY,
  "email" TEXT UNIQUE NOT NULL
);
 
-- DOWN
DROP TABLE "users";

The runner reads the 14-digit prefix, so files stamped in local time still sort correctly.

What the runner guarantees#

• One transaction per migration. If a migration fails, it rolls back and is not recorded.
• Stop on first error. Earlier migrations in the batch stay applied. Fix the failing file and run again.
• Checksums. Each applied file's SHA-256 is stored. If an applied file changes, status reports drift and up refuses to run until you restore it or pass --allow-drift.
• One runner at a time. An advisory lock makes a second concurrent up fail with MigrationError (E006). deploy --wait waits for the lock instead, so two deploys started together both succeed.
• Direct connections only. A transaction pooler can release the advisory lock on a different backend, so up, deploy and down refuse a pooler or pgbouncer host name, or port 6543 or 6432. Use the direct endpoint, or --allow-pooler for a session-mode pooler.
• Your configured schema. With schema set in turbine.config.ts, migrations run with that schema first on the search_path, so unqualified DDL and _turbine_migrations land there. A schema that does not exist fails with MigrationError (E006).

Do not write BEGIN, COMMIT or ROLLBACK in a migration. The runner refuses a file containing them, because an embedded COMMIT would end its wrapping transaction partway through. ROLLBACK TO SAVEPOINT is allowed.

No-transaction migrations#

CREATE INDEX CONCURRENTLY cannot run inside a transaction. Add -- turbine:no-transaction to the file header, and the runner applies the file one statement at a time with no wrapping transaction:

-- Migration: add_comments_post_id_index
-- turbine:no-transaction
 
-- UP
DROP INDEX CONCURRENTLY IF EXISTS "idx_comments_post_id";
CREATE INDEX CONCURRENTLY "idx_comments_post_id" ON "comments" ("post_id");
 
-- DOWN
DROP INDEX CONCURRENTLY IF EXISTS "idx_comments_post_id";

A failure partway leaves earlier statements applied and the migration unrecorded, so a rerun executes the whole file again. Write every statement to be safe to rerun.

For a concurrent index build, drop first instead of using IF NOT EXISTS. A failed build leaves an invalid index under that name, which IF NOT EXISTS would skip. turbine doctor --fix writes this shape.

Out-of-order migrations#

Any pending migration applies, even if its timestamp is older than one already applied, as when two branches each add a migration. The runner prints a warning and applies it. Enforce linear history in review if you need it.

Rolling back#

migrate down runs the -- DOWN section. Undoing an additive change is destructive (the DOWN for an added column drops it), so it asks for confirmation. In an unattended script, pass --allow-destructive:

npx turbine migrate down --allow-destructive

What --auto can and cannot do#

--auto compares your schema file with the live database and writes the obvious DDL. It knows nothing about intent:

• Type changes use a plain cast (ALTER COLUMN ... TYPE <new> USING col::<new>), which fails on rows the cast cannot convert. Use the two-phase recipe instead.
• Lossless widenings are not destructive. int to bigint, a longer varchar, or more numeric precision apply without --allow-destructive. The file marks each with a -- turbine:widening comment.
• Renames look like a drop plus an add. The data does not move. Write ALTER TABLE ... RENAME COLUMN by hand.
• SET NOT NULL has no backfill, so it fails on a column holding NULL. Backfill first.
• A new NOT NULL column without a default gets a warning pointing at --recipe backfill.
• Enum values can be added but not reordered. A reordered or removed label produces a warning, not a statement.
• A column missing from the schema becomes a DROP COLUMN, flagged as destructive. A table missing from the schema is listed and never dropped; write that by hand.

When the diff skips something it does not compare (sequence-backed defaults, some indexes and constraints), push and --auto say so instead of reporting "in sync".

Changing a column type with existing data#

When a type change needs a real conversion, add a new column, backfill it, then swap. Every step is reversible until the final drop.

Expand. Add the column as nullable, then backfill in batches, repeating the UPDATE until it changes zero rows:

ALTER TABLE "orders" ADD COLUMN "total_cents" BIGINT;
 
UPDATE "orders"
SET "total_cents" = ("total" * 100)::BIGINT
WHERE "id" IN (
  SELECT "id" FROM "orders" WHERE "total_cents" IS NULL LIMIT 5000
);

Verify before tightening:

SELECT count(*) FROM "orders" WHERE "total_cents" IS NULL;   -- expect 0

Contract. In one migration, enforce NOT NULL and swap the names. The runner's transaction makes both renames atomic:

ALTER TABLE "orders" ALTER COLUMN "total_cents" SET NOT NULL;
ALTER TABLE "orders" RENAME COLUMN "total" TO "total_old";
ALTER TABLE "orders" RENAME COLUMN "total_cents" TO "total";

Renames count as destructive, so migrate up asks before running this one.

Drop the old column later, in a separate migration, once nothing reads it:

ALTER TABLE "orders" DROP COLUMN "total_old";

migrate create <name> --recipe backfill writes this skeleton with placeholders for the table, columns and conversion. Fill in every placeholder before applying.

Checking for drift in CI#

# The database differs from the schema file
npx turbine push --dry-run --exit-code
 
# A migration is pending, modified, missing or misnamed
npx turbine migrate status --exit-code

Both exit 2 on drift and 0 when everything matches. push also fails on a warning it cannot apply, such as a removed enum label.

A named CHECK that differs only because PostgreSQL rewrote the expression is printed but does not fail the gate.

The destructive gate#

Every command scans for statements that destroy data: drops, TRUNCATE, DELETE, an UPDATE without a restricting WHERE, column type changes, renames and similar. Matches inside comments and string literals are ignored.

Declining the prompt runs nothing.

See also#

• Schema: defineSchema, push and schemaDiff().
• CLI: every command and flag.
• Typed Errors: including MigrationError (E006).