Test Data for Database Migrations: Seed the Rows That Break Them

Most migrations are only ever tested against a database that has almost nothing in it: an empty schema in CI, or a dev database seeded with rows that were generated to be valid. On that data, SET NOT NULL runs instantly and a new unique index builds without complaint. Production has years of rows written by code that no longer exists, including nulls, case-variant duplicates, orphaned children, and values longer than today's form allows. Test data for database migrations has to contain those shapes on purpose. Otherwise the deploy is the migration's first real test.

This post covers six common ways migrations fail, the rows that expose each one, and a loop that catches them in CI. You generate a pre-migration dataset with a fixed seed, load it with your own tools, run your own migration tool, and assert the outcome. JsonFabrica does only the first step. It returns JSON over HTTP: it has no database connection, never writes a row, and never runs a migration. Your psql, ORM, or driver loads the rows, and Flyway, Liquibase, Alembic, Rails, Prisma Migrate, or whatever you already use runs the migration.

This isn't about edge values in application code. For edge values in application-level tests, see boundary value test data generation. Here the data is shaped to break a schema change. The SQL examples use PostgreSQL. Lock and rewrite behavior differs between databases and between major versions, so check the notes below against the documentation for your version.

The migration test loop: generate, load, migrate, assert

Every migration test in this post follows the same four steps:

1. Generate. Request a batch of JSON documents shaped like your tables as they are before the migration, including the bad rows. JsonFabrica does this step.
2. Load. Create a fresh database, migrate it to the version just before the migration under test, and insert the JSON. Your tools do this step.
3. Migrate. Run the migration under test with your usual migration tool, and record its exit code and output.
4. Assert. Check the outcome against the JSON you loaded. Depending on the case, the migration should fail loudly, a cleanup step should handle the bad rows, or a backfill should produce the right value in every row.

The generated JSON is useful twice. It fills the database, and afterward it tells the test exactly what went in, so the assertions don't depend on reading the old state back out of a database that the migration has just changed.

Building test data for database migrations: the pre-migration templates

The example schema is a legacy customers table and an orders table with no foreign key yet. Template fields use the column names directly, which keeps the load step trivial. Each customer gets one random roll from 1 to 100, and a few bands of that roll get a production-style defect:

<createSeq('custId', 'uuid')><createSeq('custNo', 'number', 1)><setVar('roll', getRandomNumber(1, 100))>{
  "id": "<getSeq('custId')>",
  "email": "<if(getVar('roll') >= 6 && getVar('roll') <= 8)><getRandomElement('[email protected]', '[email protected]', '[email protected]')><else>customer<getSeq('custNo')>@example.com<endIf>",
  "full_name": "<if(getVar('roll') == 4 || getVar('roll') == 5)><getRandomTextWithSpaces(120, 250)><else><getRandomFullName()><endIf>",
  "phone": <if(getVar('roll') <= 3)>null<else>"555<getRandomNumber(1000000, 9999999)>"<endIf>,
  "status": "<if(getVar('roll') == 9 || getVar('roll') == 10)><getRandomElement('ACTIVE', 'legacy_trial')><else><getRandomElement('active', 'active', 'active', 'suspended')><endIf>",
  "created_at": "<getRandomDate('2019-01-01T00:00:00Z', '2026-09-01T00:00:00Z')>"
}

There's no null generator and no duplicate function in JsonFabrica, and none is needed:

• Nulls are a literal null written into the template. The if branch decides which rows get it.
• Duplicates come from getRandomElement over a short list. With only three options, any four rows in that band are guaranteed to repeat one. [email protected] is included on purpose: a plain unique index on email accepts it next to [email protected], but a unique index on lower(email) does not.
• Over-long values come from getRandomTextWithSpaces with a minimum above the new column limit. Every value from (120, 250) is between 120 and 250 characters long.
• Legacy enum values such as ACTIVE and legacy_trial are literal options in another getRandomElement call.

The clean rows take their email from a sequence, so the only duplicate emails are the ones you planted. The orders template plants a null total in two percent of rows and a zero total in another two percent:

<createSeq('orderId', 'uuid')><setVar('roll', getRandomNumber(1, 100))>{
  "id": "<getSeq('orderId')>",
  "customer_id": "ffffffff-ffff-4fff-8fff-ffffffffffff",
  "total": <if(getVar('roll') <= 2)>null<elseIf(getVar('roll') <= 4)>0<else><getRandomNumber(0, 5000, 2)><endIf>,
  "placed_at": "<getRandomDate('2019-01-01T00:00:00Z', '2026-09-01T00:00:00Z')>"
}

The literal customer_id is a deliberate orphan. Batch relations always point at real generated parents, so an orphan has to be a literal id that no parent has. Sequence UUIDs always start with 00000000-0000-4000-8000-, so an id starting with ffffffff can never match a generated customer. Save both templates with POST /v1/templates, then request them together in one batch spec:

{
  "seed": 7340021,
  "documents": [
    {
      "templateId": "tpl_premig_customer",
      "alias": "customers",
      "count": 400
    },
    {
      "templateId": "tpl_premig_order",
      "alias": "orders",
      "count": 1200,
      "relations": {
        "customer_id": { "from": "customers.id", "strategy": "round-robin" }
      }
    },
    { "templateId": "tpl_premig_order", "alias": "orphan_orders", "count": 5 }
  ]
}

The orders alias has a round-robin relation, so the batch overwrites the literal customer_id with a real customer id, cycling through the customers in order. The orphan_orders alias uses the same template with no relation, so its five rows keep the orphan id.

Six migration failure modes and the rows that expose them

Adding NOT NULL to a column that already has nulls

ALTER TABLE customers ALTER COLUMN phone SET NOT NULL;
-- ERROR:  column "phone" of relation "customers" contains null values

This is the right failure. It's much better to see it in CI than halfway through a deploy. The fix is a decision about what the existing nulls should become, followed by a backfill that runs before the constraint. Even when it succeeds, SET NOT NULL checks every row while holding an ACCESS EXCLUSIVE lock, which blocks reads and writes on the table. On PostgreSQL 12 and later, you can add CHECK (phone IS NOT NULL) NOT VALID, run VALIDATE CONSTRAINT under a lighter lock, and then SET NOT NULL can skip the scan. PostgreSQL 18 can also add a NOT NULL constraint as NOT VALID directly, but the CHECK-based approach still works on every version from 12 on.

Shrinking a varchar or tightening a CHECK constraint

ALTER TABLE customers ALTER COLUMN full_name TYPE varchar(100);
-- ERROR:  value too long for type character varying(100)

ALTER TABLE customers ADD CONSTRAINT customers_status_check
  CHECK (status IN ('active', 'suspended'));
-- ERROR:  check constraint "customers_status_check" of relation "customers"
--         is violated by some row

On PostgreSQL both of these fail loudly, which is what you want. The riskier case is a database that doesn't fail. MySQL with strict SQL mode disabled can truncate over-long values and report only a warning. Strict mode has been the default since 5.7, but older or hand-tuned servers don't always have it. A test that asserts "this migration fails on over-long names" catches that silent truncation.

Watch the cost of these statements too. Changing a column's type generally rewrites the table on PostgreSQL. Widening a varchar is an exception, but shrinking one isn't. Adding a CHECK scans every row. To split the check from the lock, add the constraint NOT VALID and run VALIDATE CONSTRAINT as a separate step.

Adding a UNIQUE index when duplicates exist

CREATE UNIQUE INDEX customers_email_lower_key ON customers (lower(email));
-- ERROR:  could not create unique index "customers_email_lower_key"

A plain CREATE INDEX blocks writes to the table while it builds. CREATE INDEX CONCURRENTLY doesn't block writes, but it can't run inside a transaction block. Many migration tools wrap each migration in a transaction by default, so check how yours lets a single migration opt out. And when a concurrent build fails on a duplicate, it leaves an INVALID index behind that you have to drop before retrying.

Usually the right outcome here is a cleanup step that merges the duplicates before the index is built. Because every customer has orders, the merge has to repoint them first:

CREATE TEMP TABLE customer_merge AS
SELECT id,
       first_value(id) OVER (PARTITION BY lower(email)
                             ORDER BY created_at, id) AS keep_id
FROM customers;

UPDATE orders o SET customer_id = m.keep_id
FROM customer_merge m
WHERE o.customer_id = m.id AND m.id <> m.keep_id;

DELETE FROM customers c USING customer_merge m
WHERE c.id = m.id AND m.id <> m.keep_id;

CREATE UNIQUE INDEX customers_email_lower_key ON customers (lower(email));

If you build the index concurrently instead, run the merge first, and start the index step with DROP INDEX CONCURRENTLY IF EXISTS so a retry after an earlier failed build doesn't trip over the leftover. Then test the fixed migration against the seeded duplicates: it should succeed, and SELECT count(*) FROM pg_index WHERE NOT indisvalid should return 0 afterward.

Adding a foreign key when orphan rows exist

ALTER TABLE orders ADD CONSTRAINT orders_customer_fk
  FOREIGN KEY (customer_id) REFERENCES customers (id);
-- ERROR:  insert or update on table "orders" violates foreign key
--         constraint "orders_customer_fk"

The five orphan_orders rows cause this. The usual cleanup moves orphans to a quarantine table, or deletes them if the business agrees. Then it adds the constraint. NOT VALID followed by VALIDATE CONSTRAINT works here as well, and it scans under a lighter lock than a plain ADD. Be careful with NOT VALID on its own, though. It enforces the key for new rows only, so existing orphans stay hidden until something validates the constraint. Make sure the migration test runs the validation step.

A backfill that must transform every row correctly

Say the migration replaces orders.total (a nullable numeric in dollars) with total_cents (a bigint):

ALTER TABLE orders ADD COLUMN total_cents bigint;
UPDATE orders SET total_cents = round(total * 100);
ALTER TABLE orders ALTER COLUMN total_cents SET NOT NULL;

On clean data this passes. On the generated data, the null totals turn into null total_cents, and the last statement fails. After you change the backfill to round(coalesce(total, 0) * 100), the migration succeeds. Success alone doesn't prove the values are right, though. A backfill that truncates cents or mishandles zero also runs without an error. The only real check is row by row, against the JSON you loaded, which the assertions section below covers.

For a large table, a single UPDATE of every row holds row locks on all of them until it commits and writes a lot of WAL. Backfilling in batches of ids avoids that, but it adds more logic you need to verify, which is one more reason to check every row.

Volume: DDL and backfills that lock on big tables

Everything above can succeed on about 1,600 rows and still cause an outage on a production table, because the scan or rewrite that took milliseconds in CI takes much longer at production size, and it holds its lock the whole time. Lock queueing makes this worse. A statement waiting for an ACCESS EXCLUSIVE lock queues behind any long-running transaction that has touched the table, even one that only read from it, and every query after it queues behind the waiting statement. Setting lock_timeout in the migration makes it fail fast instead of stalling traffic. On MySQL, which ALTER operations can run in place or instantly depends on the operation and the server version, so check the online DDL documentation for yours.

To test volume, send the same batch spec with larger count values. Batch submissions with 50 or more documents in total come back as 202 Accepted with a batchId, which you poll at GET /v1/batches/{batchId} until status is completed. JsonFabrica doesn't document a maximum batch size, so scale up in steps and watch how long each step takes to generate. Generation also counts toward your plan's usage.

Each document's random values are derived from the batch seed, its alias, and its position. So when you raise customers from 400 to 4,000, the first 400 customers keep the same names, rolls, and defects. Only the sequence-backed ids change. A useful check is to time the migration at two sizes, say 1× and 10×. If the time stays flat, the change is probably metadata-only. If it grows with the row count, the change scans or rewrites the table, and it needs a plan for production. If generating rows at your real production size isn't practical, multiply the loaded rows inside your database with INSERT ... SELECT. That's your database's work, not JsonFabrica's, and you'll need to keep unique columns unique while you do it.

Running Flyway, Liquibase, Alembic, or Rails migrations against the dataset

The harness below works the same way whatever runs your migrations. Two environment variables hold the tool-specific commands: one migrates to the version just before the migration under test, the other applies it. (For tools that can't stop at a version, such as prisma migrate deploy, migrate from the previous commit, load, then check out the branch.)

#!/usr/bin/env bash
# migration-test.sh: one migration, one fresh database,
# one pre-migration dataset
set -euo pipefail
API=https://api.jsonfabrica.com/v1
AUTH="Authorization: Bearer $JSONFABRICA_API_KEY"

# 1. Fresh database at the schema version *before* the migration under test.
#    MIGRATE_TO_PREVIOUS, e.g.: flyway migrate -target=41
#      | liquibase update-to-tag --tag=v41 | alembic upgrade 41ab3f
#      | bin/rails db:migrate VERSION=20260901120000
#    Point your tool's own connection config at the migtest database.
dropdb --if-exists migtest && createdb migtest
export DATABASE_URL=postgres:///migtest
$MIGRATE_TO_PREVIOUS

# 2. Generate the dataset (JSON only), polling if the batch was queued (202).
RES=$(curl -fsS -X POST "$API/batches" -H "$AUTH" \
  -H 'content-type: application/json' -d @migration-fixtures/batch.json)
BATCH_ID=$(jq -r .batchId <<<"$RES")
while jq -e '.status == "queued" or .status == "running"' \
    <<<"$RES" > /dev/null; do
  sleep 2
  RES=$(curl -fsS "$API/batches/$BATCH_ID" -H "$AUTH")
done
jq -e '.status == "completed"' <<<"$RES" > /dev/null \
  || { echo "$RES" >&2; exit 1; }
echo "$RES" > batch-result.json
echo "batch=$BATCH_ID seed=$(jq .seed batch-result.json)"

# 3. Load it with psql (your ORM or driver works just as well).
jq -c '.results.customers' batch-result.json > customers.json
jq -c '.results.orders + .results.orphan_orders' batch-result.json \
  > orders.json
psql "$DATABASE_URL" -v ON_ERROR_STOP=1 <<'SQL'
\set customers `cat customers.json`
\set orders `cat orders.json`
INSERT INTO customers SELECT * FROM jsonb_to_recordset(:'customers'::jsonb)
  AS c(id uuid, email text, full_name text, phone text, status text,
       created_at timestamptz);
INSERT INTO orders SELECT * FROM jsonb_to_recordset(:'orders'::jsonb)
  AS o(id uuid, customer_id uuid, total numeric, placed_at timestamptz);
SQL

# 4. Run the migration under test.
#    Record the outcome instead of stopping on failure.
#    MIGRATE_UP, e.g.: flyway migrate | liquibase update
#      | alembic upgrade head | bin/rails db:migrate
set +e
$MIGRATE_UP > migrate.log 2>&1
echo $? > migrate.exit
set -e
pytest "tests/migrations/$MIGRATION_TEST"

The jsonb_to_recordset column lists assume the legacy tables have those columns in that order. \set with backticks reads each JSON file inside psql, so a large batch never passes through a command-line argument. For more detail on this step, including ORM inserts, the ORM seed script alternative covers loading batch JSON with Prisma, Django, and plain SQL.

Batch results are grouped by alias as results.customers, results.orders, and results.orphan_orders, which is what the jq calls read.

Asserting the outcome: fail loudly, clean up, or verify every row

Each migration gets its own test file, which runs after its own harness run. All three kinds of test start by loading the JSON and checking that the fixture actually contains the shape under test. If a seed produced no null phones, the test would pass without testing anything:

# tests/migrations/conftest.py
import json, os
from decimal import Decimal
import psycopg
import pytest

@pytest.fixture(scope="session")
def data():
    with open("batch-result.json") as f:
        return json.load(f, parse_float=Decimal)["results"]

@pytest.fixture(scope="session")
def db():
    with psycopg.connect(os.environ["DATABASE_URL"]) as conn:
        yield conn

@pytest.fixture(scope="session")
def outcome():
    with open("migrate.exit") as e, open("migrate.log") as log:
        return int(e.read()), log.read()

The migration should fail loudly. A SET NOT NULL with no backfill must refuse to run, and on PostgreSQL the refusal should leave the schema unchanged:

def test_not_null_refuses_existing_nulls(data, db, outcome):
    assert any(c["phone"] is None for c in data["customers"]), (
        "fixture has no NULL phones"
    )
    code, log = outcome
    assert code != 0
    assert (
        'column "phone" of relation "customers" contains null values' in log
    )
    nullable = db.execute(
        "SELECT is_nullable FROM information_schema.columns "
        "WHERE table_name = 'customers' AND column_name = 'phone'"
    ).fetchone()[0]
    assert nullable == "YES"  # rolled back, not half-applied

The last assertion depends on transactional DDL. On PostgreSQL, when the tool runs the migration in a transaction, a failure rolls all of it back. MySQL commits implicitly after each DDL statement, so a multi-statement migration that fails partway can leave its earlier statements applied. This assertion catches that.

A cleanup step should handle the bad rows. For the duplicate merge, the JSON tells the test exactly how many customers should survive, and the five planted orphans act as a control:

def test_email_dedupe_then_unique_index(data, db, outcome):
    emails = [c["email"].lower() for c in data["customers"]]
    assert len(emails) - len(set(emails)) >= 1, (
        "fixture has no duplicate emails"
    )
    assert outcome[0] == 0, outcome[1]
    assert db.execute(
        "SELECT count(*) FROM customers"
    ).fetchone()[0] == len(set(emails))
    total_orders = len(data["orders"]) + len(data["orphan_orders"])
    assert db.execute(
        "SELECT count(*) FROM orders"
    ).fetchone()[0] == total_orders
    dangling = db.execute(
        "SELECT count(*) FROM orders o WHERE NOT EXISTS "
        "(SELECT 1 FROM customers c WHERE c.id = o.customer_id)"
    ).fetchone()[0]
    # merge repointed every real order
    assert dangling == len(data["orphan_orders"])

A backfill should be correct in every row. Compute the expected value for each order from the JSON with exact decimals, then compare it with what the migration wrote:

def test_total_cents_backfill(data, db, outcome):
    source = data["orders"] + data["orphan_orders"]
    assert any(o["total"] is None for o in source), (
        "fixture has no NULL totals"
    )
    assert outcome[0] == 0, outcome[1]
    expected = {
        o["id"]: 0 if o["total"] is None else int(o["total"] * 100)
        for o in source
    }
    actual = dict(
        db.execute("SELECT id::text, total_cents FROM orders").fetchall()
    )
    assert actual.keys() == expected.keys()
    wrong = {k: (actual[k], v) for k, v in expected.items() if actual[k] != v}
    assert not wrong, (
        f"{len(wrong)} rows wrong, e.g. {list(wrong.items())[:5]}"
    )

Test schema migrations before production, in CI, from a fixed seed

Run the harness in CI for every pull request that adds a migration. Pin the seed in the batch spec and print it, as the harness does. A migration that fails in CI then fails the same way on your laptop, because the same seed produces the same random values: the same rows come out null, duplicated, or too long. The one exception is values that come from sequences, such as the ids and the customerN email numbers. Sequences are durable counters that keep advancing between runs, so they change even with a fixed seed. If you need byte-identical rows, upload batch-result.json as a CI artifact, or fetch the same batch again with GET /v1/batches/{batchId}. Replaying a failure from its seed covers how to capture and reuse seeds in more depth.

When the fixture guard fails ("fixture has no duplicate emails"), change the seed or widen the roll band. Don't delete the guard. And when an incident reveals a new production shape, such as emails with trailing whitespace or totals stored as negative refunds, add a band to the template. The next migration that touches that table is then tested against it.

FAQ

How do you test database migrations before production? Restore an empty database to the schema version just before the migration, load a dataset that contains the row shapes production has (nulls, duplicates, orphaned child rows, over-long values, and enough volume), then run the migration with your usual tool and assert the outcome. Depending on the case, the correct outcome is that the migration fails loudly, that its cleanup step handles the bad rows, or that a backfill produced the right value in every row. Run it in CI on every migration change.

Why does a migration work locally but fail in production? Local and CI databases are usually empty or seeded with clean, valid rows, so constraints and schema changes have nothing to reject. Production holds years of rows written by older code, including nulls in columns everyone assumes are filled, case-variant duplicates, and child rows whose parent was deleted. Production tables are also much larger, so a change that is instant locally can scan or rewrite a big table while holding a lock.

How do I add a NOT NULL constraint to a column that already has null values? First decide what the existing nulls should become, then backfill them in the same migration or an earlier one, and only then set the column NOT NULL. On PostgreSQL 12 and later you can also add a CHECK (column IS NOT NULL) constraint as NOT VALID, validate it separately, and then SET NOT NULL can skip its full-table scan. Test the whole sequence against data that actually contains nulls.

Can JsonFabrica run Flyway or Liquibase migrations against my database? No. JsonFabrica only returns generated JSON over HTTP. It has no database connector, never writes rows, and never runs migrations. You load the JSON with your own tools, such as psql, an ORM, or a database driver, and run the migration with Flyway, Liquibase, Alembic, Rails, Prisma Migrate, or whichever tool you already use.

How do I make a failing migration test reproducible? Send a fixed seed with the batch request and log it. The same seed produces the same random values in every run, so the same rows come out null, duplicated, or too long, and the migration fails the same way. Values that come from sequences, such as generated ids, keep counting up between runs. If you need byte-identical rows, save the batch response as a CI artifact or fetch the earlier batch again by its batchId.

How do I verify that a backfill data migration is correct? Keep the generated JSON you loaded before the migration and use it as the source of truth. After the migration, read the new column for every row, compute the expected value for each row from the JSON in your test code, and assert that every row matches. Include rows with null, zero, and boundary inputs, because those are where backfills usually go wrong.

The migration that takes down production is usually the one that was only ever run against clean rows. JsonFabrica's batch API generates the pre-migration dataset as JSON: nulls, duplicates, orphans, and over-long values, planted at rates you choose and reproducible from a seed. Your own tools then load it and run the migration against it.