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

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. Two consequences:

  • Errors in your SQL now raise at the db.query() call site rather than on first iteration.

  • Passing a statement that returns no rows - such as an INSERT or UPDATE without a RETURNING clause - previously did nothing at all, silently. It now raises a ValueError. Use db.execute() for statements that do not return rows.

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.

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.

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.