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

# DuckDB SQL Functions

Compile Codon functions into a loadable DuckDB extension and call them from SQL. The extension runs inside the host DuckDB engine, so existing CLI and Python applications can use native calculations through their normal queries.

This walkthrough builds an expected-loss function, loads it into DuckDB, and then introduces scalar types, NULL handling, and vectorized callbacks.

**Before you begin:** install the [all-SDK distribution](https://platform.exaloop.io/start/distribution/) and a [compatible DuckDB host](https://platform.exaloop.io/db/reference/#compatibility). You will produce `risk.duckdb_extension` and load it into a local DuckDB session.

## Write and Load an Extension

Define the expected-loss calculation as an ordinary function. The extension initializer registers its SQL name and explicit argument and return types with the host. Save the following as `functions.codon`:

```python title="functions.codon"
import exaloop.databases.duckdb as duckdb


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


@duckdb.extension
def register(database):
    database.create_function("expected_loss", expected_loss, Tuple[float, float], float)
```

Compile the extension:

```sh title="Terminal"
exaloop --kind duckdb-extension build functions.codon -o build/risk.duckdb_extension
```

Use the SDK builder directly

With Codon 0.20.3 and Python 3.9+ available:

```sh title="Terminal"
python3 /path/to/sdk/duckdb/build.py functions.codon \
  --codon /absolute/path/to/codon -o build/risk.duckdb_extension
```

The output name must be a lowercase identifier followed by `.duckdb_extension`. The extension uses the host's DuckDB engine. The host must also be able to find the matching Codon runtime libraries.

### Load from the CLI

Start DuckDB with unsigned extensions enabled, then load your library:

```sh title="Terminal"
duckdb -unsigned
```

```sql title="SQL"
LOAD './build/risk.duckdb_extension';
SELECT expected_loss(1000.0::DOUBLE, 0.02::DOUBLE);
```

The query returns:

```text title="Output"
9.0
```

### Load from Python

Python can continue to orchestrate the query; the UDF itself runs as native Codon code, not as a Python function:

```python title="Python client"
import duckdb

with duckdb.connect(config={"allow_unsigned_extensions": True}) as connection:
    connection.load_extension("build/risk.duckdb_extension")
    result = connection.execute(
        "SELECT expected_loss(1000.0::DOUBLE, 0.02::DOUBLE)"
    ).fetchone()
    assert result == (9.0,)
```

Trust the extension you load

Generated extensions are unsigned. Enable loading only for code you trust: extensions have the privileges of the host process and are not sandboxed.

## Scalar Registration

`create_function()` gives the calculation a SQL name and signature. In this example, `Tuple[float, float]` means two `DOUBLE` arguments, and the final `float` means a `DOUBLE` result. The casts in the query make that match explicit, particularly for numeric literals that SQL might otherwise treat as decimals.

## Types and NULLs

The signature can use numbers, strings, binary values, dates, and nested collections. Use `Optional[T]` when your function should receive a missing value as `None` and choose what to do with it. Otherwise, NULL inputs automatically produce NULL without calling the function.

For financial calculations, use a decimal type when a floating-point approximation would be inappropriate. This example adds a fee of exactly 1.25:

```python title="decimal.codon"
import exaloop.databases.duckdb as duckdb

Amount = duckdb.Decimal[18, 2]

def add_fee(value: Amount) -> Amount:
    return Amount(value.coefficient + i128(125))

with duckdb.connect() as connection:
    connection.create_function("add_fee", add_fee, Tuple[Amount], Amount)
    with connection.query("SELECT add_fee('10.00'::DECIMAL(18,2))") as result:
        assert result.fetchall(Tuple[Amount]) == [(Amount(i128(1125)),)]
```

`Decimal[18, 2]` stores amounts with two decimal places, so its integer coefficient is measured in hundredths. The result is 11.25. See the [DuckDB type reference](https://platform.exaloop.io/db/reference/#duckdb-types) for the full mapping and date/time types.

Arithmetic inside the function follows Codon's rules, not SQL's. An uncaught exception fails the query rather than quietly replacing the answer with NULL.

## Vectorized Registration

Scalar callbacks evaluate independent rows. For algorithms that operate on whole chunks, `mode="vectorized"` passes input columns and a writable output vector to the callback. In an extension initializer, register it with `database.create_function("triple", triple, Tuple[int], int, mode="vectorized")`. The same handler also works in an embedded application:

```python title="vectorized.codon"
import exaloop.databases.duckdb as duckdb

def triple(columns, output):
    values, = columns
    for index in range(len(output)):
        if values.is_valid(index):
            output[index] = values[index] * 3

with duckdb.connect() as connection:
    connection.create_function("triple", triple, Tuple[int], int, mode="vectorized")
    with connection.query("SELECT triple(7::BIGINT), triple(NULL::BIGINT)") as result:
        assert result.fetchall(Tuple[int, Optional[int]]) == [(21, None)]
```

The example leaves a row untouched when its input is NULL. Output starts as NULL, so only the rows it writes become valid. Unlike a scalar handler, a vectorized handler sees all rows, including missing values.

DuckDB chooses the chunk size, so use `len(output)` for the loop bound. Input vectors are read-only and only valid during the callback; do not save them for later work.

### Vector API

| Operation | Contract |
| --- | --- |
| `len(vector)` | Number of rows in this callback |
| `vector.is_valid(index)` | Bounds-checked SQL validity test |
| `vector[index]` | Typed value; NULL requires `Optional[T]` or a prior validity test |
| `output[index] = value` | Bounds/type-checked write; only output is writable |
| `output.set_null(index)` | Mark an output row NULL |

Values copied out of a vector, such as strings or lists, can be kept after the callback. The vector itself cannot.

## Threads and Ownership

DuckDB may run functions on several workers. Read-only state is easy to share, but mutable state needs synchronization. Do not use one connection concurrently or issue a query recursively 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

-   [Deploy the extension](https://platform.exaloop.io/db/duckdb/deployment/) with the matching runtime libraries.
-   [Embed DuckDB in an application](https://platform.exaloop.io/db/duckdb/client/) when you want a standalone executable.
-   Consult the [type reference](https://platform.exaloop.io/db/reference/#duckdb-types) for nested values and temporal types.

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