Migrate¶
Evolve a database schema through small, ordered, reversible migration files instead of ad-hoc ALTER TABLE in a REPL. Works for SQLite, Turso/libSQL, and DuckDB/MotherDuck.
Trigger: /migrate
How It Works¶
- Identify the change and target database (add/drop/rename column, new table, index, backfill).
- Inspect current state — read the live schema and the latest applied migration version. Never write a migration blind.
- Ordered files — zero-padded, timestamped
migrations/NNNN_description.sql, each with-- Upand-- Down. - Track applied versions in a
schema_migrations(version, applied_at)table so migrations are idempotent across dev/prod. - Apply in a transaction where the engine supports DDL transactions; roll back on failure so the schema is never half-migrated.
- NOT NULL on populated tables — add nullable, backfill, then constrain.
Reversible, Idempotent Migrations¶
Bad: irreversible, unsafe on a populated table
No default fails on existing rows; no guard breaks reruns; no down migration.Good: guarded and reversible
Engine Differences¶
| Operation | DuckDB/MotherDuck | SQLite/Turso |
|---|---|---|
| Add column | ALTER TABLE ... ADD |
ALTER TABLE ... ADD |
| Change type | ALTER ... TYPE |
rebuild table (copy/swap) |
| Drop column | supported | 3.35+ only, else rebuild |
Notes¶
- Test every migration against a copy of the real database before production/MotherDuck.
- Keep migrations small and single-purpose — one logical change per file.
- Commit migration files; they are the versioned history of the schema.
- External-source data backfills belong to Backfill, not a DDL migration.
- Credentials stay in the environment, never in a migration file — migrations are committed, so a pasted token becomes git history.