SQLite is everywhere. It runs inside every Android and iOS app, inside Chrome and Firefox, inside Electron applications, inside Perfetto’s trace processor, inside the Python standard library. The SQLite database format is so pervasive that the Library of Congress recommends it for long-term digital preservation. Yet the tooling story for people who write SQLite SQL has always been somewhere between mediocre and actively misleading.
syntaqlite is a Language Server Protocol implementation for SQLite, written by Lalit Maganti, who works on Perfetto at Google and has spent years using SQLite as a query engine over trace data. The project’s premise is simple: existing SQL language servers treat SQLite as a dialect afterthought, and the consequence is a tools layer that generates false positives on valid SQLite syntax, misses SQLite-specific constructs entirely, and gives you schema completions that don’t match what SQLite actually exposes.
The interesting question is not whether a dedicated SQLite LSP is a good idea. It obviously is. The interesting question is why this is hard, and what it takes to get it right.
What Generic SQL Tools Get Wrong
Every major SQL language server, sqls, sql-language-server, and sqlfluff, is built around a grammar that covers ANSI SQL with PostgreSQL-flavored extensions. SQLite gets a compatibility shim at best. The result is a collection of specific failures that anyone who writes SQLite SQL in an editor will recognize.
Pragmas are the clearest example. SQLite has roughly eighty pragmas: PRAGMA journal_mode = WAL, PRAGMA wal_checkpoint(FULL), PRAGMA table_info('my_table'), PRAGMA foreign_keys = ON. These look like SQL statements but are not SQL. A generic parser flags them as syntax errors. Any editor integration built on a generic SQL grammar will light up your migration files with red squiggles on lines that SQLite executes without complaint.
Type affinity is subtler. SQLite does not enforce types. A column declared as INTEGER stores whatever value you give it; the declared type is just a preference hint that SQLite uses to decide how to coerce values during comparisons. The affinity rules are derived from the declared type name through a specific algorithm: if the type name contains the string INT, the affinity is INTEGER; if it contains CHAR, CLOB, or TEXT, the affinity is TEXT, and so on. A PostgreSQL-oriented linter will flag cross-type operations as errors on perfectly valid SQLite code, because PostgreSQL actually enforces types and SQLite does not.
The newer features compound this. SQLite 3.35.0 (2021) added the RETURNING clause. SQLite 3.37.0 (2021) added STRICT tables, which are the one context where SQLite actually does enforce types, making the type checker’s job conditional on whether a given table was declared with STRICT. SQLite 3.38.0 (2022) added the -> and ->> JSON operators. Generated columns (AS (expr) STORED and AS (expr) VIRTUAL) arrived in 3.31.0 (2020). Each of these is valid SQLite that a generic SQL tool mishandles.
Virtual tables are another category entirely. FTS5 full-text search, R*Tree spatial indexing, and the JSON1 extension all use CREATE VIRTUAL TABLE syntax:
CREATE VIRTUAL TABLE docs USING fts5(title, body);
CREATE VIRTUAL TABLE coords USING rtree(id, minLat, maxLat, minLon, maxLon);Virtual table modules create shadow tables automatically. A schema-aware tool that doesn’t understand this will give you incomplete or incorrect column completions for queries against FTS or R*Tree tables.
WITHOUT ROWID tables are another schema-level distinction that changes what columns are accessible. Every ordinary SQLite table has an implicit rowid column (accessible as rowid, _rowid_, or oid). WITHOUT ROWID tables use a different internal storage layout and don’t expose this column. Accurate column completions require knowing which table class you’re querying.
Finally, ATTACH DATABASE means a single SQLite connection can span multiple databases. The schema namespace has at minimum a main and a temp schema, and can have any number of attached schemas. A tool that treats the connection as a single-schema environment will miss completions and fail to resolve cross-database references.
The Architectural Decision That Makes Accuracy Possible
The way to guarantee that a SQL tool is correct about what SQLite accepts is to use SQLite’s own parser rather than reimplementing one. This is the core architectural decision in syntaqlite: it builds on SQLite’s C library to parse SQL, which means the parse results are definitionally correct for the version of SQLite it’s linked against. There is no separate grammar to drift out of sync with SQLite’s actual behavior.
This contrasts with tools like sqlfluff, which maintains its own grammar definitions per dialect. sqlfluff’s SQLite dialect is a community-maintained grammar overlay. When SQLite ships a new feature, sqlfluff’s dialect support for it depends on someone writing and merging a grammar update. The gap between SQLite releases and sqlfluff dialect coverage is a known, recurring problem for users.
Using SQLite’s parser means syntaqlite can provide accurate syntax diagnostics without any gap: if SQLite 3.45 accepts a statement, the LSP accepts it. If SQLite flags something as a syntax error, so does the LSP. The diagnostics layer is grounded in the same code path that production SQLite uses.
What the LSP Actually Provides
The LSP server communicates over JSON-RPC on stdio, making it compatible with any editor that supports the Language Server Protocol: Neovim via nvim-lspconfig, VS Code via a client extension, Emacs via eglot or lsp-mode, Helix, and Zed.
The feature set covers the standard LSP baseline applied to SQLite-specific semantics:
- Diagnostics: syntax errors and warnings surfaced inline, using SQLite’s actual parse results
- Completion: table names, column names, SQLite built-in functions, all ~80 pragmas, and keywords scoped to SQLite’s vocabulary
- Hover documentation: inline docs for functions, pragmas, and keywords
- Go-to-definition: navigating to table, view, and index definitions within the connected schema
- Schema introspection: connecting to an actual
.dbfile (or attached databases) to provide column-level completions rather than just keyword completions
The pragma completions are worth pausing on. A pragma like PRAGMA wal_checkpoint takes an optional mode argument (PASSIVE, FULL, RESTART, TRUNCATE). Surfacing these as completions in the editor requires encoding SQLite’s full pragma vocabulary with argument structure. No generic SQL tool does this because no generic SQL tool has any model of what a pragma is.
Where This Sits in the Existing Ecosystem
The existing SQLite devtools divide roughly into GUI tools and CLI tools. DB Browser for SQLite and SQLiteStudio are the main open-source GUI options. Both are capable schema browsers and query runners, but neither provides editor integration or LSP-based diagnostics. You run queries in their built-in editors, not in your usual development environment.
litecli provides a better interactive shell with autocomplete, built as a replacement for the standard sqlite3 CLI. The autocomplete is useful for interactive exploration but doesn’t extend to editor workflows or provide diagnostics.
sqlite-utils and Datasette, both from Simon Willison, are excellent for data pipeline work and database publishing respectively. They solve different problems entirely: transforming data into SQLite and exposing SQLite data as a web API.
Beekeeper Studio is a cross-platform GUI with SQLite support that is more polished than DB Browser. Still no LSP, still requires leaving your editor.
The gap syntaqlite fills is specifically the in-editor, dialect-accurate experience: writing SQL in your actual development environment with completions that reflect your real schema and diagnostics that match what SQLite will accept or reject.
Why This Matters More Than It Used To
SQLite’s role has expanded beyond the traditional use cases of mobile app local storage and small embedded applications. Cloudflare’s D1 distributed SQLite, Turso’s libSQL fork, and LiteFS for replicated SQLite mean that SQLite is increasingly appearing in production web backends where previously you would have used PostgreSQL. The SQLite on the server argument has gained real traction.
In this context, the quality of tooling matters in the same way it matters for PostgreSQL or MySQL deployments. Developers writing schema migrations, query optimizations, or complex CTEs against a production SQLite-backed application benefit from the same editor feedback loop they expect when working with other databases.
Perfetto’s use case is also instructive. The Perfetto trace processor exposes a SQL interface over performance traces, and users write non-trivial analytical queries, window functions, recursive CTEs, and custom virtual table queries to extract data. For teams working in that environment, having correct LSP support for the actual SQL dialect they’re using represents a real productivity difference.
Syntaqlite is the kind of tool that fills a gap that has existed long enough that people stopped noticing it. SQLite users have adapted to generic tools that are wrong about their dialect, added -- noqa equivalents to suppress spurious warnings, or simply disabled SQL linting in their editors. Having a tool that is correct by construction, because it uses SQLite’s own parser, changes the baseline for what SQLite development feels like.