@otter-sh/adapter-postgres

Postgres target adapter for Otter. Implements the Adapter interface from @otter-sh/core using Bun.sql — no pg or postgres.js dependency.

Responsible for: schema creation, bulk loads from otter load, view / table / incremental materializations for otter build, and the atomic staging-table swap used by table and incremental models.

Install

bun add @otter-sh/adapter-postgres

Add it to your project's dependencies alongside @otter-sh/core and @otter-sh/cli. Requires Bun.

Configuration

Import postgresAdapter and call it inside otter.config.ts:

import { postgresAdapter } from "@otter-sh/adapter-postgres";
import { defineConfig } from "@otter-sh/core";

export default defineConfig({
  profiles: {
    dev: {
      target: postgresAdapter({
        url: process.env.PG_URL ?? "postgres://localhost:5432/dev",
        schema: "analytics",
      }),
    },
  },
  sources: {},
  modelsDir: "models",
});

| Option | Type | Default | Description | | -------- | -------- | ---------- | --------------------------------------------------------- | | url | string | — | postgres://user:pass@host:port/db | | schema | string | "public" | Default target schema; used as search_path for bare ids |

The adapter sets search_path via SET LOCAL so bare identifiers from ref(), source(), and seed() resolve against the configured schema automatically.

Load strategies

| Strategy | Supported | Notes | | --------- | :-------: | ------------------------------------------------------------------------ | | append | ✔ | Multi-row INSERT per batch | | replace | ✔ | DROP TABLE IF EXISTS, then multi-row INSERT per batch | | merge | ✔ | INSERT ... ON CONFLICT (unique_key) DO UPDATE; requires --unique-key |

Materialization semantics

| Materialization | Execution | | --------------- | ------------------------------------------------------------------------------------------------------------ | | view | CREATE OR REPLACE VIEW <schema>.<model> | | table | CREATE TABLE <schema>.<model>__stg, then atomic swap into <schema>.<model> inside a single transaction | | incremental | Build staging table from compiled SQL; INSERT ... ON CONFLICT DO UPDATE into the final table; drop staging |

The swap uses DROP TABLE IF EXISTS + ALTER TABLE ... RENAME TO inside one transaction, so readers never observe a missing table.

Identifier resolution

• ref("model_id") → "model_id" (double-quoted).
• source("pg", "charges") → "raw_pg_charges" (double-quoted).
• seed("countries") → "seed_countries" (double-quoted).

Schema-qualified names are produced at execution time using the configured schema.

Programmatic use

For tests or custom runners:

import { postgresAdapter } from "@otter-sh/adapter-postgres";

const adapter = postgresAdapter({
  url: process.env.PG_URL!,
  schema: "analytics",
});

const { tables } = await adapter.introspect();
await adapter.execute("create schema if not exists analytics");
await adapter.close();

Full documentation

• Adapter reference — adapter-postgres
• Interface — adapters
• Load strategies — load-strategies
• Materializations — materializations
• Config — configuration

License

MIT