sqlaguna

sqlaguna is a loadable SQLite extension that shells out to:

pool exec --unsafe-auto-allow -a laguna-xs.2 -p "<PROMPT>"

It expects pool to print a read-only SELECT or WITH query to stdout. The extension opens the current main database file read-only, injects its live schema into the prompt, then prepares that SQL and exposes the rows as a table source.

Internally, sqlaguna prefixes your prompt with an instruction that asks the model to return the query inside <SQL>...</SQL>, includes the live main database schema from sqlite_schema, and parses that marker before falling back to looser extraction.

sqlaguna also caches materialized results per SQLite connection and prompt, so joins and rescans reuse the first result set instead of calling pool again for every nested-loop pass.

Build

make

Load

.load ./sqlaguna

Usage

SELECT *
FROM sqlaguna('list the 10 most recent users');

Because SQLite virtual tables need a fixed schema at prepare time, the extension exposes up to 32 generic output columns named c1 through c32. The generated query can still be used in subqueries, CTEs, and joins:

WITH ai AS (
  SELECT c1 AS user_id, c2 AS user_name
  FROM sqlaguna('return user id and name from users order by created_at desc limit 10')
)
SELECT ai.user_name, p.title
FROM ai
JOIN posts p ON p.user_id = ai.user_id;

Real-life demo

SQLite version 3.45.1 2024-01-30 16:01:20
Enter ".help" for usage hints.

sqlite> .load sqlaguna

sqlite> SELECT ai.c1 artist, ai.c2 total_usd FROM sqlaguna('top artists by earnings') AS ai;
artist  total_usd    
------  -------------
Sarna   7963.92708738


sqlite> SELECT a.c1 AS title, a.c2 AS total_earnings, b.c2 AS sale_count
  FROM sqlaguna('top 5 song titles and total earnings_usd per title') AS a
  JOIN sqlaguna('song title and its count per title') AS b
    ON a.c1 = b.c1
  ORDER BY CAST(a.c2 AS REAL) DESC;
title                               total_earnings  sale_count
----------------------------------  --------------  ----------
Huzurlu Rüya: mırıldanan ninni      1426.29062002   11        
Mom's Here                          695.52254138    11        
Kołysanka Przytulanka               521.11990074    9         
安らかな夢: 子守唄                  516.0984526     11        
Doggy Dreams                        487.38273903    11        

Notes

• Only read-only row-returning SQL is allowed.
• If the AI returns more than 32 columns, the query fails.
• pool must be available on PATH for the SQLite process.
• The main database must be file-backed, not :memory:, because the generated SQL runs on a separate read-only SQLite connection.
• Cached results are invalidated when the connection's total_changes counter changes.