Postgres Adapter

Use PostgreSQL as a remote storage backend with connection pooling, schema migrations, and full sync support.

Overview

The Postgres adapter stores data in a PostgreSQL database. It is suited for server-side applications, shared infrastructure, and workloads that benefit from SQL queries, full-text search, or transactional guarantees across multiple stores.

Connection

import { createStore } from "@cuitty/store/client";

const store = await createStore({
  module: "analytics",
  profile: {
    id: "postgres-sync",
    name: "Postgres Sync",
    local: { adapter: "local.sqlite", path: ".cuitty/store/analytics.sqlite" },
    remotes: [{ id: "postgres", adapter: "postgres.record", dsnRef: "secret://store/postgres/url" }],
    routes: [{ namespace: "events", class: "record", localAdapter: "local.sqlite", remoteAdapters: ["postgres"] }],
    encryption: { atRest: "preferred", remote: "required", p2p: "disabled" },
    sync: { mode: "manual" },
  },
  schema: { events: { class: "record" } },
});

Use secret references for connection material instead of embedding raw DSNs:

const remote = {
  id: "postgres",
  adapter: "postgres.record",
  dsnRef: "secret://store/postgres/url",
};

Schema migration

On first connection, Store creates the required tables (records, events, kv, sync_queue) in a store schema. Subsequent connections check the schema version and run incremental migrations as needed.

-- Tables created automatically
store.records    -- key, value (jsonb), version, updated_at
store.events     -- id, stream, payload (jsonb), timestamp
store.kv         -- key, value, updated_at
store.sync_queue -- id, operation, payload, status

You can customize the schema name:

const route = {
  namespace: "analytics",
  class: "record",
  localAdapter: "local.sqlite",
  remoteAdapters: ["postgres"],
};

Connection pooling

Store uses a connection pool internally. The default pool size is 10 connections. Configure it based on your workload:

import { testAdapter } from "@cuitty/store/adapters";

const health = await testAdapter("postgres.record", {
  dsnRef: "secret://store/postgres/url",
});

For serverless environments (AWS Lambda, Vercel), set poolSize: 1 and use a connection pooler like PgBouncer to avoid exhausting database connections.

Sync support

The Postgres adapter supports full bidirectional sync. It can act as both a sync source and target, making it a natural choice for a central sync server that multiple devices replicate against.

Supported store classes

postgres.record and postgres.event cover structured records, events, queues, streams, and related server-side data. Blob storage is handled by object adapters such as S3.