sqlite-functions

sqlite-functions is a small Rust toolkit for writing application-defined SQLite functions. Define your functions once, then either register them directly on a Rust connection or compile the same registration code into a native SQLite extension.

The initial API supports scalar and aggregate functions. Window functions and table-valued functions are planned for later releases.

Add the crate

For functions embedded in a Rust application:

[dependencies]
sqlite-functions = "0.1.2"

For a loadable extension, enable the extension bridge and build your library as a cdylib:

[lib]
crate-type = ["cdylib"]

[dependencies]
sqlite-functions = { version = "0.1.2", features = ["loadable-extension"] }

Write and register a scalar function

use sqlite_functions::rusqlite::{Connection, Result};
use sqlite_functions::{Arity, FunctionOptions, register_scalar};

pub fn register_functions(connection: &Connection) -> Result<()> {
    register_scalar(
        connection,
        FunctionOptions::new("shout", Arity::Exact(1))
            .deterministic()
            .innocuous(),
        |ctx| {
            let value = ctx.get::<Option<String>>(0)?;
            Ok(value.map(|value| value.to_uppercase()))
        },
    )
}

Functions are registered per SQLite connection. Re-exporting rusqlite ensures your callbacks and sqlite-functions use compatible types.

To export the same registration function from a native extension:

# use sqlite_functions::rusqlite::{Connection, Result};
# fn register_functions(_: &Connection) -> Result<()> { Ok(()) }
sqlite_functions::export_extension!(register_functions);

The macro generates SQLite's conventional sqlite3_extension_init symbol. Enable loadable-extension only for the native extension build; do not combine it with rusqlite's bundled or load_extension features.

Write an aggregate

Implement rusqlite::functions::Aggregate, then pass the stateless implementation to register_aggregate:

use sqlite_functions::rusqlite::functions::{Aggregate, Context};
use sqlite_functions::rusqlite::{Connection, Result};
use sqlite_functions::{Arity, FunctionOptions, register_aggregate};

struct SumSquares;

impl Aggregate<i64, Option<i64>> for SumSquares {
    fn init(&self, _: &mut Context<'_>) -> Result<i64> { Ok(0) }

    fn step(&self, ctx: &mut Context<'_>, total: &mut i64) -> Result<()> {
        let value = ctx.get::<i64>(0)?;
        *total += value * value;
        Ok(())
    }

    fn finalize(&self, _: &mut Context<'_>, total: Option<i64>) -> Result<Option<i64>> {
        Ok(total)
    }
}

fn register(connection: &Connection) -> Result<()> {
    register_aggregate(
        connection,
        FunctionOptions::new("sum_squares", Arity::Exact(1)).deterministic(),
        SumSquares,
    )
}

Function properties and schema safety

FunctionOptions::new selects UTF-8 and leaves the function accessible from schema expressions. It does not automatically mark a callback deterministic or innocuous:

Use .deterministic() only when identical inputs always produce identical outputs. SQLite requires this for expression indexes, partial indexes, and generated columns.
Use .innocuous() only after checking that the function has no side effects and cannot leak sensitive data. Hardened connections may require this for functions used by the schema.
Use .direct_only() for functions that should run only from top-level SQL. Direct-only functions cannot be used in indexes, views, triggers, constraints, or generated columns.

See SQLite's documentation for function flags and expression indexes.

Basic extension example

examples/basic-extension is an independent crate that demonstrates the public API with:

to_camel_case(TEXT) -> TEXT, using Unicode-aware heck casing rules.
murmur3_32(TEXT|BLOB) -> INTEGER, using MurmurHash3 x86 32-bit and seed 0.
capital_concat(TEXT) -> TEXT, capitalizing and concatenating non-NULL values in aggregate input order.

Build it from the repository root:

cargo build -p sqlite-functions-basic-extension \
  --features loadable-extension --release

Load the resulting library with the SQLite CLI. Omit the platform extension in the .load command when SQLite can infer it:

Linux:   target/release/libsqlite_functions_basic.so
macOS:   target/release/libsqlite_functions_basic.dylib
Windows: target/release/sqlite_functions_basic.dll

.load ./target/release/libsqlite_functions_basic
SELECT to_camel_case('customer display_name');
-- customerDisplayName

SELECT murmur3_32('hello');
-- 613153351

WITH words(position, value) AS (
  VALUES (1, 'hello world'), (2, 'rust_sqlite')
)
SELECT capital_concat(value)
FROM (
  SELECT value
  FROM words
  ORDER BY position
);
-- HelloWorldRustSqlite

Indexing a hash expression

CREATE TABLE records(id INTEGER PRIMARY KEY, value TEXT NOT NULL);
CREATE INDEX records_murmur ON records(murmur3_32(value));

SELECT *
FROM records
WHERE murmur3_32(value) = murmur3_32('customer-42')
  AND value = 'customer-42';

The query must spell the indexed expression the same way for SQLite to select the index. Keep the original-value predicate: 32-bit hashes can collide. Murmur3 is a fast non-cryptographic hash and must not be used for passwords, signatures, authentication, or other security decisions.

Development

The authoritative commands require only Rust, SQLite, and the platform SQLite development library:

cargo fmt --all -- --check
cargo clippy --workspace --all-targets -- -D warnings
cargo test --workspace
cargo build -p sqlite-functions-basic-extension --features loadable-extension
./scripts/test-extension.sh

If just is installed, just --list provides shortcuts for the same commands.

License

Licensed under either the Apache License, Version 2.0 or the MIT License, at your option.

sqlite-functions 0.1.2