Migrations in Practice

The Schema & Migrations page covers the basics: .sql files with -- UP and -- DOWN sections, tracked in _turbine_migrations. This page covers the production workflow, the real limits of auto-diffing, and the recipe for the change that trips everyone up: altering a column's type when the table already has data.

The migration CLI is Postgres-only. turbine migrate, turbine push, and turbine generate drive PostgreSQL. The other engines (SQLite, MySQL, SQL Server, PowDB) are code-first and programmatic: define the schema with defineSchema, derive metadata with schemaDefToMetadata, and manage DDL yourself. The typed query API is identical across engines. See Database Engines.

The workflow#

# 1. Create a migration: blank, auto-generated, or diff-derived with
#    destructive statements flagged
npx turbine migrate create add_users_table
npx turbine migrate create add_email_index --auto
npx turbine migrate create sync_schema --from-diff
 
# 2. Apply pending migrations locally (interactive; prompts on destructive ops)
npx turbine migrate up
 
# 3. Apply in CI / production (no prompts, applies files exactly as written)
npx turbine migrate deploy
 
# 4. Roll back the most recent migration (or the last N with --step)
npx turbine migrate down
npx turbine migrate down --step 2
 
# 5. Inspect applied vs pending, and flag drift
npx turbine migrate status

create --auto writes a starting-point migration from the diff between your defineSchema output and the live database. It is a draft; read it before committing. create --from-diff does the same but flags destructive statements inline (see below).

up applies every pending migration in timestamp order. It is the interactive command: if any pending file contains a destructive statement, it stops and asks you to confirm before running anything (see the destructive gate).

deploy runs the same pending migrations but never prompts; it applies the files exactly as written. Use it in CI/CD and deployment hooks. Pair it with --dry-run in a pipeline step to preview.

What the runner guarantees#

• Per-migration transactions. Each migration runs inside its own BEGIN / COMMIT. If its SQL fails, that migration rolls back and nothing from it is recorded in _turbine_migrations.
• Stop on first error, partial apply. A batch is processed in order and stops at the first failure. Migrations that already succeeded stay applied and recorded; the failing one and everything after it do not run. Fix the offending migration and re-run to continue.
• SHA-256 checksums. Every applied migration's file is hashed. If an already-applied file changes on disk, status reports drift and up refuses to run until you reconcile (or pass --allow-drift when intentionally rewriting history).
• Advisory lock. The runner takes a per-database Postgres advisory lock before applying, so two concurrent runs cannot race; the second exits cleanly instead of double-applying.

No-transaction migrations (CREATE INDEX CONCURRENTLY)#

Some statements cannot run inside a transaction. CREATE INDEX CONCURRENTLY is the common one: it builds without holding a write lock, but Postgres forbids it in any transaction block. Put -- turbine:no-transaction in the migration's header (before -- UP) and the runner applies that file without BEGIN / COMMIT, executing one statement per call:

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

This is exactly what turbine doctor --fix writes. Two things to know:

• Every statement must be idempotent. With no transaction, a mid-file failure leaves earlier statements applied while the migration stays unrecorded, so a rerun re-executes the whole file. IF NOT EXISTS / IF EXISTS keep each statement safe to repeat.
• A failed concurrent build leaves an INVALID index. On rerun, IF NOT EXISTS skips that corpse, so it never rebuilds. Fix it with DROP INDEX CONCURRENTLY, then rerun; turbine doctor lists invalid indexes for you.

migrate up prints a notice whenever it runs a no-transaction file. CREATE INDEX CONCURRENTLY can wait a while on other open transactions; that is normal, not a hang. The advisory lock and checksum checks apply unchanged.

Out-of-order migrations#

Turbine tracks migrations by name, not by a monotonic sequence number, so any pending migration applies, even if its timestamp predates one already applied. This lets two branches merge migrations authored in parallel: the older-timestamped file that landed second is simply pending, and the next up / deploy applies it, printing a one-line warning so the out-of-order apply is visible. If your team needs strict linear history, enforce it in review (rebase the timestamp forward) rather than expecting the runner to refuse.

Rolling back additive migrations#

migrate down runs a migration's -- DOWN section, and the reverse of an additive change is destructive: a migration that added a column has a -- DOWN that drops it, so rolling it back trips the destructive gate like any other DROP COLUMN. In an unattended rollback script (CI), pass --allow-destructive so down does not stall waiting for a prompt:

npx turbine migrate down --allow-destructive

What --auto can and cannot do#

create --auto is a diff tool, not a schema-migration planner. It compares columns and types and emits the obvious DDL; it has no notion of intent or history. Its blind spots:

• Type changes get a blind cast. A changed column type emits ALTER COLUMN "col" TYPE <newtype> USING "col"::<newtype>. The USING cast is unconditional, with no transformation hook. It works for widening casts (int to bigint, text to varchar) but fails at apply time on data the cast cannot handle (text to integer on non-numeric rows). For anything non-trivial, use the two-phase recipe.
• Renames are not detected. The differ cannot know full_name was renamed from name. It sees one column gone and one added, so it produces an add-new plus orphan-old, and the data does not move. Write renames by hand as ALTER TABLE ... RENAME COLUMN.
• SET NOT NULL has no backfill. Making a nullable column notNull emits a bare ALTER COLUMN "col" SET NOT NULL, which fails on a populated table with any NULL in that column. Backfill first (see below).
• varchar length changes are not detected. The differ compares the type name, not the length, so varchar(50) to varchar(100) produces no statement. Change the length by hand.
• Drops are emitted flagged as destructive. A table or column present in the database but absent from your schema produces a DROP flagged inline as destructive (a destructive-only diff produces a flagged migration, not "nothing to migrate"), and the destructive gate still refuses it until you confirm or pass --allow-destructive. schemaDiff() returns these in drop / the alter plan for programmatic callers.

Flagging destructive statements with --from-diff#

--from-diff derives the same forward and reverse SQL as --auto (the diff into -- UP, the reverse into -- DOWN, with a commented "irreversible, write manually" placeholder when no reverse can be derived), and annotates anything that destroys data:

• A lossy ALTER COLUMN … TYPE in UP, or a DROP TABLE / DROP COLUMN reverse in DOWN, is flagged inline with loud comments and a file-level banner.
• The statement is left intact, so migrate up still refuses it by default until you confirm or pass --allow-destructive.
• Diff warnings the differ won't apply automatically (e.g. enum value removals) are surfaced as -- NOTE: comments.

npx turbine migrate create sync_schema --from-diff

--from-diff cannot be combined with --auto or --recipe. Reach for it when a diff might touch existing data and you want the risky lines called out in the file rather than discovered at apply time.

Changing a column type with existing data#

When a type change needs a real transformation (or the blind USING cast would fail), do not edit the column in place. Use the two-phase expand/contract pattern: add a new column, backfill it in batches, verify, then swap. Every step is reversible until the final drop.

Phase 1, expand. Add the new column as nullable so the write path keeps working, then backfill in bounded batches to avoid a long lock or a bloated transaction:

-- Migration A (UP): add the target column, nullable
ALTER TABLE "orders" ADD COLUMN "total_cents" BIGINT;
 
-- Backfill in batches (run outside a single giant transaction: one
-- statement per batch, repeat until zero rows remain to convert).
UPDATE "orders"
SET "total_cents" = ("total" * 100)::BIGINT
WHERE "id" IN (
  SELECT "id" FROM "orders"
  WHERE "total_cents" IS NULL
  LIMIT 5000
);

Verify before you tighten anything:

SELECT count(*) FROM "orders" WHERE "total_cents" IS NULL;   -- expect 0
SELECT "total", "total_cents" FROM "orders" LIMIT 20;        -- spot-check

Phase 2, contract. Once the new column is fully populated and verified, enforce NOT NULL and swap the names, so no reader ever sees a half-renamed table:

-- Migration B (UP): enforce, then swap.
-- No BEGIN/COMMIT of your own: `turbine migrate` already wraps every file
-- in exactly one transaction, so both renames are atomic as written.
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";

Drop the old column later, in a separate migration deployed after the new column has been serving reads and writes long enough to be sure nothing depends on the old one:

-- Migration C (UP), shipped after B has been live for a while
ALTER TABLE "orders" DROP COLUMN "total_old";

Keeping the drop in its own later migration means Phase 2 stays instantly reversible: you have not lost the original data until Migration C runs.

Scaffold it with --recipe backfill#

0.36 adds a scaffold for that pattern:

npx turbine migrate create widen_order_total --recipe backfill

It generates a migration pre-filled with the expand/contract skeleton (the nullable add, a batched UPDATE backfill block, the SET NOT NULL, and commented swap/drop steps) with placeholders for the table, columns, and conversion expression. Review and adapt every placeholder before applying.

The destructive gate#

Turbine treats data-destroying DDL (DROP TABLE, DROP COLUMN, and similar) as opt-in, never automatic. All three commands run the same scanner; what changes is what happens on a hit:

migrate up and push refuse and ask for the typed confirmation; decline and nothing runs. deploy is the only surface that proceeds without one, because it applies reviewed, merged history in CI where no human can answer a prompt: the review happens when the migration is authored, not at deploy time. push (destructive-scanned since 0.36) remains the fast development path, diffing defineSchema against the live database and applying the difference directly; for production, author real migrations and deploy them.

See also#

• Schema & Migrations for defineSchema, DDL generation, and schemaDiff().
• CLI for every migration command and flag.
• Database Engines for why the migration CLI is Postgres-only today.
• Typed Errors including MigrationError (TURBINE_E006).