@airdraft/db-adapter-sqlite

SQLite storage adapter for Airdraft via better-sqlite3.

Use this adapter to store Airdraft content in a local SQLite file — great for self-hosted deployments on a single server, VPS, or edge runtimes that bundle SQLite (e.g. Cloudflare D1 with a custom driver).

Installation

npm install @airdraft/db-adapter @airdraft/db-adapter-sqlite better-sqlite3

Quick start

// airdraft.config.ts
import { defineConfig } from '@airdraft/core'
import { SQLiteAdapter } from '@airdraft/db-adapter-sqlite'

export default defineConfig({
  adapter: new SQLiteAdapter({
    filename: process.env.DATABASE_URL ?? '.airdraft.db',
  }),
  // … collections, plugins
})

Add to .env.local:

DATABASE_URL=.airdraft.db

Run the migration on first deploy (or on every deploy — it is idempotent):

// instrumentation.ts  (Next.js server-start hook)
import airdraft from '@/airdraft.config'
import { BaseDatabaseAdapter } from '@airdraft/db-adapter'

export async function register() {
  if (airdraft.adapter instanceof BaseDatabaseAdapter) {
    await airdraft.adapter.migrate()
  }
}

Options

new SQLiteAdapter(options)

| Option | Type | Default | Description | |---|---|---|---| | filename | string | — | Path to the .db file. Pass ':memory:' for in-memory testing. | | projectId | string | 'default' | Namespace for multi-tenant deployments. | | history | boolean | false | Mirror every write to airdraft_entry_history. Enables rollback(). | | cacheTtlMs | number | 0 | Per-entry read cache TTL. 0 disables caching. | | cacheMaxSize | number | 500 | Maximum entries in the LRU cache. |

Schema

The adapter creates two tables via migrate():

airdraft_entries

| Column | Type | Notes | |---|---|---| | id | INTEGER PRIMARY KEY | Auto-increment | | project_id | TEXT NOT NULL | Multi-tenancy namespace | | collection | TEXT NOT NULL | Collection name (first path segment) | | slug | TEXT NOT NULL | Entry slug (second path segment) | | sha | TEXT NOT NULL | SHA-256 of the serialized content | | data | TEXT NOT NULL | JSON-serialized entry data | | published | INTEGER | 1 = published, 0 = draft (NULL = published) | | created_at | TEXT | ISO-8601 datetime | | updated_at | TEXT | ISO-8601 datetime |

Unique index on (project_id, collection, slug).

airdraft_entry_history (when history: true)

| Column | Type | Notes | |---|---|---| | id | INTEGER PRIMARY KEY | | | project_id | TEXT NOT NULL | | | collection | TEXT NOT NULL | | | slug | TEXT NOT NULL | | | sha | TEXT NOT NULL | SHA at the time of the snapshot | | data | TEXT NOT NULL | Snapshot of the entry data | | created_at | TEXT | When the snapshot was taken |

Features

• Optimistic concurrency — Every write checks the current SHA. Throws ConflictError on mismatch.
• Field filters — queryEntries() translates ListOptions.filter to json_extract() predicates.
• Pagination — offset + limit via LIMIT ? OFFSET ?.
• Sort — json_extract() on any dot-notation field path.
• Atomic field ops — increment, set, push, pull applied to any nested field with SHA re-computation.
• History — Opt-in snapshot table; rollback(path, sha) restores any previous revision.
• Lazy require — better-sqlite3 is loaded on first use, so the module is tree-shakeable.

Running migration as a standalone script

// scripts/db-setup.ts
import airdraft from '../airdraft.config.js'
import { BaseDatabaseAdapter } from '@airdraft/db-adapter'

async function main() {
  if (!(airdraft.adapter instanceof BaseDatabaseAdapter)) {
    console.error('No database adapter configured')
    process.exit(1)
  }
  await airdraft.adapter.migrate()
  await airdraft.adapter.close()
  console.log('Migration complete')
}

main().catch((err) => { console.error(err); process.exit(1) })

npx tsx scripts/db-setup.ts

Testing

The adapter runs the shared contract test suite against an in-memory database:

// src/__tests__/contract.test.ts
import { describe } from 'vitest'
import { runStorageAdapterContract } from '@airdraft/db-adapter/testing'
import { SQLiteAdapter } from '../SQLiteAdapter.js'

describe('SQLiteAdapter contract', () => {
  runStorageAdapterContract(() => new SQLiteAdapter({ filename: ':memory:', history: true }))
})

License

MIT