sqlite-utils

• table.transform() now raises a TransactionError if called while a transaction is open with PRAGMAforeign_keys enabled and the table is referenced by foreign keys with destructive ON DELETE actions - CASCADE, SET NULL or SET DEFAULT. The pragma cannot be changed inside a transaction, so previously dropping the old table as part of the transform could fire those actions and silently delete or modify referencing rows. See Foreign keys and transactions for details and workarounds. (#794)
• The CLI and Python API documentation now cross-reference each other: CLI sections link to the equivalent Python API functionality and Python API sections link back to the corresponding CLI command. (#791)

• sqlite-utils insert and sqlite-utils upsert now accept a --code option for providing a block of Python code (or a path to a .py file) that defines a rows() function or rows iterable of rows to insert, as an alternative to importing from a file. (#684)
• sqlite-utils insert and sqlite-utils upsert now accept --type column-name type to override the type automatically chosen when the table is created. This is useful for CSV or TSV columns such as ZIP codes that look like integers but should be stored as TEXT to preserve leading zeros. (#131)
• New table.drop_index(name) method and sqlite-utils drop-index command for dropping an index by name. Both accept ignore=True/--ignore to ignore a missing index. (#626)
• sqlite-utils query can now read the SQL query from standard input by passing - in place of the query, for example echo "select * from dogs" | sqlite-utils query dogs.db -. (#765)
• sqlite-utils upsert can now infer the primary key of an existing table, so --pk can be omitted when upserting into a table that already has a primary key.
• table.transform() and table.transform_sql() now accept strict=True or strict=False to change a table’s SQLite strict mode. Omitting the option preserves the existing mode. (#787)
• The sqlite-utils transform command now accepts --strict and --no-strict to change a table’s strict mode. (#787)

The 4.0 release includes some minor backwards-incompatible fixes (hence the major version number bump) and introduces three major new features:

• Database migrations, providing a structured mechanism for evolving a project’s schema over time. (#752)
• Nested transaction support via db.atomic(), plus numerous improvements to how transactions work across the library. (#755)
• Support for compound foreign keys, including creation, transformation and introspection through table.foreign_keys. (#594)

Other notable changes include:

• Upserts now use SQLite’s INSERT ... ON CONFLICT ... DO UPDATE SET syntax, detect existing table primary keys automatically and reject records that are missing required primary key values. (#652)
• db.query() now executes immediately and rejects statements that do not return rows; use db.execute() for writes and DDL.
• CSV and TSV imports now detect column types by default, while inserts into existing tables preserve those tables’ column types. (#679)
• Foreign key handling now preserves ON DELETE/ON UPDATE actions during transforms and resolves referenced primary keys more accurately. (#530)
• Column names passed to Python API methods are now matched case-insensitively, mirroring SQLite’s own identifier behavior. (#760)
• The command-line tool now emits UTF-8 JSON output by default, with --ascii available to restore escaped output. (#625)
• table.extract() and extracts= no longer create lookup table records for all-null values. (#186)

See Upgrading from 3.x to 4.0 for details on backwards-incompatible changes.

The detailed release notes for the features and fixes shipped during the 4.0 pre-release cycle are available in 4.0a0, 4.0a1, 4.0rc1, 4.0rc2, 4.0rc3 and 4.0rc4.

• Breaking change: table.extract() - and the sqlite-utils extract command - no longer extract rows where every extracted column is null. Those rows now keep a null value in the new foreign key column instead of pointing at an all-null record in the lookup table. When extracting multiple columns, rows are still extracted if at least one of the columns has a value. (#186)
• The extracts= option to table.insert() and friends no longer creates a lookup table record for None values - the column value stays null. Previously every batch of inserted rows containing a None value would add a duplicate null record to the lookup table.
• Fixed a bug where table.lookup() inserted a duplicate row on every call if any of the lookup values were None. Lookup values are now compared using IS so that None values match existing rows correctly.
• JSON output from the command-line tool no longer escapes non-ASCII characters, so sqlite-utils data.db "select '日本語' as text" now outputs [{"text": "日本語"}]. This matches how values were already stored by insert and how CSV/TSV output already behaved. A new --ascii option restores the previous behavior of escaping non-ASCII characters, for output destinations that cannot handle UTF-8 - see Unicode characters in JSON. The option is available on the query, rows, search, tables, views, triggers, indexes and memory commands. The convert --multi --dry-run preview and plugins output also no longer escape non-ASCII characters. (#625)
• --no-headers now omits the header row from --fmt and --table output, not just CSV and TSV output. (#566)
• table.insert_all(..., pk=...) now raises InvalidColumns if pk= names columns that do not exist in an existing table. Previously this behaved inconsistently, with single-row inserts raising a KeyError while other row counts succeeded. (#732)
• Fixed an IndexError from table.insert(..., pk=..., ignore=True) when an ignored insert followed writes to another table on the same connection. last_pk is now populated from the explicit primary key value instead of looking up a stale lastrowid. (#554)
• Fixed a bug where a failed write statement executed with db.execute() left the driver’s implicit transaction open. Every subsequent write then joined that phantom transaction, which nothing committed, so their work was silently rolled back when the connection was closed. The implicit transaction opened by a failed statement is now rolled back before the exception is raised. A failed write inside a transaction opened with db.begin() or db.atomic() leaves that transaction open and untouched, as before.
• Fixed a bug where transaction-control statements prefixed with an empty statement - db.query("; COMMIT") - or a UTF-8 byte order mark slipped past the check that rejects them, committing the caller’s open transaction before raising a confusing OperationalError. The keyword scanner used by db.query() and db.execute() now skips leading ; and byte order marks, matching what the sqlite3 driver tolerates before the first token, so these statements are rejected with a ValueError without being executed. The same fix means db.execute("; BEGIN") no longer auto-commits the transaction it just opened.
• Documented a limitation of db.query(): a PRAGMA statement that returns no rows raises a ValueError but still takes effect, because PRAGMA statements run outside the savepoint guard used to roll back other rejected statements. Use db.execute() for row-less PRAGMA statements.
• Fixed exception masking when a statement destroys the enclosing transaction. An error such as a RAISE(ROLLBACK) trigger or INSERT OR ROLLBACK conflict rolls back the whole transaction, destroying every savepoint - the cleanup in db.atomic() and db.query() then failed with OperationalError: no such savepoint (or cannot rollback - no transaction is active), hiding the original IntegrityError from code that tried to catch it. Cleanup now checks whether a transaction is still open first, so the original exception propagates.
• sqlite-utils migrate --list is now read-only even when the migrations file uses the legacy sqlite_migrate.Migrations class, whose listing methods create the _sqlite_migrations table as a side effect. The listing now runs inside a transaction that is rolled back.
• sqlite-utils insert ... --pk <missing column> and sqlite-utils extract <missing column> now show a clean Error: message instead of a raw Python traceback. The extract command also shows a clean error when pointed at a view.
• Fixed a bug where running table.extract() more than once against the same lookup table inserted duplicate rows for values containing null - SQLite unique indexes treat NULL values as distinct, so INSERT OR IGNORE alone could not dedupe them. Each repeat extract added another copy that nothing referenced. The insert now uses an IS-based NOT EXISTS guard so null-containing rows match existing lookup rows.
• db.add_foreign_keys() no longer silently ignores requested ON DELETE/ON UPDATE actions when a foreign key with the same columns already exists - it raises AlterError suggesting table.transform(), since the actions of an existing foreign key cannot be changed in place. Exact duplicates, including actions, are still skipped so repeated calls stay idempotent. The method also now validates that compound foreign keys have the same number of columns on both sides, instead of silently discarding the extra columns.
• db.ensure_autocommit_on() now raises TransactionError if called while a transaction is open. Assigning isolation_level commits any pending transaction as a side effect, so entering the block silently committed the caller’s open transaction and made a later rollback() a no-op.
• sqlite-utils migrate --stop-before now exits with an error if the named migration has already been applied. Previously the name passed validation but was only checked against pending migrations, so every migration after it was silently applied - the exact outcome --stop-before exists to prevent. Migrations.apply(db, stop_before=...) raises ValueError in the same situation, before applying anything.
• Fixed a regression where table.insert(..., pk=..., alter=True) raised InvalidColumns if the primary key column did not exist in the table yet. With alter=True the check now waits until the record keys are known, so a pk column supplied by the records is added by the alter as it was in 3.x. A pk column found in neither the table nor the records still raises InvalidColumns.
• Fixed a bug where inserting CSV or TSV data into an existing table rewrote that table’s column types to match the incoming file. Type detection is the default in 4.0, so sqlite-utils insert data.db places places.csv --csv against a table with a TEXT zip code column would convert the column to INTEGER and corrupt values with leading zeros - "01234" became 1234. Detected types are now only applied when the insert or upsert command creates the table.
• Fixed pks_and_rows_where() raising AttributeError when called on a view, and no longer double-quotes the synthesized rowid column in its generated SQL - SQLite turns a double-quoted identifier that does not resolve into a string literal, which on a view produced a confusing KeyError instead of the OperationalError raised in 3.x. Compound primary keys returned by this method now follow PRIMARY KEY declaration order.
• The foreign_keys= argument to create() and insert() accepts a mixed list of ForeignKey objects, tuples and column name strings again. In 4.0 pre-releases mixing ForeignKey objects with tuples raised a ValueError - a regression from 3.x, where ForeignKey was a namedtuple and passed the tuple checks.
• ForeignKey objects are hashable again. The 4.0 change from namedtuple to dataclass accidentally made them unhashable, breaking patterns like set(table.foreign_keys) that worked in 3.x. ForeignKey is now a frozen dataclass - immutable and hashable, like the namedtuple was.
• Fixed a bug where compound primary key columns were returned in table column order instead of PRIMARY KEY declaration order. For a table declared as CREATE TABLE other (b TEXT, a TEXT, PRIMARY KEY (a, b)) an implicit FOREIGN KEY (x, y) REFERENCES other was introspected as referencing (b, a) when SQLite resolves it as (a, b) - running transform() on such a table then rewrote the schema with the inverted column order, silently reversing the meaning of the constraint and causing foreign key errors on valid data. table.pks, compound foreign key guessing and transform() now all use the primary key declaration order, and transform() no longer reorders a compound PRIMARY KEY (b, a) into table column order.

Breaking changes:

• table.foreign_keys now returns ForeignKey objects that are dataclasses rather than namedtupleinstances, so they can no longer be unpacked or indexed as (table, column, other_table,other_column) tuples - access their fields by name instead. Compound (multi-column) foreign keys are now represented as a single ForeignKey with is_compound=True and populated columns/other_columns tuples, where column and other_column are None. Previously they were returned as one ForeignKey per column, misleadingly suggesting several independent foreign keys. See Upgrading from 3.x to 4.0 for details. (#594)
• Removed support for using sqlean.py as a drop-in replacement for the Python standard library sqlite3 module. sqlite-utils will now use pysqlite3 if it is installed, otherwise it will use sqlite3 from the standard library.
• The db.ensure_autocommit_off() context manager has been renamed to db.ensure_autocommit_on(), because the old name described the opposite of what it did. The method 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. (#705)

Compound foreign key support:

• Tables can now be created with compound foreign keys, by passing tuples of column names in foreign_keys=: foreign_keys=[(("campus_name", "dept_code"), "departments")]. The referenced columns default to the compound primary key of the other table. Compound keys are rendered as table-level FOREIGN KEY constraints in the generated schema. See Compound foreign keys.
• table.transform() now preserves compound foreign keys, applying any column renames to them. Dropping a column that is part of a compound foreign key drops the whole constraint, matching the existing single-column behavior. drop_foreign_keys= accepts a bare column name - dropping any foreign key that column participates in - or a tuple of columns to target a compound key precisely.
• table.add_foreign_key() and db.add_foreign_keys() accept tuples of column names to add a compound foreign key to an existing table.
• db.index_foreign_keys() creates a single composite index for a compound foreign key.

Other foreign key improvements:

• ForeignKey now exposes on_delete and on_update fields reflecting the foreign key’s ONDELETE/ON UPDATE actions, and table.transform() preserves those actions. Previously a transform silently stripped clauses such as ON DELETE CASCADE from the table schema.
• table.add_foreign_key() accepts new on_delete= and on_update= parameters for creating foreign keys with actions, e.g. table.add_foreign_key("author_id", "authors", "id",on_delete="CASCADE"). (#530)
• Foreign keys declared as REFERENCES other_table with no explicit column are now resolved to the other table’s primary key by table.foreign_keys, instead of reporting other_column=None.
• Fixed a TypeError when sorting ForeignKey objects where some were compound.

Case-insensitive column matching:

Column names passed to Python API methods are now matched against the table schema case-insensitively, mirroring how SQLite itself treats identifiers. Previously many methods accepted mixed-case identifiers in the SQL they generated but then failed - or silently did nothing - when performing Python-side comparisons against the schema. (#760) Fixes include:

• table.insert() and table.upsert() now populate table.last_pk correctly when the pk=argument uses different casing to the table schema or the record keys - previously this raised a KeyError after the row had already been written.
• Upserts no longer raise or misbehave when the casing of pk= differs from the casing of the record keys. The primary key columns are correctly excluded from the generated DO UPDATE SET clause.
• table.transform() arguments types=, rename=, drop=, pk=, not_null=, defaults=, column_order= and drop_foreign_keys= all resolve column names case-insensitively. Previously options like rename={"name": "title"} against a column called Name were silently ignored.
• db.create_table(..., transform=True) now recognizes existing columns that differ only by case, instead of attempting to add them again and failing with duplicate column name. The casing used in the existing schema is preserved.
• table.lookup() returns the primary key value even if pk= casing differs from the schema, and recognizes existing unique indexes case-insensitively instead of creating redundant ones.
• table.extract() and table.convert() - including multi=True and output= - accept column names in any casing.
• Foreign key columns are validated and recorded using the casing of the actual schema columns, in foreign_keys= when creating tables, db.add_foreign_keys(), table.add_foreign_key() and table.add_column(fk_col=...). Duplicate foreign key detection is also case-insensitive.
• table.create() with pk=, not_null=, defaults= or column_order= referencing columns using different casing no longer creates an unwanted extra primary key column or raises a ValueError.

Everything else:

• Fixed a bug where table.transform() could convert DEFAULT TRUE, DEFAULT FALSE and DEFAULTNULL column defaults into quoted string defaults when rebuilding a table. Thanks, Vincent Gao. (#764)

Breaking changes:

• Write statements executed with db.execute() are now committed automatically, unless a transaction is already open in which case they join it. Previously they opened an implicit transaction that stayed open until something committed it - writes appeared to work when read on the same connection but were silently rolled back when the connection closed. Code that relied on rolling back uncommitted db.execute() writes should use the new db.begin() method to open an explicit transaction first. The transaction model is documented in full at Transactions and saving your changes.
• db.query() now executes its SQL as soon as it is called, rather than waiting until the returned generator is first iterated. Rows are still fetched lazily during iteration. SQL errors are now raised at the call site, statements such as INSERT ... RETURNING are executed and committed immediately without needing to iterate over their results, and passing a statement that returns no rows - previously a silent no-op - now raises a ValueError recommending db.execute() instead. A statement rejected this way is rolled back before the error is raised, so it has no effect on the database.
• Python API validation errors now raise ValueError instead of AssertionError. Previously invalid arguments - such as create_table() with no columns, transform() on a table that does not exist, or passing both ignore=True and replace=True - were rejected using bare assert statements, which are silently skipped when Python runs with the -O flag. Code that caught AssertionError for these cases should catch ValueError instead.
• table.upsert() and table.upsert_all() now raise PrimaryKeyRequired if a record is missing a value for any primary key column, or has a value of None for one. Previously such records - which can never match an existing row - were quietly inserted as brand new rows, or triggered a confusing KeyError after the insert had already taken place.
• db.enable_wal() and db.disable_wal() now raise a sqlite_utils.db.TransactionError if called while a transaction is open. Previously they would silently commit the open transaction as a side effect of changing the journal mode, breaking the rollback guarantee of db.atomic() and of user-managed transactions.
• The View class no longer has an enable_fts() method. It existed only to raise NotImplementedError, since full-text search is not supported for views - calling it now raises AttributeError instead, and the method no longer appears in the API reference. The sqlite-utilsenable-fts command shows a clean error when pointed at a view.
• The no-op -d/--detect-types flag has been removed from the insert and upsert commands. Type detection has been the default for CSV/TSV data since 4.0a1, so the flag did nothing - invocations using it should simply drop it. --no-detect-types remains available to disable detection.
• Database() now raises a sqlite_utils.db.TransactionError if passed a connection created with the Python 3.12+ sqlite3.connect(..., autocommit=True) or autocommit=False options. commit()and rollback() behave differently on those connections, which previously caused every write made by the library to be silently discarded when the connection closed.

Everything else:

• Fixed a bug where table.delete_where(), table.optimize() and table.rebuild_fts() did not commit their changes, leaving the connection inside an open transaction. Their work - and any subsequent writes - could then be silently rolled back when the connection was closed. All three now use db.atomic(), consistent with the other write methods.
• The sqlite-utils drop-table command 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. Both now exit with an error suggesting the correct command to use.
• Migrations applied by the new migrations system now run inside a transaction, together with the record of the migration having been applied. If a migration raises an exception its changes are rolled back and it stays pending, so it can be safely re-applied after the error is fixed. Migrations that cannot run inside a transaction, such as those executing VACUUM, can opt out using @migrations(transactional=False) - see Migrations and transactions.
• table.upsert() and table.upsert_all() now detect the primary key or compound primary key of an existing table, so the pk= argument is no longer required when upserting into a table that already has a primary key.
• db.table(table_name).insert({}) can now be used to insert a row consisting entirely of default values into an existing table, using INSERT INTO ... DEFAULT VALUES. (#759)
• Improvements to the sqlite-utils migrate command: --stop-before values that do not match any known migration are now an error instead of being silently ignored, --stop-before now works correctly with migration files that still use the older sqlite_migrate.Migrations class, and --list is now a read-only operation that no longer creates the database file or the migrations tracking table. migrations.applied() now returns migrations in the order they were applied.
• New db.begin(), db.commit() and db.rollback() methods for taking manual control of transactions, as an alternative to the db.atomic() context manager.
• New documentation: Transactions and saving your changes describes how transactions work and when changes are committed, and a new Upgrading page details the changes needed to move between major versions.

• New database migrations system, incorporating functionality that was previously provided by the separate sqlite-migrate plugin. Define migration sets using the new sqlite_utils.Migrationsclass and apply them using the sqlite-utils migrate command or the migrations Python API. (#752)
• New db.atomic() context manager providing nested transaction support using SQLite transactions and savepoints. Internal multi-step operations such as table.transform() now use this mechanism to avoid unexpectedly committing an existing transaction. (#755)
• Database objects can now be used as context managers, automatically closing the connection when the with block exits. The CLI also now closes database and file handles more reliably, resolving a number of ResourceWarning warnings. (#692)
• The sqlite-utils convert command can now accept a direct callable reference such as r.parsedate or json.loads --import json as the conversion code, as an alternative to calling it explicitly with r.parsedate(value). (#686)
• Fixed a bug where CSV or TSV files with only a header row could crash sqlite-utils insert and sqlite-utils memory when type detection was enabled. Thanks, Rami Abdelrazzaq. (#702, #707)
• Fixed a bug where installed plugins could be loaded while running the test suite, despite the test-mode safeguard that disables plugin loading. Thanks, Rami Abdelrazzaq. (#713, #719)
• table.detect_fts() now recognizes legacy FTS virtual tables that quote the content= table name using square brackets, allowing table.enable_fts(..., replace=True) to replace them correctly. (#694)
• Now depends on Click 8.3.1 or later, removing compatibility workarounds for Click's Sentineldefault values. (#666)
• Improved type annotations throughout the package, with ty now run in CI. (#697)
• Development tooling now uses uv dependency groups, with separate dev and docs groups. (#691)
• The test suite now runs against Python 3.15-dev. (#738)

• 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)

• 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)

• Fixed a bug where table.delete_where() left the connection in an open transaction, causing deleted rows to be silently restored when the connection was closed. #815

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

• 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)

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

• 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)

• 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)

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

• 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)

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.

This release introduces a new plugin system. (#567)

• Documentation describing how to build a plugin.
• Plugin hook: register_commands(cli), for plugins to add extra commands to sqlite-utils. (#569)
• Plugin hook: prepare_connection(conn). Plugins can use this to help prepare the SQLite connection to do things like registering custom SQL functions. Thanks, Alex Garcia. (#574)
• sqlite_utils.Database(..., execute_plugins=False) option for disabling plugin execution. (#575)
• sqlite-utils install -e path-to-directory option for installing editable code. This option is useful during the development of a plugin. (#570)
• table.create(...) method now accepts replace=True to drop and replace an existing table with the same name, or ignore=True to silently do nothing if a table already exists with the same name. (#568)
• sqlite-utils insert ... --stop-after 10 option for stopping the insert after a specified number of records. Works for the upsert command as well. (#561)
• The --csv and --tsv modes for insert now accept a --empty-null option, which cases empty strings in the CSV file to be stored as null in the database. (#563)
• New db.rename_table(table_name, new_name) method for renaming tables. (#565)
• sqlite-utils rename-table my.db table_name new_name command for renaming tables. (#565)
• The table.transform(...) method now takes an optional keep_table=new_table_name parameter, which will cause the original table to be renamed to new_table_name rather than being dropped at the end of the transformation. (#571)
• Documentation now notes that calling table.transform() without any arguments will reformat the SQL schema stored by SQLite to be more aesthetically pleasing. (#564)

• sqlite-utils will now use sqlean.py in place of sqlite3 if it is installed in the same virtual environment. This is useful for Python environments with either an outdated version of SQLite or with restrictions on SQLite such as disabled extension loading or restrictions resulting in the sqlite3.OperationalError: table sqlite_master may not be modified error. (#559)
• New with db.ensure_autocommit_off() context manager, which ensures that the database is in autocommit mode for the duration of a block of code. This is used by db.enable_wal() and db.disable_wal() to ensure they work correctly with pysqlite3 and sqlean.py.
• New db.iterdump() method, providing an iterator over SQL strings representing a dump of the database. This uses sqlite-dump if it is available, otherwise falling back on the conn.iterdump() method from sqlite3. Both pysqlite3 and sqlean.py omit support for iterdump() - this method helps paper over that difference.

• Examples in the CLI documentation can now all be copied and pasted without needing to remove a leading $. (#551)
• Documentation now covers Setting up shell completion for bash and zsh. (#552)

• New experimental sqlite-utils tui interface for interactively building command-line invocations, powered by Trogon. This requires an optional dependency, installed using sqlite-utils install trogon. There is a screenshot in the documentation. (#545)
• sqlite-utils analyze-tables command (documentation) now has a --common-limit 20 option for changing the number of common/least-common values shown for each column. (#544)
• sqlite-utils analyze-tables --no-most and --no-least options for disabling calculation of most-common and least-common values.
• If a column contains only null values, analyze-tables will no longer attempt to calculate the most common and least common values for that column. (#547)
• Calling sqlite-utils analyze-tables with non-existent columns in the -c/--column option now results in an error message. (#548)
• The table.analyze_column() method (documented here) now accepts most_common=False and least_common=False options for disabling calculation of those values.

• Dropped support for Python 3.6. Tests now ensure compatibility with Python 3.11. (#517)
• Automatically locates the SpatiaLite extension on Apple Silicon. Thanks, Chris Amico. (#536)
• New --raw-lines option for the sqlite-utils query and sqlite-utils memory commands, which outputs just the raw value of the first column of evy row. (#539)
• Fixed a bug where table.upsert_all() failed if the not_null= option was passed. (#538)
• Fixed a ResourceWarning when using sqlite-utils insert. (#534)
• Now shows a more detailed error message when sqlite-utils insert is called with invalid JSON. (#532)
• table.convert(..., skip_false=False) and sqlite-utils convert --no-skip-false options, for avoiding a misfeature where the convert() mechanism skips rows in the database with a falsey value for the specified column. Fixing this by default would be a backwards-incompatible change and is under consideration for a 4.0 release in the future. (#527)
• Tables can now be created with self-referential foreign keys. Thanks, Scott Perry. (#537)
• sqlite-utils transform no longer breaks if a table defines default values for columns. Thanks, Kenny Song. (#509)
• Fixed a bug where repeated calls to table.transform() did not work correctly. Thanks, Martin Carpenter. (#525)
• Improved error message if rows_from_file() is passed a non-binary-mode file-like object. (#520)

• Now tested against Python 3.11. (#502)
• New table.search_sql(include_rank=True) option, which adds a rank column to the generated SQL. Thanks, Jacob Chapman. (#480)
• Progress bars now display for newline-delimited JSON files using the --nl option. Thanks, Mischa Untaga. (#485)
• New db.close() method. (#504)
• Conversion functions passed to table.convert(...) can now return lists or dictionaries, which will be inserted into the database as JSON strings. (#495)
• sqlite-utils install and sqlite-utils uninstall commands for installing packages into the same virtual environment as sqlite-utils, described here. (#483)
• New sqlite_utils.utils.flatten() utility function. (#500)
• Documentation on using Just to run tests, linters and build documentation.
• Documentation now covers the Release process for this package.

• The sqlite-utils query, memory and bulk commands now all accept a new --functions option. This can be passed a string of Python code, and any callable objects defined in that code will be made available to SQL queries as custom SQL functions. See Defining custom SQL functions for details. (#471)
• db[table].create(...) method now accepts a new transform=True parameter. If the table already exists it will be transform to match the schema configuration options passed to the function. This may result in columns being added or dropped, column types being changed, column order being updated or not null and default values for columns being set. (#467)
• Related to the above, the sqlite-utils create-table command now accepts a --transform option.
• New introspection property: table.default_values returns a dictionary mapping each column name with a default value to the configured default value. (#475)
• The --load-extension option can now be provided a path to a compiled SQLite extension module accompanied by the name of an entrypoint, separated by a colon - for example --load-extension ./lines0:sqlite3_lines0_noread_init. This feature is modelled on code first contributed to Datasette by Alex Garcia. (#470)
• Functions registered using the db.register_function() method can now have a custom name specified using the new db.register_function(fn, name=...) parameter. (#458)
• sqlite-utils rows has a new --order option for specifying the sort order for the returned rows. (#469)
• All of the CLI options that accept Python code blocks can now all be used to define functions that can access modules imported in that same block of code without needing to use the global keyword. (#472)
• Fixed bug where table.extract() would not behave correctly for columns containing null values. Thanks, Forest Gregg. (#423)
• New tutorial: Cleaning data with sqlite-utils and Datasette shows how to use sqlite-utils to import and clean an example CSV file.
• Datasette and sqlite-utils now have a Discord community. Join the Discord here.

• New table.duplicate(new_name) method for creating a copy of a table with a matching schema and row contents. Thanks, David. (#449)
• New sqlite-utils duplicate data.db table_name new_name CLI command for Duplicating tables. (#454)
• sqlite_utils.utils.rows_from_file() is now a documented API. It can be used to read a sequence of dictionaries from a file-like object containing CSV, TSV, JSON or newline-delimited JSON. It can be passed an explicit format or can attempt to detect the format automatically. (#443)
• sqlite_utils.utils.TypeTracker is now a documented API for detecting the likely column types for a sequence of string rows, see Detecting column types using TypeTracker. (#445)
• sqlite_utils.utils.chunks() is now a documented API for splitting an iterator into chunks. (#451)
• sqlite-utils enable-fts now has a --replace option for replacing the existing FTS configuration for a table. (#450)
• The create-index, add-column and duplicate commands all now take a --ignore option for ignoring errors should the database not be in the right state for them to operate. (#450)

See also the annotated release notes for this release.

• Documentation now uses the Furo Sphinx theme. (#435)
• Code examples in documentation now have a "copy to clipboard" button. (#436)
• sqlite_utils.utils.utils.rows_from_file() is now a documented API, see Reading rows from a file. (#443)
• rows_from_file() has two new parameters to help handle CSV files with rows that contain more values than are listed in that CSV file's headings: ignore_extras=True and extras_key="name-of-key". (#440)
• sqlite_utils.utils.maximize_csv_field_size_limit() helper function for increasing the field size limit for reading CSV files to its maximum, see Setting the maximum CSV field size limit. (#442)
• table.search(where=, where_args=) parameters for adding additional WHERE clauses to a search query. The where= parameter is available on table.search_sql(...) as well. See Searching with table.search(). (#441)
• Fixed bug where table.detect_fts() and other search-related functions could fail if two FTS-enabled tables had names that were prefixes of each other. (#434)

• Now depends on click-default-group-wheel, a pure Python wheel package. This means you can install and use this package with Pyodide, which can run Python entirely in your browser using WebAssembly. (#429)

  Try that out using the Pyodide REPL:

  >>> import micropip
  >>> await micropip.install("sqlite-utils")
  >>> import sqlite_utils
  >>> db = sqlite_utils.Database(memory=True)
  >>> list(db.query("select 3 * 5"))
  [{'3 * 5': 15}]
  

• New errors=r.IGNORE/r.SET_NULL parameter for the r.parsedatetime() and r.parsedate() convert recipes. (#416)
• Fixed a bug where --multi could not be used in combination with --dry-run for the convert command. (#415)
• New documentation: Using a convert() function to execute initialization. (#420)
• More robust detection for whether or not deterministic=True is supported. (#425)

• Improved display of type information and parameters in the API reference documentation. #413

• New hash_id_columns= parameter for creating a primary key that's a hash of the content of specific columns - see Setting an ID based on the hash of the row contents for details. (#343)
• New db.sqlite_version property, returning a tuple of integers representing the version of SQLite, for example (3, 38, 0).
• Fixed a bug where register_function(deterministic=True) caused errors on versions of SQLite prior to 3.8.3. (#408)
• New documented hash_record(record, keys=...) function.

• SpatiaLite helpers for the sqlite-utils command-line tool - thanks, Chris Amico. (#398)
  • sqlite-utils create-database --init-spatialite option for initializing SpatiaLite on a newly created database.
  • sqlite-utils add-geometry-column command for adding geometry columns.
  • sqlite-utils create-spatial-index command for adding spatial indexes.
• db[table].create(..., if_not_exists=True) option for creating a table only if it does not already exist. (#397)
• Database(memory_name="my_shared_database") parameter for creating a named in-memory database that can be shared between multiple connections. (#405)
• Documentation now describes how to add a primary key to a rowid table using sqlite-utils transform. (#403)

This release introduces four new utility methods for working with SpatiaLite. Thanks, Chris Amico. (#330)

• sqlite_utils.utils.find_spatialite() finds the location of the SpatiaLite module on disk.
• db.init_spatialite() initializes SpatiaLite for the given database.
• table.add_geometry_column(...) adds a geometry column to an existing table.
• table.create_spatial_index(...) creates a spatial index for a column.
• sqlite-utils batch now accepts a --batch-size option. (#392)

• All commands now include example usage in their --help - see CLI reference. (#384)
• Python library documentation has a new Getting started section. (#387)
• Documentation now uses Plausible analytics. (#389)

• New CLI reference documentation page, listing the output of --help for every one of the CLI commands. (#383)
• sqlite-utils rows now has --limit and --offset options for paginating through data. (#381)
• sqlite-utils rows now has --where and -p options for filtering the table using a WHERE query, see Returning all rows in a table. (#382)

CLI and Python library improvements to help run ANALYZE after creating indexes or inserting rows, to gain better performance from the SQLite query planner when it runs against indexes.

Three new CLI commands: create-database, analyze and bulk.

More details and examples can be found in the annotated release notes.

• New sqlite-utils create-database command for creating new empty database files. (#348)
• New Python methods for running ANALYZE against a database, table or index: db.analyze() and table.analyze(), see Optimizing index usage with ANALYZE. (#366)
• New sqlite-utils analyze command for running ANALYZE using the CLI. (#379)
• The create-index, insert and upsert commands now have a new --analyze option for running ANALYZE after the command has completed. (#379)
• New sqlite-utils bulk command which can import records in the same way as sqlite-utils insert (from JSON, CSV or TSV) and use them to bulk execute a parametrized SQL query. (#375)
• The CLI tool can now also be run using python -m sqlite_utils. (#368)
• Using --fmt now implies --table, so you don't need to pass both options. (#374)
• The --convert function applied to rows can now modify the row in place. (#371)
• The insert-files command supports two new columns: stem and suffix. (#372)
• The --nl import option now ignores blank lines in the input. (#376)
• Fixed bug where streaming input to the insert command with --batch-size 1 would appear to only commit after several rows had been ingested, due to unnecessary input buffering. (#364)

• sqlite-utils insert ... --lines to insert the lines from a file into a table with a single line column, see Inserting unstructured data with --lines and --text.
• sqlite-utils insert ... --text to insert the contents of the file into a table with a single text column and a single row.
• sqlite-utils insert ... --convert allows a Python function to be provided that will be used to convert each row that is being inserted into the database. See Applying conversions while inserting data, including details on special behavior when combined with --lines and --text. (#356)
• sqlite-utils convert now accepts a code value of - to read code from standard input. (#353)
• sqlite-utils convert also now accepts code that defines a named convert(value) function, see Converting data in columns.
• db.supports_strict property showing if the database connection supports SQLite strict tables.
• table.strict property (see .strict) indicating if the table uses strict mode. (#344)
• Fixed bug where sqlite-utils upsert ... --detect-types ignored the --detect-types option. (#362)

• The table.lookup() method now accepts keyword arguments that match those on the underlying table.insert() method: foreign_keys=, column_order=, not_null=, defaults=, extracts=, conversions= and columns=. You can also now pass pk= to specify a different column name to use for the primary key. (#342)

• Extra keyword arguments for table.lookup() which are passed through to .insert(). #342

• The table.lookup() method now has an optional second argument which can be used to populate columns only the first time the record is created, see Working with lookup tables. (#339)
• sqlite-utils memory now has a --flatten option for flattening nested JSON objects into separate columns, consistent with sqlite-utils insert. (#332)
• table.create_index(..., find_unique_name=True) parameter, which finds an available name for the created index even if the default name has already been taken. This means that index-foreign-keys will work even if one of the indexes it tries to create clashes with an existing index name. (#335)
• Added py.typed to the module, so mypy should now correctly pick up the type annotations. Thanks, Andreas Longo. (#331)
• Now depends on python-dateutil instead of depending on dateutils. Thanks, Denys Pavlov. (#324)
• table.create() (see Explicitly creating a table) now handles dict, list and tuple types, mapping them to TEXT columns in SQLite so that they can be stored encoded as JSON. (#338)
• Inserted data with square braces in the column names (for example a CSV file containing a item[price]) column now have the braces converted to underscores: item_price_. Previously such columns would be rejected with an error. (#329)
• Now also tested against Python 3.10. (#330)

• sqlite-utils memory now works if files passed to it share the same file name. (#325)
• sqlite-utils query now returns [] in JSON mode if no rows are returned. (#328)

• The sqlite-utils memory command has a new --analyze option, which runs the equivalent of the analyze-tables command directly against the in-memory database created from the incoming CSV or JSON data. (#320)
• sqlite-utils insert-files now has the ability to insert file contents in to TEXT columns in addition to the default BLOB. Pass the --text option or use content_text as a column specifier. (#319)

• Type signatures added to more methods, including table.resolve_foreign_keys(), db.create_table_sql(), db.create_table() and table.create(). (#314)
• New db.quote_fts(value) method, see Quoting characters for use in search - thanks, Mark Neumann. (#246)
• table.search() now accepts an optional quote=True parameter. (#296)
• CLI command sqlite-utils search now accepts a --quote option. (#296)
• Fixed bug where --no-headers and --tsv options to sqlite-utils insert could not be used together. (#295)
• Various small improvements to API reference documentation.

• Python library now includes type annotations on almost all of the methods, plus detailed docstrings describing each one. (#311)
• New API Reference documentation page, powered by those docstrings.
• Fixed bug where .add_foreign_keys() failed to raise an error if called against a View. (#313)
• Fixed bug where .delete_where() returned a [] instead of returning self if called against a non-existant table. (#315)

• sqlite-utils insert --flatten option for flattening nested JSON objects to create tables with column names like topkey_nestedkey. (#310)
• Fixed several spelling mistakes in the documentation, spotted using codespell.
• Errors that occur while using the sqlite-utils CLI tool now show the responsible SQL and query parameters, if possible. (#309)

This release introduces the new sqlite-utils convert command (#251) and corresponding table.convert(...) Python method (#302). These tools can be used to apply a Python conversion function to one or more columns of a table, either updating the column in place or using transformed data from that column to populate one or more other columns.

This command-line example uses the Python standard library textwrap module to wrap the content of the content column in the articles table to 100 characters:

$ sqlite-utils convert content.db articles content\
    '"\n".join(textwrap.wrap(value, 100))'\
    --import=textwrap

The same operation in Python code looks like this:

import sqlite_utils, textwrap

db = sqlite_utils.Database("content.db")
db["articles"].convert("content", lambda v: "\n".join(textwrap.wrap(v, 100)))

See the full documentation for the sqlite-utils convert command and the table.convert(...) Python method for more details.

Also in this release:

• The new table.count_where(...) method, for counting rows in a table that match a specific SQL WHERE clause. (#305)
• New --silent option for the sqlite-utils insert-files command to hide the terminal progress bar, consistent with the --silent option for sqlite-utils convert. (#301)

• sqlite-utils schema my.db table1 table2 command now accepts optional table names. (#299)
• sqlite-utils memory --help now describes the --schema option.

• New db.query(sql, params) method, which executes a SQL query and returns the results as an iterator over Python dictionaries. (#290)
• This project now uses flake8 and has started to use mypy. (#291)
• New documentation on contributing to this project. (#292)

• New sqlite-utils memory data.csv --schema option, for outputting the schema of the in-memory database generated from one or more files. See --schema, --dump and --save. (#288)
• Added installation instructions. (#286)

This release introduces the sqlite-utils memory command, which can be used to load CSV or JSON data into a temporary in-memory database and run SQL queries (including joins across multiple files) directly against that data.

Also new: sqlite-utils insert --detect-types, sqlite-utils dump, table.use_rowid plus some smaller fixes.

sqlite-utils memory

This example of sqlite-utils memory retrieves information about the all of the repositories in the Dogsheep organization on GitHub using this JSON API, sorts them by their number of stars and outputs a table of the top five (using -t):

$ curl -s 'https://api.github.com/users/dogsheep/repos'\
  | sqlite-utils memory - '
      select full_name, forks_count, stargazers_count
      from stdin order by stargazers_count desc limit 5
    ' -t
full_name                            forks_count    stargazers_count
---------------------------------  -------------  ------------------
dogsheep/twitter-to-sqlite                    12                 225
dogsheep/github-to-sqlite                     14                 139
dogsheep/dogsheep-photos                       5                 116
dogsheep/dogsheep.github.io                    7                  90
dogsheep/healthkit-to-sqlite                   4                  85

The tool works against files on disk as well. This example joins data from two CSV files:

$ cat creatures.csv
species_id,name
1,Cleo
2,Bants
2,Dori
2,Azi
$ cat species.csv
id,species_name
1,Dog
2,Chicken
$ sqlite-utils memory species.csv creatures.csv '
  select * from creatures join species on creatures.species_id = species.id
'
[{"species_id": 1, "name": "Cleo", "id": 1, "species_name": "Dog"},
 {"species_id": 2, "name": "Bants", "id": 2, "species_name": "Chicken"},
 {"species_id": 2, "name": "Dori", "id": 2, "species_name": "Chicken"},
 {"species_id": 2, "name": "Azi", "id": 2, "species_name": "Chicken"}]

Here the species.csv file becomes the species table, the creatures.csv file becomes the creatures table and the output is JSON, the default output format.

You can also use the --attach option to attach existing SQLite database files to the in-memory database, in order to join data from CSV or JSON directly against your existing tables.

Full documentation of this new feature is available in Querying data directly using an in-memory database. (#272)

sqlite-utils insert --detect-types

The sqlite-utils insert command can be used to insert data from JSON, CSV or TSV files into a SQLite database file. The new --detect-types option (shortcut -d), when used in conjunction with a CSV or TSV import, will automatically detect if columns in the file are integers or floating point numbers as opposed to treating everything as a text column and create the new table with the corresponding schema. See Inserting CSV or TSV data for details. (#282)

Other changes

• Bug fix: table.transform(), when run against a table without explicit primary keys, would incorrectly create a new version of the table with an explicit primary key column called rowid. (#284)
• New table.use_rowid introspection property, see .use_rowid. (#285)
• The new sqlite-utils dump file.db command outputs a SQL dump that can be used to recreate a database. (#274)
• -h now works as a shortcut for --help, thanks Loren McIntyre. (#276)
• Now using pytest-cov and Codecov to track test coverage - currently at 96%. (#275)
• SQL errors that occur when using sqlite-utils query are now displayed as CLI errors.

• Fixed bug when using table.upsert_all() to create a table with only a single column that is treated as the primary key. (#271)

• New sqlite-utils schema command showing the full SQL schema for a database, see Showing the schema (CLI). (#268)
• db.schema introspection property exposing the same feature to the Python library, see Showing the schema (Python library).

• New sqlite-utils indexes command to list indexes in a database, see Listing indexes. (#263)
• table.xindexes introspection property returning more details about that table's indexes, see .xindexes. (#261)

• New table.pks_and_rows_where() method returning (primary_key, row_dictionary) tuples - see Listing rows with their primary keys. (#240)
• Fixed bug with table.add_foreign_key() against columns containing spaces. (#238)
• table_or_view.drop(ignore=True) option for avoiding errors if the table or view does not exist. (#237)
• sqlite-utils drop-view --ignore and sqlite-utils drop-table --ignore options. (#237)
• Fixed a bug with inserts of nested JSON containing non-ascii strings - thanks, Dylan Wu. (#257)
• Suggest --alter if an error occurs caused by a missing column. (#259)
• Support creating indexes with columns in descending order, see API documentation and CLI documentation. (#260)
• Correctly handle CSV files that start with a UTF-8 BOM. (#250)

This release adds the ability to execute queries joining data from more than one database file - similar to the cross database querying feature introduced in Datasette 0.55.

• The db.attach(alias, filepath) Python method can be used to attach extra databases to the same connection, see db.attach() in the Python API documentation. (#113)
• The --attach option attaches extra aliased databases to run SQL queries against directly on the command-line, see attaching additional databases in the CLI documentation. (#236)

• sqlite-utils insert --sniff option for detecting the delimiter and quote character used by a CSV file, see Alternative delimiters and quote characters. (#230)
• The table.rows_where(), table.search() and table.search_sql() methods all now take optional offset= and limit= arguments. (#231)
• New --no-headers option for sqlite-utils insert --csv to handle CSV files that are missing the header row, see CSV files without a header row. (#228)
• Fixed bug where inserting data with extra columns in subsequent chunks would throw an error. Thanks @nieuwenhoven for the fix. (#234)
• Fixed bug importing CSV files with columns containing more than 128KB of data. (#229)
• Test suite now runs in CI against Ubuntu, macOS and Windows. Thanks @nieuwenhoven for the Windows test fixes. (#232)

• Fixed a code import bug that slipped in to 3.4. (#226)

• sqlite-utils insert --csv now accepts optional --delimiter and --quotechar options. See Alternative delimiters and quote characters. (#223)