Comment by Guillaume86

2 days ago

Could this be moved into a standalone CLI tool? Is there anything supabase specific about it? I've always wanted SSDT SQL projects for postgres (SSDT is MS declarative schema management solution for SQL Server).

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 "find all references" 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...

4 comments

Guillaume86

Reply
evanelias  2 days ago

There are a few stand-alone declarative Postgres tools available, including Stripe's pg-schema-diff [1], Tusker [2] which wraps Migra [3], and sqldef [4] which supports multiple DBMS.

For sake of completeness for users of other databases: for SQLite check out stb-tester's migrator [5], and last but not least for MySQL/MariaDB there's my tool Skeema [6].

[1] https://github.com/stripe/pg-schema-diff

[2] https://github.com/bikeshedder/tusker

[3] https://github.com/djrobstep/migra

[4] https://github.com/sqldef/sqldef/

[5] https://david.rothlis.net/declarative-schema-migration-for-s...

[6] https://github.com/skeema/skeema

  • Guillaume86  1 day ago

    Thanks for the list, on the commercial side there's also Regate Flyway which looks like a full multi-DB SSDT alternative.

    I wasn't aware of sqldef I will need to take a look. Something nice about SSDT is that when we export a DB to a SQL project (files), each DB object is neatly organized is its own file, and the tooling solve ordering and dependency cycles on its own, I'm not sure any OS tool currently do it like that (skeema maybe? but no PG).

    • evanelias  1 day ago

      Flyway is primarily an imperative migration tool, rather than a declarative-first system. Their paid editions do have support for doing diffs ("state-based") and auto-generating migrations, but it's bolted-on to a workflow that was designed to be imperative-first. From what I can find, their public docs are rather lacking on what types of database objects and edge cases it supports.

      Skeema does indeed separate each DB object to its own file, and each logical schema to its own subdirectory. It also figures out how to correctly apply changes in a valid order, but yeah it's MySQL/MariaDB only. I think some of the declarative FOSS Postgres tools do attempt to provide a correct ordering, but from memory I'm not certain.

      1 reply →