Skip to content
Back to top
View this page

# 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.