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
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.
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
}
| Statement | What pg-dry-run reports |
|---|---|
UPDATE |
|
| Every matched row, its primary key, and the before and after values for each assigned column. | |
DELETE |
|
| Every matched row, plus reachable foreign keys, cascade counts, and restrictions that would make the delete fail. | |
INSERT |
|
The complete row the table would create, including values supplied by column defaults. Sequence-generated keys are reported after apply because nextval() is itself a write. |
|
| All writes | The target table, affected columns, warnings, timestamps, and the derived SQL used for the preview. |
The default limit is 1,000 rows. Larger writes are refused rather than reduced to a count that nobody can meaningfully review.
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.
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.
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()
.
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.
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.
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
, orDELETE
, including DDL and data-modifying CTEs; - an update or delete without a
WHERE
clause; UPDATE ... FROM
,DELETE ... USING
, or a statement with aWITH
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.
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
});
The proposal reports hazards it can detect but cannot preview:
Triggers and rewrite rules. ABEFORE
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 asgen_random_uuid()
andnow()
are evaluated during preview. The resolved value is what apply writes. An insert that evaluates a column default reports adefault_evaluated
warning.Sequence defaults.nextval()
cannot run in a read-only preview. The affected columns are reported in adeferred_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 ascomposite_foreign_key_skipped
rather than presented as a complete count.Cascade depth. Traversal stops atcascadeDepth
, which defaults to five. A truncated walk is reported ascascade_depth_truncated
.Freezing can changexmin
andVACUUM FREEZE
.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.
createDryRunner(options: DryRunnerOptions): DryRunner
interface DryRunner {
propose(statement: string, params?: readonly unknown[]): Promise<Proposal>;
apply(proposal: Proposal): Promise<Receipt>;
close(): Promise<void>;
}
| Option | Default | Purpose |
|---|---|---|
url |
||
| — | Connection string used for previews and applies. | |
readUrl |
||
url |
||
Optional SELECT -only connection used for previews. |
||
driver / readDriver |
||
| — | Supply your own connection or pool implementation. | |
maxRows |
||
1000 |
||
| Maximum rows one proposal may name. | ||
ttlMs |
||
5 minutes |
||
| How long a proposal remains applicable. | ||
statementTimeoutMs |
||
10 seconds |
||
| Per-statement timeout for previews and applies. | ||
labelColumns |
||
| common human-readable columns | Preferred columns for row labels. | |
cascadeDepth |
||
5 |
||
How far to follow ON DELETE CASCADE . |
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.
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.
MIT. See LICENSE.
Built by Polycore for governed production access from AI agents and human operators.