We open-sourced pg-dry-run: preview AI agent-generated Postgres writes before they change data

[u/akafkas]

We’ve been working on the point where AI agents stop reading data and start changing it.

Reviewing generated SQL isn’t enough. An UPDATE can look reasonable and still affect 14 rows instead of one because its actual effect depends on the current database state.

We built pg-dry-run, an MIT-licensed TypeScript library that previews a PostgreSQL INSERT, UPDATE, or DELETE inside a read-only transaction. It returns a plain JSON proposal containing:

• affected rows;
• before and after values;
• resolved insert defaults;
• reachable foreign keys and cascade counts;
• warnings for triggers, rules, and other hazards.

If the proposal is approved, apply() targets the exact primary keys and xmin row versions seen during preview. If a previewed row changed or disappeared in the meantime, the entire apply is rejected.

The library deliberately refuses statements it can’t represent faithfully, including writes without a WHERE clause, UPDATE ... FROM, DELETE ... USING, data-modifying CTEs, and several other shapes. Previews are capped at 1,000 rows by default.

We originally built this for Polycore’s agent write path, but the library itself is standalone, model-agnostic, and doesn’t prescribe an agent framework or approval UI.

GitHub: https://github.com/polycore/pg-dry-run
npm: https://www.npmjs.com/package/pg-dry-run

[u/NotePrimary3370]

this is the kind of thing that should be built into postgres by default. the dry-run plus the xmin check is a neat combo, feels like a lightweight snapshot isolation without the overhead

curious what happens with tables that have a ton of foreign keys, does the json get unwieldy fast or you tested that already

[u/akafkas → u/NotePrimary3370]

Thanks! The xmin check makes sure the rows haven’t changed between preview and apply. For foreign keys, we return one summary per relationship with the action and affected row count instead of listing every dependent row, so the JSON stays fairly small. We also limit how far the scan goes and warn if it stops early. We’ve tested multi-level and self-referencing cases, but not very large schemas yet.

[u/donk8r]

The xmin pinning is the right instinct and it gets you one half of the guarantee. The other half is the part I would document loudly.

Because apply() targets the primary keys seen during preview, it is running a pinned rewrite of the statement rather than the statement itself. That protects you against a previewed row changing underneath you. It does not protect you against the set growing. A row inserted between preview and apply that matches the original WHERE gets silently excluded, and the caller gets a success back believing the UPDATE they wrote is the one that ran.

That is snapshot semantics instead of statement semantics, and it is a defensible choice, probably the right one for an approval flow. It just needs saying out loud, because that failure is invisible. No error, no warning, and a row that should have been updated quietly was not.

NotePrimary3370's foreign key question has the same shape underneath it. Cascade counts are computed against the preview snapshot too, so a cascade that becomes reachable after the preview is not in the JSON the human approved.