afterschema

AfterSchema Released to Keep Old SQL Working After a Schema Change

Aftermarket 0.8.0 adds AfterSchema, run as aftermarket schema. It’s for the day an app’s database moves to a new shape and everything else that reads it breaks: the monthly report, the export script, the BI dashboard someone set up three years ago and nobody owns.

The app’s own code got updated with the migration. Those other readers didn’t, and usually nobody has a list of them. The fix people reach for is a few hand-written views, applied once and never tested. Then a report quietly shows the wrong totals, and it takes a month for anyone to notice.

A mapping

You describe each old table in terms of the new ones. This is the example that ships with the release, a shop whose 2.0 renamed customers to accounts, split the name in two, keeps deleted accounts, and stores totals in cents:

version: 1
old: v1/shop.sql
new: v2/shop.sql
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
  - old: orders
    from: purchases
    columns:
      customer_id: account_id
      total: total_cents / 100.0
      status: state
      placed: placed_at

Each value is a plain SQL expression over the new table. Columns you don’t list keep their name.

Four commands

  • schema diff lists every change between the two schemas and marks what breaks old readers. A removed column, a type that now reads values differently, a column that can suddenly be NULL. With the mapping, the changes its views take care of show as covered. It exits 1 on anything left, so it can sit in CI next to the migration.
  • schema check compiles each old query against the new schema without running it. On the example, 1 of the 12 old reports compiles on 2.0 alone, and all 12 with the views.
  • schema test runs every old read on the old database and on the new one through the views, and compares the rows exactly. Values, types, column names. 1 and 1.0 are different answers here, same as they are to a script that formats them.
  • schema compat writes the views to one .sql file. You read it, then apply it yourself with sqlite3 new.db < compat.sql.

It never writes your database

AfterSchema opens databases read-only through the sqlite3 program, in its safe mode, and loads .sql dumps into memory. It doesn’t have an apply step on purpose. The views are a change to a production database, so they go through your review and your migration process like any other change.

That also meant making sure no SQL handed to AfterSchema, from a queries file, a dump or a mapping, can make the sqlite3 shell run one of its own commands. That took three rounds of review, which the testing post goes through.

What it doesn’t do

The views serve reads. An old script that writes to customers will get an error from SQLite, and schema check tells you which statements those are. SQLite only for now. And if 2.0 still has a table called customers that means something else, there’s no room for a view with that name; diff points that out.

The AfterSchema reference has the full format, the case study walks through the example including two mistakes the test catches, and the download page has the binaries.