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 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.

I think we are getting close to the peak of "declarative"—or rather, I hope we are near the peak.

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.

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 a new database, and updating an existing database.

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).

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.