#69 made me think about how we want to handle database management in general. I researched and thought a bit about this topic. These are my thoughts/conclusions.
Some notes (hopefully there is no disagreement on those?):
- Releasing a new version of Tobira should include some easy way to migrate the old database to the new schema. Otherwise updating is terribly annoying for sys admins.
- I want commands for DB operations, e.g.
tobira db setup
, tobira db reset
and tobira db run script.sql
.
- For convenience, just starting
tobira
should do some setup/migrations automatically, as long as it's clear what to do. In case of ambiguity, Tobira shouldn't guess.
- It will regularly happen that we (devs) switch between versions requiring different DB schemas (e.g. git checkout to review a PR, ...). It shouldn't be too annoying to get the database into the correct state. Notably, for development, the actual data in the database isn't important most of the time.
- I would prefer the scripts to be written in actual
.sql
files and not Rust strings. We can still embed those files into the application (include_str!
).
Suggestion: down.sql
In addition to the obviously needed "forwards" migration (up.sql
), I think it would be beneficial to also include scripts to roll back one migration.
Suggestion: store migrations in DB
We would add a table migrations
(or so) which would contain all migrations leading to the current state of the database. All migrations should have some unique ID. Running a migration would add a row to this table, undoing a migration would remove the row.
Furthermore, I think it would be useful for development to also store the raw up.sql
and down.sql
in a text
column:
Saving down.sql
allows us to roll back a migration even if the script is not currently available in the repository. That is useful if you switch from one feature branch (foo
) to a different one (bar
). To get the database in the state expected by bar
, we have to roll back the migration of foo
and then run the migation of bar
. But since foo
's down.sql
is not in the working directory anymore, we need to store it in the DB. Of course, you can always manually undo foo
's migration before switching to the new branch, but we will likely forget that most of the time and get annoyed by how inconvenient that is.
Saving up.sql
allows us to compare the up.sql
in the working directory/file system with the one in the database. If they differ (which will happen a lot during development), Tobira could easily undo the old migration (with the down.sql
in the database) and then run the new up.sql
from the file system.
How many migration scripts?
Solution A: one migration per version
One migration script per version (e.g. v1.0.sql
, v1.1.sql
and next.sql
). We would only modify the latest (e.g. next.sql
) during development. Releasing a new version means mv next.sql 1.2.sql && touch next.sql
. The ID for the migrations in the migration
table would simply be the version. That way, Tobira knows what migrations to run when updating to a new version.
- Advantage: works great for production users as they can easily upgrade to a new version
- Advantage: having all schema changes between two versions in one file helps on some situations
- Advantage: migration potentially faster as there are fewer scripts
- Disadvantage: for developers, switching between branches means to completely undo all database changes since the last released version of Tobira and then redoing them.
Solution B: one migration per "feature" (commit/PR)
Instead of continuously changing one script until release, we could also have a separate migration script per "feature". That roughly corresponds to one migration script per PR that requires to modify the database schema. This of course results in lot more migration scripts, but also probably not ungodly many (adjust the DB schema is not too common). As ID for migrations, we would probably either want to use the current timestamp or some random short string. This is mainly to ensure that two feature branches developed in parallel will have different IDs for their migrations.
- Still works fine for production users, although it might be a bit slower than solution A, simply because there are more migrations to run.
- Advantage: more atomic migrations could make error handling/debugging easier
- Advantage: for development, switching between branches would only require undoing a small migration instead of basically resetting the whole database.
- Disadvantage: in solution (a) we could merge changes easily, whereas here, we might end up with migrations that undo part of previous migrations. Maybe.
I at least know of one library which encourages this style of migration management.