Declarative Schemas for simpler database management
1–10 of 59 posts
Re: Declarative Schemas for simpler database management
#2Combining this with the Postgres Language Server that was released this week, you can now execute statements directly within your IDE:
Re: Declarative Schemas for simpler database management
#3This is exactly backwards. You should have declarative schemas but inferring migrations is crazy. Only pain will follow.
Instead, I am a fan of doing both: either committing the resulting schema of a migration, or hand writing it aside the migration. Then have tests to ensure the database schema matches the expected schema after a migration.
Generating these artifacts is fine, but in TFA's case there is no chance I wouldn't inspect and possibly modify the generated "diff" migration. It's significantly easier to go the other way: write the migration and show me the resulting schema diff.
Re: Declarative Schemas for simpler database management
#4So 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 a new database, and updating an existing database.
Re: Declarative Schemas for simpler database management
#5This is exactly backwards. You should have declarative schemas but inferring migrations is crazy. Only pain will follow. Instead, I am a fan of doing both: either committing the resulting schema of a migration, or hand writing it aside the migration. Then have tests to ensure the database schema matches the expected schema after a migration. Generating these artifacts is fine, but in TFA's case there is no chance I w…
It seems like generating the diffs from the schema's version history is equivalent to doing it the opposite way, provided that each diff is tested to make sure the database upgrade works. Not all diffs will correspond to feasible database upgrades, so some patches would have to be rejected.
Re: Declarative Schemas for simpler database management
#6Sorry if asked and answered: can you hand-edit the generated migrations? Like, what if I want to do a create index concurrently or something?
Re: Declarative Schemas for simpler database management
#7So 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 automatically. https://learn.microsoft.com/en-us/sql/tools/sql-database-pro...
I helped set this up in a fortune 500 company a few years ago. They were using a team of db engineers to execute manually written change scripts, with manual reviews, control processes, and deployment schedules. You'd be lucky if you got your schema change to prod in a month. 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.
Re: Declarative Schemas for simpler database management
#8So 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…
>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 occur at significant temporal distances, and fail it if not, just so that we can be sure what we've read and approved matches what gets executed.
Re: Declarative Schemas for simpler database management
#9Sorry if asked and answered: can you hand-edit the generated migrations? Like, what if I want to do a create index concurrently or something?
yes, you can edit the migrations. The steps are for adding a new column, for example:
1/ Add a new column to the declarative file
2/ Generate a new migration: `supabase db diff -f my_new_migration`
3/ Review/edit the generated migration
Docs are here:
https://supabase.com/docs/guides/local-development/declarati...
Re: Declarative Schemas for simpler database management
#10You'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. Good for whoever you just bought a car.