We built something similar for the managed DB we use, but i think it's a mistake to autogenerate the migration scripts instead of autogenerating the schema from the migration. Things like changing an enum, adding a nonnull column that shouldn't have a default to an existing table that already has data in it, and migrating data from one representation to another (eg, 'oh hey, we definitely shouldn't have made our users table have an fname and an lname field. let's change to full_name and preferred_name') are easily done in a migration script but hard, if not impossible, to infer from just schema changes.
Declarative Schemas for simpler database management
11–20 of 59 posts
Re: Declarative Schemas for simpler database management
#12It seems to me Rails has been doing this but better for years.
It definitely keeps atomic and historical migrations, but also maintains a schema.sql file that can be loaded as a one-off (e.g. for mock DBs in tests).
Re: Declarative Schemas for simpler database management
#13I use ZenStack. In future releases they are moving away from supabase…
Re: Declarative Schemas for simpler database management
#14Earlier quoted context omitted.
I've used this for Microsoft SQL Server and SQL Database Projects. It's basically as you say: write it as if creating a new database, then deploy it in CI where it does a diff on the live database to come up with the actual migration strategy on the fly. If you're clever you add a manual review stage in the pipeline and have the db engineers approve the generated migration script before deployment is completed automa…
>If you're clever you add a manual review stage in the pipeline and have the db engineers approve the generated migration script before deployment is completed automatically. This is how I've set it up at my current employer. It works well. We modeled it after the Terraform Plan/Apply steps, and double check that the script generated by the "apply" step matches the script generated by the "plan" step, since these can…
> db engineers approve the generated migration script
yeah - this is definitely the intended flow here. We won't be recommending anyone blindly applying generated migrations.
As you mention, it is expected that you generate & review on your local development machine, check into source control, push & merge. We've also been using this internally for ~2 years now and it works great
Re: Declarative Schemas for simpler database management
#15See also:
“Simple declarative schema migration for SQLite”
https://david.rothlis.net/declarative-schema-migration-for-s...
Discussed previously:
https://news.ycombinator.com/item?id=31249823
Disclosure: I am the co author of that article
Re: Declarative Schemas for simpler database management
#16So to summarize. In the old situation, you write CREATE TABLE statement at the start of the project. And when you add a feature, you have to write an ALTER TABLE script. In this new situation, you just change the CREATE TABLE script. And Supabase uses migra to figure out the difference and it automatically alters the table. What's interesting is that in your SQL code, there's no longer any difference between creating…
But if you have data to migrate, it is not always possible to infer the diff, no?
Say I have a users table with a name column. Then I alter the table and split the name column into two new columns: first name and last name.
How is it possible to infer this change, just from seeing the new and old columns?
Re: Declarative Schemas for simpler database management
#17So to summarize. In the old situation, you write CREATE TABLE statement at the start of the project. And when you add a feature, you have to write an ALTER TABLE script. In this new situation, you just change the CREATE TABLE script. And Supabase uses migra to figure out the difference and it automatically alters the table. What's interesting is that in your SQL code, there's no longer any difference between creating…
I've used this for Microsoft SQL Server and SQL Database Projects. It's basically as you say: write it as if creating a new database, then deploy it in CI where it does a diff on the live database to come up with the actual migration strategy on the fly. If you're clever you add a manual review stage in the pipeline and have the db engineers approve the generated migration script before deployment is completed automa…
> When they started integrating this tool on some trial candidates they found SO many inconsistencies between environments: server settings differences, extraneous or missing indexes, vestigial "temp" tables created during previous migrations, enum tables that should be static with extra or missing rows, etc, etc. All the environment differences meant that deployments had to be manual in the past. Once they got through the initial pain of syncing up the environments the whole department got way more efficient.
That was exactly our experience too.
Perhaps we didn't highlight enough in the blog post that schema diff was not meant to replace manual review. It simply provided a good starting point for us to iterate on the migration, which often boosts efficiency.
Re: Declarative Schemas for simpler database management
#18am i missing something? what does this offer over raw sql? it honestly looks very similar
Re: Declarative Schemas for simpler database management
#19am i missing something? what does this offer over raw sql? it honestly looks very similar
It is raw sql. The update here is more of a "workflow" change.
In the past we only offered tools to create a sql migration to make a change to your database. Now you can write the state of your database into files, then the migration is generated for you.
This is very similar to something like Rails/Phoenix/whatever, where you write your models and then it generates a change. The difference is that here you write your models in raw SQL, rather than an ORM
Re: Declarative Schemas for simpler database management
#20You're going to regret this. The thing you need to be doing is testing your migrations, and some dumbass on your team is going to generate the migration during CI and load it into your database as a merge step, and you won't realise what a mistake this was until possibly years later. The good news, is you might be able to pay someone an enormous amount of money to unfuck things. Not good for you, I mean, obviously. G…
we at tahoe labs dot io are here for you in that case.