Migrations Basics

7 examples for schema migration fundamentals - 5 basic and 2 intermediate. Versioned SQL in git is the single source of truth for what production looks like.

Prerequisites

mkdir -p db/migrations
git init  # migrations live beside application code

• One ordered history table (flyway_schema_history, liquibase.databasechangelog, or custom).
• Never apply hand-edited DDL in prod without the same SQL in a merged migration file.

Basic Examples

1. First Migration File

-- db/migrations/V001__init_schema.sql
CREATE SCHEMA app AUTHORIZATION app_owner;
 
CREATE TABLE app.users (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email text NOT NULL UNIQUE,
  created_at timestamptz NOT NULL DEFAULT now()
);

• Filename carries order (V001, timestamp prefix, or tool convention).
• Idempotent patterns (IF NOT EXISTS) are tool-specific - Flyway versioned migrations are not re-run.
• Review migrations like application code in PRs.

2. Schema History Table

CREATE TABLE IF NOT EXISTS app.schema_migrations (
  version text PRIMARY KEY,
  applied_at timestamptz NOT NULL DEFAULT now(),
  checksum text
);

• Runner records applied versions after success.
• Checksum detects tampering with already-applied files.
• Never delete rows from history to "fix" deploy - forward-fix with new migration.

Related: Flyway & Liquibase - enterprise runners

3. Separate Migration Role

CREATE ROLE app_owner NOLOGIN;
CREATE ROLE app_migrator LOGIN PASSWORD 'vault';
GRANT app_owner TO app_migrator;
 
-- Migration session
SET ROLE app_owner;
CREATE TABLE app.orders (id bigint PRIMARY KEY);
RESET ROLE;

• Runtime app_api should not own tables or hold DDL rights.
• Migration CI uses same role as production deploy for identical grants.
• SET ROLE lines belong in migration wrapper or connection init for migrator only.

4. Transactional DDL (When Safe)

BEGIN;
CREATE TABLE app.feature_flags (
  key text PRIMARY KEY,
  enabled boolean NOT NULL DEFAULT false
);
INSERT INTO app.feature_flags (key) VALUES ('new_checkout');
COMMIT;

• Most DDL in PostgreSQL is transactional - failed migration rolls back.
• Exceptions: CREATE INDEX CONCURRENTLY, DROP INDEX CONCURRENTLY, VACUUM - cannot run inside transaction block.
• Split unsafe statements into separate migration files with operational notes.

5. Grant in Same Migration

CREATE TABLE app.products (id bigint PRIMARY KEY, sku text NOT NULL);
 
GRANT SELECT ON app.products TO app_readers;
GRANT SELECT, INSERT, UPDATE, DELETE ON app.products TO app_writers;

• Or rely on ALTER DEFAULT PRIVILEGES from bootstrap migration.
• Missing grants cause deploy success but runtime permission errors.
• Test migration against fresh database in CI, not only against dev laptops.

Intermediate Examples

6. Expand/Contract Preview

-- Expand: add nullable column (safe deploy)
ALTER TABLE app.orders ADD COLUMN discount_cents integer;
 
-- App v2 writes column; backfill job fills values
 
-- Contract (later migration): enforce NOT NULL after backfill
ALTER TABLE app.orders ALTER COLUMN discount_cents SET NOT NULL;

• Ship additive changes before destructive ones.
• Two-phase deploy: schema expand, app deploy, data backfill, schema contract.
• Never drop column until no code reads it.

Related: Expand/Contract Pattern - full workflow

7. CI Apply on Ephemeral DB

# pipeline excerpt
services:
  postgres:
    image: postgres:18.4
steps:
  - run: flyway migrate -url=jdbc:postgresql://postgres:5432/test
  - run: psql $TEST_URL -f ci/assert_schema.sql

• Every PR applies full migration chain to empty database.
• Catches ordering bugs and missing grants before merge.
• Match PostgreSQL major in CI to production target major.

Stack versions: This page was written for PostgreSQL 18.4 (stable 18, maintenance 17), pgvector 0.8+, PgBouncer 1.x, Patroni 3.x, and PostGIS 3.5+.