Database

Python: also in Node · Go · Rust · Swift · Dart · Java and Kotlin · .NET · C++ · Objective-C

The current writable database.

query, execute and transaction share one convenience connection, created when first needed. connect() creates additional independent connections. A remote supplied at open enables sync(). By default, it also starts automatic synchronization in both directions. Concepts explains the remote and synchronization model.

At a glance

Read and write

• query(): Run one statement with read execution intent on the convenience connection.
• execute(): Run exactly one state-changing statement on the convenience connection.
• transaction(): Begin one managed transaction on the convenience connection.
• connect(): Create another independent SQLite connection to this database.

Synchronize

• sync(): Synchronize once with the remote.
• status(): Return a typed DatabaseStatus for the current database state.
• set_sync_policy() Advanced: Replace the synchronization policy and return the new status.
• resolve_conflict(): Adopt the authenticated remote revision and replay local work.

Close

• close(): Close all owned connections, then the local store.

Maintenance

• clear_quarantine_records() Advanced: Discard full quarantine records and return the number cleared.

Read and write

Database.query

Run one statement with read execution intent on the convenience connection.

Database.query(
    sql: str,
    params: Bindings = (),
    *,
    engine: QueryEngine = 'sqlite',
) -> Rows

Parameters

• sql: One SQLite statement.
• params: Values for the statement’s placeholders.
• engine: The SQLite or DuckDB read engine.

Returns

Rows. The result rows and their column names.

Errors

• database_closed: The database is closed.
• invalid_argument: An argument has the wrong type or value.
• write_in_query: The statement writes. Use execute for it.
• result_limit_exceeded: The result has more rows than ConnectionOptions.maximum_rows.
• invalid_state: A managed transaction is active. Use its transaction object.
• analytical_engine_unavailable: The analytics package is not installed and no library is named or found.
• analytical_engine_error: The named analytical library could not load. Check OpenOptions.analytical_engine and the library file.
• transaction_control_sql: The statement starts with BEGIN, COMMIT, END, RELEASE, ROLLBACK, or SAVEPOINT. Use transaction() instead.

Other errors can occur. See Errors for every code.

Example

import potluckdb
with potluckdb.open("notes") as db:
    print(db.query("SELECT ? AS title", ("hello",))[0]["title"])

Details

The connection reference explains intent rules.

Database.execute

Run exactly one state-changing statement on the convenience connection.

Database.execute(
    sql: str,
    params: Bindings = (),
) -> ExecuteResult

Parameters

• sql: One SQLite statement.
• params: Values for the statement’s placeholders.

Returns

ExecuteResult. The affected row count and last inserted row ID.

Errors

• database_closed: The database is closed.
• invalid_argument: An argument has the wrong type or value.
• read_in_execute: The statement only reads. Use query for it.
• invalid_state: A managed transaction is active. Use its transaction object.
• database_busy: Another connection holds a write transaction. Retry after that transaction ends.
• transaction_control_sql: The statement starts with BEGIN, COMMIT, END, RELEASE, ROLLBACK, or SAVEPOINT. Use transaction() instead.

Other errors can occur. See Errors for every code.

Example

import potluckdb
with potluckdb.open("notes") as db:
    db.execute("CREATE TABLE note (id INTEGER PRIMARY KEY)")
    print(db.execute("INSERT INTO note DEFAULT VALUES").rows_affected)

Details

The connection reference explains intent rules.

Database.transaction

Begin one managed transaction on the convenience connection.

Database.transaction(
    *,
    mode: TransactionMode = 'immediate',
) -> _Transaction

Parameters

• mode: The deferred, immediate, or exclusive transaction mode.

Returns

_Transaction. The managed transaction context.

Errors

• database_closed: The database is closed.
• invalid_argument: An argument has the wrong type or value.
• invalid_state: A transaction is active, or this transaction context was already used. Finish the active transaction and use a new context.

Other errors can occur. See Errors for every code.

Example

import potluckdb
with potluckdb.open("notes") as db:
    with db.transaction() as transaction:
        transaction.execute("CREATE TABLE note (id INTEGER PRIMARY KEY)")
        transaction.execute("INSERT INTO note VALUES (?)", (5,))
    print(db.query("SELECT id FROM note")[0][0])

Details

The default mode is immediate.

Database.connect

Create another independent SQLite connection to this database.

Database.connect(
    options: ConnectionOptions | None = None,
) -> Connection

Parameters

• options: Settings owned by the new connection.

Returns

Connection. An independent connection owned by this database.

Errors

• invalid_argument: An argument has the wrong type or value.
• database_closed: The database is closed.

Other errors can occur. See Errors for every code.

Example

import potluckdb
with potluckdb.open("notes") as db:
    with db.connect() as connection:
        print(connection.query("SELECT 9")[0][0])

Details

Each explicit connection takes its own ConnectionOptions. Closing it leaves the database and siblings usable. Ordinary SQLite locking permits at most one writer at a time.

Synchronize

Database.sync

Synchronize once with the remote.

Database.sync() -> SyncResult

Returns

SyncResult. One synchronization outcome: UpToDate, Pushed, Pulled, or Conflict.

Errors

• remote_required: The database has no remote.
• database_closed: The database is closed.

Other errors can occur. See Errors for every code.

Example

import potluckdb
options = potluckdb.OpenOptions(sync_policy=potluckdb.SyncPolicy.manual())
with potluckdb.Remote.from_directory("shared") as remote:
    with potluckdb.open("notes", remote, options) as db:
        db.execute("CREATE TABLE note (id INTEGER PRIMARY KEY)")
        print(db.sync().kind)

Details

If the device has local writes to publish, this call publishes them. Otherwise, it checks the remote head and pulls a newer revision if one exists. A failed attempt keeps its prepared writes for a later attempt, and that retry can leave newer writes pending.

It returns exactly one result: UpToDate, Pushed, Pulled or Conflict. A Conflict is returned as a value without raising an exception. Automatic synchronization stays paused in that state until resolve_conflict runs. Without a remote, this call fails with remote_required.

Database.status

Return a typed DatabaseStatus for the current database state.

Database.status() -> DatabaseStatus

Returns

DatabaseStatus. The current database state.

Example

import potluckdb
with potluckdb.open("notes") as db:
    print(db.status().connection_count)

Details

It contains these values:

• Current revision
• Pending writes
• Residency and budget
• Connection count
• Synchronization state
• Next delay
• Conflict revision
• Lifecycle

next_sync_in_ms gives the delay until automatic synchronization, in milliseconds. 0 means it is due now. The driver sets it at its last step. It does not count down between steps. It can therefore overstate the remaining delay by the time since that step. It never understates it.

An idle automatic database reports its idle pull interval. None means nothing is scheduled, so waiting on it would wait forever. This occurs when any of these applies:

• The policy is manual.
• A cycle is running.
• A conflict awaits resolution.
• Automatic synchronization failed permanently.
• The database is closed.

When something is scheduled, wait using this field. Do not sleep for a guessed interval instead. On an idle machine, the interval can pass before the work completes.

connection_count includes connections you opened with connect() and the convenience connection created on the database’s first statement. A database with no statements through its convenience connection and no explicit connections reports zero.

Database.set_sync_policy

Advanced

Replace the synchronization policy and return the new status.

Database.set_sync_policy(
    policy: SyncPolicy,
) -> DatabaseStatus

Parameters

• policy: The replacement synchronization policy.

Returns

DatabaseStatus. The database state after the policy changes.

Errors

• invalid_argument: An argument has the wrong type or value.
• database_closed: The database is closed.

Other errors can occur. See Errors for every code.

Example

import potluckdb
with potluckdb.open("notes") as db:
    status = db.set_sync_policy(potluckdb.SyncPolicy.manual())
    print(status.next_sync_in_ms)

Details

SyncPolicy.manual() disables automatic work but not explicit sync() or resolve_conflict().

Database.resolve_conflict

Adopt the authenticated remote revision and replay local work.

Database.resolve_conflict() -> ConflictResolution

Returns

ConflictResolution. The adopted revision and transaction ID groups.

Errors

• revision_retired: The needed revision is no longer retained, or the remote has no published revision.
• remote_required: The database has no remote.
• database_closed: The database is closed.

Other errors can occur. See Errors for every code.

Example

from potluckdb import OpenOptions, Remote, SyncPolicy, open
opts = OpenOptions(sync_policy=SyncPolicy.manual())
with Remote.from_directory("shared") as r:
    with open("left", r, opts) as left, open("right", r, opts) as right:
        left.execute("CREATE TABLE note AS SELECT 1 AS id")
        right.execute("CREATE TABLE note AS SELECT 2 AS id")
        left.sync()
        right.sync()
        resolved = right.resolve_conflict()
        print(len(resolved.replayed), len(resolved.quarantined))

Details

The call downloads the revision and stages it durably. It applies the revision while blocking database access and restarting attached connections. It then replays safe pending statements.

The result names transaction IDs in these groups:

• Already published
• Replayed
• Quarantined
• Still pending

Prepared state survives a crash and restart. Production considerations describes replay limits, including FAIL statements and reports after interrupted resolution.

The first statement on each side also records database setup, so each side has two pending transactions. The setup replays; the conflicting CREATE TABLE is quarantined.

Close

Database.close

Close all owned connections, then the local store.

Database.close() -> None

Errors

• invalid_state: A managed transaction is active on this database or a connection. Close the database after it ends.

Other errors can occur. See Errors for every code.

Example

import potluckdb
with potluckdb.open("notes") as db:
    db.close()
    print(db.status().closed)

Details

Repeated calls have the same effect.

close() fails with invalid_state while any owned connection has an active managed transaction. This never closes your Remote.

Maintenance

Database.clear_quarantine_records

Advanced

Discard full quarantine records and return the number cleared.

Database.clear_quarantine_records() -> int

Returns

int. The number of full quarantine records cleared.

Errors

• database_closed: The database is closed.

Other errors can occur. See Errors for every code.

Example

import potluckdb
with potluckdb.open("notes") as db:
    print(db.clear_quarantine_records())

Details

Compact safety records remain until authenticated retention permits their retirement.

Which engine answers a read

Use query to choose the engine. Default reads use SQLite. Select DuckDB with engine="duckdb". Only these two engines are supported. An invalid selection fails instead of selecting the default.

The packaged Python SDK supports SQLite and DuckDB.

The native SDK loads DuckDB when you first request an analytical read. OpenOptions.analytical_engine selects the library for one database or snapshot. It takes priority over POTLUCKDB_DUCKDB_LIBRARY, package discovery where available, and the platform’s library search. An explicit selection, including an empty string, never uses a fallback.

See analytics engine installation for your platform. Without an implicit library, the read fails with analytical_engine_unavailable. If an explicit library cannot load, the read reports its loader error.

Install it with pip install potluckdb-analytics, which resolves the wheel built for this platform.

DuckDB rejects the named binding forms :name and @name. Both engines accept $name and positional ? bindings. Booleans bind as INTEGER 0/1 on both. IS therefore asks the same question on either engine, unlike a DuckDB BOOLEAN binding.

Bare table names read the database’s main schema. Qualifiers such as temp. are not seen. For table names ending in .csv, .parquet or .json, use potluckdb_scan('name'). DuckDB otherwise treats those names as files.

NUMERIC and DECIMAL columns read as doubles. A value that a double cannot hold exactly fails the query instead of arriving rounded.

A storage failure during an analytical read reports analytical_engine_error, with the failure named in its message. The same query succeeds when storage answers.

Either engine runs one read per query. The analytical engine reads a snapshot of the same data through its own in-memory database. A statement sent to this engine therefore cannot change the stored data.

An engine can remain for the session’s lifetime. Changing its settings or catalog would change later queries on that session. Only SELECT and EXPLAIN are accepted. CREATE, SET, ATTACH, PREPARE and EXPLAIN ANALYZE are refused. EXPLAIN ANALYZE is refused because it executes the explained statement.

Use execute for writes to the stored data.