# dbmate and Sqitch > Lightweight, framework-agnostic database migrations: dbmate's simple timestamped SQL files and Sqitch's dependency-graph approach with verify and revert. --- # dbmate and Sqitch You have a database and a problem most frameworks pretend doesn't exist: your schema changes over time, and those changes have to travel from your laptop to staging to production in the same order, every time, without anyone running a SQL file by hand and hoping. You don't have an ORM. You don't want one. You want plain SQL, version-controlled, applied predictably. That's exactly the gap these two tools fill, and they fill it in two very different ways. This guide gives you a working mental model of both, then shows you the day-to-day commands, then walks into the parts that bite people in production. ## How to read this Read it in order the first time. Phase 1 builds the mental model that makes everything else obvious: both tools turn schema change into ordered, repeatable SQL, but dbmate orders by timestamp and Sqitch orders by a dependency graph. Phase 2 is the muscle memory: creating, applying, and rolling back changes with each tool. Phase 3 is the production reality: drift, failed deploys mid-flight, verify scripts, and the choices you'll regret if you skip them. If you already use one tool and are eyeing the other, you can skip to Phase 2 and read the two halves side by side. ## The phases 1. [Phase 1: Two ways to order change](01-the-mental-model.md) - what these tools actually are, and why timestamps and dependency graphs are different answers to the same question. 2. [Phase 2: The everyday loop](02-the-everyday-loop.md) - create, apply, and revert migrations with dbmate and Sqitch, command by command. 3. [Phase 3: Production reality](03-production-reality.md) - drift, half-applied deploys, verify scripts, and the gotchas that cost you a weekend. --- # Two ways to order change Here's the situation these tools were built for. You changed your schema on your laptop - added a table, dropped a column, added an index. It works locally. Now that same change has to reach staging and production, in the right order, applied exactly once, with no human pasting SQL into a prod console at 11pm. Multiply that across a team where three people are all changing the schema in the same week, and "remembering what's been applied" stops being a strategy. A migration tool's whole job is to answer two questions reliably: **what changes exist**, and **which ones has this particular database already seen**. Everything else is detail. dbmate and Sqitch both answer those questions for plain SQL - no ORM, no framework, no code-generated migrations. They differ, deeply, on how they decide the *order* of changes. That one decision shapes everything about how each tool feels. ## The shared idea: SQL files plus a ledger Every migration tool, framework or not, works the same way underneath. You write the change as SQL. The tool keeps a small **ledger table** inside your database recording which changes have been applied there. When you run the tool, it compares the files on disk against the ledger, and applies whatever's missing. ```text files on disk ledger in the database ------------- ---------------------- add_users add_users ✓ applied add_posts add_posts ✓ applied add_index_on_email (not present) ← will run now ``` *What just happened:* The tool saw three change files but a ledger that only knows about two, so it runs the third and writes a new ledger row. Run it again and nothing happens, because now all three are recorded. That idempotence - "apply what's missing, skip what's done" - is the entire point. The thing to internalize: **the database itself is the source of truth for what's been applied.** Not a config file, not your memory. Each environment carries its own ledger, so prod knows prod's history and staging knows staging's. The files in your repo are the menu; the ledger is the receipt. ## dbmate: order by timestamp dbmate's answer to "what order?" is the simplest one that works: **a timestamp in the filename.** When you create a migration, dbmate prefixes it with the current UTC time down to the second. ```text db/migrations/ 20260615120301_create_users.sql 20260615142200_add_email_index.sql 20260620090145_create_posts.sql ``` *What just happened:* Three migrations, ordered by the moment they were created. dbmate applies them in ascending filename order, which is the same as chronological order. The ledger (a table called `schema_migrations`) stores just the timestamp portion of each applied file. Each file holds both directions of the change, separated by magic comments: ```sql -- migrate:up CREATE TABLE users ( id SERIAL PRIMARY KEY, email TEXT NOT NULL UNIQUE ); -- migrate:down DROP TABLE users; ``` *What just happened:* One file describes how to apply the change (`up`) and how to undo it (`down`). dbmate reads the markers to know which block to run. The `down` block is what makes rollback possible - and if you leave it empty, rollback for that step does nothing. dbmate is a single small binary, written in Go, with no runtime dependency on your application's language. The same tool migrates a Rails app, a Python service, and a Rust service identically. That language-agnosticism is the reason people reach for it over a framework's built-in migrator. ## Sqitch: order by dependency graph Sqitch makes the opposite choice, and it's the more interesting one. It says: **timestamps are a lie about ordering.** Two teammates working in parallel both create a migration "after" the current tip, but neither's change actually depends on the other. A timestamp forces a false order. What actually matters is *real* dependencies - this change needs that table to exist first. So Sqitch has no timestamps and no version numbers. Each change is a **named change** with explicitly declared dependencies, and the changes form a directed graph. Sqitch deploys them in an order that respects the graph (recorded in a plain-text file called `sqitch.plan`). ```text users ──requires──> posts ──requires──> comments │ └──requires──> post_tags ``` *What just happened:* `comments` and `post_tags` both declare they require `posts`, which requires `users`. Sqitch reads these declared dependencies and deploys in an order that never violates them. There's no "what time was this made" - there's only "what does this need to already exist." And Sqitch splits each change into **three scripts**, not two: ```text deploy/add_users.sql -- make the change revert/add_users.sql -- undo the change verify/add_users.sql -- prove the change worked ``` *What just happened:* `deploy` and `revert` mirror dbmate's up/down. The third script, `verify`, is Sqitch's signature feature: a script that fails if the change didn't actually take. We'll use it for real in Phase 3 - for now, hold the idea that Sqitch can *check its own work*, not assume the deploy succeeded. Sqitch is heavier than dbmate - a Perl application, configured per project, with its own vocabulary (`add`, `deploy`, `revert`, `verify`, `tag`). You pay that weight for the dependency graph and the verify step. ## The mental model, side by side | | dbmate | Sqitch | |---|---|---| | Orders changes by | timestamp in filename | declared dependencies (a graph) | | Files per change | one (`up` + `down`) | three (`deploy`, `revert`, `verify`) | | Identity of a change | its timestamp | a human-given name | | Can verify a deploy? | no | yes, via verify scripts | | Footprint | tiny single binary | full app, per-project config | > Neither is "better." dbmate is right when you want the smallest possible tool and your changes naturally come in a line. Sqitch is right when changes branch and merge across a team, or when "did this actually apply correctly?" is a question you need the tool to answer, not you. Hold both pictures: **a line of timestamps** versus **a graph of named, verifiable changes.** Everything in the next two phases is the commands that make each picture real. ```quiz [ { "q": "What is the ultimate source of truth for which migrations have already been applied to a given database?", "choices": ["A config file in the repo", "A ledger table inside that database", "The newest filename in the migrations folder", "The developer's memory"], "answer": 1, "explain": "Each environment carries its own ledger table recording applied changes, so prod and staging each know their own history." }, { "q": "How does dbmate decide the order in which to apply migrations?", "choices": ["By a dependency graph", "By alphabetical change name", "By the timestamp prefix in the filename", "By a manually edited version file"], "answer": 2, "explain": "dbmate prefixes each migration with a UTC timestamp and applies them in ascending order, which is chronological." }, { "q": "What does Sqitch's third script per change - the verify script - do?", "choices": ["Re-runs the deploy to be safe", "Fails if the change did not actually take effect", "Generates the revert automatically", "Records a timestamp in the plan"], "answer": 1, "explain": "Verify scripts let Sqitch check its own work: they fail when the deploy didn't truly succeed, rather than assuming it did." } ] ``` --- # The everyday loop The loop is the same shape for both tools: make a change, apply it, and - when you're wrong, which you will be - undo it. What differs is the vocabulary and the number of files you touch. We'll run each tool through the same small story: create a `users` table, then add an index, then walk a rollback. Do this once with each and the commands stick. ## dbmate: create, up, down dbmate finds your database through a connection URL. It reads `DATABASE_URL` from the environment (and from a `.env` file in the current directory if present), so set it once. ```bash export DATABASE_URL="postgres://app:secret@localhost:5432/myapp?sslmode=disable" ``` *What just happened:* dbmate now knows which database to talk to. The scheme (`postgres://`, `mysql://`, `sqlite:`) is also how dbmate picks the right driver - there's no separate config for "what database am I using." Create your first migration: ```bash $ dbmate new create_users Creating migration: db/migrations/20260615120301_create_users.sql ``` *What just happened:* dbmate stamped the current UTC time onto the name and dropped an empty file with `migrate:up` / `migrate:down` markers already in it. The name after `new` is just a human label; the timestamp is what orders it. Fill it in: ```sql -- migrate:up CREATE TABLE users ( id SERIAL PRIMARY KEY, email TEXT NOT NULL UNIQUE ); -- migrate:down DROP TABLE users; ``` Apply everything pending: ```bash $ dbmate up Applying: 20260615120301_create_users.sql Writing: ./db/schema.sql ``` *What just happened:* dbmate ran the `up` block, recorded the timestamp in the `schema_migrations` ledger table, and then dumped the full current schema to `db/schema.sql`. That schema dump is a feature, not noise - it's a single file showing the database's *current* shape, useful for code review and for spinning up a fresh database fast. Add a second migration the same way, then check where you stand: ```bash $ dbmate status [X] 20260615120301_create_users.sql [ ] 20260615142200_add_email_index.sql Applied: 1 Pending: 1 ``` *What just happened:* `[X]` means applied (it's in the ledger), `[ ]` means pending. `status` is a read-only diff between disk and ledger - run it any time you're unsure what `up` would do. Now undo. `dbmate down` rolls back the **single most recent** applied migration: ```bash $ dbmate down Rolling back: 20260615120301_create_users.sql ``` *What just happened:* dbmate ran that file's `migrate:down` block (`DROP TABLE users`) and removed its row from the ledger. One `down` = one step back. There's no "down to a specific version" - you call `down` repeatedly, newest first. This is why the `down` block matters: an empty one means `dbmate down` succeeds but changes nothing, leaving you stuck. The everyday dbmate loop, in full: ```text dbmate new create a timestamped up/down file edit the file write the SQL for both directions dbmate up apply all pending, refresh schema.sql dbmate status see applied vs pending dbmate down roll back the newest applied migration ``` ## Sqitch: add, deploy, verify, revert Sqitch is project-based. You initialize once, naming your engine and a project name: ```bash $ sqitch init myapp --engine pg Created sqitch.conf Created sqitch.plan Created deploy/ Created revert/ Created verify/ ``` *What just happened:* Sqitch wrote a config (`sqitch.conf`), an empty plan file (`sqitch.plan`, the ordered list of changes), and the three script directories. Nothing has touched your database yet - this is purely project scaffolding. Add a change. Note you name it, you don't get a timestamp: ```bash $ sqitch add users -n "Add users table" Created deploy/users.sql Created revert/users.sql Created verify/users.sql Added "users" to sqitch.plan ``` *What just happened:* Sqitch created all three scripts and appended a line to `sqitch.plan`. The `-n` is the change note (like a commit message). The change is now in the plan but **not yet deployed** - the plan is intent, the database is reality. Fill in the three scripts. Deploy makes it, revert unmakes it, verify proves it: ```sql -- deploy/users.sql CREATE TABLE users ( id SERIAL PRIMARY KEY, email TEXT NOT NULL UNIQUE ); ``` ```sql -- revert/users.sql DROP TABLE users; ``` ```sql -- verify/users.sql SELECT id, email FROM users WHERE false; ``` *What just happened:* The verify script does a harmless query that *only succeeds if the table and columns exist*. `WHERE false` returns no rows but still errors out if `users` or its columns are missing. That's the trick of a verify script - make a query that's cheap when correct and throws when not. Now deploy. You point Sqitch at a target database: ```bash $ sqitch deploy db:pg://app:secret@localhost:5432/myapp Adding registry tables to database myapp Deploying changes to db:pg://... + users .. ok ``` *What just happened:* On first run Sqitch created its own registry (its ledger - a `sqitch` schema with tables tracking deployed changes), then ran `deploy/users.sql` and recorded it. The `+ users .. ok` is one deployed change. Tip: define this target once in `sqitch.conf` (e.g. a target named `prod`) so you type `sqitch deploy prod` instead of the full URL. Add a second change that *depends on the first*, and that dependency is the whole reason to use Sqitch: ```bash $ sqitch add email_index --requires users -n "Index users.email" ``` *What just happened:* `--requires users` writes the dependency into the plan. Sqitch will refuse to deploy `email_index` to a database that doesn't already have `users` deployed - the graph is enforced, not advisory. Check your work and walk a rollback: ```bash $ sqitch verify db:pg://app:secret@localhost:5432/myapp * users ........ ok * email_index .. ok Verify successful $ sqitch revert --to users db:pg://app:secret@localhost:5432/myapp Revert all changes after "users" from myapp? [Yes] yes - email_index .. ok ``` *What just happened:* `verify` ran every verify script against the live database and confirmed each change actually took. Then `revert --to users` undid everything deployed *after* `users`, in reverse order, leaving `users` itself in place. Unlike dbmate's one-step `down`, Sqitch reverts **to a named point** - you say where to land, not how many steps. The everyday Sqitch loop, in full: ```text sqitch add [--requires X] create deploy/revert/verify, add to plan edit the three scripts write make / unmake / prove SQL sqitch deploy apply pending changes in graph order sqitch verify run all verify scripts against the DB sqitch revert --to roll back to a named change ``` ## For builders: pick the loop that matches your team If you're solo or your schema changes come in a tidy line, dbmate's two-file, timestamp-ordered loop is less to think about and a smaller binary to install in CI. If multiple people change the schema in parallel, or you genuinely want the tool to verify deploys (think compliance, think "prove the migration worked before the app starts"), Sqitch's three-file graph earns its extra ceremony. You can wire either into the same place in your pipeline - Phase 3 is about making sure that pipeline survives the bad days. ```quiz [ { "q": "What does a single `dbmate down` command do?", "choices": ["Rolls back to a chosen version", "Rolls back every applied migration", "Rolls back only the most recently applied migration", "Rolls back nothing unless you pass a count"], "answer": 2, "explain": "dbmate down reverts exactly one step - the newest applied migration. To go further you run it repeatedly." }, { "q": "In Sqitch, what does `--requires users` on a new change accomplish?", "choices": ["Copies the users deploy script", "Declares a dependency so Sqitch won't deploy this change unless users is already deployed", "Runs the users verify script first", "Adds a timestamp linking the two"], "answer": 1, "explain": "Dependencies are written into the plan and enforced: Sqitch refuses to deploy a change whose required predecessors aren't present." }, { "q": "How does Sqitch's revert differ from dbmate's down?", "choices": ["Sqitch reverts to a named change you specify; dbmate steps back one migration at a time", "They are identical", "dbmate reverts to a version; Sqitch only undoes the last change", "Neither tool can revert"], "answer": 0, "explain": "Sqitch revert --to lands at a named point, undoing everything after it; dbmate down undoes a single newest step." } ] ``` --- # Production reality The demos in Phase 2 always worked. Production doesn't. A migration fails halfway. Two teammates pick the same timestamp. The revert script you never tested turns out to be wrong on the day you need it. This phase is the set of failures that actually happen with plain-SQL migration tools, and how each tool helps or doesn't. ## The half-applied migration This is the one that ruins evenings. Your migration has two statements; the first succeeds, the second fails. Where does that leave the database? The answer depends entirely on **transactions**, and most engines wrap a migration in one by default - but not all DDL is transactional. On PostgreSQL most DDL *is* transactional, so a failed migration rolls back cleanly. On MySQL, many DDL statements (like `CREATE TABLE`, `ALTER TABLE`) cause an **implicit commit** - they can't be rolled back, so a failure mid-migration leaves the earlier statements permanently applied. ```sql -- migrate:up ALTER TABLE users ADD COLUMN phone TEXT; -- on MySQL: implicitly commits ALTER TABLE users ADD COLUMN phon TEXT; -- typo, fails - but the first ALTER already stuck ``` *What just happened:* On PostgreSQL the whole thing rolls back and the table is untouched. On MySQL the first `ALTER` is already committed, the second errors, and now `phone` exists but the ledger has *no* record of this migration. The tool thinks the migration is pending; the database disagrees. That mismatch is the worst state to be in. Two defenses, and use both: - **Keep migrations small** - ideally one logical change per migration. A migration that does one thing can't be half-done. - **Know your engine's transaction rules.** On Postgres you're mostly safe. On MySQL, assume no rollback for DDL and design so each migration is atomic on its own. dbmate runs each migration in a transaction where the engine supports it, and lets you opt a migration out with `-- migrate:up transaction:false` when you need a statement that can't run inside one (for example, Postgres's `CREATE INDEX CONCURRENTLY`). ```sql -- migrate:up transaction:false CREATE INDEX CONCURRENTLY idx_users_email ON users (email); ``` *What just happened:* `CREATE INDEX CONCURRENTLY` builds an index without locking writes, but Postgres forbids it inside a transaction. The `transaction:false` flag tells dbmate to run this migration unwrapped - at the cost that if it fails partway, there's no automatic rollback. Sqitch handles the same need with `verify` plus careful scripts; some engines let you mark a Sqitch deploy script to run without a transaction in `sqitch.conf`. ## Schema drift: when the database stops matching the files Drift is when the live schema no longer matches what your migrations describe. Someone ran an `ALTER` by hand in prod during an incident. A migration was edited *after* it was applied somewhere. A failed-but-not-recorded migration (above) left a column the ledger doesn't know about. The ledger only tracks *which files ran*, not *whether the result still matches the files*. So drift is invisible to a naive `up` - the tool sees nothing pending and reports all clear, while the actual columns differ. This is where dbmate's `db/schema.sql` dump earns its keep. Because dbmate rewrites that file after every migration, you can diff the committed schema against a fresh dump of production: ```bash $ pg_dump --schema-only "$PROD_URL" > /tmp/prod-schema.sql $ diff db/schema.sql /tmp/prod-schema.sql ``` *What just happened:* A non-empty diff means prod's actual structure has drifted from what your migrations produce. dbmate won't catch this for you - but the schema dump gives you something concrete to compare against, which a tool that only keeps a ledger does not. Sqitch's answer to a related question is `sqitch verify`: run all verify scripts against prod and confirm each deployed change still holds. It won't catch a *new* hand-added column, but it will catch a deployed change that's been broken or partially reverted - verify scripts fail loudly when reality stops matching intent. > The rule that prevents most drift: **never edit a migration that has been applied to any shared environment.** Once it's run on staging or prod, that file is history. Need a fix? Write a *new* migration. Editing an applied file means the ledger says "ran" while the file now says something different - drift you authored yourself. ## Timestamp collisions and merge order (dbmate) dbmate's timestamps are per-second. Two teammates branching off the same point can create migrations seconds apart, and once both branches merge, the *filename order* may not match the order anyone deployed in. Worse, two migrations can collide if generated in the same second. Because dbmate applies strictly by ascending timestamp, a migration with an earlier timestamp that merges *later* will sit "before" already-applied ones in the list - and dbmate won't go back and run it if a later-timestamped migration is already recorded. The fix is discipline: rebase, regenerate the timestamp on the newer migration if there's a clash, and re-run `dbmate status` after every merge to confirm what's actually pending. This is precisely the problem Sqitch's graph sidesteps. Sqitch doesn't order by time; it orders by declared dependencies, so two independent changes merging together both deploy, and a change that requires another can't jump ahead of it regardless of when its line was added to the plan. ## Reverts you never tested are not reverts Both tools let you write a down/revert script. Neither forces you to make it *correct*. A revert that drops a table you've since added data to, or one that's subtly wrong, is a trap that springs only during an incident - the worst possible time to discover it. ```text the lie: "we have rollbacks, we wrote down scripts" the truth: a revert is only real if you've run it against a DB shaped like prod ``` *What just happened:* Writing the script is necessary but not sufficient. The practice that makes rollback real: in CI, after applying a migration, immediately revert it and re-apply it. dbmate gives you `dbmate up && dbmate down && dbmate up`; Sqitch gives you `sqitch deploy && sqitch revert && sqitch deploy`. If that round-trip fails in CI, you found a broken revert on a calm Tuesday instead of during an outage. And be clear-eyed about what revert *can't* undo: a migration that dropped a column and lost its data cannot be reverted into existence. For destructive changes, the real rollback plan is a backup and a forward-fix migration, not a `down` script. Reverts handle structural mistakes, not data loss. ## In the wild: where they sit in a pipeline Both tools are single commands with no application runtime, so they slot anywhere - a CI step, a Kubernetes init container, a line in a deploy script. The common shape: run migrations as a discrete, gated step *before* the new app version starts, never lazily on first request. Gate prod migrations behind the same review as code. And whichever tool you choose, the non-negotiables are the same across every migration tool: small atomic changes, never edit applied migrations, and test your reverts. For the broader strategy of safe schema change - expand/contract, backfills, zero-downtime sequencing - see [/guides/database-migrations](/guides/database-migrations). ```quiz [ { "q": "Why is a failed migration especially dangerous on MySQL compared to PostgreSQL?", "choices": ["MySQL has no ledger table", "Many MySQL DDL statements implicitly commit and can't be rolled back, leaving a half-applied state", "MySQL ignores timestamps", "PostgreSQL can't run migrations at all"], "answer": 1, "explain": "MySQL DDL like CREATE/ALTER TABLE causes implicit commits, so a mid-migration failure leaves earlier statements applied with no ledger record - a drift you didn't intend." }, { "q": "What is the single rule that prevents most self-inflicted schema drift?", "choices": ["Always use SQLite locally", "Never edit a migration that has already been applied to a shared environment", "Always run migrations on first request", "Delete the ledger table monthly"], "answer": 1, "explain": "Once a migration has run on staging or prod it is history; fixes go in a new migration. Editing an applied file makes the ledger and the file disagree." }, { "q": "What actually makes a revert/down script trustworthy?", "choices": ["Writing it at all", "Having it generated automatically", "Running the deploy-revert-redeploy round-trip in CI against a prod-shaped database", "Marking it transaction:false"], "answer": 2, "explain": "A revert is only real once you've exercised it. CI that applies, reverts, and re-applies catches broken rollbacks before an incident does." } ] ```