SQLite

NSQLite is a high-performance NStore backed by SQLite. It works with any SQLite library, and indexes tags as full-text tokens rather than b-tree rows — there is no tags table and there are no joins over one.

It requires an FTS5-enabled SQLite of at least 3.43 (2023), for contentless full-text tables that support deletion. node:sqlite, better-sqlite3 and bun:sqlite all ship something newer.

Installation

Install @nostrify/sqlite:

npm

npm install @nostrify/sqlite

yarn

yarn add @nostrify/sqlite

pnpm

pnpm add @nostrify/sqlite

Bun

bun add @nostrify/sqlite

Usage

NSQLite has no dependency on a SQLite library. Give it an object with run and all and it works with whatever driver you already have. Either method may be synchronous or return a promise.

import { DatabaseSync } from 'node:sqlite';
import { NSQLite } from '@nostrify/sqlite';

const sqlite = new DatabaseSync('events.db');

const db = new NSQLite({
  run: (sql, params = []) => void sqlite.prepare(sql).run(...params),
  all: (sql, params = []) => sqlite.prepare(sql).all(...params),
  close: () => sqlite.close(),
});

await db.migrate(); // create the tables and indexes

migrate() is safe to call on every startup.

Insert an event

await db.event(event);

Query events

const events = await db.query([{ kinds: [1], limit: 20 }]);

Count events

const { count } = await db.count([{ kinds: [1] }]);

Remove events

await db.remove([{ kinds: [1] }]);

Drivers

Any library that can run a SQL string with bound parameters will work.

node:sqlite

import { DatabaseSync } from 'node:sqlite';

const sqlite = new DatabaseSync('events.db');

const db = new NSQLite({
  run: (sql, params = []) => void sqlite.prepare(sql).run(...params),
  all: (sql, params = []) => sqlite.prepare(sql).all(...params),
  close: () => sqlite.close(),
});

better-sqlite3

import BetterSQLite3 from 'better-sqlite3';

const sqlite = new BetterSQLite3('events.db');

const db = new NSQLite({
  run: (sql, params = []) => void sqlite.prepare(sql).run(...params),
  all: (sql, params = []) => sqlite.prepare(sql).all(...params),
  close: () => sqlite.close(),
});

bun:sqlite

import { Database } from 'bun:sqlite';

const sqlite = new Database('events.db');

const db = new NSQLite({
  run: (sql, params = []) => void sqlite.prepare(sql).run(...params),
  all: (sql, params = []) => sqlite.prepare(sql).all(...params),
  close: () => sqlite.close(),
});

@db/sqlite

import { Database } from '@db/sqlite';

const sqlite = new Database('events.db');

const db = new NSQLite({
  run: (sql, params = []) => void sqlite.prepare(sql).run(...params),
  all: (sql, params = []) => sqlite.prepare(sql).all(...params),
  close: () => sqlite.close(),
});

Caching prepared statements

NSQLite issues a small set of statement shapes with bound parameters, so a driver can cache prepared statements by SQL text and avoid re-parsing them. This is worth doing:

const statements = new Map<string, Statement>();

function prepare(sql: string) {
  let statement = statements.get(sql);
  if (!statement) {
    statement = sqlite.prepare(sql);
    statements.set(sql, statement);
  }
  return statement;
}

const db = new NSQLite({
  run: (sql, params = []) => void prepare(sql).run(...params),
  all: (sql, params = []) => prepare(sql).all(...params),
});

Recommended pragmas

For a durable on-disk store:

sqlite.exec('PRAGMA journal_mode = WAL');
sqlite.exec('PRAGMA synchronous = NORMAL');

ANALYZE is not needed. Every scan names its index or fixes its join order, so plans don't depend on SQLite's cost estimates.

Write batching

NSQLite collects events handed to it without an intervening await and commits them in one transaction, as a handful of multi-row INSERTs. The contract is unchanged — the promise from event() still resolves only once that event is durably committed — but per-event overhead is amortized across the batch.

// One transaction, not 500.
await Promise.all(events.map((event) => db.event(event)));

On 50k events, disk-backed with WAL and synchronous = NORMAL, that is 281µs an event against 823µs for awaiting each in turn — awaiting gives batches of one, since there is only ever a single event in flight to batch.

Options

const db = new NSQLite(sqlite, {
  indexTags: (event) => event.tags.filter(([name]) => name === 'e' || name === 'p'),
  tablePrefix: 'nostr',
  onQuery: (sql, params) => console.log(sql, params),
});

• indexTags — which tags to index, and therefore which are queryable. Defaults to every single-letter tag with a non-empty value under 200 characters. A filter on a tag the policy doesn't index simply matches nothing, and changing the policy only affects events written afterwards.
• tablePrefix — prefix for the table names the store creates. Defaults to nostr.
• onQuery — called with every statement executed, for debugging.

How it works

There is no tags table. An event's tags are flattened into a string of opaque tokens (e:<id>, p:<pubkey>, t:nostr), and FTS5's tokenizer is configured so each one is a single indivisible token. {"#e": [id]} is then a full-text match for the word e:<id>, and {"#t": [a, b], "#p": [c]} is one MATCH — (t:a OR t:b) AND p:c — that FTS5 answers by merging sorted posting lists in C.

That is the whole idea: intersecting tag terms is what an inverted index does, while a b-tree can only ever drive on one of them and has to intersect the rest by hand. Posting lists are delta-encoded varints too, so an indexed tag costs a byte or two rather than a whole b-tree entry — an event with 300 tags is one insert of one row, not 300 index inserts.

The problem with using FTS5 this way is ordering: Nostr wants newest-first with a small limit, and FTS5 only yields rows in rowid order. So rowid is time:

rowid = created_at × 2²⁰ + a per-second sequence number

ORDER BY rowid DESC is then ORDER BY created_at DESC, which FTS5 satisfies by walking its posting lists backwards with no sorter, and since/until become a rowid range it pushes down into that walk.

The encoding pays off outside the index too. The events table is clustered by time, so the created_at DESC index a conventional schema needs is the table; (kind) and (pubkey) are implicitly (kind, created_at) and (pubkey, created_at), since SQLite appends the rowid to every index entry; and a candidate key is one integer whose event is a seek into the table b-tree rather than into a 64-character text index.

Storage layout

• nostr_events is the value store, keyed by the time-encoded seq rowid. kind, pubkey and created_at are lifted out of the JSON so they can be indexed and tested without deserializing, and coord carries the replaceable/addressable coordinate under a partial unique index — so "one live version per coordinate" is an invariant SQLite enforces, and deleting an event takes its coordinate with it.
• nostr_tags_fts is the tag index: one row per event, holding its tag tokens plus _p:<pubkey>. Contentless and detail=none, which reduces FTS5 to a bare inverted index — no positions, no column tags, no copy of the text.
• nostr_events_fts is NIP-50 search over content, tokenized for prose and kept in step by triggers rather than by application code, so no write path can forget it.

Planning

A filter is planned with strfry's DBScan priority cascade — ids, then tags or search, then pubkey+kind, pubkey, kind, and finally the whole store — and the chosen b-tree is forced with INDEXED BY. Scans driven by the token index instead make it the driving table of a CROSS JOIN against the events table.

Both of those pin the plan, deliberately. CROSS JOIN is SQLite's one way to fix a join order, and without it a condition on the events table is enough to make the planner drive from there — seeking the index by rowid once per row, re-evaluating the MATCH every time, and sorting the result through a temp b-tree. The conditions go in the WHERE and the LIMIT comes last, so SQLite walks the posting lists backwards and stops as soon as the limit is filled with rows that survived everything. A complete plan is one statement; anything the index can't express is matched in memory, with the scan paged by keyset so memory stays bounded.

What the index doesn't carry

A posting list is only worth intersecting when it's short, and read backwards FTS5 walks a term's list in full — so a term matching a large share of the store costs its whole length however small the answer. Kinds are exactly that: there are only a handful in use. So kinds (and authors, unless a tag is already driving) are tested on the event rows the index finds, which is a column read on a row that was going to be fetched anyway, and measured 2–3x faster than intersecting a _k: posting list.

Tag values are encoded rather than stored raw: tokenizers split on punctuation and fold case, so a value is embedded verbatim only when it's lowercase alphanumeric — which ids, pubkeys and most topics are — and anything else is hex-escaped, keeping matches exact for values with spaces, capitals, emoji or URLs in them.

automerge is turned down to its minimum. Every commit leaves an FTS5 segment behind, and a query with N terms opens an iterator per term per segment, so a store written an event at a time — as a relay writes — answers a many-term filter several times slower than the same data bulk-loaded. Turning FTS5's incremental defrag up measured free on writes and 4x faster on a 100-term filter.

Search

NIP-50 search is answered from an FTS5 index that triggers keep in step with the events table. Every keyword must appear in the event's content, and a -keyword token excludes it; unsupported extension tokens (key:value) are ignored per the NIP.

Keywords match whole words, case- and accent-insensitively, so nostr matches "nostr" but not "nostrich", and cafe matches "café". Whatever a user types is passed as a literal phrase, so a keyword like OR or ( searches for that word rather than being read as query syntax.

Because rowids are timestamps, the index yields a keyword's matches newest-first and the scan simply stops at the limit, whether the keyword matches ten events or a million. Search is bounded by the number of matches rather than by the size of the database, so a term matching nothing is the cheapest search there is rather than the dearest.

Keywords alongside a tag filter are a second index, and FTS5 can't merge two of them, so they're resolved to a rowid set the tag-driven scan tests against.

Performance

Measured with better-sqlite3 on a synthetic dataset of 200k events: 500 authors and 200 topics drawn from a Zipf distribution, a tenth of the events replaceable or addressable, on disk with WAL, ingested 500 at a time.

Query	per call
by kind, limit 20	0.23 ms
by author, limit 20	0.19 ms
by author + kind, limit 20	0.22 ms
by tag, limit 20	0.31 ms
by tag + kind, limit 20	0.36 ms
by two tags, limit 20	0.41 ms
by tag + author + kind, limit 20	0.46 ms
addressable by #d	0.12 ms
by search, limit 20	0.64 ms
feed (500 authors × 2 kinds)	4.97 ms
by tag, 100 values, limit 20	5.56 ms
count by tag	4.55 ms
count by kind	7.05 ms
by search + tag, limit 20	14.30 ms
by tag, no limit (a sixth of them)	317 ms

Storage is 198 MB, about 1 KB per event, of which the events table — the JSON bodies — is 127 MB. The tag index is 3.3 MB, or about 17 bytes per event to cover all of its tags.

The two unbounded rows are the shape to design around rather than a cost to avoid: a query with no limit that matches a sixth of the store has to materialize a sixth of the store, and count has to walk what it counts. Both are linear in the answer, not in the database.

Both benchmarks live in the package:

deno bench -A --no-check NSQLite.bench.ts                    # the 1000-event fixture
deno run -A --no-check scale.bench.ts 200000 --real --burst  # a synthetic dataset

Behavior

• Replaceable and addressable events supersede older versions at the same coordinate on write, so queries never return a stale profile or list.
• Deletion requests (kind 5) delete the events they target, but only when the requester authored them. A deleted event can't be re-added — the attempt throws a RelayError.
• Ephemeral events are never stored.
• NIP-50 search is answered from a full-text index — see Search.
• NIP-40 expiration is not handled, and nothing is pruned automatically.