Anton Zhiyanov
projects books blog about

How to install an SQLite extension

SQLite is a miniature but powerful in-process/single-file database engine. It has few built-in functions compared to PostgreSQL or Oracle. Fortunately, the authors have provided an extension mechanism that allows you to add almost any missing feature to SQLite.

Unlike other DBMS, adding extensions to SQLite is a breeze. Download a file, run a database command — and you are good to go. Still, sometimes people get confused, so I decided to write this guide.

To use an extension, you need to download and install it. Let's take a look at each of these steps.

Download: manually

So you've found an extension you want to try. Now you need to download it.

An extension in SQLite is a single file:

  • *.dll for Windows (e.g. stats.dll)
  • *.so for Linux (e.g. stats.so)
  • *.dylib for macOS (e.g. stats.dylib)

Download the file and store it locally. Let's use the Downloads folder:

  • %USERPROFILE%\Downloads on Windows (e.g. C:\Users\anton\Downloads)
  • ~/Downloads on Linux or macOS (e.g. /home/anton/Downloads or /Users/anton/Downloads)

You are now ready to install the extension!

I'll use the stats extension as an example; be sure to change the name accordingly when copying the commands.

Download: package manager

Personally, I'm not a fan of managing the extensions manually. I always tend to put them in different places and can't find them later. So I created sqlpkg — a package manager for SQLite extensions.

Use the install command to download the extension with sqlpkg.

Windows:

sqlpkg.exe install nalgeon/stats

Linux/macOS:

sqlpkg install nalgeon/stats

nalgeon/stats is the ID of the extension, you can find it in the extension hub. Check it out — it has plenty of extensions!

sqlpkg installs all extensions in the special folder:

  • %USERPROFILE%\.sqlpkg on Windows
  • ~/.sqlpkg on Linux/macOS

So for our nalgeon/stats extension it will be:

  • C:\Users\anton\.sqlpkg\nalgeon\stats\stats.dll on Windows
  • /home/anton/.sqlpkg/nalgeon/stats/stats.so on Linux
  • /Users/anton/.sqlpkg/nalgeon/stats/stats.dylib on macOS

Be sure to change the path accordingly in the examples below.

Install: Command-line interface

SQLite CLI, also known as SQLite shell, is a console interface (sqlite3.exe on Windows, sqlite3 on Linux/macOS).

Start it and load the extension with the .load command:

Windows:

.load c:/Users/anton/Downloads/stats

Linux/macOS:

.load /Users/anton/Downloads/stats

Now you can use the extension! For example, the stats extension adds the median and generate_series functions:

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:

xattr -d com.apple.quarantine /Users/anton/Downloads/stats.dylib

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

Install: GUI database browser

To load extension in SQLiteStudio, SQLiteSpy, DBeaver and other similar tools, use the load_extension function.

Windows:

select load_extension('c:\Users\anton\sqlite\stats');

Linux/macOS:

select load_extension('/Users/anton/Downloads/stats');

Install: Python

Use the default sqlite3 module to load the extension.

Windows:

import sqlite3

conn = sqlite3.connect(":memory:")
conn.enable_load_extension(True)
conn.load_extension(r"c:\Users\anton\sqlite\stats")
conn.execute("select median(value) from generate_series(1, 99)")
conn.close()

Linux/macOS:

import sqlite3

conn = sqlite3.connect(":memory:")
conn.enable_load_extension(True)
conn.load_extension("/Users/anton/Downloads/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.

Install: Node.js

Use the better-sqlite3 package.

Windows:

const sqlite3 = require("better-sqlite3");
const db = new sqlite3(":memory:");
db.loadExtension(`c:\Users\anton\sqlite\stats`);
db.exec("select median(value) from generate_series(1, 99)");
db.close();

Linux/macOS:

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

Install: Go

Use the mattn/go-sqlite3 package.

Windows:

package main

import (
    "database/sql"
    "fmt"

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

func main() {
    sql.Register("sqlite3_with_extensions",
        &sqlite3.SQLiteDriver{
            Extensions: []string{
                `c:\Users\anton\sqlite\stats`,
            },
        })

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

Linux/macOS:

package main

import (
    "database/sql"
    "fmt"

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

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

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

Note that we use the same identifier sqlite3_with_extensions in the sql.Register and sql.Open.

Next steps

If you feel you're missing a feature in SQLite, check the extension hub — you'll probably find one.

And try the package manager if you like console tools.

Be aware that both are fairly new and have some rough edges. But for the most part, they get the job done.

SQLite FTW!

 Subscribe to keep up with new posts.