# sqlite-functions
[](https://github.com/anuradhawick/sqlite-functions/actions/workflows/publish-crates.yml) [](https://crates.io/crates/sqlite-functions) [](https://crates.io/crates/sqlite-functions) [](https://docs.rs/sqlite-functions) [](https://crates.io/crates/sqlite-functions)
`sqlite-functions` is a small Rust toolkit for writing application-defined
[SQLite functions](https://sqlite.org/appfunc.html). 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:
```toml
[dependencies]
sqlite-functions = "0.1.2"
```
For a loadable extension, enable the extension bridge and build your library as
a `cdylib`:
```toml
[lib]
crate-type = ["cdylib"]
[dependencies]
sqlite-functions = { version = "0.1.2", features = ["loadable-extension"] }
```
## Write and register a scalar function
```rust
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:
```rust,ignore
# 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`:
```rust
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](https://sqlite.org/c3ref/c_deterministic.html) and
[expression indexes](https://sqlite.org/expridx.html).
## Basic extension example
[`examples/basic-extension`](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:
```bash
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:
```text
Linux: target/release/libsqlite_functions_basic.so
macOS: target/release/libsqlite_functions_basic.dylib
Windows: target/release/sqlite_functions_basic.dll
```
```sql
.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
```sql
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:
```bash
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`](https://github.com/casey/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.