SQLite

SQLite is the default backend for local apps. Documents are rendered into <type> tables with two columns: hash (BLOB primary key) and doc (JSON text).

Setup

import Database from 'better-sqlite3';
import { DocumentStore, SqlitePersistence } from 'document-store';
import { mkdirSync } from 'fs';

mkdirSync('./data', { recursive: true });

// You own the database instance
const db = new Database('./data/app.db');

// Pass it to the persistence layer
const store = new DocumentStore({
    storage: new SqlitePersistence(db)
});

store.registerType('bookmark');
// Or with timestamp rename:
// store.registerType('bookmark', {
//     render: { createdAt: 'created', updatedAt: 'modified' },
// });

You can also pass a path string — SqlitePersistence will create the database:

const store = new DocumentStore({
    storage: new SqlitePersistence('./data/app.db')
});

But owning the db instance yourself is better — you'll need it for reads.

Table structure

For each registered type, document-store creates:

CREATE TABLE bookmark (
    hash BLOB PRIMARY KEY,
    doc  TEXT NOT NULL        -- JSON-encoded document
);

The doc column contains all rendered fields as JSON. Use SQLite's json_extract() to query individual fields.

Buffers and table names — two gotchas

Buffer-valued fields land as nested JSON. A uid, hash, or parent field stored as a Node Buffer is rendered as { "__buf": "<hex>" } in the JSON doc. To filter by it, extract the inner hex:

db.prepare(`SELECT doc FROM bookmark WHERE json_extract(doc, '$.uid.__buf') = ?`).all(uidHex);

Type names with dashes become underscores in the table name. registerType('journal-entry') creates table journal_entry. Quote-or-substitute when writing queries.

Reading documents

Simple queries

// All bookmarks
const all = db.prepare('SELECT doc FROM bookmark').all();
const bookmarks = all.map(row => JSON.parse(row.doc));

// By hash
const row = db.prepare('SELECT doc FROM bookmark WHERE hash = ?').get(hash);
const bookmark = row ? JSON.parse(row.doc) : null;

// Filter by field
const results = db.prepare(`
    SELECT doc FROM bookmark
    WHERE json_extract(doc, '$.url') = ?
`).all('https://example.com');

Sorting and pagination

// Newest first
const recent = db.prepare(`
    SELECT doc FROM bookmark
    ORDER BY json_extract(doc, '$.updatedAt') DESC
    LIMIT 20
`).all();

// Paginated
const page = db.prepare(`
    SELECT doc FROM bookmark
    ORDER BY json_extract(doc, '$.createdAt') DESC
    LIMIT ? OFFSET ?
`).all(pageSize, pageSize * pageNum);

Joins across document types

Documents reference each other by hash. Use standard SQL joins:

// Bookmarks with their folder names. `parent` is a Buffer, so join on its
// hex form against the parent table's `hash` (BLOB). `f.hash` is the raw
// 32-byte BLOB; convert the JSON hex string with hex() / lower().
const withFolders = db.prepare(`
    SELECT
        b.doc as bookmark,
        f.doc as folder
    FROM bookmark b
    LEFT JOIN folder f
        ON lower(hex(f.hash)) = json_extract(b.doc, '$.parent.__buf')
    ORDER BY json_extract(b.doc, '$.createdAt') DESC
`).all().map(row => ({
    bookmark: JSON.parse(row.bookmark),
    folder: row.folder ? JSON.parse(row.folder) : null,
}));

Counting and aggregation

// Count by owner. Group by the inner hex string of the Buffer field.
const counts = db.prepare(`
    SELECT json_extract(doc, '$.uid.__buf') as uidHex, COUNT(*) as count
    FROM bookmark
    GROUP BY uidHex
`).all();

// Full-text search (if you add an FTS index)
db.exec(`
    CREATE VIRTUAL TABLE IF NOT EXISTS bookmark_fts
    USING fts5(title, url, content='bookmark', content_rowid='rowid')
`);

Indexes

Create indexes for your query patterns:

db.exec(`
    CREATE INDEX IF NOT EXISTS idx_bookmark_updated
    ON bookmark(json_extract(doc, '$.updatedAt'))
`);

// Buffer-valued fields: index the inner hex string.
db.exec(`
    CREATE INDEX IF NOT EXISTS idx_bookmark_uid
    ON bookmark(json_extract(doc, '$.uid.__buf'))
`);

db.exec(`
    CREATE INDEX IF NOT EXISTS idx_bookmark_parent
    ON bookmark(json_extract(doc, '$.parent.__buf'))
`);

In-memory (testing)

const store = new DocumentStore({
    storage: new SqlitePersistence(':memory:')
});