Want a fast, reliable way to wrangle AI data locally? SQLite plus sqlite-utils is a lightweight stack for logging prompts, searching outputs, and preparing embeddings without standing up new infra.
If you’re exploring updates around sqlite-utils, see Simon Willison’s post for context: sqlite-utils on SimonWillison.net. For hands-on usage, the official docs below are the best starting point.
What is sqlite-utils?
sqlite-utils is a Python library and CLI by Simon Willison that makes it trivial to create tables, insert JSON/CSV, enable full-text search, and manage indexes in SQLite. It’s ideal for AI teams who want a simple, auditable data hub.
Quick start: log AI prompts and outputs
First, save each interaction as newline-delimited JSON (JSONL) with fields like: id, ts, user_input, model, output, latency_ms, tokens_in, tokens_out.
Ingest them into SQLite in one step (table is created automatically): sqlite-utils insert ai.db prompts prompts.jsonl –pk id –nl –flatten
Glance at a few rows to verify the schema and data: sqlite-utils rows ai.db prompts -l 3
Make it searchable with FTS5
Enable full-text search on your prompt and output columns: sqlite-utils enable-fts ai.db prompts user_input output –fts5
FTS5 lets you filter and diagnose behavior across models fast—for example, pinpointing hallucination patterns by keyword. Learn more in the SQLite FTS5 docs.
Add embeddings when you need them
Start simple: add a column for vectors as JSON and store them alongside your rows. Example: sqlite-utils add-column ai.db prompts embedding_json
Compute embeddings in your app code, then update rows to include the JSON array. If you outgrow naive filtering, pair SQLite with a vector extension such as sqlite-vec for fast similarity search.
Production-friendly tips
- Create helpful indexes for dashboards and analytics: sqlite-utils create-index ai.db prompts ts model
- Preserve auditability: never overwrite rows; append with stable ids and timestamps.
- Keep it portable: a single SQLite file makes backups and sharing trivial.
Why this matters
This setup gives you quick iteration, strong observability, and low ops overhead. It’s perfect for RAG prototypes, evaluation pipelines, and small production agents.
Sources and further reading
Takeaway: Use sqlite-utils to turn SQLite into an AI-ready data hub—log everything, search instantly, and evolve into embeddings when you’re ready.
Enjoy this? Get 2–3 smart AI nuggets each week. Subscribe to the newsletter.
