Database Schema Versioning | The Garnet Wiki

Database schema versioning is the practice of tracking every structural change to a relational database through reproducible, versioned artifacts. It matters whenever more than one person or process modifies the database, especially in CI/CD pipelines, multi-environment deployments, or when the database schema must stay in sync with application code. Without it, production outages arise from drifted schemas, lost ALTER statements, or inconsistent dev/staging/prod states.

Initializing Versioning

Start by adding a version‑tracking table to the database. This table is the single source of truth for which migrations have been applied. The schema is deliberately lightweight; the table name, column names, and constraints can follow a convention your team owns, but the mechanism is the same across platforms.

CREATE TABLE IF NOT EXISTS schema_migrations (
  version VARCHAR(255) PRIMARY KEY,
  applied_at TIMESTAMP WITH TIME ZONE DEFAULT now()
);

In Git, create a dedicated directory for migration files—commonly db/migrations/ or sql/schema/. Name files in strict lexical order so that a simple sort reflects execution order. A typical naming scheme uses a version prefix followed by a double‑underscore and a descriptive verb: V1__create_users.up.sql, V1__create_users.down.sql. The up file contains additive DDL; the down file contains reverse DDL. If a migration is irreversible, the down file should either be omitted or contain a warning comment.

To bootstrap the version table from the command line (using psql, which is a standard tool listed in the permitted set):

psql "$DATABASE_URL" -c "
CREATE TABLE IF NOT EXISTS schema_migrations (
  version VARCHAR(255) PRIMARY KEY,
  applied_at TIMESTAMP WITH TIME ZONE DEFAULT now()
);
"

Verify the table exists before any migration runs; most automation skips the CREATE TABLE step after the first run because the IF NOT EXISTS guard makes it idempotent.

Writing a Migration

Each migration file is a self‑contained script. The up script must be idempotent where possible—use CREATE TABLE IF NOT EXISTS, ALTER TABLE … ADD COLUMN IF NOT EXISTS, and avoid DROP statements in up files unless the migration is explicitly destructive. Down scripts should mirror the up script’s intent in reverse.

Example V2__add_email_column.up.sql:

ALTER TABLE users ADD COLUMN IF NOT EXISTS email TEXT;

Example V2__add_email_column.down.sql:

ALTER TABLE users DROP COLUMN IF EXISTS email;

If the database system does not support IF NOT EXISTS / IF EXISTS on ALTER TABLE, wrap the statement in a DO block or use a BEGIN … EXCEPTION pattern that the target DB supports. The goal is that re‑running the same file twice does not error out and does not double‑apply changes.

Before committing a migration, run it against a local or staging database copy. The only way to confirm that the up and down scripts are correct is to apply, then roll back, and verify that the schema state returns exactly to what it was before the migration.

Applying Migrations to an Environment

Application of migrations is a deterministic loop: read all migration files sorted by name, check the version table for each, and execute the up script for any that are unrecorded. A minimal bash‑plus‑psql approach that works across Unix‑like systems:

#!/usr/bin/env bash
set -euo pipefail

DB_URL="${DATABASE_URL:?DATABASE_URL not set}"
MIGRATIONS_DIR="db/migrations"

for file in "$MIGRATIONS_DIR"/V*.sql; do
  version="${file##*/}"           # e.g. V1__create_users.up.sql
  version="${version%%.*}"        # strip extension, keep V1__create_users
  recorded=$(psql "$DB_URL" -t -A -c "SELECT 1 FROM schema_migrations WHERE version='$version';")

  if [ -z "$recorded" ]; then
    echo "Applying $file ..."
    psql "$DB_URL" -f "$file"
    psql "$DB_URL" -c "INSERT INTO schema_migrations (version) VALUES ('$version');"
  else
    echo "Skipping $file – already applied."
  fi
done

The loop checks the version table before each run, which prevents duplicate execution and makes the process safe to re‑run after a crash or partial failure. If a migration fails part‑way through, the version table will not contain the new entry, so the loop will retry it on the next run. This is why idempotent SQL is critical.

For teams that prefer a single‑command solution, many organizations wrap the above logic in a Makefile target, a CI job step, or a small Go/Python utility. The underlying mechanism—sorted files, version‑table check, execute‑then‑record—remains the same.

Tracking Applied Versions

After migrations run, the schema_migrations table should reflect the exact state of the database. A quick health check:

SELECT version, applied_at FROM schema_migrations ORDER BY version;

If the table is empty or missing, the initialization step from the previous section was either skipped or failed. Some teams also store a checksum (e.g., md5sum of the file contents) alongside the version string. This adds a layer of detection for file content changes without a version bump, but it is optional and adds maintenance overhead. The bare minimum—version string + timestamp—is sufficient for most workflows.

To detect drift (someone applied a change directly to production without going through the migration pipeline), compare the current schema against the expected state derived from the migration files. A common pattern:

pg_dump --schema-only "$DATABASE_URL" > /tmp/current_schema.sql

cat db/migrations/V*.up.sql > /tmp/expected_schema.sql
diff /tmp/current_schema.sql /tmp/expected_schema.sql

Any diff output indicates either a missing migration or an unsanctioned direct ALTER/CREATE/DROP on the database.

Rolling Back or Redoing

Rolling back a single migration means executing its down file and removing its entry from schema_migrations. The command sequence:

psql "$DATABASE_URL" -f db/migrations/V2__add_email_column.down.sql
psql "$DATABASE_URL" -c "DELETE FROM schema_migrations WHERE version='V2__add_email_column';"

If the down script contains data‑destructive operations (e.g., DROP COLUMN, TRUNCATE), confirm that the data is either disposable or backed up first. Some organizations maintain a “point‑in‑time recovery” window where a DBA can restore from a base backup, but that is a separate disaster‑recovery procedure, not a migration rollback.

Redoing a migration (apply up after down) follows the same loop described in the applying section: the version table will no longer have the entry, so the up script runs again. This is useful after a down‑script fix or after a controlled schema change that needs re‑application.

Sharp Edges and Failure Modes

Several failure modes are silent until they surface in production:

Partial application: A migration script errors out mid‑DDL (e.g., a syntax error on the third statement). The version table may or may not be updated, depending on whether the INSERT into schema_migrations is placed before or after the DDL execution. If the INSERT is after and the DDL fails, the next run will re‑attempt the migration, potentially creating duplicate objects or constraint violations.
Duplicate version entry: Re‑running a migration that already has its version recorded produces the error:

  ERROR: duplicate key value violates unique constraint "schema_migrations_pkey"

This occurs if the version‑check step is bypassed or if two automation instances run concurrently against the same database without coordination.

Transaction boundaries: Some database systems auto‑commit DDL. If a migration consists of multiple ALTER TABLE statements and the database auto‑commits after each, a failure halfway through leaves the schema in a partially altered state. Wrap multi‑statement migrations in an explicit transaction when the DB supports it (BEGIN; ... COMMIT;), or ensure each statement in the up file is independently safe to re‑run.
Drift from direct edits: A developer or ops engineer applies a column change directly to production via a GUI or psql one‑liner. The schema_migrations table does not reflect this, and subsequent automated migration runs may attempt to add the same column again, resulting in an error or, depending on the DB, a no‑op with a warning. Regular drift checks (the pg_dump/diff pattern) are the only reliable safeguard.
File‑naming disorder: If migration files are renamed after being recorded, the version string in the table no longer matches the file name, and the automation loop may skip the file or apply it out of order. Keep file names immutable once committed to the repository.
Concurrent runs: Two CI pipelines or deployment scripts attempting migration application at the same time can both pass the “not yet recorded” check for the same version, insert duplicate rows, or lock the version table. Use database-level locking (e.g., SELECT pg_advisory_xact_lock(12345) in PostgreSQL) or a single source of truth for migration execution if concurrency is a concern.

Understanding these edges before they break in production is what separates a functional schema‑versioning workflow from one that creates more risk than it resolves. The mechanism—sorted files, version‑table guard, idempotent SQL, post‑run verification—is simple; the discipline of applying it consistently is what matters.

This page was rewritten on 17 October 2026. It replaced a templated version that was not specific to its own title. How these pages are written and checked. If anything here is wrong, tell us at [email protected] and we will correct it.