pg-dry-run

Preview the effect of agent-generated Postgres writes before they run.
Turn INSERT, UPDATE, and DELETE into row-level proposals you can inspect, approve, and apply safely.

Why · Quick start · How it works · Polycore · Safety · API

Why this exists

AI agents increasingly inspect schemas, generate SQL, and operate real applications. Read access can be contained with a read-only role. Writes need a way to inspect the effect before production data changes.

An agent can generate a perfectly valid statement that does something nobody intended:

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

The SQL looks reasonable, but production data decides whether it updates one account or fourteen. Inserts can pick up defaults that never appear in the statement, and deletes can reach other tables through foreign keys. Static checks and human review both see the query text, not its effect on the current database.

The useful safety question is not “Does this SQL look reasonable?” It is “Which rows will change, how will they change, and what else will be affected?”

pg-dry-run evaluates a write as a read and returns a plain JSON proposal with the affected rows and changes. It later applies only what was previewed; if an existing row changed in the meantime, the entire apply is rejected.

agent-generated SQL → read-only preview → policy or approval → guarded apply

It does not run an AI model or prescribe an approval UI. It provides the database mechanism an agent, CLI, admin tool, or approval system can build on.

Quick start

npm install pg-dry-run

Create a runner and propose a write:

import { createDryRunner } from "pg-dry-run";

const pg = createDryRunner({ url: process.env.DATABASE_URL });

const proposal = await pg.propose(
  "UPDATE profiles SET status = $1 WHERE email LIKE $2",
  ["suspended", "%@acme.com"],
);

proposal.rowCount; // 14, not the 1 you expected

proposal is plain JSON. A terminal, agent, or approval screen can render it like this:

14 rows would change in profiles

  a3f2…  alice@acme.com        status: active -> suspended
  d579…  bob@acme.com          status: active -> suspended
  9c46…  ci-bot@acme.com       status: active -> suspended
  b700…  intern-2024@acme.com  status: active -> suspended
  … 10 more

warning
  BEFORE UPDATE trigger touch_updated_at may change the values actually written

Pass the proposal through your own policy or approval flow. apply() writes the previewed rows in one transaction:

if (await yourApprovalFlow(proposal)) {
  const receipt = await pg.apply(proposal);
  receipt.rowsAffected; // 14
}

What the preview contains

The default limit is 1,000 rows. Larger writes are refused rather than reduced to a count that nobody can meaningfully review.

How it works

1. Parse the statement

The library parses one INSERT, UPDATE, or DELETE with PostgreSQL's parser. It rejects statement shapes it cannot represent faithfully.

For an update or delete, it copies the original predicate as an untouched syntax tree into an equivalent SELECT:

-- input
UPDATE profiles SET status = $1 WHERE email LIKE $2;

-- derived preview
SELECT
  id,
  xmin::text,
  email::text,
  status AS "status.before",
  ($1::text)::text AS "status.after"
FROM profiles
WHERE email LIKE $2;

The caller or model never supplies the derived query. Read proposal.derivedSql to inspect exactly what ran.

2. Evaluate the effect without writing

The preview runs inside BEGIN TRANSACTION READ ONLY. The library also reads Postgres catalog metadata for primary keys, column types, generated columns, triggers, rewrite rules, unique columns, and foreign keys.

Inserts take a slightly different path. Their rows are already explicit, but the finished row is not: the table may supply defaults for columns the statement never mentions. pg-dry-run resolves those values during the preview and uses the resolved values during apply.

3. Return a portable proposal

A Proposal contains the row-level diff, cascade reach, warnings, derived SQL, and the plan needed to rebuild the write. It has no methods and survives JSON serialization, so the preview and apply can happen in different processes.

Treat a proposal as a capability, not as an inert report. If it crosses a queue or network boundary, authenticate it before passing it back to apply().

4. Apply only the previewed change

For updates and deletes, each previewed row carries its primary key and xmin, the Postgres transaction ID for that row version. The apply names those exact keys and versions instead of running the original predicate again.

That gives the apply two useful properties:

• A row that starts matching the predicate after the preview cannot join the approved set.
• A previewed row that was modified or deleted causes the whole transaction to abort with StateChangedError. No partial write lands.

An insert is pinned to the values resolved during preview. For example, a created_at DEFAULT now() keeps the previewed timestamp rather than evaluating now() again after approval.

How Polycore uses pg-dry-run

pg-dry-run is the effect engine behind Polycore's Postgres write path. A Polycore runner previews agent-generated SQL beside the database, feeds the resulting effect into policy and human approval, then applies the held proposal once the write is cleared. Database credentials stay inside the customer's infrastructure.

The library handles the Postgres-specific preview and guarded apply. Polycore provides the surrounding identity, policy, approval, environment routing, and audit trail.

Safety model

Refuse instead of guessing

A wrong preview is more dangerous than no preview. pg-dry-run throws UnsupportedStatementError when it cannot derive an equivalent read.

It currently refuses:

• anything other than one INSERT, UPDATE, or DELETE, including DDL and data-modifying CTEs;
• an update or delete without a WHERE clause;
• UPDATE ... FROM, DELETE ... USING, or a statement with a WITH clause;
• assignment to an array element or object subfield;
• INSERT ... SELECT;
• either form of ON CONFLICT;
• writes to a generated column or a GENERATED ALWAYS AS IDENTITY column;
• an update or delete against a table without a primary key;
• a proposal above maxRows, which defaults to 1,000.

The original predicate is copied, not interpreted. Any predicate Postgres can parse is supported as long as the surrounding statement shape is supported.

Use a separate read connection when possible

One connection string is enough:

const pg = createDryRunner({
  url: process.env.DATABASE_URL,
});

The library generates the preview query itself, refuses statement shapes it does not understand, and runs the preview in a read-only transaction.

A separate SELECT-only role is still the stronger setup because Postgres permissions, rather than library correctness, enforce read-only access:

const pg = createDryRunner({
  url: process.env.DATABASE_URL, // apply
  readUrl: process.env.DATABASE_URL_READONLY, // preview
});

Limitations

The proposal reports hazards it can detect but cannot preview:

• Triggers and rewrite rules. A BEFORE trigger can change the values being written, and any trigger or rule can write elsewhere. The proposal includes a warning when these exist.
• Constraints. A preview cannot guarantee that the apply will satisfy every unique or check constraint. Assignments to unique columns are flagged.
• Volatile expressions. Expressions such as gen_random_uuid() and now() are evaluated during preview. The resolved value is what apply writes. An insert that evaluates a column default reports a default_evaluated warning.
• Sequence defaults. nextval() cannot run in a read-only preview. The affected columns are reported in a deferred_default warning, and generated keys appear on the receipt.
• Composite foreign keys. Cascade discovery follows one column at a time. A composite key is reported as composite_foreign_key_skipped rather than presented as a complete count.
• Cascade depth. Traversal stops at cascadeDepth, which defaults to five. A truncated walk is reported as cascade_depth_truncated.
• xmin and VACUUM FREEZE. Freezing can change xmin without changing row data. That can reject a valid apply, but it cannot allow a stale one.
• Postgres only. The rewrite could be adapted to other transactional databases, but row-version pinning currently depends on Postgres.

API

createDryRunner(options: DryRunnerOptions): DryRunner

interface DryRunner {
  propose(statement: string, params?: readonly unknown[]): Promise<Proposal>;
  apply(proposal: Proposal): Promise<Receipt>;
  close(): Promise<void>;
}

A Proposal carries rowCount, changes, cascades, warnings, derivedSql, columns, creation and expiry times, and the internal apply plan.

A Receipt carries rowsAffected, appliedAt, and the primary keys touched by the apply. For an insert, this is where database-generated keys are reported.

Errors extend PgDryRunError:

• UnsupportedStatementError
• TooManyRowsError
• ProposalExpiredError
• StateChangedError

Implement Driver to bring your own pool or use a non-server Postgres. A driver only needs to provide exclusive use of one session so every statement in a transaction shares the same connection.

Contributing

pnpm install
pnpm verify

Tests run against PostgreSQL semantics in-process through PGlite, so the normal development loop needs no server or container. See CONTRIBUTING.md for the test matrix and project conventions.

Security issues should be reported privately. See SECURITY.md.

License

MIT. See LICENSE.

Built by Polycore for governed production access from AI agents and human operators.