Skip to main content
Markdown

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 and a compatible DuckDB host. 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:

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:

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:

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:

Terminal
duckdb -unsigned
SQL
LOAD './build/risk.duckdb_extension';
SELECT expected_loss(1000.0::DOUBLE, 0.02::DOUBLE);

The query returns:

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 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:

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 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:

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​

OperationContract
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] = valueBounds/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​