SQLite is the most widely deployed database engine in existence. It ships inside every Android device, every iPhone, every macOS installation, Firefox, Chrome, and thousands of embedded applications. The SQLite source code is likely running on more machines right now than any other database software by several orders of magnitude.
And yet, if you open your editor and start writing SQLite SQL, you will almost certainly be served by a language server built for PostgreSQL, or a generic SQL formatter that understands neither PRAGMA statements nor virtual table syntax, or a GUI that shows you table schemas but cannot explain a query plan in any meaningful way. The tooling gap is real and it has persisted for a long time, in part because SQLite is easy to dismiss as a toy database that does not need the same treatment as “real” databases.
syntaqlite, a project by Lalit Maganti, pushes back on that assumption directly. The framing of “high-fidelity devtools that SQLite deserves” is pointed: it implies that existing tools are low-fidelity, and that SQLite’s ubiquity earns it something better.
Why Generic SQL Tools Fail SQLite
The problem is not that generic SQL tools are bad. It is that SQLite’s dialect is different in ways that matter for accurate tooling, and those differences compound when you try to build schema-aware completions, linters, or formatters on top of a grammar that was not designed for SQLite.
Consider a few concrete examples. SQLite’s type system uses type affinity rather than strict column types. A column declared as INTEGER will store an integer if you give it one, but it will also store a string. A column declared as FOOBAR is perfectly valid and behaves according to affinity rules. SQLite 3.37.0 introduced STRICT tables that enforce actual type checking, but pre-strict SQLite is a dynamically typed system wearing SQL’s syntax as a costume. A tool that assumes columns have fixed types will produce wrong completions and wrong linting.
SQLite also has several statements and clauses that simply do not exist in standard SQL:
-- SQLite-specific conflict resolution
INSERT OR IGNORE INTO users (email) VALUES ('test@example.com');
INSERT OR REPLACE INTO users (id, email) VALUES (1, 'new@example.com');
-- REPLACE is syntactic sugar for DELETE + INSERT
REPLACE INTO users (id, email) VALUES (1, 'replaced@example.com');
-- PRAGMA: SQLite's configuration and introspection interface
PRAGMA journal_mode = WAL;
PRAGMA table_info('users');
PRAGMA foreign_key_check;
-- RETURNING clause (SQLite 3.35+)
DELETE FROM users WHERE id = 5 RETURNING *;A language server that does not know about PRAGMA will flag it as a syntax error. A formatter that does not know about INSERT OR IGNORE will mangle or reject the statement. These are not edge cases; they are patterns that appear throughout SQLite-heavy codebases.
Virtual tables add another layer of complexity. SQLite’s virtual table mechanism allows extensions to register custom tables with arbitrary query semantics. The most commonly used ones are FTS5 for full-text search and R*Tree for geospatial indexing, but there are dozens of others. FTS5 tables use their own mini-syntax for match expressions:
CREATE VIRTUAL TABLE docs USING fts5(title, body);
-- FTS5 match syntax is not standard SQL
SELECT * FROM docs WHERE docs MATCH 'sqlite AND (language OR grammar)';No generic SQL tool understands that the right-hand side of MATCH is an FTS5 query string with its own parsing rules. Accurate tooling for SQLite has to account for this.
The LSP Landscape for SQL
Language Server Protocol support for SQL has improved over the past several years, but the implementations that exist tend to be PostgreSQL-centric. sqls is a capable SQL language server written in Go that provides completions and query execution, but its dialect support is weighted toward PostgreSQL and MySQL. sql-language-server covers multiple dialects but treats them as variations on a common grammar rather than distinct systems that share syntax.
The practical effect is that SQLite developers either use a generic SQL LSP and tolerate false positives and missed completions, or they give up on LSP support entirely and fall back to manual reference lookups. Neither is acceptable for a database that sees as much production use as SQLite does.
The schema introspection story is also weaker for SQLite than for server databases. PostgreSQL has information_schema and pg_catalog with rich metadata. SQLite has sqlite_master (or sqlite_schema in 3.37+), PRAGMA table_info(), and PRAGMA table_xinfo() for hidden columns. A devtool that wants to provide accurate, context-aware completions needs to query these pragmas and interpret their output correctly, including handling the differences between regular tables, views, virtual tables, and shadow tables created by extensions.
What High-Fidelity Actually Requires
“High-fidelity” in this context means the tooling understands SQLite’s grammar to the same degree that SQLite itself does, rather than approximating it through a generic SQL grammar.
For a language server, that means a parser derived from or validated against SQLite’s actual grammar, not a common SQL grammar with SQLite-specific tokens bolted on. It means understanding that PRAGMA statements return different result shapes depending on which pragma is being queried. It means knowing that WITHOUT ROWID tables behave differently from regular tables for the purposes of index analysis. It means handling SQLite’s limited ALTER TABLE support accurately: you can rename a table, rename a column (since 3.25.0), add a column, or drop a column (since 3.35.0), but you cannot add constraints or change a column’s type.
-- This is valid in PostgreSQL but not in SQLite
ALTER TABLE users ALTER COLUMN email TYPE TEXT NOT NULL;
-- SQLite 3.35+ can drop columns, but with restrictions
ALTER TABLE users DROP COLUMN legacy_field; -- valid if no indexes or triggers reference itFor a query analysis tool, high fidelity means that EXPLAIN QUERY PLAN output is parsed and displayed in a way that maps onto SQLite’s actual query planner concepts: full table scans, index scans, covering index scans, and the nested loop structure of multi-table joins. The raw output of EXPLAIN QUERY PLAN is text, and a good devtool should turn that into something a developer can act on.
For a formatter, it means respecting SQLite’s conventions rather than imposing PostgreSQL or MySQL conventions. The SQLite documentation itself uses a particular style, and a formatter that produces output consistent with that style reduces cognitive friction when referencing the docs.
The Broader Context
SQLite has been seeing increased serious usage in production server contexts, not just embedded applications. Litestream provides streaming replication to object storage. LiteFS adds distributed SQLite on Fly.io. Turso offers a SQLite-compatible distributed database. Cloudflare D1 runs SQLite at the edge. As SQLite moves further into server-side workloads, the tooling expectations that come with those workloads move with it.
Developers who build on Turso or D1 are writing SQLite SQL in environments where they expect the same quality of editor support they get for PostgreSQL. That expectation was not reasonable a few years ago, but it is reasonable now.
Lalit Maganti’s work on syntaqlite fits into this broader shift. Maganti works on Perfetto, Google’s performance tracing framework, which uses a custom SQL dialect called PerfettoSQL built on top of SQLite. The Perfetto team has direct experience with the limits of existing SQLite tooling because they have had to build tooling for a SQLite-based dialect at production scale. That context informs what “high-fidelity” means in practice, and it gives the project a credibility that comes from solving real problems rather than theoretical ones.
SQLite has always deserved better tooling than it has received. The database is mature, stable, well-documented, and more capable than its reputation suggests. The argument has always been that nobody was willing to invest in the tooling because SQLite was not taken seriously as a development platform. That argument is getting harder to make.