[narrowtux]

> The typical way to do schema migration is to compose a list of ALTER TABLE statements. This becomes hard to track the latest schema state as the migration accumulates. It's more intuitive for the developers to specify the desired state. Ideally, PostgreSQL could allow developers to specify the desired CREATE TABLE schema, the engine then reconcile it with the latest schema, figure out the diff, and plan the migration path internally.

This is a terrible idea. The behaviour of engines that do this is unpredictable and suddenly you lose data because the engine deleted a column.

[evanelias]

If a tool blindly drops columns, that's just a bad tool! It doesn't mean the concept is flawed.

Thousands of companies successfully use declarative schema management. Google and Facebook are two examples at a large scale, but it's equally beneficial at smaller scales too. As long as the workflow has sufficient guardrails, it's safe and it speeds up development time.

Some companies use it to auto-generate migrations (which are then reviewed/edited), while others use a fully declarative flow (no "migrations", but automated guardrails and human review).

I'm the author of Skeema (https://github.com/skeema/skeema) which has provided declarative flow for MySQL and MariaDB since 2016. Hundreds of companies use it, including GitHub, SendGrid, Cash App, Wix, Etsy, and many others you have likely heard of. Safety is the primary consideration throughout all of Skeema's design: https://www.skeema.io/docs/features/safety/

Meanwhile a few declarative solutions that support Postgres include sqldef, Migra, Tusker (which builds on Migra), and Atlas.

[__alexs]

This is exactly how auto migrations in things like Django and Prisma work. Yes you need to check the changes before you apply them but most of the time they are entirely sensible.

[dextro42]

Wouldn't it be more interesting to have this as an external tool?

You input your create table statement and it issues you back the migration statements? Then you can check it against your development database or whatever and if you feel fine use it?

This way you could check and modify the migration path without writing the alter statements.

This is one of the most frustrating thinks with sqlite for me. Changing a table doesn't always work with an alter statement but sometimes you need to drop and recreate it with the new columns. Why can't they do the magic for me. It's really frustrating and was often enough the sole reason I used postgres for private projects.

[evanelias]

There are actually a bunch of external tools that offer declarative schema management flow. Personally I agree that that the overall flow should be external to the DB, but it would be useful if databases could offer more built-in functionality in this area to make these tools easier to write.

For sqlite in particular, check out https://david.rothlis.net/declarative-schema-migration-for-s... and https://sqlite-utils.datasette.io/en/stable/python-api.html#...

[magicalhippo]

We do exactly this, but with our own "ground truth" schema in the form of an XML file defining the various tables, views, triggers and so on.

We then have a program which compares the latest schema from XML to a given database, and performs a series of CREATE, ALTER and so on to update the database so it conforms.

Since we've written it ourselves we have full control over what it does and how it does it, for example it never issues DROP on non-empty tables/columns or similar destructive actions.

We've had it for a long time now and it's worked very well for us, allowing for painless autonomous upgrades of our customers on-prem databases.

[jzig]

I find this interesting! Is there anything similar in the open source world? I have built projects with Supabase in the past and one of my gripes about it is that it becomes obnoxious to track what happens to the schema over time in version control with dozens of migration files. For example, you have no idea what the current state of a table or trigger is because there might be several updates that occured over four dozen migration files and you may not even be sure which files those changes are in. You have to just look at the database directly rather than the code.

[magicalhippo]

I haven't stumbled over anything, but I wouldn't be surprised if it exists. It's not magic, my colleague wrote the first version in a few days, and we've iterated on it since.

It just requires that there are some system views or similar that you can use to extract the current database schema, so you have something to compare against.

Our tool goes through the XML file and for each table runs a query to find the current columns, and for each column find the current configuration. Then compare with the columns in the XML file and decide what to do for each, ALTER, DROP or ignore (because possible data loss) etc. Datatype changed from "int" to "varchar(50)"? Not a problem since 50 chars are enough to store the largest possible int, so issue ALTER TABLE. Column no longer present? Check if existing column has any data, if not we can safely DROP the column, otherwise keep it and issue warning.

Views, triggers and stored procs are replaced if different. We minimize logic in the database, so our triggers and stored procs are few and minimal.

Materialized views require a bit of extra handling with the database we use, in that we can't alter but have to drop and recreate. So we need to keep track of this.

As you say it's very nice to use as a developer, as you only have to care about what the database should look like at the end of the day, not how it got there. Especially since almost all of our customers skip some versions (we release monthly).

[endisneigh]

It’s not inherently a bad idea, though. I do agree it would likely be poorly implemented.

[dextro42]

Why would you think so? The PostgreSQL developers didn't stick out by delivering bad software/features so far. If they would implement this I would actually expect a pretty good way with even better documentation.

[dumbo-octopus]

The creation of the backing migration plan would have to be interactive, certainly. But that doesn't mean the concept as a whole is destined for failure.