Internals

This is documentation about the specifics of how the schema system works. If everything works as expected, you don't NEED to read this:) See this guide for how to use the migration tool.

The original issue was that we had a schema which was documented and migrations were then made. But there is no guarantee the database state and the schema state are the same. And dumping the schema from the DB removes all comments (except SQL comment on, which was seen as a hassle compared to this.)

The general plan is to have a "master" schema which is well-documented. It's very nice developer experience for migrations to be generated after changes to the schema.

At the core of this is migra, which spits out the SQL required for database A to get the same schema as database B. migra has been deprecated and the "fork" we use is called results. It supports sql functions too:)

The script create-migration makes sure results is installed. Then it concatenates all the x-name.sql schema files into one big file. Keeping them as separate files is nice for navigation. The numbers dictate in which order they are added (which is required when one schema depends on another).

Two databases are spun up using podman compose. We wait for them to start. Then we load the master schema into DB B and run all the current migrations on DB A. Then the migrations table needs to be dropped because that isn't really part of the schema.

Then results is ran. In most cases this generates a good migration. There are however exceptions. When SQL functions are added, they are inserted at the bottom of the migration. You can just move them in the migration file.

Lastly the databases are removed.