PostgresTools Extension for VS Code

The PostgresTools extension for Visual Studio Code brings PostgreSQL inline suggestions, linting, and type checks to VSCode and VSCode-based editors.

It does so by implementing an LSP client and launching an LSP Server in the background.

What's an LSP?

The Language Server Protocol is a protocol defined by Microsoft. Its goal is to standardize language features across many editors. If you use a modern IDE and you've ever used autocompletion or linting, you've used an LSP. The server is aware of the files you have opened and analyzes them in the background. The client sends requests for e.g. autocompletions or code actions to the server.

Setting Up the LSP Server

First, you need the LSP server binary. The Postgres language server is written in Rust and is therefore compiled to various binaries for various machines. You can set it up via one of five strategies. The extensions will check them in the following order:

• The postgrestools.bin VSCode setting can point to a binary with relative or absolute paths. You can use this if you want to download a specific binary from one of the Postgres language server releases and place it in your project.
• Recommended: If you use node, you can simply run npm i -D @postgrestools/postgrestools@latest. Once you restart the extension, it should look for the server binary in your node_modules.
• If you use node but you install your packages via Yarn Plug'n'Play, you can still install @postgrestools/postgrestools, and the extension will check your .pnp.cjs file for a binary.
• You can install the LSP server globally (e.g. via brew or by downloading a binary from the GitHub releases). Make sure that its binary is exposed in your $PATH – the extension will search it for a postgrestools on Darwin/Linux or a postgrestools.exe on Windows.
• If no LSP server binary can be found via the above strategies, you will be prompted to download a binary from postgrestools's GitHub Releases. You can also do this later via the Download Server Command. Note that the above strategies will still take precedence. The found binary is copied to a temporary location in your VS Code extensions folder and run from there. When you restart the extension, the copied binary will be used, and the above places won't be searched.

Setting Up Your Project

Second, you need a postgrestools.jsonc file at the root of your repository (or, use a custom file location and point to it via the postgrestools.configFile setting). You can find sane defaults in the docs.

When you specify the db section, the LSP server will connect to your database and gather intel from there. This makes it possible to provide autocompletions and type checks.

If you omit the section, on the other hand, those features won't be enabled, and you'll only get linting.

Verify It Works

To verify that the server is installed and running correctly, you can use the "Get Current Version" command listed in Useful Commands.

To verify that the client works as intended, open a .sql file and write some gibberish – you should get red squiggly lines from pg(syntax).

Working With Supabase

The Postgres language server is not custom-tailored to work with Supabase – it works with any Postgres database.

If you do use Supabase, you can get the most of the LSP by using it against your local Supabase database (Here's how to install the Supabase CLI).

Once you have everything running locally, run supabase status to see your local DB URL.

It'll have the following format: postgresql://<username>:<password>@<host>:<port>/<database>.

If you extract the values, add them to your postgrestools.jsonc file, and restart the extension, you should be ready to go.

You can also run the LSP server against your remote database, but this might lead to small latencies and a small performance overhead (the LSP server runs prepare statements against your database for the type checking).

You should find your remote database settings at https://supabase.com/dashboard/project/<yourProjectId>/settings/database?showConnect=true.

Useful Commands

The extension adds seven commands to your VS Code Command Palette. They are all prefixed by PostgresTools.

• PostgresTools: Hard Reset (Delete All Temp and Global Binaries) is your troubleshooting weapon. It will basically remove all binaries that were copied and downloaded by the extension (not those you have installed or copied yourself). The extension will then again search for a server binary via the strategies mentioned in the setup.
• PostgresTools: Download Server lets you select and download the server binary. It'll be the matching version for your machine. If you set postgrestools.allowDownloadPrereleases to true in yor VS Code settings, you'll be able to select prereleases.
• PostgresTools: Get Current Version will print the current version and the strategy with which the server binary was found.
• PostgresTools: Start and PostgresTools: Stop will stop or start the LSP server and the client.
• PostgresTools: Restart runs stop and start in succession.
• PostgresTools: Copy Latest Server Logfile copies the latest server log file to your currently opened repo. The log file is meant to be attached to GitHub issues, it can sometimes help us to debug.

Troubleshooting

1. First, try restarting the extension via the PostgresTools: Hard Reset (...) command mentioned above.
2. Open your VS Code Terminal, select the tab Output, and select one of the postgrestools extensions on the right. You might see what went wrong in the logs.
3. If you want to open a GitHub issue, it can sometimes help us if you attach the LSP server log file. We provide the PostgresTools: Copy Latest Server Logfile command to make that as easy as possible.

FAQ

I've installed the package via NPM but getting an Error: Cannot find module '@postgrestools/cli-x86_64-windows-msvc/postgrestools.exe'.

The platform-specific package is installed as an optionalDependency. If it can't be installed for whatever reason, npm won't complain. It's likely that something went wrong during the installation.

Another known issue is that npm installs the optional dependency at an unexpected location. It should be located at node_modules/@postgrestools/cli-aarch64-apple-darwin (or another platform-specific package).

If you can't find the platform specific package, please just rerun npm install a couple of times.

If that still doesn't help, run npm uninstall @postgrestools/postgrestools and use the download server strategy mentioned here.

Why am I prompted to install the PostgresTools binary?

You will only be prompted if all other strategies fail. If you set out to use a different strategy, make sure the binary/node package is actually at the expected location:

• Using the postgrestool.bin setting -> Is the binary at the configured location?
• Using npm or yarn -> Are the node_modules installed correctly?
• Using the PATH environment variable -> Can you run $ postgrestools --version, is the binary at the expected location?

Issues

If you experience any issues, please report them at the postgres language server repository – we'll most frequently read issues there.