Case Study: Keeping Old Shop Reports Working After a Schema Change
This case uses the examples/legacy-shop folder that ships in every download archive, so you can run each step yourself. The shop is a stand-in. The commands and output below come from a real run of the downloaded aftermarket 0.8.0 on Linux, with sqlite3 3.45.
The situation
A small shop app moved from 1.0 to 2.0. The new version renamed customers to accounts and split the name into first and last. It stops deleting accounts and marks them with a deleted_at date instead. Orders became purchases, with totals in whole cents and status renamed to state.
The app was updated with its migration. Its reports weren’t. There are twelve of them in reports.sql, written against 1.0, and the people who use them didn’t know they were affected until they tried.
What broke
$ aftermarket schema check --queries reports.sql --new v2/shop.sql
FAIL line 4: SELECT id, name, email FROM customers ORDER BY id
no such table: customers
FAIL line 7: SELECT count(*) AS customers FROM customers
no such table: customers
...
FAIL line 43: SELECT max(total) AS largest FROM orders WHERE status = 'paid'
no such table: orders
1 of 12 queries compile on the new schema
The one that still works reads products, which 2.0 only added a column to. schema diff gives the same picture from the schema side:
$ aftermarket schema diff --old v1/shop.sql --new v2/shop.sql
v1/shop.sql -> v2/shop.sql: 5 change(s), 2 breaking
breaking table customers removed
breaking table orders removed
safe column products.sku added (TEXT)
safe table accounts added (id, first_name, last_name, email, created_at, deleted_at)
safe table purchases added (id, account_id, total_cents, state, placed_at)
The mapping
The fix is two views that show the old tables, read from the new ones. map.yaml says what each old column is in 2.0 terms:
views:
- old: customers
from: accounts
columns:
name: first_name || ' ' || last_name
fax: null # 2.0 dropped fax
created: created_at
where: deleted_at IS NULL # 2.0 keeps deleted accounts; 1.0 removed them
- old: orders
from: purchases
columns:
customer_id: account_id
total: total_cents / 100.0 # 2.0 keeps whole cents
status: state
placed: placed_at
id and email aren’t listed, so they keep their names. The where line matters: without it, deleted accounts would show up in reports that never saw them before.
Proving it reads the same
$ aftermarket schema test --queries reports.sql --map map.yaml
PASS line 4: SELECT id, name, email FROM customers ORDER BY id
PASS line 7: SELECT count(*) AS customers FROM customers
...
PASS line 43: SELECT max(total) AS largest FROM orders WHERE status = 'paid'
12 of 12 reads give the same rows on the new schema with the compatibility views as on the old one
Each report runs on 1.0 and on 2.0 through the views, and the rows have to match exactly. With the map, schema diff now shows both breaking changes as covered and exits 0, so it can guard the next migration in CI.
Two mistakes it catches
The fax column is the interesting one. In the sample data no customer has a fax, so fax: null gives exactly what 1.0 gave. Give one 1.0 customer a fax number and run the test again:
$ aftermarket schema test --old fax.sql --map map.yaml --queries reports.sql
FAIL line 40: SELECT email FROM customers WHERE fax IS NULL ORDER BY email
row 1 differs: old ('[email protected]'), new ('[email protected]')
11 of 12 reads give the same rows on the new schema with the compatibility views as on the old one
That’s real data loss in the migration, and the test can only see it because the sample data had it. So run schema test on a copy of real data, migrated the way production will be.
The second mistake is the cents. Map total: total_cents and forget the division:
$ aftermarket schema test --map cents.yaml --queries reports.sql
FAIL line 16: SELECT o.id, c.name, o.total
row 1 differs: old (1,'Ada Lovelace',39.7999999999999971), new (1,'Ada Lovelace',3980)
...
6 of 12 reads give the same rows on the new schema with the compatibility views as on the old one
The values are printed exactly as SQLite stores them, which is why 39.80 shows as 39.7999999999999971.
Applying it
$ aftermarket schema compat --map map.yaml --out compat.sql
wrote compat.sql: 2 view(s): customers, orders
compat.sql is a plain script, the same bytes every time for the same mapping. AfterSchema doesn’t apply it, so it goes through review like any other migration. After sqlite3 shop2.db < compat.sql, an old report run with plain sqlite3 gives the same answer on both databases:
== shop1.db
name spent
--------------- ------
Grace Hopper 314.09
Ada Lovelace 159.8
Charles Babbage 15.25
Alan Turing 8.5
== shop2.db
name spent
--------------- ------
Grace Hopper 314.09
Ada Lovelace 159.8
Charles Babbage 15.25
Alan Turing 8.5
Before that, schema test ran against both database files, and their checksums were the same afterwards. The AfterSchema reference has the full format.