Review SQL Migrations in 30 Seconds: Seed, Migrate, Compare

Reviewing application code, you judge behavior from the test output, not the syntax of the source. Migration reviews should work the same way, and a reviewer actually needs:

1. Before state: What does the data look like right now?
2. After state: What does the data look like after the migration?
3. Diff: What changed, and does the change match the stated intent?

If a migration says "consolidate first and last name into full_name," the reviewer should see a sample of real rows before and after, not just imagine them.

The catch is that this needs data, and a local database of 12 tidy rows like "John Smith" and "Jane Doe" will never surface the NULL concatenation bug, the stray spaces, or the truncation.

The fix is a three-step workflow that takes about 30 seconds of hands-on time.

Start with a database on your current schema (before the migration) and fill it with realistic variety — NULLs, edge-case strings, varying lengths, the messiness that accumulates in production.

# Seed users with realistic variation
seedfast seed --scope "500 users with varied names, some with NULL first or last names, international characters"

Seedfast generates data that reflects real-world patterns, so some names run short and others long, some fields come back NULL, and unicode shows up where it would in production. That is the real difference from hand-crafted fixtures, which only hold the cases you already thought of. You describe the variety you want in plain English as a scope, and since Seedfast reads your live schema, it seeds FK-valid rows without a fixture script to keep in sync.

Capture the before state, run the migration, capture the after state.

# Snapshot the before state
psql $DATABASE_URL -c "SELECT first_name, last_name FROM users LIMIT 20" > before.txt

# Run the migration
psql $DATABASE_URL -f migrations/20260226_consolidate_user_names.sql

# Snapshot the after state
psql $DATABASE_URL -c "SELECT full_name FROM users LIMIT 20" > after.txt

Now look at what actually happened:

# Check for NULLs that shouldn't be there
psql $DATABASE_URL -c "SELECT count(*) FROM users WHERE full_name IS NULL"

# Check for unexpected whitespace
psql $DATABASE_URL -c "SELECT full_name FROM users WHERE full_name LIKE '%  %' OR full_name LIKE ' %' LIMIT 10"

# Check for truncation
psql $DATABASE_URL -c "SELECT full_name, length(full_name) FROM users ORDER BY length(full_name) DESC LIMIT 5"

Clean results here mean the migration behaves. If they come back dirty, you have just caught a production bug from your laptop in under a minute.

The whole change is a single backfill line:

UPDATE users SET full_name = first_name || ' ' || last_name;

The trouble shows up on any row where first_name or last_name is NULL, which produces a NULL full_name, since in PostgreSQL NULL concatenated with anything is NULL.

What the reviewer sees after seeding:

 count
-------
   47
(1 row)

47 users just lost their names, and the fix is obvious once you see it. Wrap each column in COALESCE so a NULL becomes an empty string and can't poison the whole expression:

UPDATE users SET full_name = COALESCE(first_name, '') || ' ' || COALESCE(last_name, '');

The migration:

ALTER TABLE orders ALTER COLUMN shipping_address SET NOT NULL

The reviewer reads this and thinks, "Good, shipping address should be required," and approves. But in production, 8% of orders are digital-only and have no shipping address, so the migration fails on deploy.

After seeding 500 orders with realistic variety:

psql $DATABASE_URL -c "SELECT count(*) FROM orders WHERE shipping_address IS NULL"

 count
-------
    41
(1 row)

The reviewer now knows the migration needs a backfill step first, or a different path for digital orders.

Here the change retypes a money column:

ALTER TABLE products ALTER COLUMN price TYPE INTEGER USING price::INTEGER;

The stated intent is "we don't need decimal precision, let's simplify," and the cast syntax checks out, so it gets approved. But ALTER COLUMN ... TYPE rewrites every value through the USING cast, and casting numeric to integer rounds each price to a whole number.

After seeding:

  price
---------
  29.99
  14.50
  99.95
  7.25
  149.99

Every one of those prices is rounded to the nearest whole number, so $29.99 becomes $30 and $7.25 becomes $7. Across thousands of products, that drift adds up to thousands of dollars in the reports, and the reviewer catches it instantly by looking at the data, not the DDL.

The migration:

ALTER TABLE users ADD COLUMN role VARCHAR(20) NOT NULL DEFAULT 'user'

It looks harmless enough, but after seeding and inspecting the existing rows:

   role   | count
----------+-------
 user     |   412
 admin    |    23
 manager  |    65

Look again, and every existing row now has role = 'user', including the 23 admins and 65 managers. If the application stored role information in a different table or field before, this migration just demoted every admin to a regular user, since the DEFAULT lands on every existing row when the column is new.

The fix is to add the column as nullable first, backfill from the authoritative source, then add the NOT NULL constraint.

The last one adds a uniqueness guarantee on email:

ALTER TABLE customers ADD CONSTRAINT unique_email UNIQUE (email)

After seeding:

         email          | count
------------------------+-------
 john.smith@example.com |     3
 info@company.org       |     2

On deploy, the migration fails, because duplicate emails already exist, whether from a legacy import or a bulk insert that skipped its uniqueness check, and a correct constraint can't paper over data that isn't ready for it.

Five different bugs, five different fixes (a COALESCE, a backfill, a column redesign, a data cleanup), and the one thing they share is that a reviewer reading the SQL sees none of them coming.

Wrap the workflow into a script that any reviewer can run:

#!/bin/bash
# review-migration.sh — Validate a migration against realistic data
set -e

MIGRATION_FILE=$1
DATABASE_URL=${DATABASE_URL:-"postgres://dev:dev@localhost:5432/migration_review"}

if [ -z "$MIGRATION_FILE" ]; then
  echo "Usage: ./review-migration.sh <migration-file>"
  exit 1
fi

echo "=== Step 1: Reset database ==="
dropdb --if-exists migration_review
createdb migration_review

echo "=== Step 2: Apply base schema ==="
for f in migrations/*.sql; do
  if [ "$f" != "$MIGRATION_FILE" ]; then
    psql $DATABASE_URL -f "$f" 2>/dev/null || true
  fi
done

echo "=== Step 3: Seed realistic data ==="
seedfast seed --scope "Realistic data across all tables, include NULLs and edge cases" --output plain

echo "=== Step 4: Capture before state ==="
pg_dump $DATABASE_URL --data-only --inserts > /tmp/before_data.sql
psql $DATABASE_URL -c "\d+" > /tmp/before_schema.txt

echo "=== Step 5: Run migration ==="
echo "Running: $MIGRATION_FILE"
if ! psql $DATABASE_URL -f "$MIGRATION_FILE" 2>/tmp/migration_errors.txt; then
  echo "MIGRATION FAILED:"
  cat /tmp/migration_errors.txt
  exit 1
fi

echo "=== Step 6: Capture after state ==="
psql $DATABASE_URL -c "\d+" > /tmp/after_schema.txt

echo "=== Step 7: Show schema diff ==="
diff /tmp/before_schema.txt /tmp/after_schema.txt || true

echo "=== Step 8: Validate data integrity ==="
psql $DATABASE_URL -c "
  SELECT table_name, column_name
  FROM information_schema.columns
  WHERE table_schema = 'public'
    AND is_nullable = 'NO'
    AND column_default IS NULL
  ORDER BY table_name, column_name;
"

echo "=== Done. Review the output above. ==="

Now any reviewer can run ./review-migration.sh migrations/20260226_consolidate_user_names.sql and see the migration's real effect before approving the PR.

Don't rely on reviewers remembering to run the script. Make it part of your PR pipeline.

name: Migration Review Check

on:
  pull_request:
    paths:
      - 'migrations/**'

jobs:
  validate-migration:
    runs-on: ubuntu-latest

    services:
      postgres:
        image: postgres:16
        env:
          POSTGRES_DB: review
          POSTGRES_USER: review
          POSTGRES_PASSWORD: review
        ports:
          - 5432:5432

    steps:
      - uses: actions/checkout@v4
        with:
          fetch-depth: 0

      - name: Apply base schema
        run: |
          for f in migrations/*.sql; do
            psql $DATABASE_URL -f "$f" 2>/dev/null || true
          done
        env:
          DATABASE_URL: postgres://review:review@localhost:5432/review

      - name: Seed realistic data
        run: seedfast seed --scope "Realistic dataset across all tables, include NULLs and varied data" --output plain
        env:
          SEEDFAST_API_KEY: ${{ secrets.SEEDFAST_API_KEY }}
          DATABASE_URL: postgres://review:review@localhost:5432/review

      - name: Apply new migration(s)
        run: |
          NEW_MIGRATIONS=$(git diff --name-only origin/main -- migrations/ | sort)
          for f in $NEW_MIGRATIONS; do
            echo "--- Applying: $f ---"
            psql $DATABASE_URL -f "$f"
          done
        env:
          DATABASE_URL: postgres://review:review@localhost:5432/review

      - name: Validate post-migration state
        run: |
          echo "=== Row counts after migration ==="
          for table in $(psql $DATABASE_URL -t -c "SELECT tablename FROM pg_tables WHERE schemaname='public'"); do
            psql $DATABASE_URL -c "SELECT '$table' as table_name, count(*) as rows FROM $table"
          done

          echo "=== Check for unexpected NULLs ==="
          psql $DATABASE_URL -c "
            SELECT c.table_name, c.column_name, c.is_nullable
            FROM information_schema.columns c
            WHERE c.table_schema = 'public'
            ORDER BY c.table_name, c.ordinal_position;
          "
        env:
          DATABASE_URL: postgres://review:review@localhost:5432/review

When a PR touches a migration, the pipeline seeds realistic data, runs the migration, and reports what happened, so reviewers judge from concrete evidence in the checks and never have to execute the SQL in their heads. For the whole setup, including a fresh per-PR database that gives every migration its own isolated copy, see the CI/CD database seeding guide.

Testing migrations against production-scale data (millions of rows) answers the question "will this migration be fast enough?" That's about lock duration, rewrite time, and index build speed — the failure mode behind the ALTER TABLE that locked an 8-million-row table for 40 minutes.

Testing against production-shaped data (realistic variety, NULLs, edge cases, messy strings) answers a different question, "will this migration produce correct results?" That's about data integrity, constraint violations, and silent data loss, the same volume-and-variety blind spot that hides the bugs only real test data catches.

Catching a NULL concatenation bug doesn't take 5 million rows; it takes 500 that carry realistic NULL patterns, and exposing a unique constraint that breaks on duplicates just takes a dataset containing a few.

This article is about the second kind — showing reviewers what a migration does to the data while the PR is still open, leaving the performance question for the deploy pipeline.

Before approving any PR that includes a schema migration:

• Seed realistic data and run the migration locally (or check the CI results)
• Verify no unexpected NULLs were introduced
• Verify no data was silently truncated or rounded
• Verify row counts are unchanged (unless rows are intentionally removed)
• Check that new DEFAULT values make sense for existing rows, not just new ones

• If adding NOT NULL: confirm no existing rows violate the constraint
• If adding UNIQUE: confirm no existing duplicates
• If adding a FOREIGN KEY: confirm all referenced rows exist and referential integrity holds across the data you already have
• If changing a column type: confirm all existing values fit the new type

• String operations handle NULLs (use COALESCE or explicit NULL checks)
• Backfill queries produce correct results on a sample of real-shaped data
• The migration is idempotent or has a working rollback

• PR includes what the migration does AND why (not just the SQL)
• If the migration is multi-step, each step's purpose is documented
• CI migration validation check passed

• ALTER TABLE, 5 Million Rows, and the Deploy That Took Down the Site — the performance cousin of this article: testing migrations against production-scale data for lock duration and rewrite time
• Small Data, Big Lies: 6 Bugs Your Test Suite Will Never Catch — the volume-and-variety blind spot behind correctness bugs that 10-row test databases hide
• What is Referential Integrity? — why a migration that adds NOT NULL REFERENCES fails on existing orphan rows, and the producer-side discipline that prevents it
• Circular Foreign Key Seed — the FK-loop case that makes seeding realistic review data non-trivial, and three Postgres patterns that handle it
• Your Staging Database Is a Compliance Violation Waiting to Happen — generating realistic review data instead of copying and anonymizing production
• Get started with Seedfast — connect your database and seed production-shaped data for your next migration review

Nobody approves application code by reading the source and imagining what it does; we run the tests, watch the output, and confirm the behavior is what we expected. A schema migration deserves that same rigor, because SQL that looks correct and reads as sensible can still do the wrong thing the instant it touches real rows, and the only way to know is to run it against data shaped like yours. Seeding realistic data, applying the migration, and comparing the before and after takes about 30 seconds of hands-on work, and it catches the data-shape bugs that reading the SQL alone keeps letting through.

Get Started | Documentation | Pricing

Seedfast generates realistic, FK-valid test data from your schema so you can see what a migration actually does to that data before you approve it.