Upgrading

This page describes the changes you may need to make to your own code or scripts when upgrading between major versions of sqlite-utils.

For the full list of changes in every release see the Changelog.

Upgrading from 3.x to 4.0

Requirements

  • Python 3.10 or higher is required.

  • The click dependency must be version 8.3.1 or later.

Command-line changes

Type detection is now the default for CSV and TSV imports. sqlite-utils insert and sqlite-utils upsert now detect column types when importing CSV or TSV data - previously every column was created as TEXT unless you passed --detect-types. To restore the old behavior pass the new --no-detect-types flag:

sqlite-utils insert data.db rows data.csv --csv --no-detect-types

Two related things have been removed:

  • The SQLITE_UTILS_DETECT_TYPES environment variable.

  • The old -d/--detect-types flag itself. Since detection is now the default the flag did nothing - remove it from any scripts that used it.

The convert command no longer skips falsey values. sqlite-utils convert previously skipped values that evaluated to False (empty strings, 0) unless you passed --no-skip-false. All values are now converted and the --no-skip-false flag has been removed.

drop-table and drop-view check the object type. sqlite-utils drop-table now refuses to drop a view, and drop-view refuses to drop a table. Previously each would silently drop the wrong type of object if the name matched. If you relied on that (unlikely), use the matching command instead.

sqlite-utils tui has moved to a plugin. The optional terminal interface is now provided by the sqlite-utils-tui plugin:

sqlite-utils install sqlite-utils-tui

Python API changes

db.query() now rejects SQL that does not return rows. This is likely the most common change you will need to make to existing code. db.query() used to accept any SQL statement - passing one that returns no rows, such as an INSERT or UPDATE without a RETURNING clause or a CREATE TABLE, did nothing at all, silently. Those statements now raise a ValueError, and are rolled back so they have no effect on the database. Transaction control statements (BEGIN, COMMIT, END, ROLLBACK, SAVEPOINT, RELEASE) plus VACUUM, ATTACH and DETACH are also rejected with a ValueError, without being executed at all. Use db.execute() for statements that do not return rows:

# 3.x accepted this but silently did nothing:
db.query("update dogs set name = 'Cleopaws'")

# In 4.0 use execute() for SQL that does not return rows:
db.execute("update dogs set name = 'Cleopaws'")

db.query() executes immediately. db.query(sql) previously returned a generator that did not execute the SQL until you started iterating over it. The SQL now runs as soon as the method is called - rows are still fetched lazily, but errors in your SQL raise at the db.query() call site rather than on first iteration, and a write with a RETURNING clause takes effect even if you never iterate over its results.

db.table() no longer returns views. db.table(name) now raises a sqlite_utils.db.NoTable exception if name is a SQL view. Use the new db.view(name) method for views:

table = db.table("my_table")
view = db.view("my_view")

db["name"] still returns either a Table or a View depending on what exists in the database.

Upserts use INSERT … ON CONFLICT. Upsert operations now use SQLite’s INSERT ... ON CONFLICT SET syntax rather than the previous INSERT OR IGNORE followed by UPDATE. If your code depends on the old behavior, pass use_old_upsert=True to the Database() constructor - see Alternative upserts using INSERT OR IGNORE.

Upsert records must include their primary keys. table.upsert() and table.upsert_all() now raise sqlite_utils.db.PrimaryKeyRequired if a record is missing a value for any primary key column (or has None for one). Previously such records were quietly inserted as new rows. Relatedly, pk= is now optional when the table already exists with a primary key - it is detected automatically.

Floating point columns are now REAL. Auto-detected floating point columns are created with the correct SQLite type REAL instead of FLOAT. Code that inspects column types should expect REAL.

Generated schemas use double quotes. Tables created by this library now wrap table and column names in "double-quotes" where they previously used [square-braces]. If you compare table.schema strings against expected values you will need to update them.

table.convert() no longer skips falsey values. Matching the CLI change above, table.convert() now converts every value. The skip_false parameter has been removed - previously it defaulted to True, skipping empty strings and other falsey values.

Null values are no longer extracted into lookup tables. table.extract() and the sqlite-utils extract command leave rows alone if every extracted column is null - the new foreign key column is left as null instead of pointing at an all-null record in the lookup table. The extracts= insert option similarly keeps None values as null. Relatedly, table.lookup() now compares values using IS so that looking up a value containing None returns the existing matching row - previously it inserted a duplicate row on every call.

ensure_autocommit_off() is now ensure_autocommit_on(). The db.ensure_autocommit_off() context manager has been renamed to db.ensure_autocommit_on(). The old name described the opposite of what the method did: it temporarily puts the connection into driver-level autocommit mode (by setting isolation_level = None), so that statements such as PRAGMA journal_mode=wal can run outside of an implicit transaction. The behavior is unchanged - update any calls to use the new name.

View.enable_fts() has been removed. The View class previously had an enable_fts() method that existed only to raise NotImplementedError - full-text search is not supported for views. Calling it now raises AttributeError like any other missing method.

ForeignKey is now a dataclass, not a namedtuple. The ForeignKey objects returned by table.foreign_keys gained new fields - columns, other_columns, is_compound, on_delete and on_update - so that compound (multi-column) foreign keys and foreign key actions can be represented. To make room for those fields cleanly ForeignKey is now a dataclass rather than a namedtuple, so it can no longer be unpacked or indexed as a tuple. Access its fields by name instead:

# 3.x - tuple unpacking, no longer works:
for table, column, other_table, other_column in db["courses"].foreign_keys:
    ...

# 4.0 - access fields by name:
for fk in db["courses"].foreign_keys:
    fk.table, fk.column, fk.other_table, fk.other_column

Attempting the old unpacking or fk[0] indexing now raises TypeError, so any code using those patterns will fail loudly rather than silently misbehave. Like the old namedtuple, ForeignKey instances are immutable and hashable - they can be collected into sets and used as dictionary keys. Note that equality now includes the on_delete and on_update actions: a ForeignKey with ON DELETE CASCADE is not equal to one without.

Compound foreign keys - previously returned as one ForeignKey per column, misleadingly suggesting several independent single-column keys - are now returned as a single ForeignKey with is_compound=True. For these the scalar column and other_column fields are None; use the columns and other_columns tuples instead. Single-column foreign keys are unaffected apart from the class change: column/other_column behave as before and columns/other_columns are one-item tuples.

Two related behavior changes to table.transform(): compound foreign keys now survive a transform (previously they were split into separate single-column keys), and ON DELETE/ON UPDATE actions such as ON DELETE CASCADE are now preserved (previously they were silently stripped from the schema).

Validation errors raise ValueError. Invalid arguments to Python API methods - for example create_table() with no columns, or ignore=True together with replace=True - now raise ValueError. They previously raised AssertionError from bare assert statements, which were silently skipped under python -O.

Transaction behavior is now well-defined. 4.0 introduces the db.atomic() context manager and uses it consistently for every write operation - the full model is described in Transactions and saving your changes. Changes you may notice:

  • Write statements executed with raw db.execute() calls now commit automatically, unless a transaction is already open in which case they join it. Previously they opened an implicit transaction that nothing committed - if your code used db.execute() for writes and relied on db.conn.rollback() to undo them, open an explicit transaction with the new db.begin() method first.

  • Multi-step operations such as table.transform() no longer commit an existing transaction you have open - they use savepoints inside it instead.

  • db.enable_wal() and db.disable_wal() raise a sqlite_utils.db.TransactionError if called while a transaction is open, instead of silently committing it.

  • Using Database as a context manager (with Database(path) as db:) closes the connection on exit without committing - a transaction you explicitly opened with db.begin() and did not commit is rolled back.

  • Database() rejects connections created with the Python 3.12+ sqlite3.connect(..., autocommit=True) or autocommit=False options, raising sqlite_utils.db.TransactionError. On those connections every write the library made was silently discarded when the connection closed.

Packaging changes

  • sqlite-utils now uses pyproject.toml in place of setup.py.

  • pip is now a runtime dependency, used by the sqlite-utils install and uninstall commands.

New features to be aware of

Not breaking changes, but new in 4.0 and worth knowing about when you upgrade:

  • A database migrations system, incorporating the functionality of the sqlite-migrate plugin. If you used that plugin, the built-in system reads the same _sqlite_migrations table - your applied migrations will not run again. Update your migration files to use from sqlite_utils import Migrations.

  • db.atomic() for nested transaction support.

  • table.insert_all() and table.upsert_all() accept an iterator of lists or tuples as an alternative to dictionaries - see Inserting data from a list or tuple iterator.

Upgrading from 2.x to 3.0

The 3.0 release redesigned search. The breaking changes were minor:

  • table.search() returns a generator of dictionaries, sorted by relevance. It previously returned a list of tuples sorted by rowid.

  • The -c shortcut for --csv and the -f shortcut for --fmt were removed from the CLI - use the full option names.

Upgrading from 1.x to 2.0

The 2.0 release changed the meaning of upsert. In 1.x, table.upsert() and table.upsert_all() actually performed INSERT OR REPLACE operations - entirely replacing the existing row. Since 2.0 an upsert updates only the columns you provide, leaving other columns untouched.

If you want the 1.x behavior, use table.insert(..., replace=True) or table.insert_all(..., replace=True) instead.