Using from a single binary

[nyurik]

From a deployment/usage perspective, it would be ideal if the resulting executable could contain both the statically-linked sqlite, as well as any extensions. Do you see any way to achieve this?

[asg017]

What use case are you thinking of?

sqlite-loadable-rs could be compiled with crate-type = ["staticlib"] (in addition to the typical `"dylib" crate type), which can then be used to statically compile a SQLite extension into an application. Typically the application (your CLI, webserver, etc.) with handle both statically linking whatever sqlite version they want, in addition to statically linking any sqlite extensions they like.

I haven't blogged about it much, but it's possible to statically link a sqlite extension written with sqlite-loadable-rs into an application. For example the sqlite-regex crate can be added to your Rust projects, as described here. You add rusqlite as a separate dependency, which can statically link sqlite.

You can also use it to compile .a files, generate a .h file and use those to statically link in C/Go. I've done it before but haven't written much about it.

Is that the kind of thing you're going for?

[nyurik]

@asg017 thank you so much for such a detailed answer!

I was thinking of doing this:

• develop a new crate that implements md5 hashing function for sqlite
• in my app that uses sqlx, add the md5 extension crate and statically link it (together with the sqlite)
• my app will use query("SELECT md5(foo) ...") types of queries by loading needed extension at runtime. The sqlite DB files should not be modified just because I'm using an extra function - it should simply be an internal functionality of my app.
• ideally, i would love to use sqlx's compile-time statement validation via a macro: query!("SELECT md5(foo) ...")
• most importantly, the app should remain just a single binary file that contains sqlite and md5 extension

[asg017]

Yeah, I think statically compiling an extension written with sqlite-loadable-rs will do what you'd like! The sqlite3_auto_extension function will probably do what you'd like - you'd call that with the entrypoint of your extension that defines md5(), and any time after that, any new SQLite connection will have the md5() function available. Since everything is statically linked, it'll be a single binary, no extra loadable files needed. That sqlite-regex issue link above will be a good example to work off of.

I'm not too sure about the query!() macro however - does that mean your SQL gets compiled + executed at build time? Not familiar with sqlx.

I'll probably blog about statically linking SQLite extension in the next few weeks, and will also include an end-to-end example of statically linking in this repo soon as well.

[asg017]

That being said sqlite-loadable-rs might be overkill if your custom SQL functions will only be used in Rust. You're probably gonna use rusqlite anyway, and they have a create_scalar_function function that can implement a md5() function just fine. Unless you plan to also re-use your custom SQL functions in other languages (Python, Node.js, etc.), then using rusqlite/create_scalar_function will likely be simpler.

[nyurik]

@asg017 thank you for all the info! I wrote sqlite-hashes crate for the static usacase using rusqlite, but I do hope that I can somehow support dynamic lib generation as well, e.g. with a feature flag of sorts.

[anacrolix]

I'm also interested in this. I have a dynamically loaded runtime extension that I want to switch to a statically linked runtime loadable extension (see section 6 in that link).

I'm currently using rusqlite-le which is mentioned in https://github.com/phiresky/sqlite-zstd. The source and any mention of that have vanished. It seems to only support dynamically loaded extensions. In particular I get issues around a NULL sqlite3_api so I think it or the libsqlite3-sys bindings are doing something whacky. I'm able to link it statically with staticlib and refer to the extension init function but the crash occurs from there.

I saw you in your docs the sqlite_entrypoint, does that work for statically linked extensions? Per above, there might be some differences around initing the sqlite3 API that are normally deferred to SQLITE_CORE and sqlite3ext.h.

[asg017]

sqlite-loadable-rs works both as a loadable extension and a statically linked runtime extension, in my testing.

I don't have a great standalone example of this yet, but compiling with crate-type = ["staticlib"] will include a .a static file that can be used to statically link it into other applications (written in Go, C, C++, etc.)

To have a .h file, I've previously had success with rustup run nightly cbindgen --config cbindgen.toml -o xxx.h, where the cbindgen.toml looked like this, but it takes a long time to compile. You can write one from scratch.

In rust, see this issue for an example of importing an extension written with sqlite-loadable-rs into a Rust application (with rusqlite specifically).

Though do note, I've only been able to successfully do this on Linux/MacOS. I ran into some linking errors in Windows that I couldn't figure out.

Feel free to share the errors that you got when trying to statically link your extension. It can be tricky in general, there's a lot of CFLAGS and LDFLAGS you have to get right. I usually have to define -DSQLITE_CORE and -lsqlite_xxx (where I have a static file called libsqlite_xxx.a), but sometimes there's other flags needed for some platforms. And sometimes a -Wl,-undefined,dynamic_lookup for MacOS

[anacrolix]

Yeah it's quickly turning into a cesspool of linking issues when I try. 😢

[asg017]

Can you share some error messages that you see? Along with the platform you are compiling on (Macos/linux/arm etc.), the sqlite-loadable version you're on, and the compiler flags you've tried?

Also, does your Rust project have any additional dependencies that reply on other C libraries?

[anacrolix]

I think like @nyurik I haven't gone down the path of building on this crate. I have built on rusqlite. I believe the linker issues I'm having are not related to rusqlite. I'm currently making use of what is described in https://ricardoanderegg.com/posts/extending-sqlite-with-rust/ to create a dynamically loadable extension with rusqlite. I will be very interested to read your blog post if it talks about statically linking such an extension in to an executable. The branch of rusqlite referred to in the link has an example of statically linking an extension into a shared object that is loaded dynamically, it's not quite the same.

[anacrolix]

Here is the error if it means anything to you:

/Users/anacrolix/src/go1.21/pkg/tool/darwin_arm64/link: running clang++ failed: exit status 1
Undefined symbols for architecture arm64:
  "_sqlite3_api", referenced from:
      std::sync::once::Once::call_once::_$u7b$$u7b$closure$u7d$$u7d$::h1b8a0b94711ed9e1 in libdht_indexer.a(libsqlite3_sys-c5df21685f701ba8.libsqlite3_sys.61575ea2ee6fb45b-cgu.4.rcgu.o)
      core::ops::function::FnOnce::call_once$u7b$$u7b$vtable.shim$u7d$$u7d$::hea4cd6175a270b2a in libdht_indexer.a(libsqlite3_sys-c5df21685f701ba8.libsqlite3_sys.61575ea2ee6fb45b-cgu.4.rcgu.o)
      libsqlite3_sys::loadable_extension::loadable_extension_embedded_init::h57de3cfefb39c493 in libdht_indexer.a(libsqlite3_sys-c5df21685f701ba8.libsqlite3_sys.61575ea2ee6fb45b-cgu.4.rcgu.o)
     (maybe you meant: libsqlite3_sys::loadable_extension::loadable_extension_sqlite3_api::h128c8b56cbeba93d)
ld: symbol(s) not found for architecture arm64
clang: error: linker command failed with exit code 1 (use -v to see invocation)