Making SQLite extensions pip install-able | Hacker News Reader

Making SQLite extensions pip install-able

observablehq.com

161 pointssimonw16 comments

Author here, happy to answer questions!

Simon already gave a great intro to my SQLite extensions, and I just want to point folks to Anton Zhiyanov's sqlean project, for pure-C extensions: https://github.com/nalgeon/sqlean

Also, some of these extensions are also packaged as Datasette plugins, so you can also run `datasette install datasette-sqlite-regex` or `datasette install datasette-sqlite-ulid` to add these extension to your Datasette instances! https://docs.datasette.io/en/stable/plugins.html

Have you looked into cross-compiling those arm targets using Docker and Github Actions? I'm not familiar with the toolchain you have to use but other projects do that.

I've tried it! For Rust SQLite extensions, I'm able to use some cross compilers for some extra ARM targets on linux[0], I just haven't had the time to do it for all extensions yet. For MacOS arm builds cargo has the `aarch64-apple-darwin` target which works, but is a bit slower than expected at runtime. I've also tried zig for cross compiling C extensions on different platforms, which seemed to work ok but I've heard of some perf issues cropping up with those.

[0] https://github.com/asg017/sqlite-loadable-rs/blob/main/.gith...

Nice!

Is Spatialite supported?

Not yet! Spatialite is a very big and intimidating codebase that's hard to compile. I've been mulling the idea of creating several smaller GIS sqlite extensions, especially since Spatialite development is starting to get stale, but nothing to share for now.

Yes, pip installing a GIS system would be awesome!

This is such a huge step forward for the usability of custom SQLite extensions in Python world.

Alex has a whole family of SQLite extensions that he's built in Rust which are worth checking out:

- sqlite-xsv: https://github.com/asg017/sqlite-xsv

- sqlite-fastrand: https://github.com/asg017/sqlite-fastrand

- sqlite-ulid: https://github.com/asg017/sqlite-ulid

- sqlite-jsonschema: https://github.com/asg017/sqlite-jsonschema

- sqlite-regex: https://github.com/asg017/sqlite-regex

- sqlite-url: https://github.com/asg017/sqlite-url

- sqlite-http: https://github.com/asg017/sqlite-http

- sqlite-lines: https://github.com/asg017/sqlite-lines

- sqlite-path: https://github.com/asg017/sqlite-path

- sqlite-html: https://github.com/asg017/sqlite-html

So how soon do we get sqlite extensions in the browser with WASM?

It's possible but very difficult. For extensions built purely in C, you can statically compile extensions into a SQLite WASM build, which I have a few demos of with sqlite-lines [0] and sqlite-path[1].

For extensions but in Rust however, it's much more difficult. Matt @tantaman has some success cross compiling his cr-sqlite [2] project to WASM, but it's quite complex.

SQLite extensions typical rely on dlopen() to load dynamic libraries as an extension. WASM doesn't really have that, so you either have to statically compile your extension in your WASM build (which is difficult for non-C languages bc SQLite is written in C), or hack around some barely-supported WASM features that emulate dlopen(). Though I'm not the best with WASM, so hopefully someone with more WASM experience chimes in to help! It's something I'm tracking in this issue for the `sqlite-loadble-rs` project [3]

[0] https://observablehq.com/@asg017/introducing-sqlite-lines#ce...

[1] https://observablehq.com/@asg017/introducing-sqlite-path#cel...

[2] https://github.com/vlcn-io/cr-sqlite

[3] https://github.com/asg017/sqlite-loadable-rs/issues/5

Alex has developed a framework that allows him to write SQLite extensions in Rust instead of C. Very clever! This way, he can be productive and avoid a lot of potential bugs while keeping the raw performance.

As a maintainer of a large set of C SQLite extensions, I envy him :)

Does anybody know how feasible this would be with rubygems? Gems can build native extension so maybe possible?

I haven't worked much with Ruby, but would love to try it! These python package don't even need to compile native extensions, the pre-compiled SQLite extension just gets stuffed into the package under `package_data`.

For ruby gems, do you know if it's possible to include arbitrary files into a gem?

If you have the binary all ready, you could mimic this ancient gem that bundles pdftk binaries (for use with heroku):

https://github.com/charliemaffitt/pdftk-heroku

But I think it might be possible to leverage gem compile, maybe even with extensions for cross compiling.

You'd need to specify the platform to match your binaries, and those as files (for extensions) or binaries (for executables you want added to path).

https://github.com/luislavena/gem-compiler

https://github.com/rake-compiler/rake-compiler-dock

AFAIK when a gem is compiled, a makefile or a rake-file is invoked - and can be made to do just about anything. Add some ruby in the gemspec - and it should be possible to distribute almost anything as a platform specific gem.

Ed: note that sqlite3 for ruby is an example of how to build and distribute pre-compiled, native ruby extensions[1]. I suppose it might be possible to just fork it, and massage the build to spit out/include dlls (via cargo build or whatever).

That would be doing much more than creating a sqlite3-some_ext gem that depends on the sqlite3 gem, though.

I guess if you just wrapped some extensions in a gem, there would be some way to find them on the filesystem / append to a relevant path setting after install?

[1] https://github.com/sparklemotion/sqlite3-ruby/pull/320

It can be done with Ruby gems -- they have the same flexibility as regards native code, scriptable compile steps, included binaries, &al, as Python eggs do.

If you are interested in pip installable versions of SQLite's spellfix and ICU extensions, have a look at https://github.com/karlb/sqlite-spellfix and https://github.com/karlb/sqlite-icu.

In contrast to the submission, these can be installed even when no pre-built wheel is available (but ICU requires the ICU-dev package in that case, which limits the advantage). Since these are based on the official SQLite extensions written in C, this is much easier to achieve than with the rust extensions in the submission.

Side note: it seems I am quickly developing a disaffection to ML-generated blog poster images.

Reply on news.ycombinator.com