Database Change Automation | The Garnet Wiki

Database Change Automation is the systematic execution of schema and data changes across multiple database instances, ensuring consistency, traceability, and reliability in deployments. It matters when a team ships database modifications as part of every release, especially in environments with multiple environments (dev, staging, production), multiple database types (PostgreSQL, MySQL, SQL Server), and teams managing data alongside code.

Planning and Versioning Database Changes

Database changes begin with a versioned, executable script set, stored in source control. Each change is a migration script with a unique identifier, applied in order. The system tracks which migrations have been applied, using a metadata table in the database: db_version with columns version, applied_at, and description.

Scripts are named with a standard format: YYYYMMDDHHMMSS_<name>.sql. For example, 20231005143000_add_user_roles.sql contains:

-- 20231005143000_add_user_roles.sql
-- Add role column to users table

ALTER TABLE users ADD COLUMN role VARCHAR(20) DEFAULT 'user';
UPDATE users SET role = 'admin' WHERE id IN (1, 2, 3);
COMMENT ON COLUMN users.role IS 'User access level: admin, editor, user';

This script must be idempotent—running it multiple times produces the same result. A common failure is assuming a CREATE TABLE is idempotent when it’s not: CREATE TABLE IF NOT EXISTS must be used, and CREATE INDEX IF NOT EXISTS is required for consistency.

The change is committed to Git with a clear message: db: add user roles column.

Applying Changes to a Target Database

To apply changes, a deployment script runs db apply against a target database. The command reads the db_version table, compares it with the list of migration scripts in the migrations/ directory, and applies missing scripts in order.

db apply \
  --host=prod-db.example.com \
  --port=5432 \
  --database=app_prod \
  --user=deploy \
  --password-file=/secrets/db-password \
  --schema-dir=migrations/

The db command is a standalone tool that:

Connects to the database using host, port, database, user, and password (from file or environment variable).
Loads all migration scripts from schema-dir, parsing filenames to extract version numbers.
Queries the db_version table: SELECT version, applied_at FROM db_version ORDER BY version.
Compares the current state with available scripts and applies missing ones in ascending version order.
For each script, it:
Begins a transaction.
Executes the SQL.
Records the version in db_version with INSERT INTO db_version (version, applied_at, description) VALUES (?, ?, ?).
Handles failures: if any step fails, it rolls back the transaction and logs the error.

If a migration fails, the output includes the full error stack and the failed SQL:

Error: Failed to apply 20231005143000_add_user_roles.sql

SQL: ALTER TABLE users ADD COLUMN role VARCHAR(20) DEFAULT 'user';
Error: column "role" of relation "users" already exists

The tool uses psql under the hood, with --set ON_ERROR_STOP=on and --set CLIENT_MIN_MESSAGES=warning.

Managing Concurrent Changes and Rollbacks

When multiple teams modify the database, changes can conflict. A common failure is two teams applying migrations that alter the same table, but in different orders. To avoid this, a migration must be atomic and include checks for existing structures.

A migration that adds a column and a foreign key should first check for the existence of the parent table:

-- 20231010120000_add_order_items_foreign_key.sql
DO $$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM pg_tables WHERE tablename = 'orders') THEN
    RAISE EXCEPTION 'Table orders does not exist; cannot add foreign key';
  END IF;
END $$;

ALTER TABLE order_items ADD COLUMN order_id INTEGER REFERENCES orders(id);

To support rollbacks, each migration can include a rollback.sql file alongside the apply.sql. The db apply command automatically detects and executes rollback scripts when a migration fails.

A rollback script is named rollback_<version>.sql and is executed on failure or when explicitly requested:

-- rollback_20231005143000.sql
-- Remove role column from users table

ALTER TABLE users DROP COLUMN role;

The db rollback command applies the most recent migration and rolls it back:

db rollback \
  --host=staging-db.example.com \
  --database=app_staging \
  --user=deploy \
  --password=secret \
  --target-version=20231005143000

If the target version is missing, it rolls back all migrations from the current state to that version, applying rollback.sql for each.

Integrating with CI/CD Pipelines

In a CI/CD pipeline, database changes are part of the deployment artifact. The build job generates a database migration package, a single ZIP file containing all migration scripts and a db executable.


name: Deploy to Production
on: [push]

jobs:
  deploy:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v3
      - name: Build database migration package
        run: |
          mkdir -p dist/db
          cp -r migrations/* dist/db/
          cp db dist/db/
          cd dist && zip -r app-db-migration.zip db/

      - name: Deploy to production
        run: |
          ssh [email protected] << 'EOF'
          cd /app
          sudo systemctl stop app
          sudo cp /tmp/app-db-migration.zip .
          unzip app-db-migration.zip -d /app/migrations/
          /app/db apply \
            --host=localhost \
            --database=app_prod \
            --user=deploy \
            --password=$(cat /secrets/db-password) \
            --schema-dir=migrations/
          sudo systemctl start app
          EOF

The pipeline ensures that:

All migrations are tested in a staging database.
The db_version table is consistent across environments.
Failed migrations halt deployment, allowing rollback.

A common failure is missing the db_version table in a new database. The db apply command checks for it and creates it if missing:

CREATE TABLE IF NOT EXISTS db_version (
  version TEXT PRIMARY KEY,
  applied_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
  description TEXT
);

If the table is missing, the tool runs CREATE TABLE and inserts the first migration.

Handling Data-Only Migrations

Not all changes are schema-only. Some migrations require data transformations: seeding, data cleanup, or ETL steps. These are handled as separate, executable steps in the pipeline.

A data migration is defined as a script that runs a sequence of SQL commands, often with parameters. A data-migrations/ directory contains scripts like:

-- data-migrations/20231012090000_migrate_legacy_users.sql
-- Migrate old users with legacy_id to new users table

INSERT INTO users (id, name, email, created_at, role)
SELECT
  id,
  name,
  email,
  created_at,
  CASE
    WHEN legacy_role = 'admin' THEN 'admin'
    WHEN legacy_role = 'editor' THEN 'editor'
    ELSE 'user'
  END AS role
FROM legacy_users
WHERE processed = false;

The db apply command includes a --data-dir flag to process data migrations:

db apply \
  --host=prod-db.example.com \
  --database=app_prod \
  --user=deploy \
  --password=secret \
  --schema-dir=migrations \
  --data-dir=data-migrations

Data migrations are applied after schema migrations and are logged in db_version with a type = 'data'.

A sharp edge: data migrations are not idempotent by default. If a data migration runs twice, it can double the data. To fix this, the migration script includes a processed flag or a batch_id column to track which records have been processed.

Monitoring and Diagnosing Failures

After deployment, the system logs migration status to a monitoring system. The db apply command outputs JSON with metadata:

{
  "success": true,
  "applied": [
    {
      "version": "20231005143000",
      "description": "Add user roles column",
      "applied_at": "2023-10-05T14:30:00Z",
      "duration_ms": 247,
      "errors": []
    }
  ],
  "failed": [
    {
      "version": "20231010120000",
      "description": "Add order items foreign key",
      "error": "column \"order_id\" does not exist",
      "applied_at": "2023-10-10T12:00:00Z"
    }
  ]
}

This JSON is sent to a logging service via curl or journalctl integration.

A common failure mode: a migration script is applied, but the db_version table is not updated. The system detects this by checking the applied_at timestamp. If the db_version table is not updated, the next db apply runs the same migration again.

A silent failure: when a migration script contains a SET search_path command, but the db_version table is not in the default schema. The db apply command must ensure the search path is correct:

SET search_path TO public, db;

Another edge: the db_version table is not transactionally consistent. A migration that inserts into db_version but fails before committing leaves the database in an inconsistent state. The system must use SAVEPOINT and ROLLBACK TO SAVEPOINT to ensure atomicity.

Handling Multiple Database Types

The same automation works across PostgreSQL, MySQL, and SQL Server. The db tool uses a connection string format:

db apply \
  --conn-string="postgres://deploy:[email protected]:5432/app_prod?sslmode=require"

For MySQL, the connection string uses mysql:// and includes charset=utf8mb4 and timeout=30.

Each database type has its own set of DDL commands:

PostgreSQL: CREATE INDEX IF NOT EXISTS, COMMENT ON COLUMN
MySQL: ALTER TABLE ... ADD INDEX, ENGINE=InnoDB
SQL Server: CREATE TABLE ... WITH (DATA_COMPRESSION = PAGE)

The migration scripts are written in the native SQL dialect, and the db tool applies them using the appropriate client (psql, mysql, sqlcmd).

A migration script for a composite index in PostgreSQL:

-- 20231015103000_create_user_search_index.sql
CREATE INDEX IF NOT EXISTS idx_users_search ON users
  USING btree (name, email)
  WHERE role = 'user'
  WITH (fillfactor = 70);

The system detects the database type and applies the script with psql with --set ON_ERROR_STOP=on.

Schema and Data Drift Detection

Over time, databases diverge: one environment has a column not in another. To detect drift, a db diff command compares two databases:

db diff \
  --source=dev-db.example.com/app_dev \
  --target=prod-db.example.com/app_prod \
  --output=diff-report.md

The output includes:

Missing tables
Missing columns
Different data types
Indexes not present
Default values and constraints

The report is used to create a new migration script or to validate a release.

A silent failure: db diff does not detect CHECK constraints or UNIQUE constraints on partial indexes. The tool must explicitly query pg_constraint and pg_index tables for full coverage.

Summary

Database Change Automation is the practice of treating database schema and data as code, versioning and applying changes systematically. It ensures that every database instance reflects the same state across environments. The core workflow—versioned scripts, db apply, db_version tracking, data migrations, and CI/CD integration—forms a robust, repeatable process. Failures are detected early, rollbacks are automated, and monitoring provides visibility into the state of the database. The pattern is portable across platforms, with no reliance on vendor-specific tools or formats.

This page was rewritten on 18 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.