// the find
simonw/sqlite-utils
Python CLI utility and library for manipulating SQLite databases
A CLI and Python library for reading, writing, and reshaping SQLite databases — pipe in CSV/JSON/TSV and get a typed table, run ad-hoc SQL against files without loading them into a DB first, or drive the same operations from Python. Built by the Datasette author and used as the data-prep layer for that ecosystem, but it's fully useful standalone for anyone scripting against SQLite.
The `transform` command handles the workarounds SQLite's limited ALTER TABLE needs (changing a column type, dropping a column, adding a constraint) by rebuilding the table under the hood, so you don't have to hand-write that dance yourself. The `memory` command lets you run SQL joins across CSV/JSON files with zero setup, which is a genuinely useful trick for one-off data wrangling. Plugin hooks (hookspecs.py) let you register custom SQL functions without forking the library. Test suite is large and specific (separate files per feature: FTS, GIS, migrations, m2m, hypothesis-based property tests), and CI runs against multiple SQLite versions.
Everything is synchronous — no async API, so it's a bad fit inside an asyncio web app without wrapping calls in a thread pool. `transform` rebuilds the whole table to work around ALTER TABLE limits, which gets slow on large tables since there's no incremental path. Migration support (migrations.py) is a lightweight homegrown mechanism, not a full migration framework — don't expect alembic-style revision tracking or rollback semantics. Several features (FTS ranking, some CLI flags) lean on SQLite-version-specific behavior, so results can quietly differ between the bundled Python sqlite3 and a newer libsqlite3 install.