Postgres Language Server: Initial Release | Hacker News Reader

Postgres Language Server: Initial Release

github.com

362 pointssteinroe52 comments

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

I suggest it would be helpful if the README's intro expanded a little more on the project's scope. "for postgres" is a bit vague, and "focusing on developer experience and reliable SQL tooling." doesn't tell me much about what it actually does.

If I was coming to this project for the first time I think the questions I'd immediately like the answers to are:

- What language is this language server for? Is it just for SQL proper, or for PL/pgSQL, or writing C extensions, or what?

- What makes it PostgreSQL-specific? Is it just that it follows PostgreSQL's dialect of SQL?

- Does it expect to be told about your database's schema? And if so, do you tell it by giving it a database connection, or letting it see files defining the schema, or what?

Thanks for the feedback. Good points, we'll improve the README.

Regarding your questions:

1. It's currently only for SQL statements, but we'll work on function bodies and PL/pgSQL soon. 2. Exactly, we follow the the PostgreSQL dialect (we use Postgres's parser) 3. You can give it a database connection and it will query some pg_catalog tables. If you don't provide connection details, we'll bypass features requiring that.

> You can give it a database connection and it will query some pg_catalog tables. If you don't provide connection details, we'll bypass features requiring that.

It'd be nice if users could manually obtain and provided that information.

I agree. But you can just point it to an empty db with your schema.

For those who don’t read the blog post, I thought this was pretty cool:

> The idea to use tree-sitter in addition to libpg_query came from feedback[0] on our previous HN post, so thank you for that

https://news.ycombinator.com/item?id=38570680

What does a language server do? Asking for those who missed the memo.

The Language Server Protocol is essentially a standard for editors to talk to language tooling. All the usual refactor/go-to-definition/auto-complete functionality you expect from a modern IDE is implemented in a language-agnostic way in the editor in terms of calls to the language server, and language servers then provide the actual concrete implementations of that functionality.

IIRC Microsoft defined the spec in the context of creating VS Code, but these days every editor and their uncle supports it (from Vim and Emacs to Sublime Text and even IntelliJ). You can get a solid IDE-like experience for most mainstream-ish languages, and on most editors, through LSP.

Basically every editor decided it would be cool to be as slow as Emacs (said with love, as an Emacs user), and so now all the clever stuff is done in potentially several out of proc web servers, each of which uses gigabytes of RAM.

> The Language Server Protocol (LSP) is an open, JSON-RPC-based protocol for use between source code editors or integrated development environments (IDEs) and servers that provide "language intelligence tools":[1] programming language-specific features like code completion, syntax highlighting and marking of warnings and errors, as well as refactoring routines. The goal of the protocol is to allow programming language support to be implemented and distributed independently of any given editor or IDE.[2] In the early 2020s, LSP quickly became a "norm" for language intelligence tools providers.[1]

https://en.m.wikipedia.org/wiki/Language_Server_Protocol

thanks for asking! its what provides all language intelligence features in your IDE. so autocompletion, diagnostics, syntax highlighting etc. the postgres language server currently supports autocompletion, syntax error highlighting, type-checking and linting.

I am pretty excited about this. Thank you for all your effort.

My initial tests fails whenever there are CTEs. Are they not supported? I get

   processing panicked: cannot start statement within statement at Some(Token { kind: With, text: "with", span: 0..4, token_type: ReservedKeyword })

whenever I try a file containing a CTE (this one was a file with this query: `with test as (select 1 as id) select * from test;`).

thanks for the report! that was an oversight. pr with the fix is up.[0]

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

Thanks for the report. Hmm, we have some tests for CTEs, so at least we tried to support them.

I'll file an issue with your exact query.

steinroe is a very fast man: https://github.com/supabase-community/postgres-language-serv...

That's great, thank you both!

> we started working on almost two years ago

Jesus, this really puts it into perspective how much effort JetBrains have put into their IDEs, which have had superb support for all popular SQL dialects for as long as I can remember. Thank you for providing the community with a FOSS alternative, because IIRC there wasn't anything remotely comparable to JetBrains up until now.

to put things into perspective: even though it took a lot of effort, it's just a side project by two people who used it to learn rust along the way. A full-time team would have finished much faster.

That's a bit of exaggeration, IntelliJ DB tool is okeish, but there is nothing special about it. Even rather dated SQL Squirell is equally good, or even better in some places (like keeping run sqls history, better configurability of unusual parameters of some more exotic databases, like, say, DB2).

Language servers rule. Thanks for doing this!

my pleasure! I had my ide point to the debug build locally for over a year now, and it has been very rewarding to slowly see it mature (as in crash less) during my day job over time.

Any chance this will include formatting in the future?

Definitely. Of course, I can't promise that we can make it work properly, but it's high on our todo list.

What are some of the most impactful/eye-opening lessons you learned from biome?

I learned rust by doing this project. didn't have much prior systems programming experience too. usually, I learn best by just trying things until they work, but building a language server is pretty complex. after reading through a lot of similar projects, biome was the easiest to reason about. and it has exactly the architecture I had in mind: a generic workspace api where the language server is just one of many entry points.

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.

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?

its still a bit rough around the edges, but we hope to kaizen our way through based on the bug reports from the community!

about embedded sql: you are right, this must be solved on the editor side. in vscode, it should be possible via request forwarding [0]. for neovim there are plugins like otter.nvim [1].

and at least for js, we are planning to add direct support for it in our workspace api so that `postgrestools check file.ts` will emit diagnostics for embedded sql. this is only feasible because we can easily parse js/ts code in rust via oxc[2] though. are you aware of similar tools in other languages?

[0] https://code.visualstudio.com/api/language-extensions/embedd... [1] https://github.com/jmbuhr/otter.nvim [2] https://oxc.rs

Could this run as a Typescript Language Server plugin? AFAIK Svelte, Vue, etc does something similar to be able to use Typescript inside their own template files. It will be a game changer if you could use the Postgres Language Server inside your codebase.

I did some research on it and afaik, all these tools run their language services as typescript plugins within the tsserver itself. this means they do not communicate to their own language server running next to it. right now, I am thinking to a. make a wasm build work and then try my luck with the tsserver plugin and b. enable embedded sql support for typescript in the CLI at least by parsing the code with oxc

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.

Python 3.14 will likely add PEP-750 t-strings which will make LSP integrations like this much more tangible.

https://discuss.python.org/t/pep750-template-strings-new-upd...

that's something we are currently looking into for typescript. at first, I thought a tsserver plugin will do. but a bit of research suggested that such a plugin can not call other language servers. this must be solved on the editor side instead. in vscode, it should be possible via request forwarding [0]. for neovim there are plugins like otter.nvim [1].

and at least for js, we are planning to add direct support for it in our workspace api so that e.g. `postgrestools check file.ts` will emit diagnostics for embedded sql.

[0] https://code.visualstudio.com/api/language-extensions/embedd... [1] https://github.com/jmbuhr/otter.nvim

>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.

Good question. If you rarely change your db, it'll probably not be as useful.

But some platforms (such as Supabase) rely on many parts of Postgres.

You use INSERT hooks to trigger queue insertions, Row Level Security (RLS) to secure data, SQL functions for aggregate queries. Plus the schema changes you do to support business use-cases.

Normally, you'll write these things into SQL migration files. Without an LSP, you'd have to look up the current state/implementation of schemas/functions, and you'd have to run the migrations to see whether there are errors. With the LSP, that's easier.

steinroe and I both use a lot of migrations in our day jobs (a whatsapp newsletter and a fintech startup).

Hope this helps.

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

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?

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.

Not just in code, anywhere that accepts a comment (or carries a JSON Schema), e.g.

  # just as an example, I'm pretty sure that
  # IJ already knows this is SQL
  spring:
    datasource:
      hikari:
        # language=sql
        connection-test-query:
          SELECT count(1) FROM my_table

and also in not-obviously-sql literals

  #!/usr/bin/env python
  # language=sql
  MY_AWESOME_QUERY = """
  WITH awesome AS (
  -- ...
  ) SELECT * FROM awesome
  """

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.

IntelliJ works great. Nothing you’ve tried in VS Code “comes close.”

Not sure what you’re looking for exactly.

If it’s just a nice GUI for interacting with postgres, Postico (for mac) is fantastic. Autocomplete is good, and the UI is very intuitive if you’re familiar with mac. It’s also a native app so it’s wicked fast. None of that web app pretending to be an app nonsense lol.

If you’re looking for a better experience interacting with a postgres server in a static typed language, like java, i’d check out Jooq. It’s been a while since i’ve used it but it basically lets you query a postgres database with proper types in your java code.

we write about this in the blog post, but the tldr is that the Postgres syntax is ever-evolving and very verbose. its almost impossible to properly parse Postgres code in a sustainable way. all these tools usually try to do exactly that and eventually give up. we are building upon libpg_query instead, which is the actual Postgres server code extracted into a C library. that parser is built to parse executable SQL though, so we had to find a few workarounds to make it work.

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

its still in the cards! it will be more like a pretty printer instead of a formatter though. Meaning we will prettify valid code only. but its a bigger effort, and we want to focus on a stable basis first.

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

https://github.com/t1mmen/srtd

In a similar vein, Tusker is helpful tool that diffs your declarative schema and auto-generates migrations. It uses live postgres connections so you can connect and diff against a running instance too, allowing you to sync up multiple instances. You still need something to run the migrations, but generating them is fully automated. https://github.com/bikeshedder/tusker

Oh, fun to see my project mentioned on HN! I’m glad to see it’s useful to others :)

that is really awesome! declarative schema management is also high on my bucket list, and might even become part of this project. thanks for sharing, will check it out.

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

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

I do not have experience with monaco, but you should be able to run the language server remotely and connect to it from the editor via the usual language server protocol.

we currently do not provide a wasm build which would enable us to run the server within the browser too, although that's something I am actively poking around with.

Happy to see this coming along.

Reply on news.ycombinator.com