aiosqlite

aiosqlite provides an asyncio-compatible interface to SQLite databases by wrapping the standard library’s sqlite3 module. It replicates sqlite3’s connection and cursor APIs almost exactly, but every method becomes an async coroutine, so applications built on asyncio can talk to SQLite without blocking the event loop.

Under the hood, aiosqlite runs a single dedicated worker thread per connection and funnels every query through a shared queue, guaranteeing that SQLite calls — which are not safe to use across arbitrary threads — happen serially on one thread while the asyncio event loop stays free to do other work. Context managers handle closing connections and cursors automatically, and the library also exposes convenience helpers like execute_fetchall and an async iterdump for common patterns.

What You Get

• Async equivalents of sqlite3’s Connection and Cursor objects, with the same method names and semantics
• A dedicated worker thread per connection that serializes SQLite calls off the event loop
• Async context managers for connections and cursors that close resources automatically
• Convenience helpers like execute_fetchall, execute_insert, and an async iterdump for streaming SQL dumps
• A fully typed API (py.typed marker) for editor and mypy support

Common Use Cases

• Adding SQLite persistence to an asyncio web service (e.g. FastAPI or aiohttp) without blocking request handling
• Local caching or job-queue storage for async workers that need durable state
• Prototyping and testing async data-access layers before moving to a networked database
• Desktop, bot, or CLI asyncio apps (e.g. Textual TUIs, Discord bots) that need embedded, file-based storage

Under The Hood

Architecture aiosqlite is a small, cleanly layered module: core.py defines the Connection proxy, which owns a single background thread and a queue-based transaction pipeline; every sqlite3 call is wrapped as a partial and pushed onto that queue, executed serially on the worker thread, with results or exceptions marshalled back to the asyncio event loop through call_soon_threadsafe. cursor.py’s Cursor class is a thin proxy over sqlite3’s cursor that reuses the same execution plumbing and adds chunked async iteration for streaming query results. context.py supplies a decorator that wraps coroutine-returning methods in a dual-purpose object implementing both Coroutine and an async context manager, so a single method like execute() can be used as either a plain await or an async with block. This design cleanly isolates the one hard constraint — sqlite3 objects must be used from a single thread — behind an async-friendly interface, and the worker thread plus its queue is the abstraction everything else hinges on.

Tech Stack aiosqlite targets Python 3.9+ and has zero runtime dependencies beyond the standard library — the entire implementation is three small modules. Packaging uses flit_core as the build backend, docs are built with Sphinx and published to Read the Docs, and the dev toolchain (declared as optional dependencies) includes black, usort/ufmt for formatting, mypy for static typing, and flake8 with flake8-bugbear for linting. Continuous integration uses uv to install dependencies and runs the test/lint/build/publish pipeline across multiple Python versions on macOS, Ubuntu, and Windows, publishing to PyPI via trusted publishing on tagged releases.

Code Quality Tests live in a substantial smoke-test module built on the standard library’s IsolatedAsyncioTestCase rather than pytest, covering connection lifecycle, cursor iteration, error propagation, backup, iterdump, and authorizer callbacks, alongside a dedicated performance-test module; coverage configuration enforces a minimum threshold. Static analysis is enforced through mypy and flake8 with flake8-bugbear, both run in CI alongside the test matrix, and the package ships a py.typed marker so downstream consumers get type-checked signatures. Error handling favors letting sqlite3’s own exception types propagate unchanged — they’re re-exported directly from sqlite3 rather than wrapped — and naming conventions mirror the stdlib API closely for familiarity.

API Design aiosqlite’s core design goal is API parity with the synchronous sqlite3 module — Connection and Cursor expose the same method names with await added, so migrating existing sqlite3 code is largely mechanical. The dual coroutine/context-manager return type is the one genuinely distinctive ergonomic choice, letting the same call site work as a plain await or an async with block depending on whether the caller needs the cursor for iteration. Beyond that, aiosqlite deliberately stays a thin, familiar wrapper rather than introducing new query concepts or abstractions, which suits its scope but doesn’t set it apart technically from a straightforward thread-executor wrapper around sqlite3.