syntaqlite: high-fidelity devtools that SQLite deserves

but it turns out that's a wrapper for the binary CLI tool, not a Python library I can import and call.

I actually learned this trick from you! Your post was about Go but same principle applies with Rust of course.

Adding bindings for Python and a few other major languages (Go, Kotlin/Java) was actually something I wanted to do before 0.1. But I had already been dragging cutting a release, polishing things so I told myself "enough is enough" and launched. And of course, the thing I decided to cut is the first thing someone asks for!

Thankfully, because I already was planning for this, the library's architecture was almost completely in the right shape. So today I worked on adding:

1. Python bindings for the C library (https://docs.syntaqlite.com/main/getting-started/python/)
2. Adding the functionality in Rust which computes the "lineage" of columns and tables in a select statement and plumbing that through the layers (see 3b in the tutorial)

Now you can do:

❯ uv run --with syntaqlite -- python3 -c "import syntaqlite; print(syntaqlite.validate('select column from event').lineage.tables)"
['event']

Hope this is what you were looking for!

I'm particularly interested in parsing SELECT statements to introspect them and figure things out like which tables they are accessing.

If you still want to do raw AST walking, I also improved upon the approach from your research repo. instead of raw dicts, I generate typed AST node objects so you get auto-complete/type-checking. See 4. in https://docs.syntaqlite.com/main/getting-started/python/

I had Claude knock out an example Python extension with a WASM build as well. You can try that out here: https://tools.simonwillison.net/pyodide-repl

Run this code:

import micropip
await micropip.install("https://raw.githubusercontent.com/simonw/research/a526b0faa9edb10c58f5e202efdd0a8ac9a0c1b1/syntaqlite-python-extension/dist/syntaqlite-0.1.0-cp311-cp311-emscripten_3_1_46_wasm32.whl")
import syntaqlite
syntaqlite.format_sql('select * from dogs')
# Outputs 'SELECT * FROM dogs;\n'
syntaqlite.validate('select id, name2 from dogs', tables = [{"name": "dogs", "columns": ["id", "name"]}])
# [{'severity': 'error', 'message': "unknown column 'name2'", 'start_offset': 11, 'end_offset': 16}]

More details here: https://github.com/simonw/research/tree/main/syntaqlite-python-extension#readme