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
• Install: Command-line interface
• Install: GUI database browser
• Install: Python
• Install: Node.js
• Install: Go

Download

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.

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 might not support extensions.

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 that the "stock" SQLite on macOS might not support extensions, so this method might not work.

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_ext",
        &sqlite3.SQLiteDriver{
            Extensions: []string{
                `c:\Users\anton\sqlite\stats`,
            },
        })

    db, err := sql.Open("sqlite3_ext", ":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_ext",
        &sqlite3.SQLiteDriver{
            Extensions: []string{
                "/Users/anton/Downloads/stats",
            },
        })

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

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

★ Subscribe to keep up with new posts.