@airdraft/db-adapter-postgres

PostgreSQL storage adapter for Airdraft via pg.

Use this adapter to store Airdraft content in a PostgreSQL database — suitable for self-hosted deployments, managed cloud databases (Neon, Supabase, RDS, PlanetScale Postgres, etc.), and multi-tenant setups where multiple Airdraft projects share a single connection pool.

Installation

npm install @airdraft/db-adapter @airdraft/db-adapter-postgres pg
npm install -D @types/pg

Quick start

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

export default defineConfig({
  adapter: new PostgresAdapter({
    connectionString: process.env.DATABASE_URL!,
  }),
  // … collections, plugins
})

Add to .env.local:

DATABASE_URL=postgresql://user:pass@localhost:5432/mydb

Run the migration on first deploy (idempotent — safe to run on every deploy):

// 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 PostgresAdapter(options)

| Option | Type | Default | Description | |---|---|---|---| | connectionString | string | — | PostgreSQL connection string (postgresql://…). | | pool | Pool | — | Provide an existing pg.Pool to share across adapters (e.g. in multi-tenant cloud). Mutually exclusive with connectionString. | | projectId | string | 'default' | Namespace for multi-tenant deployments. | | history | boolean | false | Mirror every write to airdraft_entry_history. | | cacheTtlMs | number | 0 | Per-entry read cache TTL. 0 disables caching. | | cacheMaxSize | number | 500 | Maximum entries in the LRU cache. |

Schema

airdraft_entries

| Column | Type | Notes | |---|---|---| | id | SERIAL PRIMARY KEY | | | project_id | TEXT NOT NULL | Multi-tenancy namespace | | collection | TEXT NOT NULL | | | slug | TEXT NOT NULL | | | sha | TEXT NOT NULL | SHA-256 of the serialized content | | data | JSONB NOT NULL | Entry data | | published | BOOLEAN | NULL = published (default), FALSE = draft | | created_at | TIMESTAMPTZ | | | updated_at | TIMESTAMPTZ | |

Unique constraint on (project_id, collection, slug). pg error code 23505 → ConflictError.

airdraft_entry_history (when history: true)

Same columns as airdraft_entries minus id/published, plus a SERIAL history id.

Features

• JSONB storage — data is stored as native JSONB. Use (data->>'field')::numeric for numeric comparisons.
• Optimistic concurrency — SHA checked on every write; ConflictError thrown on mismatch.
• Efficient pagination — COUNT(*) OVER() window function returns total rows in a single query.
• Field filters — queryEntries() maps filter entries to data->>'field' = $n predicates. $contains maps to ILIKE.
• Transactions — history snapshots written inside a BEGIN/COMMIT block.
• Connection pooling — Uses a pg.Pool; pass pool to share across multiple adapter instances.
• Atomic field ops — increment, set, push, pull with SHA re-computation. pull correctly returns [] (not NULL) when the last element is removed.

Multi-tenant cloud setup

When many projects share one Postgres cluster, pass a shared pool and set projectId per project:

import { Pool } from 'pg'
import { PostgresAdapter } from '@airdraft/db-adapter-postgres'

const pool = new Pool({ connectionString: process.env.DATABASE_URL })

// per-project adapter, e.g. in a Next.js route handler
const adapter = new PostgresAdapter({ pool, projectId: project.slug })

Testing

Runs the shared contract suite using @electric-sql/pglite — a WASM Postgres that needs no Docker or external service:

npm test

The test file (src/__tests__/contract.test.ts) creates an in-memory PGlite instance, wraps it in a pg.Pool-compatible shim, and runs two suites: one without history and one with history enabled.

License

MIT