cysqlite: A Ground-Up Rewrite That Could Change How Python Talks to SQLite

Charles Leifer’s seven-year Cython project is finally ready for testing—including in your browser

If you’ve ever wrestled with Python’s sqlite3 module—especially its quirky transaction handling—there’s good news. Charles Leifer, the maintainer behind pysqlite3 and the creator of Peewee ORM, has been quietly building a complete rewrite called cysqlite. After nearly seven years of development, it’s ready for the world to try.

The Core Insight

The Python standard library’s sqlite3 module has served developers well, but it carries design decisions from a different era. The biggest pain point? Transaction handling.

The sqlite3 module provides two different autocommit variants, neither of which exactly matches SQLite’s own autocommit mechanism. This impedance mismatch has caused countless hours of debugging and unexpected behavior in production applications.

cysqlite starts fresh with Cython—a language that compiles to C—delivering both the performance benefits of a native extension and transaction semantics that actually align with SQLite itself.

But the transaction fix is just the beginning.

Why This Matters

Custom Virtual Tables: This is the feature that excites power users most. SQLite’s virtual table mechanism lets you expose arbitrary data sources as queryable tables. Want to query a CSV file, a REST API, or your filesystem using SQL? Virtual tables make it possible. The sqlite3 module’s support for this is limited; cysqlite provides first-class support via its TableFunction API.

Performance: Cython compilation means cysqlite can achieve performance closer to C extensions while maintaining Python’s ergonomics. For applications doing heavy database work, this matters.

Modern Python: Built for contemporary Python, cysqlite doesn’t carry legacy baggage from Python 2 era decisions.

The most surprising development? Simon Willison managed to get cysqlite running in the browser via Pyodide/WebAssembly. He used Claude Code to build a WASM wheel, opening up the possibility of running sophisticated SQLite operations entirely client-side.

import micropip
await micropip.install(
  "https://simonw.github.io/research/cysqlite-wasm-wheel/cysqlite-0.1.4-cp311-cp311-emscripten_3_1_46_wasm32.whl"
)
import cysqlite
print(cysqlite.connect(":memory:").execute(
  "select sqlite_version()"
).fetchone())

Key Takeaways

• Seven years of development isn’t slow—it’s thorough. Charles has been refining this library to get it right
• Transaction handling aligned with SQLite solves one of the most common footguns in the sqlite3 module
• Virtual table support unlocks use cases that were previously awkward or impossible in pure Python
• WASM compatibility (thanks to Simon’s experiment) opens browser-based SQLite applications
• Drop-in compatibility isn’t the goal—better semantics are. Expect some migration effort

Looking Ahead

cysqlite represents a bet that the Python SQLite ecosystem is ready for a clean-slate approach. The project is still young—version 0.1.4 at time of writing—but the fundamentals look solid.

For projects with heavy SQLite usage, especially those that have been frustrated by transaction edge cases or wanted virtual table support, now is the time to experiment. The library is available on GitHub and PyPI.

The bigger picture: SQLite continues to evolve from “that file-based database” to a legitimate application platform. Tools like cysqlite that remove friction between SQLite’s capabilities and Python’s developer experience will accelerate that evolution.

Sometimes the most impactful open-source work isn’t flashy. It’s seven years of patient development on something fundamental.

Based on analysis of “cysqlite—a new sqlite driver” from Simon Willison’s Weblog, originally by Charles Leifer