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.