Skip to main content
Markdown

DuckDB in Your Application

Embed DuckDB in a native Codon executable to query local data and register custom SQL functions in the same process. Query results convert to explicit Codon tuple types, with streaming and batch iteration available for large outputs.

The first example registers a calculation, calls it from SQL, and checks the result. The embedded SDK supplies the database engine.

Before you begin: install the all-SDK distribution. No DuckDB server, Python package, or separate DuckDB CLI is required.

Embed DuckDB in a Codon Application​

For applications written in Codon, open an embedded connection and register functions directly:

application.codon
import exaloop.databases.duckdb as duckdb

def expected_loss(balance: float, probability: float) -> float:
    return balance * probability * 0.45

with duckdb.connect(threads=4) as connection:
    connection.create_function(
        "expected_loss", expected_loss, Tuple[float, float], float)
    with connection.query(
        "SELECT expected_loss(1000.0::DOUBLE, 0.02::DOUBLE)"
    ) as result:
        assert result.fetchall(Tuple[float]) == [(9.0,)]

For this example in application.codon, run:

Terminal
exaloop --with duckdb run -release application.codon

The program exits successfully when the query returns 9.0.

Use an individual SDK without the CLI
Terminal
codon run -release -plugin /path/to/sdk/duckdb application.codon

The embedded plugin includes DuckDB v1.4.3 with Parquet and JSON support linked in, so these formats work without extension downloads. It does not require the Python DuckDB package or CLI.

Connection and Result API​

Use context managers for connections and results. A normal query loads the complete result; stream=True lets you start consuming it without first materializing the whole output.

APIContract
duckdb.connect(path=":memory:", threads=1)Open a database and connection; positive thread count required
duckdb.version()Linked DuckDB version string
connection.create_function(name, operation, Parameters, Returns, side_effects=False, mode="scalar")Register a specialized UDF; mode is the literal "scalar" or "vectorized"
connection.query(sql, stream=False)Return a materialized result, or opt into streaming
connection.execute(sql)Execute SQL and close/discard its result
connection.close()Idempotent disconnect and database close; aliases become closed
result.columnsCopied list of column names
result.fetchmany(Row, size=2048)Consume at most size rows into an owned list; size must be positive
result.batches(Row, size=2048)Iterate over owned row batches
result.rows(Row)Iterate over rows with bounded batch buffering
result.fetchall(Row)Consume all remaining rows into list[Row]
result.close()Idempotently release the result and buffered chunk

Choose Row to match the query's SQL types, including Optional for nullable columns. The type reference covers less obvious cases, such as SUM(BIGINT) returning a 128-bit integer.

For large result sets, combine streaming with batch iteration:

streaming.codon
import exaloop.databases.duckdb as duckdb

total = 0
with duckdb.connect() as connection:
    with connection.query("SELECT range FROM range(10003)", stream=True) as result:
        for batch in result.batches(Tuple[int], size=4096):
            total += sum(row[0] for row in batch)
assert total == 10003 * 10002 // 2

Finish a streaming result before issuing another query on its connection. Batching limits client-side conversion memory, although SQL operators such as sorting may still need working memory.

Query parameters

The embedded API accepts SQL strings and does not support bound parameters. Interpolating untrusted values can introduce SQL injection, so this API requires queries whose text is controlled by the application.

Threads and Ownership​

DuckDB may run functions on several workers. Read-only state can be shared, but mutable state needs synchronization. A connection does not support concurrent use or recursive queries from its own callback.

By default, a function is treated as deterministic, so DuckDB may reuse or precompute its result. Register side_effects=True for a function that reads mutable state or produces random values; this does not guarantee evaluation order or make that state thread-safe.

Next Steps​