A framework for building loadable SQLite extensions in Rust.
Inspired by rusqlite, pgx, and Riyaz Ali's similar SQLite Go library. 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.
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
.dylibor.soor.dll(depending on your operating system). These are different than application-defined functions that many language clients support (such as Python's.create_functionor 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!
```rs // add(a, b) fn add(context: mut sqlite3_context, values: &[mut sqlite3value]) -> Result<()> { let a = api::valueint(values.get(0).expect("1st argument")); let b = api::valueint(values.get(1).expect("2nd argument")); api::resultint(context, a + b); Ok(()) }
// connect(seperator, string1, string2, ...) fn connect(context: mut sqlite3_context, values: &[mut sqlite3value]) -> Result<()> { let seperator = api::valuetext(values.get(0).expect("1st argument"))?; let strings:Vec<&str> = values .get(1..) .expect("more than 1 argument to be given") .iter() .filtermap(|v| api::valuetext(v).ok()) .collect(); api::result_text(context, &strings.join(seperator))?; Ok(()) }
pub fn sqlite3extensioninit(db: *mut sqlite3) -> Result<()> { definescalarfunction(db, "add", 2, add, FunctionFlags::DETERMINISTIC)?; definescalarfunction(db, "connect", -1, connect, FunctionFlags::DETERMINISTIC)?; Ok(()) } ```
```sql sqlite> select add(1, 2); 3 sqlite> select connect('-', 'alex', 'brian', 'craig'); alex-brian-craig
```
Table functions, (aka "Eponymous-only virtual tables"), can be added to your extension with define_table_function.
rs
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.
sql
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:
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 the current implementation will change soon.
rs
define_virtual_table::<CustomVtab>(db, "custom_vtab", None)?
These virtual tables can be created in SQL with the CREATE VIRTUAL TABLE syntax.
```sql
create virtual table xxx using custom_vtab(arg1=...);
select * from xxx;
```
Some real-world non-Rust examples of traditional virtual tables in SQLite:
cargo init --lib a new project, and add sqlite-loadable to your dependencies in Cargo.toml.
toml
[dependencies]
sqlite-loadable = "TODO"
Also change the lib's crate-type to "cdylib": TODO add name="" here? or pass in a name in cargo init?
toml
[lib]
crate-type=["cdylib"]
Then, fill in your src/lib.rs with a "hello world" extension:
```rs use sqliteloadable::prelude::*; use sqliteloadable::{ api, definescalarfunction, Result, };
pub fn hello(context: mut sqlite3_context, values: &[mut sqlite3value]) -> Result<()> { let name = api::valuetext(values.get(0).expect("1st argument as name"))?; api::resulttext(context, format!("hello, {}!", name).asstr())?; Ok(()) }
pub fn sqlite3helloinit(db: *mut sqlite3) -> Result<()> { definescalarfunction(db, "hello", 1, hello, FunctionFlags::UTF8 | FunctionFlags::DETERMINISTIC)?; Ok(()) }
```
Build it cargo build, spin up the SQLite CLI, and try out your new extension!
sql
$ sqlite3
sqlite> .load target/debug/libhello
sqlite> select hello('world');
hello, world!
See also these real-world projects that use sqlite-loadable-rs:
sqlite-xsv - An extremely fast CSV/TSV parser in SQLitesqlite-regex - An extremely fast and safe regular expression library for SQLitesqlite-base64 - Fast base64 encoding and decoding in SQLiteI plan to release many more extensions in the near future!
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!
$ cargo build --example --release ```
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 TODO faster than the "offical" CSV SQLite extension written in C, and sqlite-regex is TODO faster than TODO.
unsafe Rustsqlite-loadable-rs uses the SQLite C API heavily, which means unsafe code. Theres no real way to avoid it, but there's a lot of raw pointer passing/dereferencing happening under the hood. 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!
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!
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.
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!
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.