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
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?
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.
> 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
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.
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 })
[0] https://github.com/supabase-community/postgres-language-serv...
I'll file an issue with your exact query.
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.
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.
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?
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
https://discuss.python.org/t/pep750-template-strings-new-upd...
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
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.
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.
Any plans on supporting such workflows?
The crazy part is it can do this for a SQL string in your code.
# 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
#!/usr/bin/env python
# language=sql
MY_AWESOME_QUERY = """
WITH awesome AS (
-- ...
) SELECT * FROM awesome
"""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.
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.
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?)
If so, do you have any examples, or recommendations?
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.