Building syntaqlite: A SQLite DevTools Project Created with AI Assistance

What is syntaqlite?

syntaqlite is a set of developer tools for working with SQLite, created by Lalit Maganti and released on GitHub. The project was built over approximately 250 hours during three months of evenings, weekends, and vacation days.

Why this project was needed

Maganti works on Perfetto at Google, maintaining PerfettoSQL, a SQLite-based language for querying performance traces with about 100K lines of code used internally. Users expected formatters, linters, and editor extensions, but existing SQLite tools weren't reliable, fast, or flexible enough to adapt to PerfettoSQL.

The technical challenge

The core difficulty was building an accurate parser that works exactly like SQLite's parser. Unlike many languages, SQLite has no formal specification for parsing, doesn't expose a stable parser API, and doesn't even build a parse tree in its implementation. The only viable approach was extracting and adapting relevant parts of SQLite's source code.

SQLite is written in C with an incredibly dense style. Maganti spent days just understanding the virtual table API and implementation. The parser requires handling over 400 grammar rules, each needing specification of how syntax maps to parse tree nodes. This work is both hard (understanding the complex codebase) and tedious (repetitive rule specification and testing).

AI's role in development

Maganti credits AI coding agents as the main reason this project finally happened after eight years of wanting it. He systematically documents where AI helped and where it was detrimental, backing claims with evidence from his project journal, coding transcripts, and commit history.

This type of tool is useful for developers working with SQLite who need accurate parsing for formatters, linters, and editor extensions. The approach of adapting SQLite's own parser code ensures compatibility with SQLite's exact behavior.