Anton Zhiyanov
projects books blog about

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.

Other extensions

There are a lot of useful extensions that do not quite fit the scope for various reasons. To learn more about them, visit the SQLite extension hub.

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

Install the sqlean.py package, which is a drop-in replacement for the default sqlite3 module:

pip install sqlean.py

All extensions from the main set are already included:

import sqlean
sqlean.extensions.enable_all()

conn = sqlean.connect(":memory:")
conn.execute("select median(value) from generate_series(1, 99)")
conn.close()

You can also use the default sqlite3 module and load extensions manually:

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 for macOS users. "Stock" SQLite on macOS does not support extensions, so the default sqlite3 module won't work. Use the sqlean.py package.

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.

Or check out the extension hub, which contains even more extensions.

SQLite FTW!

★ Subscribe to keep up with new posts.