I built this last summer and thought it was fairly complete. And it turns out I was naive. 6 months later, I have resolved ~100 issues reported by the users.
Incredible tool, definitely going to get a working poc for this with my team. Curious how this can apply across many databases, say in a cluster, checking for discrepancies etc. Wondering if that is a supported feature?
Tried it out now with SQLite to see what it does with difficult migrations, like adding a foreign key constraint to an existing table. Looks like it just generates invalid migrations, eg:
The SQLite docs have a section about it, the idea is:
1. Create new table with foreign key constraint
2. Copy data from old to new
3. Drop old table
4. Rename new table to old name
I use https://github.com/ariga/atlas for this, migration based and schema base both has there good and bad side, I prefer both even in one project, schema based can make dev faster, eaiser, migration based make feel reliable.
Ariel from Atlas here. This setup is pretty common (declarative locally, versioned in real environments). Since migrations are auto-generated (by Atlas) on PRs, most developers never touch the versioned workflow directly.
I wrote a very rudimentary schema and automatic migration system for SQLite. One problem that I ran into recently was deploying changes that spanned two migrations, because the tool doesn’t know how to step through commits to do successive auto-migrations between schema versions. I guess there are rather obvious ways to handle this if you generate and then commit the full migration sql for each schema change. Nonetheless I’m curious if this is a problem you have had to think about, if you find it interesting or if it sounds like a bad path to go down, and if atlas does anything smart in this department. Thanks in advance!
Yeah, after looking more into sqldef and alternatives I stumbled on Atlas too and I like the explicit support for migration based flow for exactly the same reasons. I want to know exactly what kind of migration will be applied to my prod database beforehand.
For example add temporarily nullable column to a large table, deploy new code which starts writing to the new column, in background populate that column for existing rows in batches and finally alter column to be mandatory non-nullable.
Another example of non-trivial schema management case is to make schema change after new version rollout completes: simple migration at the start of the container can't do that.
It must be a solved problem, but I didn't see a good tool for it which would allow expressing these imperative changes in a declarative way which can be comitted and reviewed and tested along the app code. It is always bunch of adhoc ugly scripts on a side and some hand waving deployment instructions.
I tend to prefer to hand-roll schema migrations... but I use grate[1] for the most part. That said, I've created similar tooling for different scenarios.
Also means I can stop with my hobby project that was supposed to do the same. Wasn't far along and haven't worked on it in months anyway.
So I'll spend my time on another hobby project then that also solves something that is already solved 100 times over, but I don't like the other solutions (simple log monitoring including systemd and sending error emails if something is found).
Pleasantly surprised this post was about a neat little tool and not another migration manager. This always seemed like a weak spot in regular SQL. Wish it were declarative like Spanner DDL.
When using Postgres, I just try to keep my schema script idempotent and also work with any version of the DB. Like I start with a CREATE TABLE IF NOT EXISTS, then if I add a new col, it goes into there and also a separate ALTER. But at some point it gets cluttered and I delete the ALTERs once things are stable. Maybe something could hit the fan, I have to restore an old backup from before the last purge, and this tool helps me make it compatible quickly.
I built this last summer and thought it was fairly complete. And it turns out I was naive. 6 months later, I have resolved ~100 issues reported by the users.
reply