October 9, 20264 min read

Test the Data Your Migration Must Preserve

Catch a migration that silently loses a price, test the corrected SQL and rollback, and share native Ptah test reports without a vendor account.

Simon HartSolutions Engineer

Ptah helps you plan, review, and apply database migrations. Try in your browser

The migration completed. The order’s price became zero.

Our example adds an integer cents column to an existing orders table. The first attempt creates the column with a default of zero but forgets to copy the old price. Every SQL statement succeeds. A test that inserts an order before the change catches the mistake immediately:

    FAIL    step "(unnamed step)" — expected scalar "1995", got "0"

This is a real failed assertion from Ptah 0.13.0, run against its disposable SQLite test database. The corrected migration, rollback test, schema test, and HTML reports are in the example files.

Run the following commands from a writable copy of that example directory.

Put an existing order through the change

Our version 1 table stores an amount. Version 2 introduces amount_cents. The test describes what must survive the transition:

cases:
  - name: price survives migration and rollback
    steps:
      - migrate_to: 1
      - exec: INSERT INTO orders (id, amount) VALUES (1, 19.95)
      - migrate_to: latest
      - assert: {query: "SELECT amount_cents FROM orders WHERE id = 1", scalar: "1995"}
      - migrate_to: 1
      - assert: {query: "SELECT amount FROM orders WHERE id = 1", scalar: "19.95"}

Ptah migrates to version 1, inserts the fixture, and applies the remaining migrations. It then reads the new column. The assertion expects 1995 cents for the existing 19.95 order. After that, it migrates back and checks that the original amount is still present.

The broken/ directory contains the version that omits the data copy. Run it with the same test:

ptah migrations test --dir tests --migrations-dir broken

=== MIGRATION TEST ===
FAIL  case "price survives migration and rollback"
    PASS    step "(unnamed step)" — migrated to 1
    PASS    step "(unnamed step)" — exec ok
    PASS    step "(unnamed step)" — migrated to latest
    FAIL    step "(unnamed step)" — expected scalar "1995", got "0"

1 cases, 0 passed, 1 failed, 0 skipped

The command exits 1. Notice that the migration steps passed before the assertion failed. This is a data requirement that SQL execution alone did not establish. The migration linter examples cover complementary checks on the change itself.

Fix the migration, keep the assertion

The corrected up migration adds the missing conversion:

ALTER TABLE orders ADD COLUMN amount_cents integer NOT NULL DEFAULT 0;
UPDATE orders SET amount_cents = CAST(ROUND(amount * 100) AS INTEGER);

ptah migrations test --dir tests --migrations-dir migrations

=== MIGRATION TEST ===
PASS  case "price survives migration and rollback"
    PASS    step "(unnamed step)" — migrated to 1
    PASS    step "(unnamed step)" — exec ok
    PASS    step "(unnamed step)" — migrated to latest
    PASS    step "(unnamed step)" — scalar "1995"
    PASS    step "(unnamed step)" — migrated to 1
    PASS    step "(unnamed step)" — scalar "19.95"

1 cases, 1 passed, 0 failed, 0 skipped

This run exits 0. It checks both the forward value and the value after rollback. The old amount column remains in this example, so rollback can remove the new column without reconstructing the old price.

The fixture tests one positive price. It does not establish a complete currency conversion policy: rounding boundaries, negative amounts, overflow, and currencies with different minor units need their own cases. Write those requirements before accepting a migration for real orders.

Test a schema without a migration history

A schema test starts from a declared schema. Here the cents column has a CHECK (amount_cents >= 0) constraint. We insert a valid order, count it, and require the negative insert to fail:

cases:
  - name: prices must be nonnegative
    steps:
      - exec: INSERT INTO orders (id, amount_cents) VALUES (1, 1995)
      - assert: {query: SELECT count(*) FROM orders, scalar: "1"}
      - assert: {query: "INSERT INTO orders (id, amount_cents) VALUES (2, -1)", error_contains: "CHECK constraint failed"}

ptah schema test --dir schema-tests --schema-file schema.sql

=== SCHEMA TEST ===
PASS  case "prices must be nonnegative"
    PASS    step "(unnamed step)" — exec ok
    PASS    step "(unnamed step)" — scalar "1"
    CAUGHT  step "(unnamed step)" — error contained "CHECK constraint failed"

1 cases, 1 passed, 0 failed, 0 skipped

CAUGHT means the database returned the error that the test expected. An unexpected successful insert would fail that assertion. The two commands exercise different starting points: migration tests traverse history; schema tests exercise the declared end state. The testing documentation describes both formats.

Open the result in a browser

Add --report html to retain the native report:

ptah migrations test --dir tests --migrations-dir broken --report html > failed-report.html
ptah migrations test --dir tests --migrations-dir migrations --report html > passed-report.html

The failing test still exits 1 while writing the report. In CI, retain the file as an artifact even when the test job fails.

Ptah’s native HTML test report marks the price assertion as failed.

Open the failed report and compare it with the passing report. These are Ptah’s generated files. We opened both in light and dark browser themes; those checks made no external requests. The report carries the assertion result, so a reviewer can see the failure without replaying a terminal recording.

Keep testing in your own toolchain

These test commands are part of Ptah’s MIT-licensed open-source CLI. They ran without an account or paid subscription. With no database URL, this example uses the runner’s temporary SQLite database.

Atlas documents migration and schema testing as Pro features available to free and paid users, with atlas login required. That is the distinction to evaluate; Pro does not automatically mean payment is required. See its migration testing and schema testing documentation, checked on October 8, 2026. The comparison describes Atlas’s documented requirements.

If you want this workflow in an open-source CLI that you can operate without a vendor account, follow From Atlas to Ptah. It walks through retaining migration history and verifying the next change. Keep the data test beside the migration so the same assertion runs in local development and CI.

Put Ptah to work

Example files

← All articles