SQL Databases

Nostrify can store events in a variety of SQL databases thanks to Kysely.

NOTE

We recommend using Nostrify with Postgres, which is the fastest and most complete implementation. This page is about the NDatabase class, which works with many SQL databases, but suffers in performance at scale.

Installation

Install @nostrify/db from JSR.

npm

npx jsr add @nostrify/db

Deno

deno add @nostrify/db

yarn

yarn dlx jsr add @nostrify/db

pnpm

pnpm dlx jsr add @nostrify/db

Bun

bunx jsr add @nostrify/db

Usage

NDatabase implements NStore, allowing you to use it interchangeably with relays.

First create a Kysely instance and connect it to whichever database you choose, then pass it to NDatabase.

import { NDatabase } from '@nostrify/db';
import { Kysely } from 'kysely';

const kysely = new Kysely(/* set up your database */);

const db = new NDatabase(kysely);
await db.migrate(); // create the database tables

Insert an event

await db.event(event);

Query events

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

Count events

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

Remove events

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

Full text search

NDatabase supports NIP-50 search with the fts option:

const db = new NDatabase(kysely, {
  fts: 'sqlite',
});

• sqlite uses the built-in SQLite FTS5.

Search filters

Once enabled, you can query with search filters:

const events = await db.query([{ kinds: [1], search: 'hello world' }]);

NOTE

If FTS is not enabled, the search filter will always return an empty array.

Custom tag indexes

By default, NDatabase will index all single-letter tags. For more control, add a custom indexTags function:

const db = new NDatabase(kysely, {
  indexTags(event: NostrEvent): string[][] {
    // Return the tags that you want to index!
    return event.tags.filter(([name]) => ['a', 'd', 'e', 'proxy'].includes(name));
  },
});

Tables

NDatabase manages two tables:

• nostr_events stores Nostr events. Each property has its own column.
• nostr_tags stores tags to be indexed for tag filters.

TIP

By default, all single-letter tags are indexed. You can customize this behavior by passing a custom indexTags function into NDatabase.

NOTE

If FTS is enabled, the following table will also be created:

• nostr_fts5 to store the SQLite search index.

Migrating the database

Run await db.migrate() to create the necessary tables and indexes before use. You should call this every time the program starts.

SQLite on Deno

Using @db/sqlite and @soapbox/kysely-deno-sqlite, you can connect to an SQLite database in Deno.

import { NDatabase } from '@nostrify/db';
import { Database } from '@db/sqlite';
import { DenoSqlite3Dialect } from '@soapbox/kysely-deno-sqlite';
import { Kysely } from 'kysely';

const kysely = new Kysely({
  dialect: new DenoSqlite3Dialect({
    database: new Database('./nostr.sqlite3'),
  }),
});

const db = new NDatabase(kysely);
await db.migrate();

Postgres on Deno

Using x/postgresjs you can connect to a Postgres database in Deno.

import { NDatabase } from '@nostrify/db';
import { PostgresJSDialect } from 'kysely-postgres-js';
import { Kysely } from 'kysely';
import postgres from 'postgres';

const databaseUrl = Deno.env.get('DATABASE_URL');

const kysely = new Kysely<Database>({
  dialect: new PostgresJSDialect({
    postgres: postgres(databaseUrl),
  }),
})

const db = new NDatabase(kysely);
await db.migrate();

TIP

There are a few different Postgres drivers for Deno. See which one works best for you.

Other databases

Kysely maintains a list of supported dialects.

It may be possible to get other dialects working. Or build your own!