better-sqlite3-expert

better-sqlite3-expert is a native Node.js addon that provides a loadable SQLite extension for query analysis and index recommendation. It wraps the built-in SQLite sqlite3expert utility (by D. Richard Hipp) and registers the expert_suggest() SQL function, which analyzes a given SQL query and returns CREATE INDEX statements for optimal indexes.

Installation

npm install better-sqlite3-expert

Or with pnpm:

pnpm add better-sqlite3-expert

During installation, node-gyp-build attempts to load a prebuilt binary from prebuilds/. If no matching binary is found, it falls back to compiling the extension from source.

Usage

The package exports a single function getLoadablePath() that returns the path to the compiled extension binary. Pass this path to your SQLite binding's .loadExtension() method.

With better-sqlite3

const Database = require('better-sqlite3');
const getLoadablePath = require('better-sqlite3-expert');

const db = new Database('my_database.db');

// Load the extension
db.loadExtension(getLoadablePath());

// Now the expert_suggest() function is available in SQL
const sql = `
  SELECT u.name, COUNT(o.id) as order_count
  FROM users u
  JOIN orders o ON o.user_id = u.id
  WHERE u.active = 1
  GROUP BY u.id
  ORDER BY order_count DESC
`;

const indexes = db.prepare('SELECT expert_suggest(?)').pluck().get(sql);
console.log(indexes);

With node-sqlite3

const sqlite3 = require('sqlite3');
const getLoadablePath = require('better-sqlite3-expert');

const db = new sqlite3.Database('my_database.db');

db.loadExtension(getLoadablePath(), (err) => {
  if (err) throw err;

  db.get(
    "SELECT expert_suggest(?, ?) AS result",
    ["SELECT * FROM users WHERE email = ?", 100],
    (err, row) => {
      if (err) throw err;
      console.log(row.result);
    }
  );
});

Via SQLite CLI

.load /path/to/expert.so sqlite3_expert_init
SELECT expert_suggest('SELECT * FROM users WHERE email = ?');

API

getLoadablePath()

Returns an absolute path to the compiled extension binary, with the .so extension stripped. The file is searched in the following order:

1. build/Release/expert.so
2. build/Debug/expert.so
3. prebuilds/{platform}-{arch}/expert.so

If none of these files exist, an error is thrown.

The module can be used in two equivalent ways:

const path = require('better-sqlite3-expert');
// or
const { getLoadablePath } = require('better-sqlite3-expert');

SQL function expert_suggest(SQL [, sample_percent])

Analyzes the provided SQL statement(s) and returns recommended CREATE INDEX statements.

Parameters:

• SQL (string, required) — One or more SQL statements to analyze.
• sample_percent (integer, optional, default 100) — The percentage of table rows used to generate sqlite_stat1 statistics:
  • 0 or less — no statistics generated; indexes are recommended based on the database schema only.
  • 1–99 — use the specified percentage of rows for sampling.
  • 100 (default) — full scan of all rows.

Return value:

• A string containing CREATE INDEX statements (one per line) if indexes are recommended.
• -- no indexes suggested if no indexes are needed.

How It Works

1. The native addon is compiled into a shared library (expert.so) via node-gyp from the source files in the native/ directory.
2. On npm install, node-gyp-build tries to load a matching prebuilt binary. If none is found, it automatically builds the extension from source.
3. When loaded as a SQLite extension, the expert_suggest() SQL function becomes available.
4. Internally, the function uses sqlite3expert to:
   • Inspect the database schema.
   • Enumerate candidate indexes for the given query.
   • Evaluate each candidate using EXPLAIN QUERY PLAN.
   • Return only those indexes that improve the query execution plan.

Supported Platforms

The package.json officially declares support for:

• OS: macOS (darwin), Linux, Windows
• CPU: x64, arm64

Prebuilt binaries are currently provided only for linux-x64. For other platforms, the extension will be compiled from source during installation.

Development

Prerequisites

• node-gyp build tools (Python, C/C++ compiler, etc.)

Downloading SQLite sources

Before building from source, you may need to download the SQLite amalgamation and the sqlite3expert source files:

npm run download

This downloads sqlite3.h, sqlite3ext.h, sqlite3expert.c, and sqlite3expert.h from the official SQLite repository into native/sqlite3/.

Building from source

git clone https://github.com/foul11/better-sqlite3-expert.git
cd better-sqlite3-expert

# Install dependencies
npm install

# Build the native addon
npx node-gyp rebuild

Prebuilding (cross-compilation)

npm run prebuild

The custom scripts/prebuild.js script compiles the extension using node-gyp and strips the resulting binary. Currently only linux-x64 is configured. The output is placed into prebuilds/{platform}-{arch}/.

To add another target, edit scripts/prebuild.js and uncomment the desired platform entry.

Project Structure

better-sqlite3-expert/
├── index.js                    # Entry point — getLoadablePath()
├── binding.gyp                 # node-gyp configuration
├── package.json
├── native/
│   ├── expert_extension.c      # SQL function registration (expert_suggest)
│   ├── expert_extension.h      # Header file
│   └── sqlite3/
│       ├── sqlite3expert.c     # sqlite3expert source code
│       ├── sqlite3expert.h     # sqlite3expert header
│       ├── sqlite3ext.h        # Loadable extension API
│       └── sqlite3.h           # SQLite header
├── scripts/
│   ├── prebuild.js             # Prebuild script (node-gyp based)
│   └── download.sh             # SQLite amalgamation downloader
└── prebuilds/                  # Prebuilt binaries
    └── linux-x64/
        └── expert.so

License

MIT

Copyright © 2026 foul11