9 ms·

Postgres Language Server

963 points by kiwicopple 3y ago

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 https://github.com/psteinroe) who the creator and maintainer. [0] LSP: https://microsoft.github.io/language-server-protocol/ https://microsoft.github.io/language-server-protocol/ [1] pganalyze: https://pganalyze.com/ https://pganalyze.com/

deleted 3y ago
[deleted]
koolba 3y ago
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 3y ago
  it leverages your code-base, rather than connecting to your database. it uses to libpg_query (https://github.com/pganalyze/libpg_query https://github.com/pganalyze/libpg_query) to construct the syntax tree which can then be used to for the LSP features
  - continuational 3y ago
    Ah, does that mean autocompletion for fields is off the table?
    - deleted 3y ago
      [deleted]
    - steinroe 3y ago
      It will definitely be supported at some point. We will have some features that require a db connection, eg autocompletion or code actions such as „Execute the current statement“. But always as an opt in.
isodev 3y ago
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 3y ago
  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
  - ptrwis 3y ago
    I also wanted to ask about extensions. How does it work internally? Does it ask the database for parsing or does it have its own syntax analyzer?
    - steinroe 3y ago
      we are using the actual Postgres server source to parse the sql file(s) into both an abstract- and a concrete syntax tree. Its bundled with the language server and therefore works standalone. We will add the option to connect to a database, but only to enable features such as autocompletion and code actions a la „Execute the statement under the cursor“.
  - yard2010 3y ago
    Are you looking for contributions? Can I join the party?
    - kiwicopple 3y ago
      yes, we'd love more contributors
iddan 3y ago
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.
- steinroe 3y ago
  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.
- nonethewiser 3y ago
  The vscode-postgres extension is pretty good for crafting queries. https://github.com/Borvik/vscode-postgres https://github.com/Borvik/vscode-postgres
codeptualize 3y ago
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!)
- tracker1 3y ago
  FWIW, the MS SQL tooling is decent... Hopefully this goes as far to understand the db/schema/table structure of a connected database.
- kshahkshah 3y ago
  Check out Beekeeper Studio
  - bbkane 3y ago
    Love Beekeeper Studio!
  - rapind 3y ago
    A monthly fee? No thanks.
    - kshahkshah 3y ago
      Sheesh. It's open source, has a free version, and then a premium version so the bootstrapped, not-VC backed, creators avoid starving themselves and their families while working on the tool...
      - reactordev 3y ago
        Sadly, this is why this space has laggard for so long. Devs don’t want to pay for tooling but expect others to pay for their product.
TekMol 3y ago
I am confused what this does. Is it that I type in my IDE "SEL" and is suggests "SELECT"? In which situation would this be useful?
- Hendrikto 3y ago
  Essentially, LSPs are generic implementations of features like autocomplete, expand selection, refactor, etc., with a standardized API. Different editors and IDEs can use the same LSP, without the need to reinvent/reimplement the wheel.
- kiwicopple 3y ago
  it would also be able to suggest columns/fields from your database, as well as picking up errors. for example, given this table: create table profiles ( id serial primary key, first_name text, last_name text ); and this query: select id, firstname from profiles; it would then help to detect if you had an error in your select query (in this case, "firstname" should be "first_name"), and your code editor would show a warning, even before you try to run the code inside your database
  - lovasoa 3y ago
    I don't think it connects to a database, so it cannot suggest field names yet. Correct me if I'm wrong.
    - steinroe 3y ago
      not yet, but we will add this. As of now, the project is just a poc to show that the approach of using libg_query to parse the source works. Most of the work is still ahead.
avg_dev 3y ago
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?
- kiwicopple 3y ago
  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 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
- SpaghettiCthulu 3y ago
  > 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? I think that would depend on IDE support. For example, JetBrains IDEs have a feature for this.
- kyrra 3y ago
  1) looks like intelliJ paid versions support (or will support) LSPs soon. https://blog.jetbrains.com/platform/2023/07/lsp-for-plugin-developers/ https://blog.jetbrains.com/platform/2023/07/lsp-for-plugin-d... > Starting with the 2023.2 release cycle, the LSP API is publicly available as part of the IntelliJ Platform in the following IDEs: IntelliJ IDEA Ultimate, WebStorm, PhpStorm, PyCharm Professional, DataSpell, RubyMine, CLion, Aqua, DataGrip, GoLand, Rider
- cube2222 3y ago
  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.
psibi 3y ago
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 https://github.com/oxalica/nil/tree/main/docs). Is there something equivalent available for this ?
- kiwicopple 3y ago
  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.
Exuma 3y ago
Man I was just looking for this forever, you rock.
Exuma 3y ago
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/ https://github.com/mason-org/mason-registry/
- steinroe 3y ago
  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.
mkl 3y ago
> Optionated Code Formatting "Optionated"? Opinionated or optional? Or both?
- rizky05 3y ago
  [dead]
- kiwicopple 3y ago
  sorry, I believe Philipp meant that you can "configure it" - similar to Prettier: https://prettier.io/docs/en/configuration.html https://prettier.io/docs/en/configuration.html I've updated the Readme
  - gfody 3y ago
    I liked “optionated” it’s like opinionated but with BYO opinions
imams666 3y ago
[flagged]
michaelcampbell 3y ago
> Not SQL with flavors, just Postgres. I like the focus on this.
tacone 3y ago
Very nice. Hope to see something along the lines of Prettier for SQL, one day. That's sorely needed.
- freedomben 3y ago
  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 3y ago
    the language server protocol supports range operations, although I don’t know how many editors enable it. plus it’s arguably harder to implement. I will add benchmarks once the server is ready, and we can see how far we can get from there. thanks for the feedback!
- steinroe 3y ago
  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.
peter_l_downs 3y ago
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!
- kiwicopple 3y ago
  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 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.
- peferron 3y ago
  > 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 3y ago
    fwiw, pgadmin was not for a lack of trying: https://redmine.postgresql.org/issues/6304 https://redmine.postgresql.org/issues/6304. I won't have many details about migra form the CLI team until they come online, but no doubt we'll pick up the migra support if it has been abandoned. (but I agree with the sentiment: we can always find ways to be more collaborative)
    - peferron 3y ago
      Thank you for sharing that! It's unfortunate that the pgAdmin devs weren't interested, although I understand them wanting to keep it focused on being a GUI.
Wonnk13 3y ago
would love to take this for a spin in Emacs. I always lsp support for sql was lacking.
yrashk 3y ago
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 https://github.com/omnigres/omnigres
- lfittl 3y ago
  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-parser#using-libclang-to-extract-c-source-code-from-postgres https://pganalyze.com/blog/pg-query-2-0-postgres-query-parse...), and we'll also need people to test the PR once available.
spapas82 3y ago
The libpg query library has a very important problem: It does not work on windows https://github.com/pganalyze/libpg_query/issues/44 https://github.com/pganalyze/libpg_query/issues/44 I'd recommend starting with fixing that instead. It would be much more helpful for the community.
- Operyl 3y ago
  Sounds like they’d love your contribution. I hate when people prescribe tasks to others like this.
  - kiwicopple 3y ago
    yes, we (and I assume the pganalyze team) would always appreciate contributions fwiw, we know of this limitation and decided the acceptable workaround for Windows devs is simply to host the language server and they can connect to it via TCP.
    - spapas82 3y ago
      Can't you just give some love to the issue https://github.com/pganalyze/libpg_query/issues/44 https://github.com/pganalyze/libpg_query/issues/44 instead ? As I said before this would be very helpful for the community because there are a lot of libraries that use libpg_query and cannot be used on windows (f.e see https://github.com/lelit/pglast/issues/7 https://github.com/lelit/pglast/issues/7). It seems that the only problem for fixing the problem is: > Thanks for the offer, but the problem is our team being time limited / having an engineer with a Windows machine ready to take this on, not that we wouldn't want to pay someone to work on it :) (https://github.com/pganalyze/libpg_query/issues/44#issuecomment-1309061509 https://github.com/pganalyze/libpg_query/issues/44#issuecomm...) Hosting the LSP elsewhere is not really needed since if people wanted to go that way they could use Remote ssh (https://code.visualstudio.com/docs/remote/ssh https://code.visualstudio.com/docs/remote/ssh) to host the whole dev environment on linux and connect to it. Thank you
      - macintux 3y ago
lovasoa 3y ago
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 https://sql.ophir.dev
- steinroe 3y ago
  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.
  - JohnBooty 3y ago
    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. thank you thank you thank you. I feel that this approach, while unfashionable, is actually pretty excellent most of the time.
ComputerGuru 3y ago
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.)
- swader999 3y ago
  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 {}
  - NilsIRL 3y ago
    I think what parent comment is asking for is the ability switch between languages withing a single file. I don't think this will work with the setup you described.
    - swader999 3y ago
      No, it would not.
- petrzjunior 3y ago
  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.
  - scns 3y ago
    In Intellij you can connect to the DB and get autocomplete in SQL strings.
pama 3y ago
Looks great. Has anyone tried an integration with Emacs?
srivmutk 3y ago
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
lfittl 3y ago
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 https://github.com/pganalyze/libpg_query#resources
- derefr 3y ago
  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?
  - lfittl 3y ago
    Generally I agree that this would be great to have, and Postgres does have a set of libraries it already maintains as part of the main source tree (i.e. libpq, etc), and there is a shared set of code between the backend and the "frontend" (https://github.com/postgres/postgres/tree/master/src/common https://github.com/postgres/postgres/tree/master/src/common). So theoretically you could imagine the parser moving into that shared code portion, sharing code but not necessarily requiring linking to a library from the backend. However, the challenge from what I've understood from past conversations with some folks working on Postgres core is that the parser is currently heavily tied into the backend - note the parser isn't just the scan.l/gram.y file, but also the raw parse node structs that it outputs. You can see how many files we pull in from the main tree that are prefixed with "src_backend": https://github.com/pganalyze/libpg_query/tree/15-latest/src/postgres https://github.com/pganalyze/libpg_query/tree/15-latest/src/... Further, there isn't a canonical way to output node trees into a text format today in core, besides the rather hard to work with output of debug_print_parse - there have been discussions on -hackers to potentially utilize JSON here, which may make this a bit easier. Note that in libpg_query we currently use Protobuf (but used to use JSON), which does have the benefit of getting auto-generated structs in the language bindings - but Protobuf is not used in core Postgres at all today. All in all, I think there is some upstream interest, but its not clear that this is a good idea from a maintainability perspective.
toasted-subs 3y ago
It’s surprising how powerful Postgres is and seeing tools to make it easier seems extremely nice!
timcobb 3y ago
> A Language Server for Postgres. Not SQL with flavors, just Postgres. My heroes, thank you for working on this.
dgdosen 3y ago
This should work fine with vim-dadbod and vim-dadbod-ui... Right?
anotherevan 3y ago
I would love to see Dbeaver integrate this!
deleted 3y ago
[deleted]
radicalriddler 3y ago
Nice. I've been thinking about writing a more "general" sql lsp using rust so I might have a read of how they're doing this.
- radicalriddler 3y ago
  It looks very barebones at the moment. Looks like they've just pulled down a tower-lsp boilerplate, the parser crate has a little bit more in it.
Sparkyte 3y ago
I will have to test this.
runeks 3y ago
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.

963 points by kiwicopple 3y ago

deleted 3y ago

[deleted]

koolba 3y ago

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 3y ago
it leverages your code-base, rather than connecting to your database. it uses to libpg_query (https://github.com/pganalyze/libpg_query https://github.com/pganalyze/libpg_query) to construct the syntax tree which can then be used to for the LSP features
- continuational 3y ago
  Ah, does that mean autocompletion for fields is off the table?
  - deleted 3y ago
    [deleted]
  - steinroe 3y ago
    It will definitely be supported at some point. We will have some features that require a db connection, eg autocompletion or code actions such as „Execute the current statement“. But always as an opt in.

isodev 3y ago

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 3y ago
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
- ptrwis 3y ago
  I also wanted to ask about extensions. How does it work internally? Does it ask the database for parsing or does it have its own syntax analyzer?
  - steinroe 3y ago
    we are using the actual Postgres server source to parse the sql file(s) into both an abstract- and a concrete syntax tree. Its bundled with the language server and therefore works standalone. We will add the option to connect to a database, but only to enable features such as autocompletion and code actions a la „Execute the statement under the cursor“.
- yard2010 3y ago
  Are you looking for contributions? Can I join the party?
  - kiwicopple 3y ago
    yes, we'd love more contributors

iddan 3y ago

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.

steinroe 3y ago
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.
nonethewiser 3y ago
The vscode-postgres extension is pretty good for crafting queries. https://github.com/Borvik/vscode-postgres https://github.com/Borvik/vscode-postgres

codeptualize 3y ago

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

tracker1 3y ago
FWIW, the MS SQL tooling is decent... Hopefully this goes as far to understand the db/schema/table structure of a connected database.
kshahkshah 3y ago
Check out Beekeeper Studio
- bbkane 3y ago
  Love Beekeeper Studio!
- rapind 3y ago
  A monthly fee? No thanks.
  - kshahkshah 3y ago
    Sheesh. It's open source, has a free version, and then a premium version so the bootstrapped, not-VC backed, creators avoid starving themselves and their families while working on the tool...
    - reactordev 3y ago
      Sadly, this is why this space has laggard for so long. Devs don’t want to pay for tooling but expect others to pay for their product.

TekMol 3y ago

I am confused what this does. Is it that I type in my IDE "SEL" and is suggests "SELECT"? In which situation would this be useful?

Hendrikto 3y ago
Essentially, LSPs are generic implementations of features like autocomplete, expand selection, refactor, etc., with a standardized API. Different editors and IDEs can use the same LSP, without the need to reinvent/reimplement the wheel.
kiwicopple 3y ago
it would also be able to suggest columns/fields from your database, as well as picking up errors. for example, given this table: create table profiles ( id serial primary key, first_name text, last_name text ); and this query: select id, firstname from profiles; it would then help to detect if you had an error in your select query (in this case, "firstname" should be "first_name"), and your code editor would show a warning, even before you try to run the code inside your database
- lovasoa 3y ago
  I don't think it connects to a database, so it cannot suggest field names yet. Correct me if I'm wrong.
  - steinroe 3y ago
    not yet, but we will add this. As of now, the project is just a poc to show that the approach of using libg_query to parse the source works. Most of the work is still ahead.

avg_dev 3y ago

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?

kiwicopple 3y ago
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 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
SpaghettiCthulu 3y ago
> 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? I think that would depend on IDE support. For example, JetBrains IDEs have a feature for this.
kyrra 3y ago
1) looks like intelliJ paid versions support (or will support) LSPs soon. https://blog.jetbrains.com/platform/2023/07/lsp-for-plugin-developers/ https://blog.jetbrains.com/platform/2023/07/lsp-for-plugin-d... > Starting with the 2023.2 release cycle, the LSP API is publicly available as part of the IntelliJ Platform in the following IDEs: IntelliJ IDEA Ultimate, WebStorm, PhpStorm, PyCharm Professional, DataSpell, RubyMine, CLion, Aqua, DataGrip, GoLand, Rider
cube2222 3y ago
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.

psibi 3y ago

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 https://github.com/oxalica/nil/tree/main/docs). Is there something equivalent available for this ?

kiwicopple 3y ago
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.

Exuma 3y ago

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

Exuma 3y ago

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/ https://github.com/mason-org/mason-registry/

steinroe 3y ago
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.

mkl 3y ago

> Optionated Code Formatting "Optionated"? Opinionated or optional? Or both?

rizky05 3y ago
[dead]
kiwicopple 3y ago
sorry, I believe Philipp meant that you can "configure it" - similar to Prettier: https://prettier.io/docs/en/configuration.html https://prettier.io/docs/en/configuration.html I've updated the Readme
- gfody 3y ago
  I liked “optionated” it’s like opinionated but with BYO opinions

imams666 3y ago

[flagged]

michaelcampbell 3y ago

> Not SQL with flavors, just Postgres. I like the focus on this.

tacone 3y ago

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

freedomben 3y ago
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 3y ago
  the language server protocol supports range operations, although I don’t know how many editors enable it. plus it’s arguably harder to implement. I will add benchmarks once the server is ready, and we can see how far we can get from there. thanks for the feedback!
steinroe 3y ago
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.

peter_l_downs 3y ago

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!

kiwicopple 3y ago
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 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.
peferron 3y ago
> 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 3y ago
  fwiw, pgadmin was not for a lack of trying: https://redmine.postgresql.org/issues/6304 https://redmine.postgresql.org/issues/6304. I won't have many details about migra form the CLI team until they come online, but no doubt we'll pick up the migra support if it has been abandoned. (but I agree with the sentiment: we can always find ways to be more collaborative)
  - peferron 3y ago
    Thank you for sharing that! It's unfortunate that the pgAdmin devs weren't interested, although I understand them wanting to keep it focused on being a GUI.

Wonnk13 3y ago

would love to take this for a spin in Emacs. I always lsp support for sql was lacking.

yrashk 3y ago

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 https://github.com/omnigres/omnigres

lfittl 3y ago
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-parser#using-libclang-to-extract-c-source-code-from-postgres https://pganalyze.com/blog/pg-query-2-0-postgres-query-parse...), and we'll also need people to test the PR once available.

spapas82 3y ago

The libpg query library has a very important problem: It does not work on windows https://github.com/pganalyze/libpg_query/issues/44 https://github.com/pganalyze/libpg_query/issues/44 I'd recommend starting with fixing that instead. It would be much more helpful for the community.

Operyl 3y ago
Sounds like they’d love your contribution. I hate when people prescribe tasks to others like this.
- kiwicopple 3y ago
  yes, we (and I assume the pganalyze team) would always appreciate contributions fwiw, we know of this limitation and decided the acceptable workaround for Windows devs is simply to host the language server and they can connect to it via TCP.
  - spapas82 3y ago
    Can't you just give some love to the issue https://github.com/pganalyze/libpg_query/issues/44 https://github.com/pganalyze/libpg_query/issues/44 instead ? As I said before this would be very helpful for the community because there are a lot of libraries that use libpg_query and cannot be used on windows (f.e see https://github.com/lelit/pglast/issues/7 https://github.com/lelit/pglast/issues/7). It seems that the only problem for fixing the problem is: > Thanks for the offer, but the problem is our team being time limited / having an engineer with a Windows machine ready to take this on, not that we wouldn't want to pay someone to work on it :) (https://github.com/pganalyze/libpg_query/issues/44#issuecomment-1309061509 https://github.com/pganalyze/libpg_query/issues/44#issuecomm...) Hosting the LSP elsewhere is not really needed since if people wanted to go that way they could use Remote ssh (https://code.visualstudio.com/docs/remote/ssh https://code.visualstudio.com/docs/remote/ssh) to host the whole dev environment on linux and connect to it. Thank you
    - macintux 3y ago

lovasoa 3y ago

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 https://sql.ophir.dev

steinroe 3y ago
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.
- JohnBooty 3y ago
  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. thank you thank you thank you. I feel that this approach, while unfashionable, is actually pretty excellent most of the time.

ComputerGuru 3y ago

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

swader999 3y ago
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 {}
- NilsIRL 3y ago
  I think what parent comment is asking for is the ability switch between languages withing a single file. I don't think this will work with the setup you described.
  - swader999 3y ago
    No, it would not.
petrzjunior 3y ago
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.
- scns 3y ago
  In Intellij you can connect to the DB and get autocomplete in SQL strings.

pama 3y ago

Looks great. Has anyone tried an integration with Emacs?

srivmutk 3y ago

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

lfittl 3y ago

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 https://github.com/pganalyze/libpg_query#resources

derefr 3y ago
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?
- lfittl 3y ago
  Generally I agree that this would be great to have, and Postgres does have a set of libraries it already maintains as part of the main source tree (i.e. libpq, etc), and there is a shared set of code between the backend and the "frontend" (https://github.com/postgres/postgres/tree/master/src/common https://github.com/postgres/postgres/tree/master/src/common). So theoretically you could imagine the parser moving into that shared code portion, sharing code but not necessarily requiring linking to a library from the backend. However, the challenge from what I've understood from past conversations with some folks working on Postgres core is that the parser is currently heavily tied into the backend - note the parser isn't just the scan.l/gram.y file, but also the raw parse node structs that it outputs. You can see how many files we pull in from the main tree that are prefixed with "src_backend": https://github.com/pganalyze/libpg_query/tree/15-latest/src/postgres https://github.com/pganalyze/libpg_query/tree/15-latest/src/... Further, there isn't a canonical way to output node trees into a text format today in core, besides the rather hard to work with output of debug_print_parse - there have been discussions on -hackers to potentially utilize JSON here, which may make this a bit easier. Note that in libpg_query we currently use Protobuf (but used to use JSON), which does have the benefit of getting auto-generated structs in the language bindings - but Protobuf is not used in core Postgres at all today. All in all, I think there is some upstream interest, but its not clear that this is a good idea from a maintainability perspective.

toasted-subs 3y ago

It’s surprising how powerful Postgres is and seeing tools to make it easier seems extremely nice!

timcobb 3y ago

> A Language Server for Postgres. Not SQL with flavors, just Postgres. My heroes, thank you for working on this.

dgdosen 3y ago

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

anotherevan 3y ago

I would love to see Dbeaver integrate this!

deleted 3y ago

[deleted]

radicalriddler 3y ago

Nice. I've been thinking about writing a more "general" sql lsp using rust so I might have a read of how they're doing this.

radicalriddler 3y ago
It looks very barebones at the moment. Looks like they've just pulled down a tower-lsp boilerplate, the parser crate has a little bit more in it.

Sparkyte 3y ago

I will have to test this.

runeks 3y ago

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.