sqlite-utils: a CLI and library for the boring parts of SQLite

Installing it takes one line, and the CLI assumes a shell

The install path is short enough that there is nothing to get wrong.

pip install sqlite-utils

Homebrew is offered as an alternative for macOS. The Python requirement is not on the README but in pyproject.toml, which declares requires-python of 3.10 or later and carries classifiers for 3.10 through 3.14. The package is licensed Apache-2.0, and the console script entry point is sqlite_utils.cli:cli, which is what puts the sqlite-utils command on your path.

The dependencies explain the feature set better than the marketing does. click and click-default-group build the command line interface, tabulate is what makes the --table output line up in columns, sqlite-fts4 provides FTS4 support for enabling search, python-dateutil handles date parsing on insert, and pluggy is the plugin mechanism. pip appears in the runtime dependency list, which is unusual and worth knowing if you are building a locked environment.

The CLI is designed around pipes. Output is JSON by default, which means every command composes with jq or with another sqlite-utils invocation without a format flag. Add --csv and you get CSV, add --table and you get an aligned table for a human.

sqlite-utils memory queries a file without creating a database

This is the feature that gets the most use and appears third in the README's highlights list. sqlite-utils memory loads CSV, TSV or JSON into an in-memory database, runs the SQL you give it, including joins, and prints the result. Nothing touches the disk.

$ sqlite-utils memory dogs.csv "select * from t"

The table name is t when you pass a file path, and stdin when data arrives on standard input.

$ cat dogs.csv | sqlite-utils memory - "select name, age from stdin"

That single command replaces the usual incantation of loading a CSV into a throwaway database file to answer one question. It is also the right way to explore a data dump before committing to a schema, since nothing you try gets written anywhere.

Once you do want to keep the data, the same table argument carries over. insert takes a database file, a table name and a source, and the --csv flag selects the format.

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

Schema inference is automatic here: columns and types are derived from the first record and the table is created if it does not exist. Passing --pk id tells it which column is the primary key. For JSON from a network endpoint the README pipes curl straight in, using - for the table source and --pk id for the key, which is a good example of how the tool is meant to sit in a shell pipeline rather than behind a config file.

Transformations cover the ALTER TABLE gaps SQLite leaves open

SQLite's ALTER TABLE is deliberately limited. It can rename a table, add a column and drop a column depending on version, but it cannot change a column's type, and it cannot add a constraint. sqlite-utils transform fills those gaps by rewriting the table under the hood.

The README's own list of what this buys you names changing the type of a column as the example. That is the operation you reach for after a data import discovers that a column of integers came in as text, and it is the one that makes you think carefully, because a transform is a table rewrite rather than an in-place edit.

The other schema-shaped features are extract, enable-fts and migrations. extract pulls columns out into separate tables, which is how you normalise data you inherited without hand-writing the new table and its foreign key. enable-fts configures SQLite full-text search against a table so you can run search queries ordered by relevance, with a tokenizer argument accepted on both the table method and the CLI command.

Migrations are the feature that changes how a project uses this. sqlite-utils migrate runs numbered Python migration files against a database and records what has run, so schema changes become reviewable code in a repository instead of a sequence of things somebody remembered to type. That is the same pattern as Django or Alembic, applied to a single file database with no server involved.

Version 4.2 fixed a dependency error it had shipped hours earlier

The release history is worth reading closely because it shows both the pace and the sharp edges. Version 4.2 shipped on 2026-08-13 and version 4.2.1 shipped the same day, roughly four hours later. The patch release exists for exactly one reason: a crashing bug in 4.2 caused by a missing typing_extensions module.

That is a packaging mistake, the kind that turns into a broken deploy for everyone who upgraded on the day. It is also an argument for pinning a version rather than tracking a channel, which is ordinary advice but has a concrete example attached here.

The 3.39.1 release is the more interesting one for anyone automating schema work. It fixed a bug where table.delete_where() left the connection in an open transaction, which caused deleted rows to be silently restored when the connection closed. A method that appeared to delete rows and then had them come back is the exact failure mode you want to know about before putting it in a job with no human watching.

On the feature side, 4.2 added table.checks, table.column_checks and table.table_checks for introspecting CHECK constraints, and a sqlite_utils.ANY marker type for creating and introspecting ANY columns, with transform() and extract() preserving them in STRICT tables. It also made default_values unescape doubled single quotes so 'O''Brien' comes back as O'Brien, decode bare TRUE, FALSE and NULL literals as real Python values, and safely quote the enable-fts tokenizer argument to stop a crafted value injecting extra SQL. That last one is a security fix in a tool that builds SQL from user-supplied strings, which is the right thing to have found and fixed.

Upgrading from 3.x means reading the upgrade guide

The README carries a line for anyone arriving from 3.x: see the 4.0 upgrade guide. The jump from 3.39.1 to 4.2 is a major version change across roughly forty patch releases, and the release history in this repository only shows the tail of it.

That gap matters for anyone with an existing dependency pin. A caret range on the 3.x line will not pull 4.x, so nothing breaks silently on upgrade, which is the good outcome. The cost is on the other side: an old constraint keeps you on a line that stopped getting fixes, including the delete_where transaction bug and whatever came after it.

The project's documentation is structured for this. There is a separate CLI reference, a Python API reference, a migrations page and a plugins page, all under the stable documentation path, with a changelog that the README badges link to directly. Read the upgrade guide first, then diff your actual usage against the Python API reference rather than trying to infer changes from the release list.

Plugin support is the extension point worth knowing about if your use case is off the beaten path. Plugins are installed through the pluggy mechanism to add custom SQL functions, which means you can put a domain specific function into the same query language rather than precomputing values in Python. That is a real advantage over doing the same work with csvkit or a shell loop.

Where this stops being the right tool

sqlite-utils is scoped to SQLite, single file or in-memory, and it does not hide that. There is no server, no connection pooling, no user management and no network protocol. If your data lives in Postgres or MySQL and you want a command line for it, the related project db-to-sqlite exists to export into a SQLite file first, which tells you the direction of travel this tool chain assumes.

Schema inference from the first record is convenient and occasionally wrong. If a JSON array has heterogeneous objects, or a numeric column arrives as a string on row forty and an integer on row one, the inferred type follows what it saw first. This is the same class of problem you get with any automatic schema tool, and the fix is always the same: look at the table before you load a million rows into it.

The bigger structural limit is that transforms rewrite tables. On a large database that is a long operation with a copy in the middle, and a failure partway through leaves you relying on whatever atomicity SQLite gives you for that statement. The delete_where bug is a reminder that this layer does have edges, so for anything you cannot regenerate from source, take a copy of the file first.

Datasette is the natural companion rather than a competitor. The README lists it first under related projects, along with csvs-to-sqlite, db-to-sqlite and dogsheep, a personal analytics toolkit built on top of sqlite-utils. The pattern is: build and transform with sqlite-utils, then explore and publish with Datasette.

Editorial conclusion

sqlite-utils is at its best in the places where raw SQLite is genuinely awkward: inferring a schema from a JSON file, rewriting a column type, normalising with extract, and writing reproducible migrations as Python files. It is the wrong tool for a database you do not own and must not alter, since its transformations rewrite tables. Two things to weigh before adopting it in a pipeline. Read the 4.0 upgrade guide, because the move from 3.x to 4.x was a major version and behaviour changed, and note that a dependency error shipped in 4.2 and was fixed in 4.2.1 hours later, so pin your version rather than tracking latest. Start with sqlite-utils memory against a CSV file, which writes nothing, before you point insert or transform at a database that matters.

Frequently asked questions

Official sources

1. License: Apache-2.0
2. Project website
3. README
4. Releases
5. simonw/sqlite-utils on GitHub