sqlite-utils: The Command-Line Database Tool That Actually Thinks Like a Developer

Most database tools either talk down to you with a GUI or make you feel like you’re filling out forms. sqlite-utils does neither. It’s a Python library and CLI that treats SQLite like what it actually is: the most underrated database in the world, now given a proper interface for people who think in commands.

The library is by Simon Willison, the co-creator of Django and one of the more interesting thinkers in open-source. It shows in the design.

What it actually does

The simplest thing: you can create a SQLite database from JSON in one command.

sqlite-utils insert data.db mytable records.json

That’s it. It reads the JSON, infers the schema, creates the table, and inserts the rows. No CREATE TABLE, no schema definition, no migration. If the column types need to change later, there are commands for that too.

You can also pipe data in:

curl -s https://api.example.com/items | sqlite-utils insert data.db items -

Or query it straight from the command line without dropping into the SQLite shell:

sqlite-utils query data.db "select name, count(*) from items group by name" --csv

For a tool built on SQLite, the experience is surprisingly un-SQLite-like. It abstracts away the parts that are friction — schema definition, type coercion, awkward shell interaction — while keeping full access to the underlying database when you need it.

The interesting design decision

The library lets you insert data without defining a schema first, and it figures out what you meant. But the more interesting feature is how it handles schema changes: it adds columns if new keys appear in subsequent inserts. This is not how most databases think. Most databases say “your schema is the contract.” sqlite-utils says “your data is the source of truth.”

This makes it excellent for exploratory work — journalism, research, one-off analysis — where you don’t know exactly what shape your data will take until you’re already wrist-deep in it.

It’s also worth looking at how the Python API mirrors the CLI almost perfectly. db["mytable"].insert({"name": "Alice", "age": 30}) works exactly the way you’d expect from reading the CLI docs. This kind of surface-area coherence is rare and the result of consistent design thinking rather than accretion.

Who it’s for

Journalists who scrape data and need to query it without spinning up a Postgres instance. Developers prototyping an API and wanting to persist data without committing to a database layer. Data engineers doing one-time transformations. Anyone who has reached for a spreadsheet and immediately regretted it.

The project also has excellent support for full-text search (via SQLite’s FTS5) and geographic queries (via SpatiaLite). The full-text search setup is a single method call: db["articles"].enable_fts(["title", "body"]). Datasette, Willison’s companion project for publishing SQLite as a web API, pairs with it naturally.

What a builder can learn

Two things worth studying in the codebase:

1. The table.transform() method. SQLite doesn’t support ALTER COLUMN. If you need to change a column’s type or rename it, you have to create a new table, copy the data, drop the old one, and rename the new one. sqlite-utils abstracts this cleanly — you declare what you want the table to look like, and it handles the migration. Reading how it’s implemented is a good lesson in working around database limitations without leaking the complexity upward.

2. The CLI is built with Click. The way the commands are organized — each subcommand as its own function, composed cleanly — is a solid model for any CLI tool in Python. The command surface feels large (there are a lot of subcommands) but each one does one thing well.

The project is also one of the best-documented open-source tools you’ll find. Willison writes obsessively good docs, and sqlite-utils is no exception. The documentation is the kind that makes you think “I should document my stuff like this.”

sqlite-utils is maintained by Simon Willison and licensed under Apache 2.0.