Skip to content

differences-from-sqlite3.md

Latest commit

 

History

History
108 lines (94 loc) · 5.55 KB

differences-from-sqlite3.md

File metadata and controls

108 lines (94 loc) · 5.55 KB
Raw

Differences from sqlite3

dqlite-dbapi is shaped like the stdlib sqlite3 module, but it talks to a replicated cluster over the network rather than a local file. This page covers the differences you are most likely to notice when porting code.

Things you'll likely notice

  • Autocommit by default. No implicit BEGIN before DML — the opposite of stdlib's implicit-transaction model. See Transactions.
  • One statement per execute(). cursor.execute("SELECT 1; SELECT 2;") raises ProgrammingError (client-side, before any network round-trip). Split into separate calls. There is no executescript() — it raises NotSupportedError.
  • qmark parameters only. Use ? placeholders. Stdlib also accepts :name; this driver advertises only qmark and rejects mappings with ProgrammingError.
  • Foreign keys are enforced by default. dqlite defaults every fresh connection to PRAGMA foreign_keys = ON — the opposite of stdlib/pysqlite, which default OFF. Issue PRAGMA foreign_keys = OFF per connection for SQLite's legacy unenforced behavior.
  • SERIALIZABLE isolation only. Every statement is ordered by Raft; weaker isolation levels are not exposed.
  • connect() timeouts differ from stdlib. stdlib's timeout is the busy (lock-wait) budget; here timeout is a per-RPC-phase deadline and the busy budget is the separate busy_timeout argument. connect() also exposes safety caps — max_message_size, max_continuation_frames, max_total_rows — and trust_server_heartbeat.
  • Result sets are fully materialized at execute() time. Stdlib streams rows lazily from the C engine; dqlite drains every continuation frame into the cursor's in-memory buffer before execute() returns, and fetchone() / fetchmany() / fetchall() iterate that buffer. For large queries, cap memory with max_total_rows (forwarded from connect()) or shape queries with an explicit LIMIT.
  • No SQLite extension APIs. create_function, create_aggregate, create_window_function, iterdump, backup, set_authorizer, serialize, blobopen, executescript, and register_converter have no server-side counterpart in dqlite; their stubs raise NotSupportedError.
  • A lost connection stays lost. When the wire session drops (transport error, leader change, or an interrupted call), closed becomes True, invalidated becomes True, and every further call raises InterfaceError. The driver never reconnects behind your back, because a silent reconnect would drop transaction state; open a new connection (a pool does this for you).
  • Bad connect() arguments raise ProgrammingError, not TypeError / ValueError, so configuration mistakes stay inside the dbapi.Error hierarchy.
  • rowcount after SELECT is len(rows). Stdlib returns -1 for queries. dqlite knows the full result at execute time, so it reports the count. rowcount stays -1 for non-result paths, for all PRAGMA statements (read or write — matching stdlib), and for a never-executed cursor.

NULL in BOOLEAN/DATETIME columns depends on the server version

A 2026 dqlite server change (upstream commit f30fc99) changed how a NULL cell is encoded in columns declared BOOLEAN, DATE, DATETIME, or TIMESTAMP:

  • Before: a NULL came back as False (for BOOLEAN) or "" (for datetime types) — indistinguishable from a real value.
  • After: a NULL comes back as None.

There is no handshake that distinguishes the two server versions, and the driver decodes faithfully whatever the server sends. Code like if row[0] is None: will silently miss rows on an old cluster and start matching after an upgrade. Check your dqlite cluster version before relying on is None for these columns.

Minor / edge-case differences

You can usually ignore these, but they are here so nothing surprises you:

  • lastrowid is None (not 0) before the first INSERT. After the first INSERT both drivers agree and keep the value sticky across UPDATE/DELETE/DDL.
  • fetchmany(0) returns [] (PEP 249 read literally). Pass None or omit the argument to default to arraysize. fetchmany(negative) raises ProgrammingError.
  • execute("") raises ProgrammingError (empty/whitespace/comment-only SQL). Stdlib treats it as a no-op.
  • Empty result sets report the UNKNOWN type code in description[i][1] (importable from dqlitedbapi). Test it with type_code == UNKNOWN; for column types on an empty result, query PRAGMA table_info(...).
  • CTE-prefixed DML (WITH ... INSERT/UPDATE/DELETE) sets lastrowid and reports rowcount as the affected-row count. That rowcount is a dqlite extra: stdlib sqlite3 returns -1 for CTE-prefixed DML (it counts only non-CTE DML). WITH ... SELECT and WITH ... RETURNING return rows as usual.

Type-code sentinels: compare with ==, not in {}

The PEP 249 type sentinels (STRING, BINARY, NUMBER, DATETIME, ROWID) match a description type code through __eq__, and each wraps several wire types (NUMBER covers INTEGER+FLOAT+BOOLEAN; DATETIME covers DATE+TIMESTAMP+ISO8601). A bare wire-int type code does not hash equal to a sentinel, so set/dict membership silently returns False:

type_code = cur.description[i][1]
if type_code == STRING or type_code == NUMBER:   # correct
    ...
if type_code in {STRING, NUMBER}:                # WRONG: silently False
    ...

Stdlib sqlite3 doesn't export these sentinels at all, so the chained-equality form is also the cross-driver-portable idiom.