Claude Code maps SQLite result columns back to source tables for Datasette
Simon Willison used Claude Code (Opus 4.8) to research how to programmatically identify the source `table.column` for each result column in arbitrary SQLite queries, finding multiple viable approaches including `apsw`, a `ctypes` bridge, and `EXPLAIN` output parsing.
Score breakdown
The research identifies concrete, working methods to recover column provenance from arbitrary SQLite queries in Python — a capability Python's standard library omits — which the post describes as a prerequisite for adding richer result metadata to Datasette.
- 01SQLite exposes source table/column metadata via its column-metadata API when compiled with `SQLITE_ENABLE_COLUMN_METADATA`.
- 02Python's standard `sqlite3` module does not surface this column-metadata API.
- 03Claude Code (Opus 4.8) was used to research solutions to the problem.
The post documents research into a problem Willison wants to solve for Datasette: given an arbitrary SQL query — including those with joins, CTEs, and complex syntax — programmatically identify which source `table.column` each result column originates from. The goal is to let Datasette render query results with additional contextual information tied to the underlying table and column.
Python's built-in `sqlite3` module does not expose this API, creating a gap that requires workarounds.
SQLite does compute this provenance information internally and exposes it through its column-metadata API, but only when compiled with the `SQLITE_ENABLE_COLUMN_METADATA` flag. Python's built-in `sqlite3` module does not expose this API, creating a gap that requires workarounds. Claude Code (Opus 4.8) was tasked with finding solutions and identified three: using the third-party `apsw` library, which provides direct access via `cursor.description_full`; a pure-stdlib approach using `ctypes` to call the `sqlite3_column_table_name()` C function directly, implemented in a file called `column_provenance.py`; and a third method that cleverly interrogates the output of SQLite's `EXPLAIN` command. The post notes that Claude Fable was not used because it is currently banned by the US government.
Key facts
- 01SQLite exposes source table/column metadata via its column-metadata API when compiled with `SQLITE_ENABLE_COLUMN_METADATA`.
- 02Python's standard `sqlite3` module does not surface this column-metadata API.
- 03Claude Code (Opus 4.8) was used to research solutions to the problem.
- 04One solution uses the third-party `apsw` library via `cursor.description_full`.
- 05A second solution uses a pure-stdlib `ctypes` bridge (`column_provenance.py`) to call the `sqlite3_column_table_name()` C function.
- 06A third solution interrogates the output of SQLite's `EXPLAIN` command.
- 07The motivation is to enrich Datasette query results with provenance information about which table and column each result originates from.
Topics
Summary and scoring are generated automatically from the original article. We always link back to the publisher and never republish images or paywalled content. Last processed Jun 14, 2026 · 09:08 UTC. How this works →