-   [](https://platform.exaloop.io/)
-   Databases
-   [DuckDB](https://platform.exaloop.io/db/duckdb/)
-   Client applications

# 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](https://platform.exaloop.io/start/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:

```python title="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:

```sh title="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

```sh title="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.

| API | Contract |
| --- | --- |
| `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.columns` | Copied 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](https://platform.exaloop.io/db/reference/#duckdb) covers less obvious cases, such as `SUM(BIGINT)` returning a 128-bit integer.

For large result sets, combine streaming with batch iteration:

```python title="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

-   [Use decimal or vectorized functions](https://platform.exaloop.io/db/duckdb/extension/#types-and-nulls) for calculations beyond scalar floating-point values.
-   [Package the executable](https://platform.exaloop.io/db/duckdb/deployment/#embedded-deployment) for another machine.
-   [Profile the application](https://platform.exaloop.io/start/profiling/) to find expensive calculations.

Source: https://platform.exaloop.io/db/duckdb/client/