Migrations in Practice

The Schema & Migrations page covers the basics: .sql files with -- UP and -- DOWN sections, tracked in _turbine_migrations. This page is the honest operational guide: the exact workflow, the real limits of auto-diffing, and the recipe for the one 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; only the migration tooling stays on Postgres today. 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; always 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 is the production apply. It runs the same pending migrations as up but never prompts; it applies the files exactly as written. Use it in CI/CD and deployment hooks where there is no human to answer a prompt. 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's transaction rolls back and nothing from it is recorded in _turbine_migrations.
• Stop on first error, partial apply. When applying a batch of pending migrations, the runner processes them 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. So a failed up can leave you partway through the batch: fix the offending migration and re-run to continue from where it stopped.
• 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 you are intentionally rewriting history).
• Advisory lock. The runner takes a per-database Postgres advisory lock before applying, so two concurrent migrate up / deploy 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, including the implicit one that wraps a multi-statement query. 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 loud notice whenever it runs a no-transaction file, and CREATE INDEX CONCURRENTLY can wait a while on other open transactions (that is normal, not a hang). The advisory lock and checksum checks are unchanged.

Out-of-order migrations#

Turbine tracks migrations by name, not by a monotonic sequence number, so there is no strict ordering barrier: any pending migration applies, even if its timestamp predates one already applied. This is what lets two branches merge migrations that were authored in parallel: the older-timestamped file that landed second is simply pending, and the next up / deploy applies it. When the runner applies a migration whose timestamp is older than the most recently applied one, it prints 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 a destructive one. A migration that added a column has a -- DOWN that drops it, so rolling it back trips the destructive gate exactly like any other DROP COLUMN. This is correct by design, not a bug: down will refuse and ask for the typed confirmation. 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, but it has no notion of intent or history. Know its blind spots before you trust a generated file:

• Type changes get a blind cast. A changed column type emits ALTER COLUMN "col" TYPE <newtype> USING "col"::<newtype>. That 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 (for example text to integer on non-numeric rows). For anything non-trivial, use the two-phase recipe instead.
• Renames are not detected. The differ has no way to 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. If you need a rename, write it 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. On a populated table with any NULL in that column, apply fails. Backfill the column first (see below).
• varchar length changes are not detected. The differ compares the underlying type name, not the length, so varchar(50) to varchar(100) produces no statement. Change the length by hand if you need it enforced.
• Table and column drops are emitted flagged as destructive. A table or column present in the database but absent from your schema is surfaced in the diff and in migrate status. create --auto and create --from-diff write the DROP into the migration flagged inline as destructive (a destructive-only diff produces a flagged migration, not a "nothing to migrate"), and the destructive gate still refuses it until you confirm or pass --allow-destructive. Review every flagged line before applying: dropping data is a deliberate act. schemaDiff() also 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 goes into -- UP and the reverse into -- DOWN, with a clearly commented "irreversible, write manually" placeholder when no reverse can be derived), but it also 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 interactively or pass --allow-destructive (see the destructive gate).
• 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. Confirm every row converted and the values are what you expect:

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 atomically in one transaction, so no reader ever sees a half-renamed table:

-- Migration B (UP): enforce, then swap in a single transaction
ALTER TABLE "orders" ALTER COLUMN "total_cents" SET NOT NULL;
 
BEGIN;
ALTER TABLE "orders" RENAME COLUMN "total" TO "total_old";
ALTER TABLE "orders" RENAME COLUMN "total_cents" TO "total";
COMMIT;

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 if you find a problem: you have not lost the original data until Migration C runs.

Scaffold it with --recipe backfill#

Writing that pattern by hand is error-prone, so 0.36 adds a scaffold:

npx turbine migrate create widen_order_total --recipe backfill

This 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 you to fill in the table, columns, and conversion expression. It is a starting template, not a turnkey migration: 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.

• migrate up scans every pending migration for destructive statements. If it finds any, it stops, prints exactly which statements in which files are destructive, and asks for a two-step typed confirmation (the literal phrase destroy my data, then yes) before applying anything. Decline and nothing runs; no data is touched. You can skip the prompt with --allow-destructive when you know what you are doing.
• migrate deploy applies files exactly as written, with no prompt; it is the non-interactive production path. Before it runs anything destructive it prints a one-line NOTICE listing the data-destroying statements, then proceeds. That is by design: production apply is deterministic and unattended, so the review happens when the migration is authored and merged, not at deploy time.
• push: as of 0.36, turbine push runs the same destructive scanner over the statements it is about to apply. Destructive operations require the same typed confirmation as migrate up (or --allow-destructive); without it, push refuses and applies nothing. push is still the fast development path that diffs defineSchema against the live database and applies the difference directly; for production, author real migrations and deploy them.

The ceremony differs by surface. All three run the same scanner; what changes is what happens when they find something destructive:

deploy is the only surface that runs destructive SQL without confirmation. That is deliberate: it is for reviewed, merged history applied in CI, where there is no human to answer a prompt.

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).