sqlite-utils

SQLite database manipulation CLI and library

TLDR

Import JSON into SQLite

$ sqlite-utils insert [database.db] [table] [data.json]

Import CSV into SQLite

$ sqlite-utils insert [database.db] [table] [data.csv] --csv

Query with SQL

$ sqlite-utils [database.db] "[SELECT * FROM table]"

Query and output as JSON

$ sqlite-utils [database.db] "[SELECT * FROM table]" --json

List tables

$ sqlite-utils tables [database.db]

Show schema

$ sqlite-utils schema [database.db]

Create FTS search index

$ sqlite-utils enable-fts [database.db] [table] [column1] [column2]

Export table to JSON

$ sqlite-utils rows [database.db] [table] > [data.json]

SYNOPSIS

sqlite-utils command [database] [options] [arguments]

sqlite-utils provides a CLI and Python library for working with SQLite databases. It simplifies common tasks: importing data, running queries, and managing schema.
Data import handles JSON, CSV, TSV, and newline-delimited JSON. Types are inferred automatically. Primary keys and foreign keys can be specified. Existing tables are updated or replaced as configured.
Querying supports SQL with multiple output formats. The memory subcommand runs queries against in-memory databases, useful for quick data processing without persistent files.
Full-text search (FTS) enables fast text searching. Enable-fts creates virtual tables for specified columns. Search queries use SQLite's FTS5 syntax for relevance ranking.
Table management includes creation, alteration, and inspection. Schema command shows CREATE statements. Columns can be added without recreating tables.
The tool integrates well with Unix pipelines. Data flows in from curl or other tools, gets processed, and outputs to further commands.

PARAMETERS

insert DB TABLE [FILE]

Insert data from JSON, CSV, or stdin.

rows DB TABLE

Output rows as JSON.

tables DB

List tables.

schema DB

Show database schema.

query DB SQL

Run SQL query.

memory SQL

Run against in-memory database.

enable-fts DB TABLE COLUMNS

Enable full-text search.

search DB TABLE QUERY

Full-text search.

create-table DB TABLE COLUMNS

Create table with columns.

drop-table DB TABLE

Drop table.

add-column DB TABLE COL [TYPE]

Add column to table.

indexes DB [TABLE]

List indexes.

--csv

Input is CSV.

--tsv

Input is TSV.

--nl

Input is newline-delimited JSON.

--pk COLUMN

Primary key column.

--json

Output as JSON.

--table

Output as table.

-c, --csv

Output as CSV.

CAVEATS

Large inserts may need --batch-size adjustment. FTS increases database size. Type inference may not always be correct. Some features require SQLite version 3.25+. Memory usage grows with large datasets.

HISTORY

sqlite-utils was created by Simon Willison around 2019 as part of the Datasette ecosystem. It provides command-line access to SQLite manipulation that previously required Python scripting or separate tools. The library is also usable from Python for programmatic database work.