Skip to content

Using SQLite from asyncio with aiosqlite

SQLite is a library, not a server: every query runs as a function call in your process, and a slow query through the standard sqlite3 module blocks the event loop for its full duration. aiosqlite moves each connection onto its own background thread and gives it an async API. Measured on Python 3.14 with a join that took 0.6 s, the event loop's worst stall was 604 ms with sqlite3 and 1 ms with aiosqlite. The thread hop is not free: 10,000 point lookups took 0.08 s with sqlite3 and 0.55 s with aiosqlite, about 55 µs per query against 8 µs. And SQLite's single-writer design still applies: four concurrent writer tasks and two readers on separate connections hit 189 database is locked errors in the default journal mode, and none in WAL mode. This guide sets up aiosqlite to get the first benefit without the other two problems.

Prerequisites

1. Open a connection and query

aiosqlite mirrors the sqlite3 API with await; the connection and cursors are async context managers:

import aiosqlite


async def get_user(db_path: str, user_id: int):
    async with aiosqlite.connect(db_path) as db:
        db.row_factory = aiosqlite.Row
        async with db.execute("select id, name from users where id = ?", (user_id,)) as cur:
            return await cur.fetchone()

Each aiosqlite.Connection owns one background thread, and every call on it is sent to that thread through a queue and awaited. The SQLite connection object itself never leaves its thread, which is how aiosqlite satisfies SQLite's thread-affinity rules. Open a connection once and reuse it for many queries — opening per query creates and tears down a thread each time.

Verify: a long-running query no longer delays other tasks: a heartbeat task keeps ticking while it runs.

Worst event loop stall during a 0.6 s SQLite query 2 horizontal bars comparing sqlite3, called directly with the others. Worst event loop stall during a 0.6 s SQLite query sqlite3, called directly 604 ms aiosqlite 1 ms Python 3.14; a heartbeat task sleeping 5 ms measured its own lateness while the join ran. The query took the same 0.60 s either way; only where it ran changed.

2. Know what the thread hop costs

Every await on an aiosqlite connection is a handoff to its thread and back. For many tiny queries, that overhead dominates:

# 10,000 point lookups
for i in range(10_000):
    async with db.execute("select v from t where id = ?", (i,)) as cur:
        await cur.fetchone()            # measured 0.553 s total; sqlite3 direct: 0.081 s

Measured: about 55 µs per lookup through aiosqlite against 8 µs directly, because each execute and each fetchone is a separate cross-thread round trip. Reading all 10,000 rows in a single query and fetchall() took 2.9 ms. So write queries that do the work in SQL and fetch in bulk: an IN list or a join instead of a loop of lookups, fetchall() or fetchmany(1000) instead of row-at-a-time iteration over large results.

Verify: profile a request that makes many small queries; replacing the loop with one query reduces its latency by the per-query overhead times the count.

3. Turn on WAL and a busy timeout

SQLite allows one writer at a time per database file. In the default rollback-journal mode, a writer also blocks readers. Several aiosqlite connections writing concurrently then fail with database is locked:

async def open_db(path: str) -> aiosqlite.Connection:
    db = await aiosqlite.connect(path, timeout=5.0)    # wait up to 5 s for a lock
    await db.execute("pragma journal_mode = wal")      # readers no longer block on writers
    await db.execute("pragma synchronous = normal")    # safe with WAL, far fewer fsyncs
    await db.execute("pragma foreign_keys = on")
    return db

Measured with four writer tasks of 200 inserts each and two reader tasks holding short read transactions, all on separate connections with a 0.1 s busy timeout: rollback-journal mode completed 795 of 800 inserts and raised 189 database is locked errors across writers and readers; WAL mode completed all 800 with 0 errors, and finished sooner (0.47 s against 0.60 s). WAL is a property of the database file, so setting it once persists it. The timeout argument is SQLite's busy timeout — how long a connection waits for a lock before raising.

Verify: pragma journal_mode returns wal, and a concurrent read/write test produces no lock errors.

Concurrent writers and readers by journal mode A grid of 2 rows by 4 columns. Concurrent writers and readers by journal mode journal mode inserts done lock errors time delete (default) 795 / 800 189 0.60 s wal 800 / 800 0 0.47 s Four aiosqlite writer connections and two readers, busy timeout 0.1 s.

4. Funnel writes through one connection

WAL lets readers proceed alongside a writer, but writes are still serialized by SQLite. Rather than have many connections compete for the write lock, give the application one dedicated writer connection and a pool of readers:

class Database:
    def __init__(self, path: str, readers: int = 4) -> None:
        self.path, self.n_readers = path, readers

    async def open(self) -> None:
        self.writer = await open_db(self.path)
        self.write_lock = asyncio.Lock()
        self.readers: asyncio.Queue[aiosqlite.Connection] = asyncio.Queue()
        for _ in range(self.n_readers):
            self.readers.put_nowait(await open_db(self.path))

    async def write(self, sql: str, params=()) -> None:
        async with self.write_lock:                  # one transaction at a time, in order
            await self.writer.execute(sql, params)
            await self.writer.commit()

    async def read(self, sql: str, params=()):
        db = await self.readers.get()
        try:
            async with db.execute(sql, params) as cur:
                return await cur.fetchall()
        finally:
            self.readers.put_nowait(db)

The asyncio.Lock serializes writers in your process without them ever hitting SQLite's lock, so no busy-wait or database is locked error occurs. For write-heavy workloads, batch: group many inserts into one transaction on the writer, since each commit is a disk sync.

Verify: under concurrent load, the writer connection never raises lock errors, and read latency stays flat while writes run.

5. Know when SQLite is the wrong tool

aiosqlite makes SQLite safe to call from asyncio; it does not make SQLite a client-server database. It fits embedded, single-process uses: local caches, desktop and CLI tools, tests, edge devices, and small services with one process. It fits poorly when several processes write concurrently — each process's writer competes for the same file lock — or when the database must be shared over a network filesystem, where SQLite's locking is unreliable.

# tests: an in-memory database per test, same SQL as production SQLite
@pytest.fixture
async def db():
    async with aiosqlite.connect(":memory:") as conn:
        await conn.executescript(SCHEMA)
        yield conn

If you use SQLAlchemy, the sqlite+aiosqlite:// URL runs the same aiosqlite underneath, with the same thread-per-connection model and the same WAL advice. For multi-process production services, PostgreSQL with psycopg 3 async connections and pools or asyncpg is the usual next step.

Verify: the deployment runs one writing process per database file, on local disk.

How should this app use SQLite from asyncio? A decision on What is the workload with 4 outcomes. How should this app use SQLite from asyncio? What is the workload? rare, sub-millisecond queries sqlite3 is fine stall is tiny real queries in a service aiosqlite + WAL loop stays free concurrent writes, one process one writer connection asyncio.Lock many writing processes use PostgreSQL file lock contention aiosqlite fixes blocking; WAL and a single writer fix contention.

Verification

SQLite under asyncio is set up well when:

  • Queries run through aiosqlite, and the loop does not stall during long ones.
  • Connections are reused, and many-small-query loops are replaced with bulk queries.
  • WAL mode and a busy timeout are set on every connection.
  • Writes go through one connection, serialized in-process.

Diagnostic Hook: count database is locked errors and measure event-loop lag. Lock errors mean several connections are competing to write — funnel writes through one. Loop lag that tracks query duration means some code path still calls sqlite3 directly on the loop.

Pitfalls & edge cases

  • Calling sqlite3 directly in async code. A 0.6 s query stalled the loop for 604 ms.
  • Opening a connection per query. Each creates and destroys a thread.
  • Row-at-a-time loops. Each call is a thread round trip of tens of microseconds.
  • Default journal mode with concurrent access. Use WAL.

Frequently Asked Questions

Does aiosqlite make SQLite queries run in parallel?

No. Each connection runs its queries on its own background thread, which keeps the event loop free, but SQLite still allows one writer at a time per database file.

How do I fix 'database is locked' with aiosqlite?

Enable WAL mode, set a busy timeout through the timeout argument, and send all writes through one connection guarded by an asyncio.Lock. In testing, WAL took a concurrent workload from 189 lock errors to none.

Is aiosqlite slower than sqlite3?

Per call, yes: point lookups measured about 55 µs through aiosqlite versus 8 µs directly, because each call crosses threads. Do more work per query to amortize it.

Should I use SQLite for an async web service?

For a single-process service with modest write rates it works well with aiosqlite and WAL. With several writing processes, use a server database such as PostgreSQL.