HN Reader

Hey HN!

We have released the initial version of the Postgres Language Server we started working on almost two years ago[0]. You can try it out by downloading the binary from the repo[1]. It is also available on npm, as a vscode extension and via nvim-lspconfig and mason.[2]

We fell into plenty of rabbit holes along the way, but dug our way out of each. We're now using mostly pragmatic, almost naive solutions for our problems.

You can find more details in this blog post.[3]

Try it out and let us know what breaks. Bug reports, ideas, and contributions are all welcome-especially if you want to hack on some Rust.

Last, but not least, we want to give a shoutout to Biome[4]. We spent a lot of time studying their codebase and have been adopting many of their approaches. We wouldn't be here without their work.

[0] Announcement Show HN: https://news.ycombinator.com/item?id=37020610

[1] Repository: https://github.com/supabase-community/postgres-language-serv...

[2] Installation Guides: https://pgtools.dev/#installation

[3] Blog Post: https://www.supabase.com/blog/postgres-language-server

[4] Biome: https://biomejs.dev

co-author here: The most interesting part is probably the parsing of SQL files.

The first issue is that the Postgres parser is complex and always changing, so you can't really roll your own parser to parse SQL. The second is that the parser only works with valid and complete SQL statements, but an LSP should help you with those that are invalid or incomplete.

The simple solution is to actually use two parsers – the original libpg_query that's used by Postgres itself, and tree-sitter for incomplete statements, and merge the parsed results. With that, you can both get a workable AST for diagnostics and tree-sitters CST for autocompletion, for example.

Very nice! Going to check it out over the weekend.

Happy to see this coming along.

For anyone struggling to manage their PL/pgSQL codebase via migration files. This is a game changer.

https://github.com/t1mmen/srtd

The best I've seen is jetbrains. Not only does it parse the query but it matches against actual tables if you give it a source.

The crazy part is it can do this for a SQL string in your code.

>This project provides a toolchain for Postgres development,

I'm a little bit confused about what "Postgres development" is in this context. Is this for people writing stored procedures in Postgres? Or people writing other kinds of large and complex queries that merit language server support?

I'm just kind of curious what uses cases are out there for something like this.

Is there a way to make this work inside a browser, via monaco-editor?

If so, do you have any examples, or recommendations?

Assuming it works, this is a game changer. Currently I use DBeaver for SQL linting/autocomplete which is great.

What about parsing Py/C++/Rs/Java for SQL statements in strings? Perhaps by using multiline strings, VS Code could use a different language server depending on wether the line is within an SQL multiline string? That would allow statically checking and autocompleting SQL statements within strings in code. What are your thoughts on that?

In an ideal world there is static checkability of everything. Somehow, ideally, potential errors must be caught before runtime. For many type safe languages we can do this. With SQL we can do this now thanks to this language server. However, will it work when the SQL is embedded in code?

I'm very happy that something like this exists!

I'm wondering why have there have been no good IDE experiences so far for Postgres? Or put another way, what has been the most challenging part of building this?

Nothing I have tried so far comes close to what I'm used to with statically typed languages. One would think something as strict as Postgres would have good autocomplete by now but I've yet to find something.

This is awesome. Does anyone know if it'll possible to integrate it with tools like SQLX (Rust's pseudo-ORM) to get type hinting when writing in-like SQL code within Rust, or just have it work when I'm writing SQL within a simple Python script? That would truly be next-gen.

This is cool-- but I rarely write sql in .sql files. I tend to use things like lib/pq in Golang or sqlx in Rust for writing raw sql in .go or .rs files.

Any plans on supporting such workflows?

Really excited about trying this, great job so far!

I think formatting/prettier-type functionality was mentioned as a possibility of this project, is that still in the cards?

(I can’t seem to find a formatter that understands stored procedure; does it even exist?)