Show HN: Preview Postgres writes from AI agents with xmin checks (pg-dry-run) Pg-dry-run, a new open-source library, lets developers preview the effects of AI-generated Postgres writes before they are applied, converting INSERT, UPDATE, and DELETE statements into row-level JSON proposals that can be inspected and approved. The tool, installable via npm, reports affected rows, before-and-after values, and foreign key impacts, and rejects applies if underlying data changes, addressing the risk of AI agents executing unintended database modifications. 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 why-this-exists · Quick start quick-start · How it works how-it-works · Polycore how-polycore-uses-pg-dry-run · Safety safety-model · API 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: js 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: js 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 https://docs.polycore.ai/pg-dry-run . 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 , 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. One connection string is enough: js 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: js 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. 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 .Freezing can change xmin and VACUUM 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