Postgres Language Server Launches After Two Years of Development

BigGo Editorial Team
Postgres Language Server Launches After Two Years of Development

The developer community now has access to a powerful new tool for PostgreSQL development with the release of the Postgres Language Server, a project that has been in development for nearly two years. This open-source language server provides essential features like autocompletion, syntax error highlighting, type-checking, and linting for PostgreSQL's SQL dialect.

GitHub repository for the Postgres Language Server, highlighting its development and community contributions
GitHub repository for the Postgres Language Server, highlighting its development and community contributions

Solving the SQL Development Experience

The Postgres Language Server addresses a significant gap in the development workflow for those who work extensively with PostgreSQL databases. Before this tool, developers often had to write SQL without real-time feedback, running migrations to catch errors or looking up schema information manually. The language server brings modern IDE capabilities to SQL development, allowing developers to catch errors before execution.

What makes this project particularly interesting is its approach to parsing SQL. As one of the co-authors explained in the comments, they faced a fundamental challenge: PostgreSQL's parser is complex and constantly evolving, making it impractical to create a custom parser. Additionally, a language server needs to handle both valid and incomplete SQL statements.

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.

Versatile Implementation and Integration

The Postgres Language Server is built with versatility in mind. While it leverages the Language Server Protocol (LSP) for IDE integration, its transport-agnostic design means the features can be accessed through multiple interfaces including a CLI, HTTP APIs, and WebAssembly modules. This makes the tooling accessible regardless of a developer's preferred environment.

The server is already available as a downloadable binary, an npm package, a VSCode extension, and can be installed via nvim-lspconfig and mason for Neovim users. This wide availability demonstrates the team's commitment to making the tool as accessible as possible.

Future Directions and Community Impact

Currently, the language server focuses on SQL statements, but the developers have indicated plans to expand support to function bodies and PL/pgSQL in the future. The community has already begun reporting bugs and suggesting improvements, with the developers showing impressive responsiveness by quickly addressing issues.

One particularly exciting possibility that community members have raised is the potential for integrating the language server with embedded SQL in various programming languages. This could allow for static checking and autocompletion of SQL statements within code written in languages like Python, Rust, or TypeScript. While this functionality would require editor-side solutions, the developers have mentioned they're exploring options for direct support in their workspace API.

For developers working with platforms that heavily leverage PostgreSQL features like triggers, Row Level Security, and SQL functions, this tool represents a significant improvement in the development experience. By providing immediate feedback on syntax errors and offering autocompletion based on schema information, the Postgres Language Server helps streamline database development workflows and catch potential issues earlier in the development process.

Reference: Postgres Language Server

Error output from the postgres-tools CLI, highlighting the practical application of SQL syntax checking and type validation
Error output from the postgres-tools CLI, highlighting the practical application of SQL syntax checking and type validation