Sqitch: Database Change Management Without Version Numbers

Sqitch is a database change management tool that refuses two assumptions almost every other migration tool makes: that changes need a version number, and that a tool should generate SQL for you. Instead, every change has a name, declares which other changes it depends on, and comes as three native SQL scripts: deploy, revert and verify. The result is a tool that DBAs tend to like and application developers tend to find demanding. This article explains the model, the daily commands, and when the trade is worth it.

The plan file is the source of truth#

A Sqitch project is a directory with a sqitch.plan, a sqitch.conf and three script directories. The plan lists changes in order, one per line, with dependencies in brackets, a timestamp, the planner and a note:

%syntax-version=1.0.0
%project=petclinic
%uri=https://schemadrawer.com/petclinic

owners_and_pets 2024-01-01T00:00:00Z schemadrawer <[email protected]> # Create owners, types and pets.
vets [owners_and_pets] 2024-01-01T00:01:00Z schemadrawer <[email protected]> # Create vets, specialties and vet_specialties.
visits [vets] 2024-01-01T00:02:00Z schemadrawer <[email protected]> # Create visits.

There is no V3 here. visits is the change that creates visits, and it requires vets, which requires owners_and_pets. Order in the plan still matters (Sqitch deploys top to bottom), but identity is the name plus the project, and the dependency list is what Sqitch checks before deploying or reverting anything. The configuration file is minimal, naming the engine and, optionally, a default target:

[core]
    engine = pg

Three scripts per change#

sqitch add vets --requires owners_and_pets -n 'Create vets' appends the plan line and creates deploy/vets.sql, revert/vets.sql and verify/vets.sql from templates. You fill in all three, in the SQL of your engine:

-- Deploy petclinic:vets

CREATE TABLE vets
(
    id         INT         NOT NULL,
    first_name VARCHAR(50) NOT NULL,
    last_name  VARCHAR(50) NOT NULL,
    CONSTRAINT pk_vets PRIMARY KEY (id)
);

-- Verify petclinic:vets

SELECT id, first_name, last_name FROM vets WHERE 0 = 1;

-- Revert petclinic:vets

DROP TABLE vets;

The verify script is the distinctive part. It is run after deploy (with sqitch deploy --verify) or on demand with sqitch verify, and it must fail if the change is not in place. Selecting the expected columns with an always-false predicate is the idiom: it errors if a column is missing and returns nothing otherwise. Verify scripts turn "the migration ran" into "the schema is what the migration promised", which is a stronger statement and one most tools never make.

Dependencies instead of numbers#

Named dependencies buy three things. Merges stop being renumbering exercises: two branches that each add a change do not collide on a version, they just both append to the plan, and the only conflict is the textual one in sqitch.plan, which git shows plainly. Dependencies can cross projects (otherproject:change), which lets a shared schema be a Sqitch project of its own. And a change can be reworked: after a tag, sqitch rework vets creates deploy/[email protected] alongside a fresh deploy/vets.sql, so a view or function can be redefined under the same name with full history, something version-numbered tools express as yet another migration.

Tags mark releases in the plan (sqitch tag v1.0.0 -n 'First release') and are the natural targets for revert: sqitch revert --to @v1.0.0 undoes everything deployed after the tag, running each revert script in reverse order and refusing if a later change that depends on one being reverted would be left dangling.

The registry#

Sqitch records what it did in a registry, a schema named sqitch on PostgreSQL, with tables for projects, changes, dependencies, tags, events and releases. Unlike a single history table, the registry keeps who deployed which change when, every revert and fail event, and the dependency graph as deployed, so sqitch log and sqitch status can answer questions like "what changed in production last Tuesday" without a separate audit system. Tools that read the database schema for documentation need to skip that registry schema, or its six plainly named tables show up next to yours.

Everyday commands#

sqitch init petclinic --engine pg --uri https://example.com/petclinic
sqitch add owners_and_pets -n 'Create owners, types and pets.'
sqitch add vets --requires owners_and_pets -n 'Create vets.'
sqitch deploy db:pg://user@localhost/petclinic --verify
sqitch status db:pg://user@localhost/petclinic
sqitch tag v1.0.0 -n 'First release'
sqitch revert db:pg://user@localhost/petclinic --to @v1.0.0
sqitch rebase db:pg://user@localhost/petclinic

Targets are database URIs or names configured in sqitch.conf, so the same plan deploys to a laptop, a CI database and production by changing one argument. rebase is revert followed by deploy, the development loop for a change you are still editing. Installing Sqitch means installing Perl modules or using the official sqitch/sqitch Docker image, which is what most CI pipelines do.

Strengths#

• Native SQL, nothing generated. What is in deploy/ is what runs, in the full dialect of the engine, including procedural code, extensions and permissions.
• Verify as a first-class step. Deployment ends with a proof, not a hope.
• Revert designed in. Every change has a revert script from the start, and revert is dependency-aware.
• Rework. Redefining a function or view under its own name, with history, is built in.
• Engine coverage. PostgreSQL, MySQL, MariaDB, SQLite, Oracle, Snowflake, Vertica, Exasol, Firebird and CockroachDB; SQL Server is not among them.

Weaknesses#

• Three scripts per change is real work. Teams that would never write a down migration in another tool will feel the cost immediately, and an empty revert script defeats the point.
• The learning curve. Plan syntax, tags, rework and the target model are more concepts than "put numbered files in a directory".
• No ORM integration. Nothing generates changes from models; you write DDL.
• A smaller ecosystem. Fewer framework integrations, fewer tutorials, and a Perl dependency that surprises people, even if Docker hides it.
• Plan file conflicts. Concurrent branches conflict in sqitch.plan often; they are trivial to resolve but they happen.

When to choose it#

Sqitch fits teams whose database is a product in its own right: a PostgreSQL schema with functions, triggers, policies and views maintained by people who think in SQL, deployed to several environments and customers, where being able to prove a deployment and undo it matters. It also fits organisations with database engineers who own the schema separately from application teams. It fits less well where migrations are a by-product of an ORM, where the schema is a handful of tables, or where nobody will write verify scripts honestly. If that is your situation, the runner-style tools compared in the Go migration tools comparison or the pair in Flyway vs Liquibase will serve you with less ceremony.

Where SchemaDrawer fits#

A Sqitch plan describes dependencies, not the resulting schema, and the resulting schema is what people want to see. SchemaDrawer takes the project directory, sqitch.plan, sqitch.conf and the deploy, revert and verify directories, runs sqitch deploy against a throw-away PostgreSQL, MySQL, MariaDB, Oracle or SQLite of the version you choose, and returns an ER diagram of the tables, columns, primary keys and foreign keys that came out, with Sqitch's own registry tables left out of the picture. The Sqitch page lists the upload layout, and the PostgreSQL page the versions available.

Key takeaways#

• Changes have names and dependencies, not version numbers; the plan file is the source of truth.
• Every change is deploy, revert and verify, in native SQL.
• Tags, rework and a rich registry give history and rollback most tools lack.
• The price is more writing, more concepts and a smaller ecosystem.
• Choose it when the database is a first-class product maintained by people who write SQL.