source: simon willison: mapping sqlite result columns back to their source `table.column`
level: technical
simon willison investigated how to determine the source table and column for each result column in arbitrary sqlite queries. this capability would allow datasette to annotate query results with metadata about their origins, even for complex queries involving joins and common table expressions. sqlite internally tracks this information and exposes it through its column-metadata api when compiled with sqlite_enable_column_metadata, but python's standard sqlite3 module does not provide access to it.
several approaches were identified to retrieve this column provenance. the third-party apsw library offers direct access via cursor.description_full. alternatively, a pure-python solution using ctypes can call the sqlite3_column_table_name() c function, which is not otherwise exposed to python. another method involves parsing the output of the explain command to infer column origins. willison used claude code (opus 4.8) to explore these solutions, noting that fable was unavailable due to a us government ban.
the work aims to enhance datasette, a tool for exploring and publishing data, by automatically linking result columns to their source tables. this would improve data understanding and traceability for users running ad-hoc sql queries. the findings highlight practical techniques for working with sqlite's internal metadata, which could benefit other tools and libraries that need to map query results to database schema elements.
why it matters: enables automatic annotation of sql query results with source table and column metadata, improving data exploration and debugging in tools like datasette.
source: simon willison: mapping sqlite result columns back to their source `table.column`