sqlite-utils

// Overview

sqlite-utils is a Python CLI utility and library for building and manipulating SQLite databases. Its core move is turning loose data into a queryable table with one command: pipe JSON, CSV or TSV into it and it creates a database file and a table with an appropriate schema for you, so a scraped API response or an exported spreadsheet becomes something you can run SQL against immediately.

The rest of the toolkit is aimed at what you do next. It can run in-memory SQL queries — including joins — directly against CSV, TSV and JSON files without creating a database first; configure SQLite full-text search over your tables and query it ranked by relevance; transform tables in ways plain ALTER TABLE cannot, such as changing a column's type; extract repeated columns into separate lookup tables to normalise existing data; and manage schema changes over time with Python migration files and the sqlite-utils migrate command. Plugins can add custom SQL functions and extra features.

It is maintained by Simon Willison as part of the Datasette ecosystem, alongside Datasette itself for exploring and publishing data, db-to-sqlite for pulling MySQL or PostgreSQL into a SQLite file, and the Dogsheep family of personal-analytics tools built on top of it. In LLM work it most often shows up as the layer that collects and shapes a corpus — logs, scrapes, exports — before it is embedded, evaluated or fed to a model.

// What it does

• Pipe JSON, CSV or TSV straight into a new SQLite database, with the table schema created automatically
• Run in-memory SQL queries, including joins, directly against CSV, TSV and JSON files with sqlite-utils memory
• Configure SQLite full-text search on a table and run relevance-ordered search queries against it
• Transform tables beyond what ALTER TABLE allows — change column types, and extract columns into separate normalised tables
• Manage schema evolution with Python migration files and the sqlite-utils migrate command
• Use the same functionality as a Python library, and extend it with plugins that register custom SQL functions

// Getting started

Install from PyPI (or Homebrew on macOS), then use it either as a command-line tool or as a Python library.

pip install sqlite-utils
# or on macOS
brew install sqlite-utils

sqlite-utils insert dogs.db dogs dogs.csv --csv

curl https://api.github.com/repos/simonw/sqlite-utils/releases \
  | sqlite-utils insert releases.db releases - --pk id

sqlite-utils dogs.db "select id, name from dogs"
sqlite-utils dogs.db "select * from dogs" --table
sqlite-utils memory dogs.csv "select * from t"

import sqlite_utils
db = sqlite_utils.Database("demo_database.db")
# This line creates a "dogs" table if one does not already exist:
db["dogs"].insert_all([
    {"id": 1, "age": 4, "name": "Cleo"},
    {"id": 2, "age": 2, "name": "Pancakes"}
], pk="id")

Commands and code are distilled from the project's own documentation — always check the official repo for the latest.

// When to use it

• Turn a scraped API response or a pile of CSV exports into a SQLite file you can query, search and hand to other tools
• Explore an unfamiliar dataset with SQL without setting up a database server or writing an import script
• Build and evolve the schema of a small analytics or evaluation dataset over time using the migrations system
• Add full-text search to a corpus of documents or logs before embedding or sampling it for LLM work

// How sqlite-utils compares

sqlite-utils alongside other open-source data wrangling tools AI/TLDR tracks, ranked by GitHub stars.