Comment by wmanley
3 years ago
Disclosure: I'm the co-author of the blog post.
One nice thing about this approach is that because it works by inspecting a database that has been put in a desired state. This means it doesn't matter how you set up your database (with an ORM, a SQL file, etc.) the same function can be used to do the migration.
I'd really like if something like this were built into SQLite. That way we'd have more confidence that it would be/stay correct and complete. It seems generic enough that it could be a part of built-in functionality. I would have proposed it upstream, but AIUI the SQLite authors don't accept external patches.
Edit: Another benefit of this being built-in: there are plenty of places where SQLite is applicable where you can't, or don't want to run Python.
SQLite actually ships a little-known utility called sqldiff that implements a subset of this idea: https://www.sqlite.org/sqldiff.html
It's more focused on content diffs than schema diffs though.
Wow, I hadn't come across that before. It's so close to being what we want, but not quite right. They've even considered this specific use case:
> 3. The sqldiff utility is not designed to support schema migrations and is forgiving with respect to differing column definitions. Normally, only the column names and their order are compared for like-named tables before content comparison proceeds. > > However, the single-table comparison option, with "sqlite_schema" named, can be used to show or detect detailed schema differences between a pair of databases. When doing this, the output should not be used directly to modify a database.
An additional benefit: The CREATE TABLE statements serve as documentation of the structure of the production database.