Declarative Schemas for simpler database management

This is exactly backwards. You should have declarative schemas but inferring migrations is crazy. Only pain will follow.<p>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.<p>Generating these artifacts is fine, but in TFA&#x27;s case there is no chance I wouldn&#x27;t inspect and possibly modify the generated &quot;diff&quot; migration. It&#x27;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 &quot;declarative&quot;—or rather, I hope we are near the peak.<p>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.<p>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 - &quot;anything in excess is bad&quot;.<p>Anyways, I think an imperative approach offers much higher control and predictability at a lower cost. The world inherently is imperative.

So to summarize.<p>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.<p>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.<p>What&#x27;s interesting is that in your SQL code, there&#x27;s no longer any difference between creating a new database, and updating an existing database.

We built something similar for the managed DB we use, but i think it&#x27;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&#x27;t have a default to an existing table that already has data in it, and migrating data from one representation to another (eg, &#x27;oh hey, we definitely shouldn&#x27;t have made our users table have an fname and an lname field. let&#x27;s change to full_name and preferred_name&#x27;) are easily done in a migration script but hard, if not impossible, to infer from just schema changes.

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

You&#x27;re going to regret this.<p>The thing you need to be doing is <i>testing</i> 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&#x27;t realise what a mistake this was until possibly <i>years</i> later.<p>The good news, is you <i>might</i> 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.

Could this be moved into a standalone CLI tool? Is there anything supabase specific about it? I&#x27;ve always wanted SSDT SQL projects for postgres (SSDT is MS declarative schema management solution for SQL Server).<p>SSDT can also sync db projects (nicely organized DDL .sql files representing the schema) and databases (one way or the other), with the IDE support you can do stuff like &quot;find all references&quot; on a column or any other DB object, and build the project to check for errors. Linting the schema becomes possible, etc I have a hard time when I have to go back to imperative schema management...

Sorry if asked and answered: can you hand-edit the generated migrations? Like, what if I want to do a create index <i>concurrently</i> or something?

&gt;declarative<p>&gt;create_table<p>Commands are not declarative. You want a single file with the state of your schema in it? We call that a schema file. It is strictly a negative to declare your schema in SQL, an imperative language. There is no way to guarantee this create_table will run safely, because it&#x27;s not meant to ever be run.

Combining this with the Postgres Language Server that was released this week, you can now execute statements directly within your IDE:<p><a href="https:&#x2F;&#x2F;x.com&#x2F;psteinroe&#x2F;status&#x2F;1907803477939527728" rel="nofollow">https:&#x2F;&#x2F;x.com&#x2F;psteinroe&#x2F;status&#x2F;1907803477939527728</a>

So I guess this is a bit the same as using something like Drizzle to define your tables and handle your migrations?

I use ZenStack. In future releases they are moving away from supabase…

am i missing something? what does this offer over raw sql? it honestly looks very similar

See also:<p>“Simple declarative schema migration for SQLite”<p><a href="https:&#x2F;&#x2F;david.rothlis.net&#x2F;declarative-schema-migration-for-sqlite&#x2F;" rel="nofollow">https:&#x2F;&#x2F;david.rothlis.net&#x2F;declarative-schema-migration-for-s...</a><p>Discussed previously:<p><a href="https:&#x2F;&#x2F;news.ycombinator.com&#x2F;item?id=31249823">https:&#x2F;&#x2F;news.ycombinator.com&#x2F;item?id=31249823</a><p>Disclosure: I am the co author of that article