Simon Willison’s new sqlite-utils 4 post is a timely reminder: SQLite is still the fastest way to tame messy AI data locally—without spinning up servers or notebooks. Here are three battle-tested CLI workflows you can use today.
Why sqlite-utils for AI projects
AI work is 80% data wrangling. sqlite-utils wraps SQLite with a friendly CLI and Python API so you can import, clean, search, and export datasets quickly and reproducibly.
- Local, lightweight, and fast—ideal for private or regulated data.
- Transparent schema and versionable .db files that play nicely with Git.
- Powerful features like indexing and full-text search (FTS5).
Workflow 1 — Import messy CSV/JSON in seconds
Load raw files straight into a clean SQLite table, set a primary key, and let sqlite-utils infer types.
- Load CSV:
sqlite-utils insert data.db reviews reviews.csv --csv --pk id - Load newline-delimited JSON:
sqlite-utils insert data.db items items.json --pk id --nl - List tables to confirm:
sqlite-utils tables data.db
Workflow 2 — Add instant full‑text search (FTS5)
Turn free text (e.g., user reviews, support tickets) into fast, ranked search. Under the hood this uses SQLite FTS5.
- Create an FTS index on your text column:
sqlite-utils create-fts data.db reviews review_text - Query with MATCH (example):
sqlite-utils query data.db "select rowid, * from reviews_fts where reviews_fts match 'battery NEAR life' limit 20" --csv
Tip: FTS5 is included in most modern SQLite builds. If you see errors, check your SQLite binary or use a Python environment where FTS5 is available.
Workflow 3 — Query and export clean slices
Aggregate, filter, and ship tidy extracts for downstream modeling or sharing with teammates.
- Top products by average rating:
sqlite-utils query data.db "select product_id, avg(rating) as avg_rating from reviews group by product_id order by avg_rating desc limit 20" --csv > top_products.csv - Dump a filtered subset to JSON:
sqlite-utils rows data.db reviews --where "rating >= 4" --json > positive_reviews.json
Pro tips
- Always set a primary key (e.g.,
--pk id) for reliable upserts and deduping. - Index columns you filter on often:
sqlite-utils create-index data.db reviews product_id - Keep your .db in Git to track schema changes alongside code and prompts.
- Document your import and transform commands in a Makefile for repeatability.
Sources
- Announcement: sqlite-utils 4 by Simon Willison
- Docs: sqlite-utils documentation
- Reference: SQLite FTS5
Takeaway
For small-to-midsize AI datasets, sqlite-utils + SQLite is a speed advantage: import fast, search instantly, and export clean slices—no infra, no friction.
Enjoy these Nuggets? Get one useful AI tactic in your inbox—subscribe to our newsletter: theainuggets.com/newsletter
