A Language Server for Postgres | Hacker News Reader

A Language Server for Postgres

github.com

99 pointstbatchelli22 comments

Previously: Postgres Language Server (963 points, 121 comments, 2023) (https://news.ycombinator.com/item?id=37020610)

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?

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.

Thank you!

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.

This looks pretty damn good. Thank you!

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

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 :)

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.

Will take a look, thank you!

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

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.

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.

Something like https://atlasgo.io/?

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.

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

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

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.

It's past time to move beyond LSP. However I'm glad to see more language servers because that means there will be more who will want to improve the situation.

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!

Can we get an (easy to use) vscode debugger for plpgsql next?

Would love to get this plugged into intellij/datagrip

I’ll second the confusion.

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.

IntelliJ got pretty good support for postgres language already. What else would this bring?

Reply on news.ycombinator.com