[rjbwork]

My pipe dream is a terraform-like, delcarative, cross-dialect way to manage database schemas/code.

Sql Server kind of gets there with the .sqlproj and DACPAC stuff but it is quite fiddlesome to setup. I've only seen liquibase as a semi-close alternative in the FOSS space and it really just works with explicitly defined migration chains AFAICT rather than semantic diff and change generation.

I think if anyone is going to bring something like that to market, even for just postgres, it will be SupaBase.

[evanelias]

Doing this properly in a generic cross-dialect fashion is quite challenging – not just due to the SQL itself, but also the operational differences in schema changes across different databases. By this I mean considerations like locking, online schema change (non-blocking/non-disruptive), detecting risky/destructive changes, and so forth. Often these topics have important differences between major versions of the same DBMS, let alone between completely different databases.

Fully understanding that requires years of hands-on DBA-equivalent experience, and very few people actively have that knowledge across multiple DBMS products while also being software engineers.

My tool Skeema has offered declarative pure-SQL schema management for MySQL/MariaDB since 2016, see https://github.com/skeema/skeema, but the architecture isn't extendable to other database systems. The design is pretty specific to the first-class concerns of schema changes in MySQL/MariaDB, for example use of external OSC tools, generic sharding support, MySQL-like option handling, no need for transactional behavior since DDL isn't transactional, etc.

Lately I've been fiddling with a design for a separate more-generic/multi-DBMS product, but it's very slow going. It requires constant research into how each DBMS handles various fine details and tweaking things accordingly, because my depth of experience is in MySQL and not Postgres, sqlite, etc. At this early stage I'm not sure how it will end up.

[take-five]

Something like https://atlasgo.io/?

[rjbwork]

Similar. But this looks like it requires generating explicit migrations. The equivalent would be if every time you wanted to make a change to your terraform, you had to plan and save the generated plan file to your repository.

Still not quite the right workflow IMO. I think TF nails it and that SQL things are held back by legacy thinking in the space.

[take-five]

From their docs [1] it seems that they support a workflow similar to Terraform.

[1]: https://atlasgo.io/declarative/apply

[rjbwork]

Ahh, so they do! Their quick start link took me to their "versioned" workflow which is...basically the same thing any of a dozen tools has done for decades. Strange landing page choice to funnel into your market equivalence rather than differentiator.