pg-dry-run

pg-dry-run is the open-source effect engine in Polycore’s Postgres write path. It turns one INSERT, UPDATE, or DELETE into a read-only proposal, then applies only the rows and values that proposal named.

This page covers one-off SQL writes from the optional Postgres write family. A stable, repeated workflow should be a named action with a narrow input schema and explicit business rules.

Read the source

Review the parser, derived reads, apply path, tests, and documented safety boundaries.

Use the library directly

Add the preview-and-apply mechanism to your own CLI, agent, admin tool, or approval system.

Why SQL text is not enough

An agent can generate a valid statement whose effect depends on production data:

UPDATE profiles SET status = 'suspended' WHERE email LIKE '%@acme.com';

The statement does not tell an approver whether it matches one account or fourteen. An insert can inherit a sensitive default that is absent from the SQL. A delete can reach other tables through foreign keys. pg-dry-run evaluates those effects before Polycore decides whether the write can run.

The Polycore write path

Preview beside the database

The runner calls propose() using a database connection held inside your infrastructure. The control plane and caller never receive that credential.

Evaluate the effect

Runner-side policy sees the operation, table, written columns, row count, cascade reach, and warnings. It can allow, hold, or refuse the write.

Review the rows

When approval is required, Polycore presents a capped row-level diff with the requester, target environment, and policy reason.

Apply the held proposal

Approval releases the proposal already held by the runner. The original predicate is not rerun against a potentially wider set of rows.

Effect for policy, detail for people

Polycore separates the compact data used by policy and audit from the row values a person may need during review.

Policy effect

Operation, table, row count, written columns, cascade reach, and warnings. It contains no row values, so policy can evaluate it and the audit trail can retain it.

Review detail

A bounded set of primary keys, labels, before and after values, plus the derived SQL. It is shown to the reviewer and carries explicit truncation metadata.

Policy can make a decision about the consequence rather than trying to infer it from SQL text:

policy.ts

import { definePolicy } from "@polycore/runner";

export default definePolicy({
  "postgres.update": ({ effect, environment }) => {
    if (effect?.kind !== "postgres.write") {
      return {
        decision: "admin_approval",
        because: "This change could not be previewed.",
      };
    }

    const scoped =
      effect.table === "public.orders" &&
      effect.columns.every(
        (column) => column === "status" || column === "updated_at",
      ) &&
      effect.cascades.length === 0;

    return scoped && effect.rowCount <= 25 && environment !== "production"
      ? { decision: "allow" }
      : {
          decision: "admin_approval",
          because: `${effect.rowCount} rows in ${effect.table}.`,
        };
  },
});

See Approvals and audit for policy defaults, approval behavior, and the audit model.

What each preview resolves

UPDATE
INSERT
DELETE

The proposal names every matched row by primary key and records the before and after value for each assigned column. Each row also carries its Postgres xmin from preview time.

The proposal resolves the full row, including values supplied by column defaults. A key drawn from a sequence is deferred because nextval() is a write; that key appears on the receipt after apply.

The proposal names every row to delete and walks incoming foreign keys. It reports cascade counts, updates caused by SET NULL or SET DEFAULT, and restrictions that would make the delete fail.

What the apply guarantees

The approved set cannot grow

Updates and deletes are rebuilt from the primary keys in the proposal. A row that starts matching the original predicate after preview is not eligible.

Stale approval changes nothing

Every existing row is matched on the version it had at preview time. One modified or missing row aborts the entire apply transaction.

A preview does not make broad database authority safe. Use a separate write credential with only the grants this path needs. Keep generic reads on a SELECT-only role or read replica.

Anything the library cannot transform faithfully is refused rather than approximated. Triggers, rewrite rules, generated columns, constraints, deferred defaults, and truncated cascade walks are reported as warnings where relevant. The complete refusal list and limitations live in the pg-dry-run README.

Where the boundary sits

Parses the mutation, derives the read-only preview, inspects Postgres metadata, builds the row-level proposal, and pins the later apply to it.

Polycore

Attributes the caller, evaluates runner-side policy, routes human approval, selects the environment, isolates credentials, and records the request, decision, and outcome.

Configure Postgres writes

Set up separate read and write roles, enable the optional write family, and validate the full path with your team.

Integration model

Data platforms

Interfaces

Build the catalog

pg-dry-run

Read the source

Use the library directly

Why SQL text is not enough

The Polycore write path

Effect for policy, detail for people

Policy effect

Review detail

What each preview resolves

What the apply guarantees

The approved set cannot grow

Stale approval changes nothing

Where the boundary sits

pg-dry-run

Polycore

Configure Postgres writes

Read the source

Use the library directly

​Why SQL text is not enough

​The Polycore write path

​Effect for policy, detail for people

Policy effect

Review detail

​What each preview resolves

​What the apply guarantees

The approved set cannot grow

Stale approval changes nothing

​Where the boundary sits

pg-dry-run

Polycore

Configure Postgres writes

Why SQL text is not enough

The Polycore write path

Effect for policy, detail for people

What each preview resolves

What the apply guarantees

Where the boundary sits