> Complete content index: https://sirannon.sondelali.com/llms.txt

# SQLite extensions

> Load a compiled SQLite extension into every connection a database holds, including the ones it opens later, and learn which runtimes can load one at all.

URL: https://sirannon.sondelali.com/docs/extensions
Section: Core features
Version: 0.3 (@delali/sirannon-db@0.3.0, latest)

A compiled SQLite extension adds functions, virtual tables, or collations to the engine itself, so a query can call them the way it calls `length` or `json_extract`. `db.loadExtension(path)` loads one into a Sirannon database.

SQLite scopes a loaded extension to the connection that loaded it, and a Sirannon database holds several: one writer, a pool of readers, one connection per consistent snapshot read, and one for live queries. `loadExtension` therefore loads onto the writer, onto every reader, and onto every connection that database opens afterwards, so a query calling the extension's functions works whichever connection serves it.

This page builds one file, `extensions.ts`, step by step. You will load an extension you supply, read the paths Sirannon refuses before it touches the filesystem, and find out which runtimes can load one.

```bash
rm -rf data && mkdir -p data
```

## Load an extension

Put your compiled extension somewhere the process can read and name it on the call. The command below passes the path as an argument so that one file works for whichever extension you have.

```bash
npx tsx extensions.ts ./ext/uuid.dylib
```

```ts title="extensions.ts"

const driver = betterSqlite3()
const sirannon = new Sirannon({ driver })

const db = await sirannon.open('orders', './data/orders.db')

const extensionPath = process.argv[2]
if (extensionPath) await db.loadExtension(extensionPath)

await sirannon.shutdown()
```

A successful load returns `void`, so the file prints nothing of its own, and the functions your extension registers become callable through `db.query`, `db.execute`, and every other statement path from that moment on. What a query of them prints depends on the extension you loaded, so this page shows no panel for it.

The call takes the writer lock, so no write commits while it proceeds. Sirannon also takes loads and connection opens one at a time, so a connection opened while a load is in flight either loads that extension on its way in or is one of the connections that load reaches.

SQLite offers no call that unloads an extension. A load that fails part-way therefore leaves the extension on the connections it reached, and Sirannon reports the failure so that you know which state the database is in.

## The paths Sirannon refuses

Three shapes of path fail with `EXTENSION_ERROR` before Sirannon opens anything, because each one is a way for an attacker-supplied string to reach the dynamic linker. The loop below prints all three.

```ts title="extensions.ts"
import { Sirannon } from '@delali/sirannon-db' // [!code --]
import { Sirannon, type SirannonError } from '@delali/sirannon-db' // [!code ++]

const driver = betterSqlite3()
const sirannon = new Sirannon({ driver })

const db = await sirannon.open('orders', './data/orders.db')

const extensionPath = process.argv[2]
if (extensionPath) await db.loadExtension(extensionPath)

// [!code ++:13]
const refused: { code: string; message: string }[] = []

for (const candidate of ['', '../vendor/uuid.dylib', 'ext/\tuuid.dylib']) {
  try {
    await db.loadExtension(candidate)
  } catch (err) {
    const failure = err as SirannonError
    refused.push({ code: failure.code, message: failure.message })
  }
}

console.log(JSON.stringify({ extensions: driver.capabilities.extensions, refused }, null, 2))

await sirannon.shutdown()
```

```json result
{
  "extensions": true,
  "refused": [
    {
      "code": "EXTENSION_ERROR",
      "message": "Failed to load extension '': Extension path is empty or contains null bytes"
    },
    {
      "code": "EXTENSION_ERROR",
      "message": "Failed to load extension '../vendor/uuid.dylib': Extension path must not contain directory traversal segments"
    },
    {
      "code": "EXTENSION_ERROR",
      "message": "Failed to load extension 'ext/\tuuid.dylib': Extension path contains control characters"
    }
  ]
}
```

A bare name such as `uuid.dylib` passes those checks, and both Node drivers then resolve it against the process working directory before the runtime opens it. Sirannon resolves that name on purpose, because a bare name passed straight to the linker would let it search its own paths and open a different file of the same name. Sirannon refuses a driver that declares `extensions: true` and supplies no resolver for the same reason, and it refuses a resolver that returns a relative path.

A path that passes every check and names no file reaches the runtime, and the failure states the dynamic linker's own message, which names the file it tried and the directories it looked in. That message differs by operating system, so match on `EXTENSION_ERROR` and log the message without parsing it.

Keep the file under the name its author gave it. SQLite derives the entry point it calls from the file name, so `uuid.dylib` sends it looking for `sqlite3_uuid_init`, and renaming a library breaks a load that would otherwise have worked. SQLite also appends the platform's own suffix where the name you gave has none, which is why a missing file can appear in the linker's message with a doubled extension.

Sirannon loads through each runtime's own loading call, and both Node drivers keep the SQL `load_extension` function switched off, so a statement that calls it fails. The `node` driver opens its connections with extension loading available and switched off, and it switches loading on for the length of one call alone.

## Which runtimes can load one

`driver.capabilities.extensions` states whether a runtime loads compiled extensions at all, and the panel above prints `true` for `better-sqlite3`.

| Driver | Loads a compiled extension |
| --- | --- |
| `better-sqlite3` | Yes. |
| `node` | Yes, on a build of Node whose `node:sqlite` exposes `loadExtension` and `enableLoadExtension`. |
| `bun` | Yes. |
| `wa-sqlite` | No. It uses SQLite compiled to WebAssembly, which offers no dynamic loading call, so a browser loads no compiled extension. |
| `expo` | No. `expo-sqlite` offers no extension loading call, so a device running Expo loads no compiled extension. |

Each of the two refusals states which runtime refuses, so an application that runs the same code on a server and in a browser can tell a missing file from a runtime that could never load one.

## Extensions and the writer worker

A database with the [writer worker](/docs/writer-worker) turned on performs its writes on a worker thread, which holds a connection of its own. `loadExtension` reaches that connection like any other, and the worker records the resolved path.

That record matters when the worker restarts. A respawned worker opens a fresh connection, so Sirannon loads every recorded extension onto it before it accepts any work. Writes that depend on an extension's functions therefore keep working across a crash and a respawn.

A worker whose rebuilt driver opens connections without a loading call fails that reload with `EXTENSION_ERROR`, and the message says so, so Sirannon catches a driver that declares `extensions: true` and forgets the call at the respawn, before the next write reaches it.