sqlite-loadable-rs

A framework for building loadable SQLite extensions in Rust. Inspired by rusqlite, pgx, and Riyaz Ali's similar SQLite Go library. See Introducing sqlite-loadable-rs: A framework for building SQLite Extensions in Rust (Dec 2022) for more details!

If your company or organization finds this library useful, consider supporting my work!

Warning Still in beta, very unstable and unsafe code! Watch the repo for new releases, or follow my newsletter/RSS feed for future updates.

Background

SQLite's runtime loadable extensions allows one to add new scalar functions, table functions, virtual tables, virtual filesystems, and more to a SQLite database connection. These compiled dynamically-linked libraries can be loaded in any SQLite context, including the SQLite CLI, Python, Node.js, Rust, Go, and many other languages.

Note Notice the word loadable. Loadable extensions are these compiled dynamically-linked libraries, with a suffix of .dylib or .so or .dll (depending on your operating system). These are different than application-defined functions that many language clients support (such as Python's .create_function() or Node.js's .function()).

Historically, the main way one could create these loadable SQLite extensions were with C/C++, such as spatilite, the wonderful sqlean project, or SQLite's official miscellaneous extensions.

But C is difficult to use safely, and integrating 3rd party libraries can be a nightmare. Riyaz Ali wrote a Go library that allows one to easily write loadable extensions in Go, but it comes with a large performance cost and binary size. For Rust, rusqlite has had a few different PRs that attempted to add loadable extension support in that library, but none have been merged.

So, sqlite-loadable-rs is the first and most involved framework for writing loadable SQLite extensions in Rust!

Features

Scalar functions

Scalar functions are the simplest functions one can add to SQLite - take in values as inputs, and return a value as output. To implement one in sqlite-loadable-rs, you just need to call define_scalar_function on a "callback" Rust function decorated with #[sqlite_entrypoint], and you'll be able to call it from SQL!

// add(a, b)
fn add(context: *mut sqlite3_context, values: &[*mut sqlite3_value]) -> Result<()> {
    let a = api::value_int(values.get(0).expect("1st argument"));
    let b = api::value_int(values.get(1).expect("2nd argument"));
    api::result_int(context, a + b);
    Ok(())
}

// connect(seperator, string1, string2, ...)
fn connect(context: *mut sqlite3_context, values: &[*mut sqlite3_value]) -> Result<()> {
    let seperator = api::value_text(values.get(0).expect("1st argument"))?;
    let strings:Vec<&str> = values
        .get(1..)
        .expect("more than 1 argument to be given")
        .iter()
        .filter_map(|v| api::value_text(v).ok())
        .collect();
    api::result_text(context, &strings.join(seperator))?;
    Ok(())
}
#[sqlite_entrypoint]
pub fn sqlite3_extension_init(db: *mut sqlite3) -> Result<()> {
    define_scalar_function(db, "add", 2, add, FunctionFlags::DETERMINISTIC)?;
    define_scalar_function(db, "connect", -1, connect, FunctionFlags::DETERMINISTIC)?;
    Ok(())
}

sqlite> select add(1, 2);
3
sqlite> select connect('-', 'alex', 'brian', 'craig');
alex-brian-craig

See define_scalar_function for more info.

Table functions

Table functions, (aka "Eponymous-only virtual tables"), can be added to your extension with define_table_function.

define_table_function::<CharactersTable>(db, "characters", None)?;

Defining a table function is complicated and requires a lot of code - see the characters.rs example for a full solution.

Once compiled, you can invoke a table function like querying any other table, with any arguments that the table function supports.

sqlite> .load target/debug/examples/libcharacters
sqlite> select rowid, * from characters('alex garcia');
┌───────┬───────┐
│ rowid │ value │
├───────┼───────┤
│ 0     │ a     │
│ 1     │ l     │
│ 2     │ e     │
│ 3     │ x     │
│ 4     │       │
│ 5     │ g     │
│ 6     │ a     │
│ 7     │ r     │
│ 8     │ c     │
│ 9     │ i     │
│ 10    │ a     │
└───────┴───────┘

Some real-world non-Rust examples of table functions in SQLite:

• json_each / json_tree
• generate_series
• pragma_* functions
• html_each

Virtual tables

sqlite-loadable-rs also supports more traditional virtual tables, for tables that have a dynamic schema or need insert/update support.

define_virtual_table() can define a new read-only virtual table module for the given SQLite connection. define_virtual_table_writeable() is also available for tables that support INSERT/UPDATE/DELETE, but this API will probably change.

define_virtual_table::<CustomVtab>(db, "custom_vtab", None)?

These virtual tables can be created in SQL with the CREATE VIRTUAL TABLE syntax.

create virtual table xxx using custom_vtab(arg1=...);

select * from xxx;

Some real-world non-Rust examples of traditional virtual tables in SQLite include the CSV virtual table, the full-text search fts5 extension, and the R-Tree extension.

Examples

The examples/ directory has a few bare-bones examples of extensions, which you can build with:

$ cargo build --example hello
$ sqlite3 :memory: '.load target/debug/examples/hello' 'select hello("world");'
hello, world!

# Build all the examples in release mode, with output at target/debug/release/examples/*.dylib
$ cargo build --example --release

Some real-world projects that use sqlite-loadable-rs:

• sqlite-xsv - An extremely fast CSV/TSV parser in SQLite
• sqlite-regex - An extremely fast and safe regular expression library for SQLite
• sqlite-base64 - Fast base64 encoding and decoding in SQLite

I plan to release many more extensions in the near future!

Usage

cargo init --lib a new project, and add sqlite-loadable to your dependencies in Cargo.toml.

[package]
name = "xyz"
version = "0.1.0"
edition = "2021"

[dependencies]
sqlite-loadable = "0.0.3"

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

Then, fill in your src/lib.rs with a "hello world" extension:

use sqlite_loadable::prelude::*;
use sqlite_loadable::{
  api,
  define_scalar_function, Result,
};

pub fn hello(context: *mut sqlite3_context, values: &[*mut sqlite3_value]) -> Result<()> {
    let name = api::value_text_notnull(values.get(0).expect("1st argument as name"))?;
    api::result_text(context, format!("hello, {}!", name))?;
    Ok(())
}

#[sqlite_entrypoint]
pub fn sqlite3_hello_init(db: *mut sqlite3) -> Result<()> {
    define_scalar_function(db, "hello", 1, hello, FunctionFlags::UTF8 | FunctionFlags::DETERMINISTIC)?;
    Ok(())
}

Build it cargo build, spin up the SQLite CLI, and try out your new extension!

$ sqlite3
sqlite> .load target/debug/libhello
sqlite> select hello('world');
hello, world!

(MacOS workaround)

Benchmarks

See more details at benchmarks/, but in general, a "hello world" extension built with sqlite-loadable-rs is about 10-15% slower than one built in C, and several orders of magnitude faster than extensions written in Go with riyaz-ali/sqlite (20-30x faster).

However, it depends on what your extension actually does - very rarely do you need a "hello world" type extension in real life. For example, sqlite-xsv is 1.5-1.7x faster than the "offical" CSV SQLite extension written in C, and sqlite-regex is 2x faster than the regexp extension.

Caveats

Heavy use of unsafe Rust

sqlite-loadable-rs uses the SQLite C API heavily, which means unsafe code. I try my best to make it as safe as possible, and it's good that SQLite itself is one of the most well-tested C codebases in the world, but you can never be sure!

Maybe doesn't work in multi-threaded environments

Just because I haven't tested it. If you use SQLite in "serialized mode" or with -DSQLITE_THREADSAFE=1, then I'm not sure if sqlite-loadable-rs will work as expected. If you try this and find problems, please file an issue!

Doesn't work with rusqlite

If you already have Rust code that uses rusqlite to make scalar functions or virtual tables, you won't be able to re-use it in sqlite-loadable-rs. Sorry!

Though if you want to use an extension built with sqlite-loadable-rs in an app that uses rusqlite, consider Connection.load_extension() for dynamic loading, or Connection.handle() + sqlite3_auto_extension() for static compilation.

Probably can't be compiled into WASM

SQLite by itself can be compiled into WASM, and you can also include extensions written in C if you compile those extensions statically before compiling with emscripten (see sqlite-lines or sqlite-path for examples).

However, the same can't be done with sqlite-loadable-rs. As far as I can tell, you can't easily compile a Rust project to WASM if there's a C dependency. There are projects like the wasm32-unknown-emscripten target that could maybe solve this, but I haven't gotten it to work yet. But I'm not an expert in emscripten or Rust/WASM, so if you think it's possible, please file an issue!

Larger binary size

A hello world extension in C is 17KB, while one in Rust is 469k. It's still much smaller than one in Go, which is around 2.2M using riyaz-ali/sqlite, but something to consider. It's still small enough where you won't notice most of the time, however.

Roadmap

• Stabilize scalar function interface
• Stabilize virtual table interface
• Support aggregate window functions (#1)
• Support collating sequences (#2)
• Support virtual file systems (#3)

Supporting

I (Alex 👋🏼) spent a lot of time and energy on this project and many other open source projects. If your company or organization uses this library (or you're feeling generous), then please consider supporting my work, or share this project with a friend!