Postgres Language Server

The Postgres Language Server is a collection of language tools and a Language Server Protocol (LSP) implementation designed for Postgres. It is built using Postgres' own parser libpg_query to guarantee 100% syntax compatibility.

The server uses a transport-agnostic Server-Client architecture, allowing features to be accessed via both the Language Server Protocol (LSP) and a Command Line Interface (CLI).

Available features include:

• Autocompletion & Hover
• Syntax Diagnostics
• Type Checking (leveraging EXPLAIN error insights)
• Formatting
• Migration Linting
• Database Linting
• PL/pgSQL Support

The project provides two primary entry points for interacting with the Postgres language server functionality, both of which utilize the shared pgls_workspace API:

1. Language Server (pgls_lsp): A server that listens for incoming LSP (Language Server Protocol) messages from an IDE or editor.
2. Command-Line Interface (pgls_cli): A CLI tool used for direct commands like check. The published binary is named postgres-language-server (formerly postgrestools).

The postgres-language-server works by:

1. Accepting input source code and splitting it into individual SQL statements.
2. Parsing and analyzing each statement.
3. Connecting to a Postgres database to maintain an in-memory schema cache (containing tables, columns, functions, and type information).
4. Using the parsed results and the schema cache to answer queries about the SQL statements.

To maintain performance, the engine uses a delta-based approach: when a file changes, it only re-parses and re-analyzes the affected statements.

The package provides two independent APIs. Each manages its own workspace independently; do not use both in the same context.

Run the language server's analysis on specific files or directories. You can use flags to target specific subsets of files based on Version Control System (VCS) status.

Common usage patterns:

• Check specific files: postgres-language-server check [PATH]...
• Check staged files (local development): Use the --staged flag to lint only files prepared for commit.
• Check changed files (CI environments): Use the --changed flag to lint files that differ from your defaultBranch.
• Check piped input: Use --stdin-file-path=PATH to format code piped from stdin. The extension of the provided path determines how the code is checked.

Example (piped input):

echo 'let a;' | postgres-language-server check --stdin-file-path=test.sql

The banVacuumFull rule (Diagnostic Category: lint/safety/banVacuumFull) flags the use of VACUUM FULL in SQL code. VACUUM FULL rewrites the entire table and acquires an ACCESS EXCLUSIVE lock, which blocks all reads and writes for the duration of the operation. This can be extremely disruptive on large tables.

To perform online table maintenance without blocking access, use regular VACUUM or pg_repack instead.

By default, the Postgres Language Server uses libpg_query to check for syntax errors in PL/pgSQL function bodies. However, this method lacks location information and reports errors on the entire function body.

To receive fine-grained, token-level error reporting and sophisticated static analysis, you should enable the plpgsql_check extension in your development database. The language server will automatically detect the extension and forward its reports as diagnostics.

CREATE EXTENSION IF NOT EXISTS plpgsql_check;

The lint/safety/addingRequiredField rule flags attempts to add a new column to an existing table that is marked as NOT NULL but lacks a DEFAULT value.

Why this is a problem:

• For populated tables, the operation will fail immediately because existing rows cannot satisfy the NOT NULL constraint.
• Existing application code that does not know about the new column will fail during INSERT operations.

Recommended Solutions:

1. Make the column optional initially: Add the column without the NOT NULL constraint. Once all existing data is updated and application code is aware of the column, apply the NOT NULL constraint.
2. Use a DEFAULT value (Postgres 11+): If you are using PostgreSQL version 11 or later, you can add the column with both a NOT NULL constraint and a non-volatile DEFAULT value. This allows Postgres to automatically populate existing rows.

The Postgres Language Server is available as an extension for several editors and IDEs:

• VSCode: Install via the Supabase.postgrestools extension.
• Cursor: Available via Open VSX.
• Neovim: Configurable via nvim-lspconfig using the postgres_lsp setting.
• Zed: Supported via the zed-postgres-language-server integration.
• Sublime Text: Supported via the LSP package.