Regarding stored procs/funcs in particular and managing them like a codebase using a declarative tool, I have a blog post about this at https://www.skeema.io/blog/2023/10/24/stored-proc-deployment... – and although my product is specific to MySQL, a lot of the concepts in the first half of that post are generic and apply to any declarative tool. Some FOSS solutions to look into for Postgres include sqldef and Tusker.
While developing, srtd can hot/live reload your templates into the local db when changed.
I built this to scratch my own itch, and it’s been working VERY well for us. Huge DX benefits, and it’s made code reviews a LOT easier, since the reviewer can focus on what’s actually changed.
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.
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.
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.
Edit: they're right with me on this. From the README: "It is built on a Server-Client architecture with a transport-agnostic design." Way to go!
I’m interested in this for the opposite reason, jetbrains have fantastic sql support but I’d rather have a lang server that gives me a ‘good enough’ experience in the substantially faster vscode or zed.
