5 ms·

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 (Supaba

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 2y ago
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/ 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 2y ago
  Thank you!
jci 2y ago
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 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 2y ago
  Will take a look, thank you!
suralind 2y ago
pg-schema-diff from Stripe, although not sure whether it supports functions, but likely does.
t1mmen 2y ago
https://github.com/t1mmen/srtd 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 2y ago
  This looks pretty damn good. Thank you!
  - tmountain 2y ago
    Just tried it out. This is exactly what I was looking for. Very nice work!
    - t1mmen 2y ago
      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 :)

evanelias 2y ago

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/ 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 2y ago
Thank you!

jci 2y ago

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 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 2y ago
Will take a look, thank you!

suralind 2y ago

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

t1mmen 2y ago

https://github.com/t1mmen/srtd 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 2y ago
This looks pretty damn good. Thank you!
- tmountain 2y ago
  Just tried it out. This is exactly what I was looking for. Very nice work!
  - t1mmen 2y ago
    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 :)