4.0a1

• Breaking change: The db.table(table_name) method now only works with tables. To access a SQL view use db.view(view_name) instead. (#657)
• The table.insert_all() and table.upsert_all() methods can now accept an iterator of lists or tuples as an alternative to dictionaries. The first item should be a list/tuple of column names. See Inserting data from a list or tuple iterator for details. (#672)
• Breaking change: The default floating point column type has been changed from FLOAT to REAL, which is the correct SQLite type for floating point values. This affects auto-detected columns when inserting data. (#645)
• Now uses pyproject.toml in place of setup.py for packaging. (#675)
• Tables in the Python API now do a much better job of remembering the primary key and other schema details from when they were first created. (#655)
• Breaking change: The table.convert() and sqlite-utils convert mechanisms no longer skip values that evaluate to False. Previously the --skip-false option was needed, this has been removed. (#542)
• Breaking change: Tables created by this library now wrap table and column names in "double-quotes" in the schema. Previously they would use [square-braces]. (#677)
• The --functions CLI argument now accepts a path to a Python file in addition to accepting a string full of Python code. It can also now be specified multiple times. (#659)
• Breaking change: Type detection is now the default behavior for the insert and upsert CLI commands when importing CSV or TSV data. Previously all columns were treated as TEXT unless the --detect-types flag was passed. Use the new --no-detect-types flag to restore the old behavior. The SQLITE_UTILS_DETECT_TYPES environment variable has been removed. (#679)

3.39

• Fixed a bug with sqlite-utils install when the tool had been installed using uv. (#687)
• The --functions argument now optionally accepts a path to a Python file as an alternative to a string full of code, and can be specified multiple times – see Defining custom SQL functions. (#659)
• sqlite-utils now requires Python 3.10 or higher.

sqlite-utils 4.0a1 is now available as an alpha with some minor breaking changes.

4.0a0

• Upsert operations now use SQLite's INSERT ... ON CONFLICT SET syntax on all SQLite versions later than 3.23.1. This is a very slight breaking change for apps that depend on the previous INSERT OR IGNORE followed by UPDATE behavior. (#652)
• Python library users can opt-in to the previous implementation by passing use_old_upsert=True to the Database() constructor, see Alternative upserts using INSERT OR IGNORE.
• Dropped support for Python 3.8, added support for Python 3.13. (#646)
• sqlite-utils tui is now provided by the sqlite-utils-tui plugin. (#648)
• Test suite now also runs against SQLite 3.23.1, the last version (from 2018-04-10) before the new INSERT ... ON CONFLICT SET syntax was added. (#654)

3.38

• Plugins can now reuse the implementation of the sqlite-utils memory CLI command with the new return_db=True parameter. (#643)
• table.transform() now recreates indexes after transforming a table. A new sqlite_utils.db.TransformError exception is raised if these indexes cannot be recreated due to conflicting changes to the table such as a column rename. Thanks, Mat Miller. (#633)
• table.search() now accepts a include_rank=True parameter, causing the resulting rows to have a rank column showing the calculated relevance score. Thanks, liunux4odoo. (#628)
• Fixed an error that occurred when creating a strict table with at least one floating point column. These FLOAT columns are now correctly created as REAL as well, but only for strict tables. (#644)

3.38a0

• Plugins can now reuse the sqlite-utils memory command with the new return_db=True parameter. #643

3.37

• The create-table and insert-files commands all now accept multiple --pk options for compound primary keys. (#620)
• Now tested against Python 3.13 pre-release. (#619)
• Fixed a crash that can occur in environments with a broken numpy installation, producing a module 'numpy' has no attribute 'int8'. (#632)

3.36

• Support for creating tables in SQLite STRICT mode. Thanks, Taj Khattra. (#344)
  • CLI commands create-table, insert and upsert all now accept a --strict option.
  • Python methods that can create a table - table.create() and insert/upsert/insert_all/upsert_all all now accept an optional strict=True parameter.
  • The transform command and table.transform() method preserve strict mode when transforming a table.
• The sqlite-utils create-table command now accepts str, int and bytes as aliases for text, integer and blob respectively. (#606)

3.35.2

• The --load-extension=spatialite option and find_spatialite() utility function now both work correctly on arm64 Linux. Thanks, Mike Coats. (#599)
• Fix for bug where sqlite-utils insert could cause your terminal cursor to disappear. Thanks, Luke Plant. (#433)
• datetime.timedelta values are now stored as TEXT columns. Thanks, Harald Nezbeda. (#522)
• Test suite is now also run against Python 3.12.

3.35.1

• Fixed a bug where table.transform() would sometimes re-assign the rowid values for a table rather than keeping them consistent across the operation. (#592)

3.35

Adding foreign keys to a table no longer uses PRAGMA writable_schema = 1 to directly manipulate the sqlite_master table. This was resulting in errors in some Python installations where the SQLite library was compiled in a way that prevented this from working, in particular on macOS. Foreign keys are now added using the table transformation mechanism instead. (#577)

This new mechanism creates a full copy of the table, so it is likely to be significantly slower for large tables, but will no longer trigger table sqlite_master may not be modified errors on platforms that do not support PRAGMA writable_schema = 1.

A new plugin, sqlite-utils-fast-fks, is now available for developers who still want to use that faster but riskier implementation.

Other changes:

• The table.transform() method has two new parameters: foreign_keys= allows you to replace the foreign key constraints defined on a table, and add_foreign_keys= lets you specify new foreign keys to add. These complement the existing drop_foreign_keys= parameter. (#577)
• The sqlite-utils transform command has a new --add-foreign-key option which can be called multiple times to add foreign keys to a table that is being transformed. (#585)
• sqlite-utils convert now has a --pdb option for opening a debugger on the first encountered error in your conversion script. (#581)
• Fixed a bug where sqlite-utils install -e '.[test]' option did not work correctly.