Probing FTS5 Support in node:sqlite: A Field Guide to Graceful Degradation

Probing FTS5 Support in node:sqlite: A Field Guide to Graceful Degradation

A recall feature I had shipped with confidence died in a new environment with a
single line:

SQLITE_ERROR: no such module: fts5

The code was not wrong. The assumption was. I had assumed that because SQLite
was present, SQLite's full-text search engine was present too. With
node:sqlite — Node's built-in SQLite binding — that assumption is not
guaranteed.

This is the field guide I wish I had: how to probe for FTS5 honestly, how to
degrade to a deterministic fallback without pretending, and how to keep the
whole thing testable.

Why FTS5 Is Not Guaranteed

SQLite is compiled with compile-time options, and FTS5 is one of them
(SQLITE_ENABLE_FTS5). Every runtime that embeds SQLite makes its own choices
about which modules to enable. Node's built-in binding ships the build the
Node.js project maintains; other distributions — packaged runtimes, OS vendors,
minimal containers — make different choices to save size.

So "SQLite works" and "FTS5 works" are two separate claims. The cruel part: the
failure is lazy. Ordinary CREATE TABLE statements succeed, inserts succeed,
and the first CREATE VIRTUAL TABLE ... USING fts5(...) — possibly deep inside
a recall path in production — is where the engine finally says no.

Metadata Checks Can Lie

The tempting fix is a metadata check:

SELECT * FROM pragma_compile_options, then look for ENABLE_FTS5.

That is sometimes fine. But metadata describes the build, not the behavior your
wrapper will actually deliver: some embeddings expose restricted exec paths,
some proxy layers rewrite statements, and some builds report options
inconsistently. For a capability gate, I trust behavior over biography.
Probe by doing.

The Behavior Probe

Probe once, at startup, in a disposable in-memory database — never against your
real schema:

import { DatabaseSync } from "node:sqlite";

export type RecallBackend = "fts5" | "like";

let cached: RecallBackend | null = null;

export function detectBackend(
  factory: () => DatabaseSync = () => new DatabaseSync(":memory:"),
): RecallBackend {
  if (cached !== null) return cached;
  let probe: DatabaseSync | null = null;
  try {
    probe = factory();
    probe.exec("CREATE VIRTUAL TABLE _fts5_probe USING fts5(content)");
    probe.exec("INSERT INTO _fts5_probe (content) VALUES ('probe')");
    probe.exec("DROP TABLE _fts5_probe");
    cached = "fts5";
  } catch {
    cached = "like";
  } finally {
    probe?.close();
  }
  return cached;
}

Three details matter:

1. Probe in :memory:. A failed CREATE VIRTUAL TABLE leaves no residue, but a successful probe must never touch your real schema.
2. Exercise it, do not just declare it. Create, insert, drop. Some builds accept the declaration and fail on the first query.
3. Cache the verdict. Capability does not change mid-process; re-probing on every recall is wasted work and noisy logs.

The injectable factory is not decoration — it is how we will test the absent
path later.

Negotiating the Backend

With the verdict cached, the recall path becomes a negotiation instead of a
gamble:

export function recall(db: DatabaseSync, query: string, limit = 25) {
  if (detectBackend() === "fts5") {
    const stmt = db.prepare(
      `SELECT title, body, bm25(notes_fts) AS rank
         FROM notes_fts
        WHERE notes_fts MATCH ?
        ORDER BY rank
        LIMIT ?`,
    );
    return stmt.all(asFtsPhrase(query), limit);
  }

  const stmt = db.prepare(
    `SELECT title, body
       FROM notes
      WHERE body LIKE '%' || ? || '%' ESCAPE '\\'
      ORDER BY id
      LIMIT ?`,
  );
  return stmt.all(escapeLike(query), limit);
}

Two escaping rules keep user input as data:

• FTS5: the MATCH clause speaks a query DSL (", *, AND, NEAR...). Wrap the entire user string in double quotes and double any internal quotes, so a hostile or clumsy query becomes a harmless phrase.
• LIKE: % and _ are wildcards. Escape them (and the escape character), or every user query becomes a full-scan surprise.

function asFtsPhrase(q: string): string {
  return `"${q.replaceAll('"', '""')}"`;
}

function escapeLike(q: string): string {
  return q.replace(/[\\%_]/g, (ch) => `\\${ch}`);
}

The Fallback, Honestly

Let us be precise about what the LIKE path is: a capped, deterministic scan. A
leading-wildcard LIKE cannot use an ordinary index, so you are reading rows.
That is acceptable for tens of thousands of short documents and unacceptable
for millions — which is exactly why the LIMIT is non-negotiable and the
ordering (ORDER BY id) is fixed: same input, same output, every time, on
every build.

What you must never do is dress the fallback up as FTS5. Expose the verdict:

export function recallStatus() {
  const backend = detectBackend();
  return {
    backend,
    detail:
      backend === "fts5"
        ? "full-text search active (bm25 ranking)"
        : "fts5 module absent; capped LIKE scan active",
  };
}

Wire that into your health endpoint or startup banner. Operators deserve to
know which engine they are running; silent substitution is how "search got
worse" becomes a mystery.

Degrade or Fail-Closed? A Doctrine, Not a Mood

Graceful degradation is correct here because the blast radius is quality:
search becomes dumber, nothing becomes unsafe. The same probe pattern applied
to a security mechanism must invert: if you probe for a sandbox binary, a
crypto provider, or a permissions enforcer and it is absent, the only honest
answer is DENY. Degrade features; fail-closed controls. Confusing those two
postures is how "graceful" systems become exploitable ones.

Testing the Path You Hope Never Runs

The absent path is the one nobody exercises — until a new runtime exercises it
for you. Because detectBackend accepts a factory, the degraded branch is one
stub away:

test("recall degrades deterministically when fts5 is absent", () => {
  resetBackendCache();
  const stub = {
    exec: (sql: string) => {
      if (sql.includes("USING fts5")) throw new Error("no such module: fts5");
    },
    close: () => {},
  } as unknown as DatabaseSync;

  expect(detectBackend(() => stub)).toBe("like");
  // ...then assert recall() returns capped, stably-ordered rows
});

Test both branches on every run, on every CI matrix entry. The probe is only
as portable as the test matrix that proves it.

The Checklist

• Probe behavior, not metadata; create, insert, drop.
• Probe once at startup, in a throwaway database; cache the verdict.
• Ship a deterministic, capped fallback with fixed ordering.
• Escape MATCH and LIKE; user input is data, never query syntax.
• Publish the active backend in health/status output.
• Inject the probe's factory so the absent path is testable.
• Degrade features; fail-closed controls. Never swap the postures.

Closing Question

node:sqlite gives us an embedded database with zero dependencies — and a
portfolio of build-time choices we do not control. How do you handle optional
engine features in your projects: degrade loudly, fail closed, or bundle your
own SQLite and own the whole matrix? I would genuinely like to compare notes
in the comments.

1. _