Utility Extensions for SQLite

This repo contains a set of utility extensions. At the time of writing, it contains two main extensions: decimal and ltree. The decimal extension offers support for large decimal numbers in order to support numerically precise accounting. Inspired by PostgreSQL's ltree extension, the ltree extension contains functions that represent TEXT data as a single-branch hierarchical tree. It differs slightly from Postgres's ltree extension in a few major ways. First, its syntax lets single labels contain dots (which are used as separators between labels/nodes). (This obviously comes at a slight performance cost for the benefit of slightly more flexibility.) Second, it does not contain anything similar to lquery or ltxtquery. Instead, I recommend that you use SQLite's FTS5 extension instead. (There are examples of this usage in the docs directory and in the e2e-tests directory.)

Why this exists

I built this extension (or "set" of extensions) for two main reasons. First, I built it to support a budgeting application I've started building for my personal use. Second, I built it as a bit of an experiment in vibe coding (mostly trying to figure out how I can use AI to generate robust and maintainable code). Claude Opus 4.5 wrote almost all of the source code and about three-quarters of the tests in this repository, while I wrote a set of initial tests and most of the documentation. I had to steer Claude into generating some of the code (by writing and documenting some unimplemented APIs and by reminding it not to unnecessarily collect iterators into Vec), but with clear documentation and testing standards, I was able to get Claude to basically generate all of this code a bit more quickly than I would have been able to write it manually.

Installation

In order to install this extension, you will want to download the binary for your architecture from Releases. Once you have downloaded the binary and moved it to a desired location (e.g. ~/.sqlpkg/sqlite-util-extensions), you can load the extension as you would any other SQLite extension. (For instance, from the sqlite3 CLI: .load ~/.sqlpkg/sqlite-util-extensions.)

Usage

Detailed usage is in the docs directory.

Development

In order to develop this library, you will need to have Rust installed on your computer. This application targets the latest stable release of Rust; you may be able to get it to compile with earlier versions.

Run your typical Rust commands to build, test, and lint the code:

cargo build # build the binary
cargo build --release # build the binary in release mode
cargo test # Run unit tests
cargo fmt # Format the code
cargo check # Check for errors without building
cargo clippy -- -D warnings # Lint the code

End-to-End Tests

The end-to-end tests are in a separate Rust library in the e2e-tests directory. To run them, you will either need to cd into that directory or run

cargo test --manifest-path e2e-tests/Cargo.toml

from the root directory.