[HN Gopher] Postgres Language Server: Initial Release
___________________________________________________________________
Postgres Language Server: Initial Release
Author : steinroe
Score : 331 points
Date : 2025-03-29 09:13 UTC (1 days ago)
(HTM) web link (github.com)
(TXT) w3m dump (github.com)
| steinroe wrote:
| 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
| kiwicopple wrote:
| 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
| vaylian wrote:
| Language servers rule. Thanks for doing this!
| steinroe wrote:
| 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.
| d4rkp4ttern wrote:
| What does a language server do? Asking for those who missed the
| memo.
| rad_gruchalski wrote:
| > 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
| pdpi wrote:
| 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.
| steinroe wrote:
| 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.
| thom wrote:
| 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.
| mjw1007 wrote:
| 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?
| juleswritescode wrote:
| 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.
| jmholla wrote:
| > 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.
| homebrewer wrote:
| > 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.
| steinroe wrote:
| 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.
| piokoch wrote:
| 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).
| ayhanfuat wrote:
| 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;`).
| juleswritescode wrote:
| 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.
| juleswritescode wrote:
| steinroe is a very fast man: https://github.com/supabase-
| community/postgres-language-serv...
| ayhanfuat wrote:
| That's great, thank you both!
| steinroe wrote:
| thanks for the report! that was an oversight. pr with the fix
| is up.[0]
|
| [0] https://github.com/supabase-community/postgres-language-
| serv...
| crooked-v wrote:
| Any chance this will include formatting in the future?
| juleswritescode wrote:
| Definitely. Of course, I can't promise that we can make it
| work properly, but it's high on our todo list.
| javajosh wrote:
| What are some of the most impactful/eye-opening lessons you
| learned from biome?
| steinroe wrote:
| 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.
| casper14 wrote:
| Very nice! Going to check it out over the weekend.
| Pedro_Ribeiro wrote:
| 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.
| steinroe wrote:
| 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
| pphysch wrote:
| 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...
| stevage wrote:
| >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.
| juleswritescode wrote:
| 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.
| juleswritescode wrote:
| 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.
| edg5000 wrote:
| 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?
| steinroe wrote:
| 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
| norman784 wrote:
| 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.
| steinroe wrote:
| 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
| tmountain wrote:
| For anyone struggling to manage their PL/pgSQL codebase via
| migration files. This is a game changer.
|
| https://github.com/t1mmen/srtd
| steinroe wrote:
| 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.
| perrygeo wrote:
| 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
| t1mmen wrote:
| Oh, fun to see my project mentioned on HN! I'm glad to see it's
| useful to others :)
| 0xa2 wrote:
| Happy to see this coming along.
| vendiddy wrote:
| 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.
| doctorpangloss wrote:
| IntelliJ works great. Nothing you've tried in VS Code "comes
| close."
| steinroe wrote:
| 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.
| lurking_swe wrote:
| 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.
| matus_congrady wrote:
| Is there a way to make this work inside a browser, via monaco-
| editor?
|
| If so, do you have any examples, or recommendations?
| steinroe wrote:
| 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.
| t1mmen wrote:
| 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?)
| steinroe wrote:
| 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.
| ninetyninenine wrote:
| 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.
| mdaniel wrote:
| 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 """
| bitbasher wrote:
| 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?
___________________________________________________________________
(page generated 2025-03-30 23:01 UTC)