The ultimate SQLite extension set

I really like SQLite. It's a miniature embedded database, perfect for both exploratory data analysis and as a storage for small apps (I've blogged about that previously).

It has a minor drawback though. There are few built-in functions compared to PostgreSQL or Oracle. Fortunately, the authors provided an extension mechanism, which allows doing almost anything. As a result, there are a lot of SQLite extensions out there, but they are incomplete, inconsistent and scattered across the internet.

I wanted more consistency. So I started the sqlean project, which brings the extensions together, neatly packaged into domain modules, documented, tested, and built for Linux, Windows and macOS. Something like a standard library in Python or Go, only for SQLite.

I plan to write in detail about each module in a separate article, but for now — here's a brief overview.

Main set

These are the most popular functions. They are tested, documented and organized into the domain modules with clear API.

Think of them as the extended standard library for SQLite:

• crypto: Hashing, encoding and decoding data.
• define: User-defined functions and dynamic SQL.
• fileio: Reading and writing files and catalogs.
• fuzzy: Fuzzy string matching and phonetics.
• ipaddr: IP address manipulation.
• math: Math functions.
• regexp: Pattern matching using regular expressions.
• stats: Math statistics — median, percentiles, etc.
• text: Powerful Unicode-aware string functions.
• time: High-precision date/time.
• uuid: Universally Unique IDentifiers.
• vsv: CSV files as virtual tables.

The single-file sqlean bundle contains all extensions from the main set.

There are precompiled binaries for Windows, Linux and macOS.

How to install an extension

The easiest way to try out the extensions is to use the pre-bundled shell. But you can also load them individually.

First, download the extension. Then load it as described below.

Examples use the stats extension; you can specify any other supported extension. To load all extensions at once, use the single-file sqlean bundle.

Command-line or IDE

SQLite command-line interface (CLI, aka 'sqlite3.exe' on Windows):

sqlite> .load ./stats
sqlite> select median(value) from generate_series(1, 99);

IDE, e.g. SQLiteStudio, SQLiteSpy or DBeaver:

select load_extension('c:\Users\anton\sqlite\stats.dll');
select median(value) from generate_series(1, 99);

Note for macOS users. macOS may disable unsigned binaries and prevent the extension from loading. To resolve this issue, remove the extension from quarantine by running the following command in Terminal (replace /path/to/folder with an actual path to the folder containing the extension):

xattr -d com.apple.quarantine /path/to/folder/stats.dylib

Also note that the "stock" SQLite CLI on macOS does not support extensions. Use the custom build.

Python

Use the sqlite3 module to load an extension:

import sqlite3

conn = sqlite3.connect(":memory:")
conn.enable_load_extension(True)
conn.load_extension("./stats")
conn.execute("select median(value) from generate_series(1, 99)")
conn.close()

Note that the "stock" SQLite on macOS might not support extensions, so this method might not work.

Node.js

Using better-sqlite3:

const sqlite3 = require("better-sqlite3");
const db = new sqlite3(":memory:");
db.loadExtension("./stats");
db.exec("select median(value) from generate_series(1, 99)");
db.close();

Go

Using mattn/go-sqlite3:

package main

import (
    "database/sql"
    "fmt"

    sqlite3 "github.com/mattn/go-sqlite3"
)

func main() {
    sql.Register("sqlite3_ext",
        &sqlite3.SQLiteDriver{
            Extensions: []string{"./stats"},
        })

    db, err := sql.Open("sqlite3_ext", ":memory:")
    db.Query("select median(value) from generate_series(1, 99)")
    db.Close()
}

Next steps

If you feel that you are missing some function in SQLite, check the sqlean repository — you'll probably find one.

★ Subscribe to keep up with new posts.