Postgres Language Server

[kiwicopple]

hey HN. this is a Language Server[0] designed specifically for Postgres. A language server adds features to IDEs (VSCode, NeoVim, etc) - features like auto-complete, go-to-definition, or documentation on hover, etc.

there have been previous attempts at adding Postgres support to code editors. usually these attempts implement a generic SQL parser and then offer various "flavours" of SQL.

This attempt is different because it uses the actual Postgres parser to do the heavy-lifting. This is done via libg_query, an excellent C library for accessing the PostgreSQL parser outside of the server. We feel this is a better approach because it gives developers 100% confidence in the parser, and it allows us to keep up with the rapid development of Postgres.

this is still in early development, and mostly useful for testers/collaborators. the majority of work is still ahead, but we've verified that the approach works. we're making it public now so that we can develop it in the open with input from the community.

a lot of the credit belongs to pganalyze[1] for their work on libpg_query, and to psteinroe (https://github.com/psteinroe) who the creator and maintainer.

[0] LSP: https://microsoft.github.io/language-server-protocol/

[1] pganalyze: https://pganalyze.com/

[codeptualize]

Letss gooo!! Supabase keeps on delivering things that I need.

I am regularly surprised how bad the tooling is for SQL/databases. Especially that there is no decent formatter that supports plpgsql, and doesn't absolutely mangle your queries (sometimes actually breaking your queries..).

Best options atm are imo TablePlus and DataGrip, I have tried a bunch of options in vscode and although there are useful tools, it's all not really there.

(Also excited to see what else they will release this launch week!)

[bob1029]

> I am regularly surprised how bad the tooling is for SQL/databases

Agreed. Even when using something open like SQLite, you will find a lot of difficulty in answering simple (to me) questions like "how do I parse this command text into an AST".

[cpursley]

Agree, would love to see this built into TablePlus.

What I really want is a state-based way to define my database. EdgeDB & Prism have made some progress here but they really only handle table changes. Really wish this were built into Postgres somehow (or any solid migration story).

I want to be able to define my tables, views, functions and everything and the engine behind it automatically figures out all the necessary changes. For example, change a table (add a column or something) and it is smart enough to update any dependent views and functions. Including handling of data migrations.

[mbork_pl]

> I am regularly surprised how bad the tooling is for SQL/databases.

This. I'd really like to have a working SQL pretty printer/formatter. Psql is great, but lacks autocompletion in several places.

That said, a big shoutout to pspg (https://github.com/okbob/pspg) - an excellent pager for psql (also for general csvs).

[kshahkshah]

Check out Beekeeper Studio

[pjmlp]

Tooling is quite good on comercial space, now on free beer side, it is another matter.

[epgui]

It has tons of annoying quirks, but I couldn't imagine running a DBT project without it: https://github.com/sqlfluff/sqlfluff

[tracker1]

FWIW, the MS SQL tooling is decent... Hopefully this goes as far to understand the db/schema/table structure of a connected database.

[j45]

SequelPro is quite decent for MySql.

Tooling for Postgres seems to be more for the technical who prefer command line than not.

[lfittl]

Excited to see this - and excellent use case for libpg_query (I'm the original author and still help maintain it together with the rest of the team) and appreciate the shout out to pganalyze!

If anyone else has a use case for using the Postgres parser outside the server, we have a healthy ecosystem of libraries that build on the core C library (we maintain bindings for Ruby, Go and Rust ourselves), as well as various projects using it (e.g. sqlc uses it for a type-safe way for using hand-written SQL in Go): https://github.com/pganalyze/libpg_query#resources

[derefr]

With all the various systems making use of Postgres's parser, is there a reason that the Postgres team haven't "seen the writing on the wall" about the demand for their parser, and so extracted their parser into a standalone library?

I imagine that Postgres itself would then consume the parser library as a — possibly regularly-snapshot-vendored — static library dependency; while other applications and wrapper libraries would be free to consume it as a dynamic shared library; and it could be freely independently distro-packaged; and so forth. In other words, it would become a de-facto "libxml for SQL" — the kind of venerable C lib that you expect everything else to just be a wrapper around.

Is it just that doing this would involve making functions with internal linkage into functions with external linkage, and thereby preventing some WPO opportunities when compiling Postgres itself?

[ComputerGuru]

What kind of support do we have editor-side for swapping between "LSP contexts" automatically? If I'm editing a .cs file and my editor is using the C# LSP, then start something that a plugin <somehow> recognizes as entering into an SQL mode as I type `foo("SELECT ..` can that somehow trigger the pgsql LSP too?

(I'm a vim user but I'll accept a VS Code answer since that's basically "state of the art" when it comes to LSP and where most others get their inspiration from.)

[phil294]

To add to the correct IntelliJ answer: VSCode, on the other hand, does not allow for multiple language services or servers to handle the same file at the same time. Instead, any language support (always by means of extension, either built-in or external) has to individually add support for embedded languages, either using language services or request forwarding [1]. For example, the built-in HTML plugin includes JS support inside <script/> tags by simply spinning up its own tsserver [2].

This seems to have been a design decision from the very start, but I have a hard time understanding why. It seems like a conceptual mistake to me. It also eats up system resources like crazy, but that's a lost cause anyway. Instead of harnessing the capabilities of existing implementations, every "wrapper" kind of language needs to build its own solution, which leads to these solutions often not being great. For example, said HTML/JS solution has issues with external dependencies [3]. Or, I've been trying to enable support for CoffeeScript or Elm inside Vue Single File Component files, but I couldn't so far, even though I'm the author of the CoffeeScript extension. [4][5]

Any source file always has exactly one language type assigned to it (possibly auto-detected), so no, dynamically "switching" is not possible without manually changing it for the entire file. For things like SQL inside PHP, Jetbrains products really are superior.

[1] https://code.visualstudio.com/api/language-extensions/embedd... [2] https://github.com/microsoft/vscode/blob/7fd6f1b1d46bd2223a9... [3] https://github.com/microsoft/vscode/issues/26338 [4] https://github.com/vuejs/language-tools/issues/3200 [5] https://github.com/vuejs/vetur/issues/1184

[marcelarie]

I don't know if this exists but it could be done using Treesitter. This is already done for syntax highlighting, nvim could check the syntax from a string after a specific pattern, like gql`` in Typescript.

This should be configured by the user, idk if a default implementation could work correctly in all cases.

[swader999]

Neovim does this. For example, if you have two lsp configured in nvm-lspconfig.lua as follows, it will use a specific lsp tailored to either SQL or ruby:

-- Initialization of Neovim's LSP client local nvim_lsp = require('lspconfig')

-- Setup for the Ruby LSP nvim_lsp.solargraph.setup {}

-- Setup for an SQL LSP (assuming one exists with the name 'sql_lsp' in this example) nvim_lsp.sql_lsp.setup {}

[petrzjunior]

JetBrains's IntelliJ and friends do that with language plugins. They detect the language according to keywords and patterns and are able to do highlighting and intellisense for multiple languages in a single file. I have not tried this with LSP though, because LSP support landed just in the last version.

[e12e]

I'm using Helix these days, but I seem to recall neovim handling markdown with code blocks fine.

Less sure about "minor" dsl-like support, like online css/html/js in php etc? (Or SQL strings in c# etc).

[isodev]

This is very cool! Does it also support extensions? For example, would it be aware of additional functions and handling of geometries if PostGIS is installed?

[kiwicopple]

that's theoretically possible, but beyond the scope of the work we have planned in the near feature.

it's a great idea though - I will add it to the "Discussions" to track for the future. I can personally see the benefit of PostGIS support

[koolba]

Does it connect to your actual database as well to get the list of tables, columns, etc? Or just leverage the same code base to be able to parse the raw text without any data dictionary information into a parse tree?

[kiwicopple]

it leverages your code-base, rather than connecting to your database. it uses to libpg_query (https://github.com/pganalyze/libpg_query) to construct the syntax tree which can then be used to for the LSP features

[yrashk]

Great project that helps creating better DX for Postgres!

At Omnigres [1] we also use libpg_query for programmatic query manipulations as libpg_query has a deparser and that allows one to manipulate AST and output the text representation back.

I'm trying to find time to contribute Postgres 16 support to it!

[1] Omnigres: https://github.com/omnigres/omnigres

[lfittl]

Thanks Yuri - and appreciate your contributions to libpg_query!

We at pganalyze have plans to add Postgres 16 support to libpg_query sometime between the RC1 and the final release - mostly to watch out for the rare case where a feature gets reverted during the beta releases.

If you'd like to have a go at it before then definitely welcome (here is some context on the extraction process using libclang: https://pganalyze.com/blog/pg-query-2-0-postgres-query-parse...), and we'll also need people to test the PR once available.

[avg_dev]

Thanks, looks very interesting.

I have a couple of questions:

1. Will I be able to use this in my IDE (say JetBrains or VSCode) alongside the programming language’s LSP? So that this project would only be in effect for the Pg SQL bits?

2. I am a Rust newbie but I’m an experienced developer. Is there any room for someone like me to contribute to the project?

[cube2222]

I think paid jetbrains IDEs already do this on their own, as they embed datagrip, no?

It checks the syntax, gives you autocomplete based on the databases you are connected to, it even supports string language injection, so you'll get SQL autocomplete and checking for strings containing SQL in your code.

I'd love to understand what the advantages here are, other than the obvious one of being available for many editors as a language server.

[kiwicopple]

1. yes this is possible, although it depends on your IDE. Here is a relevant discussion for eclipse (which also links to the VSCode implementation): https://github.com/eclipse/che/issues/4609. Note: i also didn't know how this worked, so I learned something today too.

2. Yes, absolutely. Feel free to reach out to me directly, or just start contributing directly if you want

[michaelcampbell]

> Not SQL with flavors, just Postgres.

I like the focus on this.

[Exuma]

Can you try to get it added to Mason so people don't have to try and do a custom install?

https://github.com/mason-org/mason-registry/

[steinroe]

I will add it to mason once it’s in an usable state. Feel free to open a discussion or issue to keep track of the lsp registries.

[lovasoa]

I am very curious to see how it develops. I'm currently working on multiple SQLPage [1] websites, and getting a better IDE experience would be awesome. Writing only SQL is refreshing compared to all the boilerplate required in traditional web dev, but the developer experience is not yet as polished as in, let's say, typescript.

[1] https://sql.ophir.dev

[steinroe]

this is exactly the reason why I started this project. every singe line of code is business logic if you write parts of your backend directly within the database. plus zero latency and no extra servers. I want to make this approach more accessible with this project.

[iddan]

This is amazing. I've been using DataGrip for working with Postgres in the past year and a half but I would much instead use VSCode for complex queries. I love how DB languages become more and more first-class citizens in the editor and LSP worlds instead of a special type of language with specialised tooling.

[nonethewiser]

The vscode-postgres extension is pretty good for crafting queries.

https://github.com/Borvik/vscode-postgres

[steinroe]

agreed! especially for smaller teams and startups it can make a lot of sense to push more logic in the database, and I hope to make this a bit more approachable with this project.

[peter_l_downs]

Out of curiosity, how has it been working with the pganalyze team? It seems like Supabase has been able to collaborate really effectively with other teams in the Postgres space (including pgAdmin and pgmigra, for the `diff` migrations tooling), and it's wonderful to see!

[peferron]

> It seems like Supabase has been able to collaborate really effectively with other teams in the Postgres space (including pgAdmin and pgmigra, for the `diff` migrations tooling)

I'm thankful to Supabase for their efforts, but I'm not sure I'd pick pgAdmin and migra as examples of "really effective" collaboration with other teams: as far as I know, pgAdmin's CLI feature wasn't merged upstream and sits in an outdated Supabase fork of pgAdmin which doesn't fully support Postgres 15, and migra (which has been abandoned for a while) was just used as-is.

[kiwicopple]

actually I forgot to mention this one to Lukas in all the chaos leading up to our Launch Week. I will do it now

we talked in the past about using their library to extend some of Dan Lynch's work (https://github.com/pyramation/postgres-ast-deparser) to create a sort of "postgres package registry" with dynamic DDL introspection. No progress on that one yet, but I'm hopeful that we'll find the bandwidth for it one day.

[psibi]

This looks nice!

As someone interested in developing a client for it, I'm interested in couple of things: what are the features supported by it currently, the tweak-able configuration that can be passed to it and the various code action available. I like the way nil language server has documented it (https://github.com/oxalica/nil/tree/main/docs). Is there something equivalent available for this ?

[kiwicopple]

it's very much still a "POC" to verify that the libpg_query approach works - perhaps I should have made that more clear from the description. We have a PR open which adds source code generation to the Rust crate which is close to merging.

your comment is useful and why we submitted this to ShowHN - it's easier to get these sort of early feature requests than in 3 months once we've implemented more functionality. I'll drop a note in GitHub Discussions to investigate the Nil approach.

[srivmutk]

Always wondered why something like this wasn't made earlier. Either way I'm happy that it's being made now, writing SQL in an IDE like this should be easier

[runeks]

IMHO having an SQL language server they does only parsing, ie. validates only syntax, is a bit like a Java language server that does parsing only — ie. does not check whether e.g. a given variable/method actually exists or whether types match.

The schema is so integral to SQL — since it defines precisely the valid names and types — that a language server that isn't aware of this is of limited use in practice.

[Exuma]

Man I was just looking for this forever, you rock.

[tacone]

Very nice. Hope to see something along the lines of Prettier for SQL, one day. That's sorely needed.

[freedomben]

I've been looking for something like this recently, and there are actually quite a few options. Unfortunately, they can only handle files up to a particular size. My use case is to pretty up a huge SQL dump so that I can grab it. Every existing tool tries to read the entire thing into memory, and ends up crashing.

[steinroe]

it’s on the roadmap! once the parser is stable and an advanced and scalable data model is implemented, features like this will hopefully be quite straightforward to implement.

[timcobb]

> A Language Server for Postgres. Not SQL with flavors, just Postgres.

My heroes, thank you for working on this.

[dgdosen]

This should work fine with vim-dadbod and vim-dadbod-ui... Right?