sqlite-utils Documentation

The following features are new in 4.0 and do not break existing functionality:

• Database Migrations: A built-in migration system is now available. If you previously used the sqlite-migrate plugin, the new system is compatible. Update your migration files to use from sqlite_utils import Migrations.
• Nested Transactions: Use db.atomic() for nested transaction support.
• Iterator Support: table.insert_all() and table.upsert_all() now accept an iterator of lists or tuples as an alternative to dictionaries.

Most functionality in sqlite-utils is accessible through two primary interfaces:

1. CLI tool: The sqlite-utils command-line utility for quick database manipulations.
2. Python API: A library for integrating database creation and population workflows directly into your Python code.

Note: sqlite-utils is not a full ORM; it is a set of utility helpers focused on making the initial database creation and data population process productive.

If you attempt to insert a Python dictionary or list into a table, sqlite-utils will automatically create a TEXT column and store the data as a serialized JSON string. This allows you to leverage SQLite's built-in JSON functions to query complex nested structures.

# Inserting nested data
db.table("niche_museums").insert({
    "name": "The Bigfoot Discovery Museum",
    "address": {
        "streetAddress": "5497 Highway 9",
        "addressLocality": "Felton, CA"
    }
})

# Querying JSON using standard SQL
db.execute("""
    select json_extract(address, '$.addressLocality')
    from niche_museums
""").fetchall()
# Returns [('Felton, CA',)]

In sqlite-utils, most write methods (insert(), upsert(), update(), delete(), delete_where(), transform(), create_table(), create_index(), enable_fts(), etc.) and raw SQL via db.execute() run inside their own transaction and commit automatically before returning.

Your changes are saved to disk as soon as the method call finishes. You do not need to call commit() manually for standard operations.

db = Database("data.db")
db.table("news").insert({"headline": "Dog wins award"})
# The new row is already saved - no commit() required

When using sqlite-utils memory with files, the in-memory tables are named after the files without their extensions.

Additionally, the tool creates aliases for these tables using SQL views:

• t1, t2, etc., correspond to the files in order.
• t refers to the first table.
• For data piped from standard input (- or stdin), use stdin, t, or t1 as the table name.

If two files have the same name, they are assigned a numeric suffix (e.g., data_2).

Version 4.0 introduces a more robust transaction model using db.atomic().

• Automatic Commits: Write statements executed via db.execute() now commit automatically unless a transaction is already open.
• db.begin(): If you rely on db.conn.rollback() to undo writes made via db.execute(), you must now explicitly open a transaction using db.begin() first.
• Context Manager: Using Database as a context manager (with Database(path) as db:) closes the connection on exit without committing. Any transaction you explicitly opened with db.begin() that was not committed will be rolled back.
• WAL mode: db.enable_wal() and db.disable_wal() will now raise a sqlite_utils.db.TransactionError if called while a transaction is open.

To create a table with a primary key spanning multiple columns, pass a tuple of column names to the pk= parameter in .create(), .insert(), .insert_all(), .upsert(), or .upsert_all().

# Creating a table with a compound PK
db.table("cats").create({
    "id": int,
    "breed": str,
    "name": str,
    "weight": float,
}, pk=("breed", "id"))

The .transform() method works by dropping the old table and creating a new one. If PRAGMA foreign_keys is enabled, this can trigger destructive ON DELETE actions (like CASCADE) on tables referencing the target table.

To protect against this, sqlite-utils attempts to toggle PRAGMA foreign_keys off and on during the operation. However, because PRAGMA foreign_keys cannot be changed inside an active transaction, calling .transform() inside a with db.atomic(): block or after db.begin() will raise a sqlite_utils.db.TransactionError if destructive foreign keys exist.

Solutions:

1. Call .transform() outside of the transaction.
2. Manually disable foreign keys before starting the transaction:

db.execute("PRAGMA foreign_keys = off")
with db.atomic():
    db["authors"].transform(types={"id": str})
db.execute("PRAGMA foreign_keys = on")

sqlite-utils uses a migration system to apply repeatable changes to SQLite databases. A migration is a Python function that accepts a sqlite_utils.Database instance and performs operations like creating tables, adding columns, or inserting rows.

Migrations are organized into named sets using the sqlite_utils.Migrations class. When a migration is applied, it is recorded in a special _sqlite_migrations table within the database. This allows you to run the migration process multiple times safely; sqlite-utils will only execute migrations that haven't been recorded yet.