[tmountain]

As someone who’s written a ton of PL/pgSQL code recently, I have been saving all my functions and their subsequent revisions in database migration files (Supabase). This feels like such a huge step backwards compared to browsing a well structured codebase with namespaces and modules. I know that Postgres provides schemas, and I am using them to a limited extent, but I am really speaking towards just having a tree oriented set of files on disk like I would with TypeScript, Golang, etc… and a way to refresh this tree against what’s currently active in my database. Had anyone else had a similar issue or better still, resolved it?

[evanelias]

Declarative tools/workflows solve this much more elegantly than imperative migration tools. There's some initial discussion in sibling comment thread here, although that isn't specific to procedures/functions.

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.

[tmountain]

Thank you!

[t1mmen]

https://github.com/t1mmen/srtd might help here. The general idea is to define functions/policies/etc as «SQL templates», your source of truth. The templates built into Supabase migrations.

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.

[tmountain]

This looks pretty damn good. Thank you!

[tmountain]

Just tried it out. This is exactly what I was looking for. Very nice work!

[t1mmen]

Thank you, I’m glad to hear it works well for others too!

If you come across issues or can think of anything that would improve it, please let me know :)

[jci]

Yeah, I’m in the midst of building out something for this. Involves a few pieces, the first is being able to apply the files in the right order https://github.com/joshainglis/topcat Essentially the output of that should be a file that should drop and recreate everything. Easy to build some automation around. And if you’re just operating on functions and views ect that should be all you need to allow a tree based code-base. If you also want to handle tables etc you’ll want to add a diffing tool (migra, atlas) etc.

[tmountain]

Will take a look, thank you!

[suralind]

pg-schema-diff from Stripe, although not sure whether it supports functions, but likely does.