README.md

dqlite-dbapi

PEP 249 compliant interface for dqlite.

Installation

pip install dqlite-dbapi

Sync Usage

import dqlitedbapi

conn = dqlitedbapi.connect("localhost:9001")
cursor = conn.cursor()
cursor.execute("SELECT 1")
print(cursor.fetchone())
conn.close()

Async Usage

import asyncio
from dqlitedbapi.aio import aconnect

async def main():
    conn = await aconnect("localhost:9001")
    cursor = conn.cursor()
    await cursor.execute("SELECT 1")
    print(await cursor.fetchone())
    await conn.close()

asyncio.run(main())

PEP 249 Compliance

• apilevel = "2.0"
• threadsafety = 1
• paramstyle = "qmark"

Transactions

dqlite-dbapi does not issue implicit BEGIN before DML — each statement runs in the underlying SQLite engine's autocommit mode unless the caller has explicitly opened a transaction. This deviates from PEP 249 §6's prescribed implicit-transaction model and from stdlib sqlite3 / psycopg, both of which auto-BEGIN on the first DML; users porting from those drivers will see different behaviour and must add explicit BEGIN calls (see below) to recover atomic multi-statement semantics. The dqlite C and Go reference clients have the same opt-in contract; this driver matches them rather than stdlib.

If you use SQLAlchemy via sqlalchemy-dqlite, the dialect emits BEGIN for every engine.begin() / connection.begin() block — no explicit BEGIN needed; the autocommit-by-default gap below applies only to direct dbapi users.

Every write still goes through Raft consensus and every read is serializable; isolation is always SERIALIZABLE, but transaction grouping is opt-in.

To group statements into a transaction, issue an explicit BEGIN through a cursor (or use dqliteclient's transaction() async context manager from the layer below):

cur = conn.cursor()
cur.execute("BEGIN")
cur.execute("INSERT INTO t VALUES (?)", (1,))
cur.execute("INSERT INTO t VALUES (?)", (2,))
conn.commit()       # COMMIT

The bare BEGIN SQL is the SQLite default (BEGIN DEFERRED). dqlite's Raft FSM serializes the transaction across the cluster regardless of the DEFERRED / IMMEDIATE / EXCLUSIVE qualifier, so the qualifier does not change isolation semantics — only the lock-acquisition timing on the leader. The Connection.isolation_level attribute exists for pre-3.12 stdlib parity: the getter returns None, the setter accepts only None (no-op) and rejects every other value ("", "DEFERRED", "IMMEDIATE", "EXCLUSIVE", "SERIALIZABLE", "AUTOCOMMIT", etc.) with NotSupportedError. dqlite is autocommit-by-default at the dbapi layer; explicit transactions are managed via conn.commit() / conn.rollback() per PEP 249. The SQLAlchemy dialect rejects AUTOCOMMIT on the same grounds.

Connection-level commit() / rollback() semantics:

• Calling commit() / rollback() before any query has run is a silent no-op (preserves the "no spurious connect" contract — we don't open a TCP connection just to send COMMIT).
• Calling commit() / rollback() with no active transaction (e.g. right after a DDL statement) is also silently successful, matching stdlib sqlite3 semantics.
• The context-manager exit (with conn: ...) commits on clean exit and attempts rollback on exception. Commit failures propagate — silent swallowing was a footgun that could hide data loss.
• Operational caveat: a leader-flip during COMMIT raises OperationalError with a code in dqlitewire.LEADER_ERROR_CODES. The write may or may not have been persisted (Raft may already have replicated the commit log entry before the flip). Use idempotent DML (INSERT OR REPLACE, UPDATE on a unique key) or an out-of-band state-check before retrying.

Differences from aiosqlite

This driver is PEP 249-shaped (sync) and exposes an async surface under dqlitedbapi.aio that mirrors the sync dqlitedbapi surface plus async-specific aconnect / AsyncConnection / AsyncCursor.

The full PEP 249 module attributes (apilevel, threadsafety, paramstyle, sqlite_version, sqlite_version_info), exception hierarchy (Warning, Error, InterfaceError, DatabaseError, DataError, OperationalError, IntegrityError, InternalError, ProgrammingError, NotSupportedError), type constructors (Date, Time, Timestamp, DateFromTicks, TimeFromTicks, TimestampFromTicks, Binary), type sentinels (STRING, BINARY, NUMBER, DATETIME, ROWID), the working register_adapter / unregister_adapter (process-global; mirrors stdlib's pre-3.12 behavior), and stdlib-sqlite3-parity stubs that raise NotSupportedError (register_converter, complete_statement, enable_callback_tracebacks) are all re-exported under dqlitedbapi.aio so cross-driver code porting from aiosqlite imports them from one namespace. One notable deviation:

• AsyncConnection.__aexit__ does NOT close the connection. It commits / rolls back per the body outcome and leaves the connection reusable, matching stdlib sqlite3.Connection.__exit__ and PEP 249 §7. aiosqlite.Connection.__aexit__ DOES close (closes its proxy thread + sqlite3 connection). Cross-driver code porting from aiosqlite must add an explicit await conn.close() (or switch to a pool) for eager-close semantics.

# aiosqlite-style (closes on aexit):
async with aiosqlite.connect(":memory:") as conn:
    ...

# dqlitedbapi.aio-style (does NOT close on aexit, matches stdlib sqlite3):
async with await dqlitedbapi.aio.aconnect(...) as conn:
    ...
    await conn.close()  # explicit

Layering (pool ownership)

Each dqlitedbapi.Connection (sync or async) owns exactly one underlying dqliteclient.DqliteConnection. The dbapi layer does NOT itself pool client connections — pooling lives one layer up:

• SQLAlchemy users: SA's QueuePool (and async siblings) over dbapi Connection objects is the production pool.
• Direct dbapi users: a custom pool over dbapi Connection objects is the supported pattern.
• dqliteclient.ConnectionPool is for direct-client usage and is unused by dqlitedbapi.

A dbapi Connection.close() always closes the underlying client transport — not "return to a pool" — because the dbapi never borrowed from one.

Limitations vs. stdlib sqlite3

• Multi-statement SQL is rejected. cursor.execute("SELECT 1; SELECT 2;") raises ProgrammingError("You can only execute one statement at a time.") (stdlib's wording, but client-side, before any wire round-trip). Split into separate execute() calls; there is no executescript (it raises NotSupportedError).

• paramstyle = "qmark" only. stdlib accepts both ? (qmark) AND :name (named) within a single connection; this driver advertises only qmark and rejects mappings with ProgrammingError.

• Autocommit-by-default at the server. Opposite of stdlib sqlite3's implicit-transaction model — see Transactions above.

• Cursor.rowcount = len(rows) after SELECT. stdlib returns -1 (rowcount is undefined for queries). dqlite knows the full result set at execute time, so the literal-PEP-249 reading is honoured. rowcount = -1 is preserved for true non-result paths (PRAGMA write, never-executed cursor).

• Cursor.executemany clears lastrowid to None. stdlib leaves the prior INSERT's rowid sticky across executemany. The driver diverges deliberately (which row's rowid is "the" rowid for a batch of N inserts is ambiguous).

• Cursor.close() scrubs description / lastrowid / _rows. stdlib preserves description and lastrowid post-close; dqlite scrubs to None to enforce the closed-cursor "no operation performed" surface.

• Cursor.fetchmany(0) returns []. stdlib (verified 3.12.3) and psycopg3 both treat size=0 as "fetch arraysize" or "fetch all remaining"; dqlite reads PEP 249 literally (size=0 → 0 rows). Pass None or omit the argument to default to arraysize.

• cursor.execute("") raises ProgrammingError("empty statement"). stdlib silently accepts empty / whitespace-only / comment-only SQL as a no-op; the driver pre-flight rejects per PEP 249 §7 to surface caller bugs at the call site.

• threadsafety = 1 (stdlib reports 3). Each Connection is thread-affine: methods called from a foreign thread raise ProgrammingError. Use one Connection per thread or use the async surface for a single-thread-per-loop model.

• No executescript / create_function / create_aggregate / create_window_function / iterdump / backup / set_authorizer / serialize / blobopen. stdlib-specific APIs that have no server-side counterpart in dqlite. Stubs raise NotSupportedError.

• SERIALIZABLE isolation only. Every statement is ordered by Raft; weaker isolation levels aren't exposed.

• PEP 249 type sentinels (STRING, BINARY, NUMBER, DATETIME, ROWID) are unhashable. Use chained equality against description[i][1], NOT set/dict membership:

  type_code = cur.description[i][1]
  if type_code == STRING or type_code == NUMBER:  # OK
      ...
  if type_code in {STRING, NUMBER}:               # raises TypeError
      ...

  The sentinels wrap multiple wire type codes (NUMBER covers INTEGER+FLOAT+BOOLEAN, DATETIME covers DATE+TIMESTAMP+ISO8601), so no canonical hash can satisfy the Python hash-eq invariant. Stdlib sqlite3 doesn't export these sentinels at all, so the chained-equality form is the cross-driver-portable idiom.

• Binary(value) leaks bare TypeError on bad input. Binary is the stdlib sqlite3.Binary = memoryview alias, kept as a direct alias so isinstance(Binary(b), memoryview) holds for cross-driver porting code. Bad input (Binary("not bytes"), Binary(123), Binary(None)) raises bare TypeError from the underlying memoryview constructor, outside the dqlitedbapi.Error hierarchy. The sibling Date/Time/Timestamp/*FromTicks constructors all wrap as DataError; Binary deliberately does not. Callers who want PEP 249 §7 hierarchy purity for binary input should wrap their own try/except (TypeError, ValueError) and re-raise as dqlitedbapi.DataError.

• WITH ... INSERT/UPDATE/DELETE (CTE-prefixed pure DML) reports zero rowcount and no lastrowid. The driver dispatches between the row-returning and execute paths via a prefix-based heuristic; CTE-prefixed pure DML is misclassified as row-returning and the server's actual count / id is dropped. Stdlib sqlite3 handles this correctly because it dispatches at the SQLite engine level. Workaround: rewrite as plain DML, or use INSERT ... RETURNING id to get the id back through the row-returning path. (PEP 249 doesn't mandate lastrowid correctness for INSERT-via-CTE.)

Cross-version semantic shift: NULL in BOOLEAN/DATETIME columns

Upstream dqlite commit f30fc99 (query: preserve SQLITE_NULL type for NULL values, 2026-01-25) changed the wire encoding of NULL cells in columns declared BOOLEAN, DATE, DATETIME, or TIMESTAMP:

• Before f30fc99: a NULL cell was emitted with the column's coerced type — BOOLEAN(0) (decodes to False) or ISO8601("") (decodes to ""), indistinguishable from a real FALSE / empty string.
• After f30fc99: a NULL cell is emitted with SQLITE_NULL and decodes to None.

Code that does if cur.fetchone()[0] is None: against an old-server cluster will silently miss NULL rows. After a cluster upgrade past f30fc99, the same code starts firing where it previously read False / "". There is no driver-level handshake distinguishing the two server versions — check your dqlite cluster version before relying on is None for BOOLEAN / DATETIME columns.

Layering

Three related packages play different roles:

• dqliteclient.DqliteConnection — the low-level async wire client. Directly speaks the dqlite wire protocol.
• dqlitedbapi.Connection — a sync PEP 249 wrapper built on top, runs a dedicated event-loop thread so sync code can use the async client transparently.
• dqlitedbapi.aio.AsyncConnection — the PEP 249–shaped async counterpart for code already running inside an event loop.

Use dqlitedbapi (sync or async) unless you specifically need wire-level control.

Development

See DEVELOPMENT.md for setup and contribution guidelines.

License

MIT