How to load extensions into SQLite?

Question

I need a standard deviation function in SQLite. I have found one here:

http://www.sqlite.org/contrib?orderby=date

but its part of an extension file to SQLite. I've never installed one of these before and I don't know how to. I found this existing function, load_extension, at http://www.sqlite.org/lang_corefunc.html, but I don't understand what the parameters X and Y are.

Basically, I need someone to give me a step by step guide on how to install the aggregate extension file. Can anyone do this?

Oni

Answer 1

SQLite extensions are libraries with dynamic linkage. You can find some examples here (This is a fossil repository, click on “login/fill captcha” to enable hyperlinks). See for example md5.c.

• load_extension must be enabled in SQLite (pragma IIRC)
• it requires as first argument the path of the library
• The second argument is the name of the entry point function (in md5.c it is sqlite3_extension_init). Its prototype must be int(sqlite3*, char **, const sqlite3_api_routines *).
• In SQL you can try SELECT load_extension('md5.so', 'sqlite3_extension_init'); or simply SELECT load_extension('md5.so');

You can try to compile md5.c, and from the sqlite shell use .load md5.so

Eras

I am currently having a similar question. I am trying to execute SELECT load_extension('mod_spatialite') in sqlite, I have the corresponding mod_spatialite.dll - file, but I can't get it to work. Where do I need to put the .dll-file? – Incubate

@Incubate Are you using full path to the .dll in load_extension() statement? If so, try not to do so. I realised that the deduction of extension entry point name is not working with Windows-style paths. – Remote

@Incubate i have the same problem. how you solved it? – Precursory

@Incubate Same problem here! Any resolution? – Sharper

Answer 2

You can follow loadext.html:

1. Download the .c file of the extension
2. Compile the .c file locally using commands from Section 3, "Compiling A Loadable Extension" in loadext.csv (check a related SO question if you have compilation errors)
3. Load the library via sqlite3_load_extension(PATH) (.load PATH in the CLI or SELECT load_extension(PATH) in queries), where PATH is a relative or full path to the compiled library, preferably without filename extension for compatibility reasons (i.e., .load csv, not .load csv.dylib or whatever extension your system adds)

For extension-functions on MacOS, it would be:

curl --location --output extension-functions.c 'https://www.sqlite.org/contrib/download/extension-functions.c?get=25'
gcc -g -fPIC -dynamiclib extension-functions.c -o extension-functions.dylib

Testing the extension:

SELECT load_extension("extension-functions");
CREATE TABLE test(x REAL);
INSERT INTO test (x) VALUES
  (1),
  (2),
  (3),
  (4),
  (5);
SELECT stdev(x) FROM test;

Expected output: 1.58113883008419

Also Larry Brasfield at sqlite.org explains where you can put the compiled library to make it accessible across the system:

If you provide an absolute path in the .load command or to the sqlite3_load_extension() call, or to the load_extension() SQLite function from SQL (when extension loading is enabled), then the extension specified by that path will be loaded (if it can be.)

If you want to name the extension to one of the load mechanisms without an absolute path, then you must arrange that the compiled extension can be located by the search that dlopen(...) conducts. That can vary, but usually it examines directories specified by an environment variable, LD_LIBRARY_PATH, in the order listed by its colon-separated values left-to-right, then /lib and /usr/lib.

So, you can put your extension somewhere already searched, or you could use the "env" command to launch the SQLite CLI with a modified value for $LD_LIBRARY_PATH that includes where you put the extension. Or you might simply modify that environment variable for a session in which you are using the CLI. (This can lead to subtle problems, so be sure you understand the potential effects going into that.)

Poorhouse

Answer 3

I wrote a detailed guide on how to install a SQLite extension, but here is a TLDR (1-2 are about getting the extension, 3-7 are about loading it into SQLite):

1. You can find a pre-compiled extension for your operating system (aka "package") in the SQLite package registry.

The standard deviation function is available in the nalgeon/stats package.

2. Alternatively, you can download a .c file and compile it using gcc:

gcc -fPIC -shared extension.c -o extension.so

3. For SQLite CLI (sqlite3.exe), use the .load command (I'll use the stats extension as an example from here on):

.load /Users/anton/Downloads/stats

4. For SQLiteStudio, SQLiteSpy, DBeaver and other similar tools, use the load_extension function:

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

5. For Python, use the default sqlite3 module:

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()

Alternatively, use the sqlean.py package.

6. For Node.js, use the better-sqlite3 package:

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();

7. For Go, use the mattn/go-sqlite3 package:

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()
}

And that's it!

Dairymaid

Answer 4

Here is the complete way to do this on Windows:

1. Install MinGW with GCC
2. Download the .c file of wanted extension (for example uuid.c)
3. Download from this page Source Code and Precompiled Binaries for Windows
4. Put files from "sqlite-dll-win64-x64-XXXXXXX.zip" (or win32) and "sqlite-tools-win32-x86-XXXXXXX.zip" to the System32 folder
5. Put wanted uuid.c file and files from "sqlite-amalgamation-XXXXXXX.zip" to any folder you want to
6. Run CMD and go to the folder with uuid.c file
7. Type gcc -g -shared uuid.c -o uuid.so
8. If someone (even yourself) told you that on Windows you should use .dll extension, spit in this person's face
9. Shove the resulting .so file into the folder System32
10. Run CMD again and type .load uuid OR type SELECT load_extension('uuid') as SQL script

You're done here!

@James Kerfoot, I mention you as my "contemporary". So, you have to compile .c file into .so and put into the System32 folder

Amazing

It absolutely doesn't have to have .so extension in Windows -- .dll is absolutely fine, and you absolutely don't need MinGW or GCC to compile it, Visual C++ compiler does an excellent job. – Outgeneral