Versioning Your SQL Like Git Commits
The industry standard for database change management has been migration-based tools like Flyway and Liquibase. The mental model is simple: treat database changes like a sequential commit history. You write small, ordered SQL scripts, and the tool ensures they are applied exactly once, in order, to every environment.
A typical migrations directory looks like this:
db/migrations/
├── V1__create_users_table.sql
├── V2__add_email_column.sql
├── V3__add_index_to_email.sql
└── V4__create_orders_table.sql
The naming convention is critical. V1__, V2__, V3__ are version prefixes. The tool uses these to determine order. The double underscore separates the version from the human-readable description.
How the Engine Works
Step 1 — Bootstrap: On first run, the tool creates a tracking table in your database. Flyway calls it flyway_schema_history; Liquibase calls it databasechangelog. This table is the source of truth for what has already been applied.
-- What flyway_schema_history looks like after V1 and V2 are applied
SELECT version, description, checksum, success FROM flyway_schema_history;
version | description | checksum | success
--------+---------------------+------------+--------
1 | create users table | 1234567890 | true
2 | add email column | 0987654321 | true
Step 2 — Diff: On every pipeline run, the tool scans the db/migrations/ directory, computes a checksum for each file, and compares against the history table. Scripts already in the table are skipped. Scripts not in the table are queued for execution.
Step 3 — Apply: The tool executes pending scripts in version order, wrapping each in a transaction where the database supports it. If a script fails, the transaction rolls back and the history table records the failure.
The Immutability Rule
This is the most important rule in migration-based tooling: never modify a script that has already been applied to any environment. The tool stores a checksum of each file at the time it was first run. If you change even a single character — including a comment — the checksum no longer matches, and the tool throws a Checksum Mismatch error and refuses to proceed.
This is a feature, not a bug. It guarantees that every environment (dev, staging, production) has had the exact same SQL executed against it, in the exact same order. The history is an immutable audit log.
Running Flyway in a pipeline is a single command pointed at the target database's connection details and the migrations directory, which validates every migration's checksum, applies whichever versions are still pending in order, and reports the schema's new current version.
Pros: Predictable, explicit, easy to audit, works with any SQL database, massive ecosystem of CI/CD integrations.
Cons: You must write every ALTER TABLE by hand. If someone manually changes the database outside the migration process (called schema drift), the tool has no way to detect or reconcile it — it only knows about changes made through its own scripts.