sqlite-utils 4.0a1 introduces a few minor backward-incompatible changes
sqlite-utils 4.0a1 Release Summary
Last night I published a new alpha release of sqlite-utils — the 128th release since starting the project in 2018.
`sqlite-utils` is both:
- A Python library for creating and manipulating SQLite databases.
- A CLI tool for interacting directly with those databases in the terminal.
Nearly every feature in the package is available through both interfaces.
About This Release
This alpha is expected to be the final pre-release before 4.0 stable.
Since the project follows semantic versioning, moving to 4.0 signals backward-incompatible changes from 3.x.
Most changes are minor — I work to avoid breaking user code when possible. We reached 3.38 before needing a major release.
See the full details in the annotated release notes for 4.0a1.
---
Breaking Changes
1. Table/View Access
`db.table(table_name)` now works only for tables.
To access a SQL view, use:
db.view(view_name)Instead of the former `db["name_of_table_or_view"]` shortcut.
- Reason: Improves type hint clarity; tables and views have different interfaces (views cannot `.insert()`).
---
2. Default Floating-Point Type Change
Default type changed from `FLOAT` to `REAL` (SQLite's correct floating-point type).
Affects auto-detected columns when inserting data.
---
3. Conversion Defaults
`table.convert()` and CLI `sqlite-utils convert` no longer skip falsy values by default.
Removed `--skip-false` option.
---
4. Identifier Quoting
Tables and columns are now wrapped in double quotes rather than square brackets.
Example before:
create table [my table] (
[id] integer primary key,
[key] text
)Example now:
create table "my table" (
"id" integer primary key,
"key" text
)---
5. CLI Functions Argument
`--functions` now accepts:
- A path to a Python file
- Multiple arguments in one invocation
---
6. Type Detection Defaults
CSV/TSV imports now detect column types by default in `insert` and `upsert` CLI commands.
Before: all columns were TEXT unless `--detect-types` was given.
Now: use `--no-detect-types` to revert.
Environment variable `SQLITE_UTILS_DETECT_TYPES` removed.
---
New Features
Iterator Support for Bulk Inserts
`table.insert_all()` and `table.upsert_all()` now accept:
- Iterator of lists/tuples instead of dictionaries
- First item is a list/tuple of column names
Details: docs
---
Packaging Migration
Moved from `setup.py` to `pyproject.toml`.
Includes dev environment recipes using uv, documented in the Justfile.
---
Improved Schema Memory
Tables in the Python API now better track:
- Primary key
- Schema details from creation
- #655
---
Integration Notes
These updates can improve workflows for developers working with:
- Large datasets
- Automated ingestion pipelines
- Cross-platform publishing systems
For example, AiToEarn lets creators:
- Use AI to generate content
- Publish across Douyin, Kwai, WeChat, Bilibili, Rednote (Xiaohongshu), Facebook, Instagram, LinkedIn, Threads, YouTube, Pinterest, and X
- Perform analytics and AI model ranking
Relevant links:
Efficient data handling in sqlite-utils complements multi-platform publishing by providing structured, optimized storage for content pipelines.
---
Final Thoughts
The `sqlite-utils 4.0a1` release tightens type handling, brings standard SQL conformity to schema generation, and streamlines imports and CLI argument behavior.
For developers blending database automation with AI-assisted publishing, pairing these improvements with a platform like AiToEarn could yield faster, more consistent deployment pipelines across numerous content channels.
