21–30 of 59 posts
It 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).
I'm curious what makes it better than the approach in the blog?
If it's this:
> also maintains a schema.sql file that can be loaded as a one-off
That's exactly what the declarative files provide
So 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?
1. Migration that adds firstname and lastname columns will all nulls
2. Deploy application code change to start populating firstname and lastname alongside fullname, still reading fullname in the code.
3. backfill the firstname and lastname values with a script/command/migration
4. change app code to read firstname and lastname and stop writing fullname
5. drop the fullname column
This 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.
Alter table foo add column bar;
Update foo set bar=baz;
Alter table foo modify column bar NOT NULL;This 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…
When writing a migration, the resulting schema is usually much, much less important than the characteristics of the migration itself. When I review a migration, my first question isn’t “is this the right schema” but “is this migration going to bring downtime”. I’d much rather a smooth migration to an incorrect schema than having a critical table locked for minutes/hours.
I think that updates of stateful components should be imperative (explicit migrations), not declarative (implicit migrations). For example I don’t think Terraform is great tool to manage RDS: it doesn’t tell you the consequences of changing an attribute (database restart or other downtime-inducing stuff), I’d much rather I had to explicitly say how to get from state A to state B.
Similarly, I don’t think SQL migrations are perfect: they’re still declarative, you still need implicit knowledge to know if a migration will take a lock and what will be the consequences. I’d much rather have to code “take explicit lock; alter table xxx;”.
This tool probably allows editing migrations, but I don’t think it’s a step in the right direction. Maybe it’s a step towards databases being much better at migrations (so that we can rely on never having downtime), but even then I think it’ll get worse before it gets better
This 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…
When implemented well, with appropriate guardrails and linters, it's perfectly safe. And it has many benefits over imperative schema migrations, such as a substantially better Git versioning flow, and ability to synchronize environments / solve schema drift natively.
The only major conceptual downsides are the inability to handle row data migrations, and inability to handle renames. These can be major hurdles for some smaller companies, but are more irrelevant at companies with large DB infrastructure, who require special-case handling for those operations anyway. In other words, if you have large tables, row data migrations are already substantially more complex than running a single UPDATE statement, and you can't do them using a traditional imperative migration tool anyway.
> there is no chance I wouldn't inspect and possibly modify the generated "diff" migration
Of course, you're generally supposed to do that with these tools. Database changes should always be reviewed carefully. Same with non-database-related infrastructure-as-code tools.
So 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…
This implies somebody with admin rights makes alterations in ad-hoc way without first doing it in test env.
If they continue with adhoc stuff, then it means auto-generated migrations will be different in test vs prod. (I prefer to test exactly same thing that will be used in prod)
This 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…
As a concept, declarative schema management isn't crazy at all. Several thousand companies use this approach, including some huge names like Google, Meta, and GitHub, but many smaller companies too. When implemented well, with appropriate guardrails and linters, it's perfectly safe. And it has many benefits over imperative schema migrations, such as a substantially better Git versioning flow, and ability to synchroni…
> Of course, you're generally supposed to do that with these tools
this seems to be the crux of the comments - we'll try to make it much clearer than declarative schemas don't skip any migrations/review process, they just provide another way of generating the migrations (note: you can also generate them directly from the database using the CLI)
So 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?
Side note, but why do folks always bring up this "firstname lastname" example? It is not ever possible to implement that correctly in an automated fashion: some people have spaces in their first names, and some people have spaces in their last names. (I don't mean to single you out, as this exact example comes up multiple times in every declarative vs imperative discussion!)
In my experience, declarative APIs are very powerful abstractions for specific cases where finding the path to the declared state is better left to a machine. This is seldom the case - in most cases, offering the programmer control over the changes leads to better behaviors.
Kubernetes and IaC tools lead the way to a declarative state of infrastructure and these add a ton of value. But, they were also incredibly hard to build - it took many years before Kubernetes eventing and control loop abstracts were rock solid. Most CRD-backed implementations suffer from tons and tons of bugs, and most CRDs are not declarative - they abstract away an imperative operation! I guess this is nothing new - "anything in excess is bad".
Anyways, I think an imperative approach offers much higher control and predictability at a lower cost. The world inherently is imperative.
Earlier quoted context omitted.
>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