Upgrading to 4.0

sqlite-utils 4.0 includes several breaking changes. This page describes what has changed and how to update your code.

Python library changes

db[“name”] only returns tables

In previous versions, db["table_or_view_name"] would return either a Table or View object depending on what existed in the database.

In 4.0, this syntax only returns Table objects. Attempting to use it with a view name will raise a sqlite_utils.db.NoTable exception.

Before (3.x):

# This could return either a Table or View
obj = db["my_view"]
obj.drop()

After (4.0):

# Use db.view() explicitly for views
view = db.view("my_view")
view.drop()

# db["name"] now only works with tables
table = db["my_table"]

This change improves type safety since views lack methods like .insert() that are available on tables.

db.table() raises NoTable for views

The db.table(name) method now raises sqlite_utils.db.NoTable if the name refers to a view. Use db.view(name) instead.

Default floating point type is REAL

When inserting data with auto-detected column types, floating point values now create columns with type REAL instead of FLOAT. REAL is the correct SQLite affinity for floating point values.

This affects the schema of newly created tables but does not change how data is stored or queried.

convert() no longer skips False values

The table.convert() method previously skipped rows where the column value evaluated to False (including 0, empty strings, and None). This behavior has been removed.

Before (3.x):

# Rows with falsey values were skipped by default
# --skip-false was needed to process all rows
table.convert("column", lambda x: x.upper(), skip_false=False)

After (4.0):

# All rows are now processed, including those with falsey values
table.convert("column", lambda x: x.upper() if x else x)

Table schemas use double quotes

Tables created by sqlite-utils now use "double-quotes" for table and column names in the schema instead of [square-braces]. Both are valid SQL, but double quotes are the SQL standard.

This only affects how the schema is written. Existing tables are not modified.

Upsert uses modern SQLite syntax

Upsert operations now use SQLite’s INSERT ... ON CONFLICT SET syntax on SQLite versions 3.24.0 and later. The previous implementation used INSERT OR IGNORE followed by UPDATE.

To use the old behavior, pass use_old_upsert=True to the Database() constructor:

db = Database("my.db", use_old_upsert=True)

CLI changes

Type detection is now the default

When importing CSV or TSV data with the insert or upsert commands, sqlite-utils now automatically detects column types. Previously all columns were treated as TEXT unless --detect-types was passed.

Before (3.x):

# Types were detected only with --detect-types
sqlite-utils insert data.db mytable data.csv --csv --detect-types

After (4.0):

# Types are detected by default
sqlite-utils insert data.db mytable data.csv --csv

# Use --no-detect-types to treat all columns as TEXT
sqlite-utils insert data.db mytable data.csv --csv --no-detect-types

The SQLITE_UTILS_DETECT_TYPES environment variable has been removed.

convert –skip-false removed

The --skip-false option for sqlite-utils convert has been removed. All rows are now processed regardless of whether the column value is falsey.

sqlite-utils tui is now a plugin

The sqlite-utils tui command has been moved to a separate plugin. Install it with:

sqlite-utils install sqlite-utils-tui

Python version requirements

sqlite-utils 4.0 requires Python 3.10 or higher. Python 3.8 and 3.9 are no longer supported.