Attach a Postgres database

DuckDB can ATTACH a Postgres database and query its tables as if they were local. The challenge at generation time is type introspection: SQG needs the Postgres schema to type-check your queries, but you don’t want it reaching out to production.

A postgres source solves this declaratively. During generation SQG:

1. starts a throwaway Postgres testcontainer,
2. applies your schema to it natively (true Postgres types),
3. attaches it into the in-memory DuckDB introspection connection,
4. type-checks your queries, then tears the container down.

The container, the schema, and the synthesized ATTACH are never emitted into the generated code. At runtime your application attaches the real production database under the same alias — the BASELINE “schema owned elsewhere” contract.

Note

The container path requires Docker at generation time (reuses the same testcontainers integration as the Postgres generator). Only projects that declare a postgres source need it, and only the sql blocks whose queries actually reference the source are affected — see Scoping. If Docker is unavailable, SQG fails fast with a pointed message; to introspect without Docker, point the source at an existing database with url.

Declare a postgres source

1. Declare the source in sqg.yaml with type: postgres. The name is the DuckDB attach alias.

2. Describe its schema with BASELINE blocks tagged :source=<name>. These are valid Postgres DDL and run natively against the container — they are not run against DuckDB and not emitted as migrations.

3. Write queries against the attached catalog (<name>.public.<table>).

queries.sql

-- Schema of the external production database. Applied natively to the throwaway
-- container during generation; not emitted into the generated code.

-- BASELINE prod_orders :source=prod
CREATE TABLE orders (
    id BIGINT PRIMARY KEY,
    customer TEXT NOT NULL,
    total NUMERIC(10, 2),
    created_at TIMESTAMP
);

-- QUERY getOrder :one
@set id = 1
SELECT id, customer, total, created_at FROM prod.public.orders WHERE id = ${id};

-- QUERY listOrders
SELECT id, customer FROM prod.public.orders ORDER BY id;

sqg.yaml

version: 1
name: orders
sql:
  - files:
      - queries.sql
    gen:
      - generator: typescript/duckdb
        output: ./src/generated/
sources:
  - name: prod
    type: postgres
    # image: postgres:16-alpine   # optional, this is the default

SQG starts the container, runs the :source=prod schema into it, attaches it as prod, and the queries type-check with real Postgres types. The generated SQL reads ... FROM prod.public.orders ... verbatim — the same text that runs against production at runtime.

Attach the real database at runtime

Attaching the production database is your application’s responsibility — but SQG generates a typed helper for each postgres source so you don’t hand-write the ATTACH. The helper is named attach<Source> (here attachProd) and attaches under the same alias the source used. Call it once, then run queries:

TypeScript (DuckDB)

import { DuckDBInstance } from '@duckdb/node-api';
import { Orders } from './generated/queries';

const db = await DuckDBInstance.create();
const conn = await db.connect();

const orders = new Orders(conn);
await orders.attachProd(process.env.DATABASE_URL!); // ATTACH '…' AS prod (TYPE postgres)

const order = await orders.getOrder(42n);

Java (DuckDB JDBC)

try (Connection conn = DriverManager.getConnection("jdbc:duckdb:")) {
    Orders orders = new Orders(conn);
    orders.attachProd(System.getenv("DATABASE_URL"));   // ATTACH '…' AS prod (TYPE postgres)

    Optional<Orders.GetOrderResult> order = orders.getOrder(42L);
}

Python (DuckDB)

orders = Orders(conn)
orders.attach_prod(os.environ["DATABASE_URL"])   # ATTACH '…' AS prod (TYPE postgres)

order = orders.get_order(42)

The DSN never goes through SQG — you pass it in from your application’s own config/environment, so dev and prod differ by environment alone with identical generated code. The string is interpolated into the ATTACH (DuckDB does not accept a bind parameter there), so treat it as trusted configuration.

The helper runs INSTALL postgres; LOAD postgres; before the ATTACH (both idempotent), so it works even when DuckDB extension autoloading is disabled. INSTALL is a no-op once the extension is present; in a fully offline runtime, install the postgres extension into DuckDB ahead of time.

Need more control than the helper offers (read-only, a DuckDB secret, or ATTACH '' reading PGHOST/PGPORT/PGUSER/PGPASSWORD/PGDATABASE from the environment)? Skip the helper and run your own ATTACH … AS prod (TYPE postgres) — just keep the prod alias.

What must line up

1. Same catalog alias — the source name and your runtime ATTACH … AS <name> must match; the generated SQL bakes in <name>.public.<table> literally.
2. Schema-qualified path — Postgres tables live under public, so queries read prod.public.<table>.

Sources are scoped to the blocks that use them

A postgres source is project-level, but it only affects the sql blocks whose queries actually reference it (via a :source=<name> BASELINE or a <name>. catalog reference). Adding a source used by one block leaves every other block’s generated code byte-identical — unrelated generators don’t get an attach<Source> helper, and SQG won’t start a container while processing them. Put differently: a generator gets the helper only if its SQL talks to the source.

Introspect an existing database instead of a container

Set url on a postgres source to introspect against an existing database (a dev/staging Postgres) instead of a throwaway container:

sqg.yaml

sources:
  - name: prod
    type: postgres
    url: $DATABASE_URL   # literal DSN, or $ENV_VAR / ${ENV_VAR} read from the environment

With url set, SQG:

• does not start Docker — useful in CI or any Docker-less environment, and ~2–3s faster per run;
• uses the database’s real, live schema for introspection, so it cannot drift from a hand-maintained mirror;
• ignores the source’s :source=<name> BASELINE blocks (the live schema wins). You can drop them, or keep them as documentation.

A url of the form $VAR or ${VAR} is read from the environment so credentials stay out of sqg.yaml; any other value is treated as a literal DSN. The generated runtime helper is unchanged — only generation-time introspection differs. Point url at a database that mirrors production’s schema (a migrated dev DB), never at production itself.

Lower-level alternative: a manual BASELINE attach

If you don’t want SQG to manage a container — for example to keep generation Docker-free in CI — you can attach a stand-in catalog yourself in a plain BASELINE block and recreate the schema in it:

-- BASELINE attach_prod
ATTACH ':memory:' AS prod;
-- BASELINE prod_schema
CREATE SCHEMA prod.public;
-- BASELINE prod_orders
CREATE TABLE prod.public.orders (id BIGINT, customer VARCHAR, total DECIMAL(10,2), created_at TIMESTAMP);

This needs no Docker and runs anywhere, but you get DuckDB-native types during introspection rather than real Postgres types, and you maintain the schema in DuckDB DDL. Prefer a postgres source when type fidelity matters. To stay Docker-free and keep real Postgres types, give the postgres source a url pointing at a database you manage — SQG introspects against it directly (a reachable database is then required at generation time).

Tip

BASELINE blocks run before MIGRATE blocks. You can read from an attached source and own additional tables via MIGRATE — those MIGRATE tables are emitted into getMigrations(). See the SQL Syntax reference.