(great = simple, short, easy to write, read, maintain. at very least no more complex than the thing it testing!)
> Apart from parsing the SQL query, I also considered an alternative testing approach that I might implement in the future: go through each migration one by one, and insert some dummy data into the database before applying it, to make sure that we test each migration being applied on a non-empty database. The data would either have to be generated automatically based on the current database schema, or we could commit some example DB dataset together with each migration, to make sure that we have some representative data sample available.
Suggests that this may also be a fairly complicated direction. Although it's not entirely clear to me why he can't just put one record in the db before any migrations, and then pull it all through. Plus it has the added drawback of removing your coverage for the (not unimportant) zero case.
Grab some representative data from production and keep feeding that into your migration tests. Keep updating those. Worth each minute if you care about quality.
I currently prevent this sort of thing using a populated database. This database has actual real data (user info overwritten with random data).
Using a pre-populated database catches many more errors than this article's approach does.
Using a pre-populated database that has actual data generated by the users over a year or so catches even more errors.
This approach is fragile and misses the actual error, fixing only the symptom. The actual error is "there's a hole in my tests big enough to fly a passenger jet through".
The correct approach is to take a dump of the production database, scrub PII from it, and then perform your migration.