Back to Explore
GuideVibeFix editorialUpdated Nov 19, 2026

Let an agent query your database without breaking it: a read-only-first, verifiable SQL workflow

Letting an agent query your database is routine — and risky. Five guardrails: a read-only connection first, show the SQL before running it, EXPLAIN before ANALYZE, attach the query to every number, and route all writes through a separate reviewed channel.

A person looking at a large monitor showing a data analytics dashboard with users, page views, sales and conversion charts

Where this guide sits: how it differs from the database guides already published

First, a boundary statement. The site already carries several database guides, each covering its own area: vibe-database-selection-guide is about choosing a database, vibe-project-schema-migration-guide is about changing table structures, vibe-project-db-backup-disaster-recovery-guide is about backups and disaster recovery, vibe-project-slow-query-index-guide is about slow queries and index tuning, and vibe-project-data-import-migration-guide is about importing and migrating data. This guide covers none of those. It covers one thing only: when you let an agent query your database and analyse the numbers, how do you make sure it only reads, never writes by accident, and that every conclusion can be checked?

That deserves a guide of its own, because "let the agent look at the database" is one of the most common — and most accident-prone — things vibe coders do. Once an app is live, you always have questions: how many users signed up last week? Which feature gets used most? Why did this order fail? Asking feels natural, and the agent answers promptly. But how often do you stop to ask: what identity is it connecting with? Did you check the SQL it ran? Can you verify the number it gave you? If the answer is "I never thought about it", this guide is for you.

The sources are PostgreSQL's official documentation: the EXPLAIN reference (postgresql.org/docs/current/sql-explain.html) and the SET TRANSACTION reference (postgresql.org/docs/current/sql-set-transaction.html). The read-only transactions, the difference between EXPLAIN and EXPLAIN ANALYZE, and the rollback pattern for profiling writes all come from those two documents. The working advice in this guide — "show the SQL before running it", "attach the query to every number" — is practice built on those mechanisms, and the text says which parts are documentation and which are advice. The shop database in the worked example is fictional, used only for demonstration.

The risks: three common ways it goes wrong

The first failure mode is the agent changing data when you thought it was only looking. The classic case: you ask it to "look into why this order failed", and in the course of "fixing" the problem it runs an UPDATE. Or you ask it to "clean up the test data", and its idea of test data is not yours. An agent's default behaviour is to get the job done, not to look without touching. If the database connection you gave it has write permission, it can change data — whether or not you asked it to.

The second failure mode is a number that is wrong but looks right. The agent writes a SQL query, runs it, and reports a number with great confidence. But the query may have missed a status filter (counting refunded orders as sales), may have a timezone problem ("last week" by UTC or by local time?), or may have doubled the rows by joining an extra table. The number itself will never tell you it is wrong. Only verification can. And most people's first reaction to a number from an agent is to use it, not to check it.

The third failure mode is a query that brings the database to its knees. A SELECT with no WHERE clause, a table with millions of rows, a query with no LIMIT runs fast on small data and can saturate the database a few months after launch. The agent does not know how big your tables are, or whether it is peak traffic right now; it just knows to fetch the data first. Worse, EXPLAIN ANALYZE actually executes the query — used against a write, it changes data. PostgreSQL's documentation warns about this explicitly, and many people do not know it.

All three share one root cause: the problem is not that the agent is "dumb", it is that there are no guardrails. The agent does what you ask, but you never drew the boundaries. The five rules in this guide are five guardrails.

Rule 1: read-only first — never give the agent write permission

The first guardrail is the most important one: the connection the agent uses for analysis can read, and nothing else. PostgreSQL's documentation is explicit about what a read-only transaction forbids: INSERT, UPDATE, DELETE, MERGE, and COPY FROM (when writing to a non-temporary table), all CREATE, ALTER, and DROP commands, and COMMENT, GRANT, REVOKE, and TRUNCATE. You set it with one command at the start of the transaction:

BEGIN TRANSACTION READ ONLY;
SELECT count(*) FROM orders WHERE status = 'paid';
COMMIT;

Or set the session default to read-only:

SET SESSION CHARACTERISTICS AS TRANSACTION READ ONLY;

The documentation adds a caveat worth remembering: read-only is a "high-level notion" that does not prevent all writes to disk — internal statistics updates, for example, still happen. So a read-only transaction is not a magic shield. What it blocks is the dangerous category: the agent issuing a write statement. Even with that caveat, it is the highest-value guardrail available: one command blocks the worst class of mistake.

Beyond transaction-level read-only, the more thorough option is a dedicated read-only database account for the agent. That account has only SELECT permission at the database level, so it cannot write even by accident. Many vibe projects host their database on services like Supabase or Neon, where creating a read-only account usually takes a few minutes. The principle: the connection the agent uses and the connection the app itself uses are separate. The app needs to write, so it gets write permission. The agent only reads, so it only gets read permission. Never share one account or one connection string between them.

If you do only one thing from this guide, do this one: from today, the agent queries the database only through a read-only connection. That single rule blocks more mistakes than the other four combined.

Rule 2: show the SQL before running it

The second guardrail is procedural: make the agent write out the SQL it wants to run and show it to you, and only run it after you confirm. Do not let it "just check and tell me the result". Ask for "write the query first, let me look at it". It feels like an extra step, but it is the most important step in the whole workflow: SQL can be checked; a bare number cannot. One look at the SQL reveals a missing status filter, a misspelled column, a missing LIMIT. Looking at the number alone reveals nothing.

Set the rule when you ask the question: "write out the SQL you are going to run, and wait for me to confirm before executing it." The agent may resist the first time; after two or three repetitions it remembers. Better still, write the rule into the project's CLAUDE.md or AGENTS.md, so it becomes the default for the project rather than something you negotiate each time.

When you review SQL, check four things. First, are the table and column names right — agents happily invent a column from memory, writing users_id when the column is user_id. Second, is the WHERE clause complete — status, time range, tenant isolation, anything missing? Third, is there a LIMIT — run analysis queries with LIMIT 100 first to see the shape, then widen it once it looks right. Fourth, is it a read-only statement — SELECT or WITH at the start means read; INSERT, UPDATE, DELETE, or DROP at the start means write. A write statement gets stopped on sight, whatever the agent's justification.

Rule 3: EXPLAIN first, then ANALYZE

The third guardrail is reading the execution plan. PostgreSQL's EXPLAIN command shows the plan the planner generates for a statement: how tables will be scanned (sequential scan or index scan), which join algorithms will combine rows, how many rows it expects to process, and the estimated cost. The key point: plain EXPLAIN only plans, it does not execute the query, so it is safe — even against an UPDATE or DELETE, it changes nothing. The documentation's own words: EXPLAIN displays the execution plan the planner generates, including how tables are scanned and which join algorithms are used.

EXPLAIN SELECT * FROM orders WHERE customer_id = 2;

EXPLAIN ANALYZE is different: it actually executes the statement and adds real run-time statistics to the plan. The documentation warns explicitly: when the ANALYZE option is used, the statement is actually executed. EXPLAIN discards the output a SELECT would return, but other side effects happen as usual. If you want to use EXPLAIN ANALYZE on an INSERT, UPDATE, DELETE, MERGE, CREATE TABLE AS, or EXECUTE statement without letting it affect your data, the documented approach is to wrap it in a transaction and roll back:

BEGIN;
EXPLAIN ANALYZE ...;
ROLLBACK;

Two rules to give the agent: first, run plain EXPLAIN on every query and look at it — a sequential scan of a large table, or an estimated row count two orders of magnitude away from reality, are both signals to stop. Second, EXPLAIN ANALYZE is for SELECT only, and only after you confirm it; for writes it must be wrapped in BEGIN and ROLLBACK. If the agent cannot keep the two apart, let it use only plain EXPLAIN — seeing a little less about real timings is a good trade for never accidentally changing data.

One more detail: how accurate the plan's estimates are depends on how fresh the table statistics are. The documentation notes that the planner relies on pg_statistic data, which the autovacuum daemon normally maintains — but if a table has recently changed a lot, you may need to run the ANALYZE command manually to update statistics. When the agent reports "estimated 10 rows, actually 100,000", the query is often not wrong; the statistics are stale.

Rule 4: every number must be verifiable

The fourth guardrail concerns conclusions: every number the agent reports must come with what it takes to verify it — the query itself, the row count, and the run time. A number without those should not be used directly. A workable format is: one sentence for the conclusion, the full SQL, and the rows returned:

Conclusion: 1,284 paid orders last week.
Query: SELECT count(*) FROM orders WHERE status = 'paid' AND created_at >= '2026-11-09' AND created_at < '2026-11-16';
Returned: 1 row, 1,284.

There are three ways to verify, from cheapest to most expensive. The cheapest is to compute the same number a different way: ask the agent to group by day and sum, then COUNT(*) the whole table, and see whether the two agree. If they disagree, at least one formulation is wrong. The middle option is to look at the row counts: how many rows a query returned, or would affect, is itself a clue. When COUNT(*) returns 0, do not believe "there is no data" first — ask whether the WHERE clause is wrong. The most expensive is to run it yourself: copy the SQL into a database client and execute it, and see whether the result matches. Numbers that matter — for investors, for a retrospective — are worth that cost.

Time ranges are a particularly easy place to go wrong. When the agent writes WHERE created_at >= '2026-11-09', ask: is that UTC or local time? What timezone is stored in the database? Does "last week" mean Monday to Sunday, or the past seven days? These questions look fussy, but one timezone mistake shifts the number by a whole day. Making the agent state its time range and timezone in the answer is the cheapest protection available.

Another common trap is row multiplication from JOINs. Join orders to order lines, and one order with three lines gets counted as three orders by COUNT(*). Agents frequently forget the DISTINCT or the subquery that prevents this. The check is simple: COUNT the orders table alone first, then add the JOIN and compare. If the number grew, something is wrong.

Rule 5: writes take a different road

The fifth guardrail is a complete separation of "asking" and "changing". Analysis sessions only read. Any write — fixing a wrong row, backfilling a field, deleting test accounts — does not happen in the analysis session. It goes through a different route: written as a migration file or a fix script, reviewed, and then executed. A migration has a name, a version, a rollback path, and a code review. An UPDATE typed into a chat has none of those, and when it goes wrong you cannot even tell which sentence did it.

There is a second reason for the separation: analysis and repair need different contexts. Analysis needs "read-only, cautious, every number verifiable". Repair needs "back up first, compute the blast radius first, verify after executing". Mixed into one session, the two sets of requirements interfere with each other. Separated, each session's rules are simpler and easier to follow.

If the analysis turns up data that needs fixing, the correct move is: have the agent write down the problem and the proposed fix, and stop there. Do not execute. You read the proposal, confirm the blast radius (run a SELECT COUNT(*) first to see how many rows it would touch), and then decide whether it goes through a migration file or a dedicated repair session. That pause is the fifth guardrail itself.

Read-only-first SQL analysis workflow: after the question, write the SQL and review it, check the plan with EXPLAIN, execute in a read-only transaction with a row limit, verify by counting, and attach the full query to every conclusion; writes go through a separate human-reviewed channel

One more detail that is easy to miss: the longer a report takes, the more its numbers can drift. If a report runs for several minutes and new orders arrive halfway through, the early queries and the later queries see different data, and the totals no longer add up. PostgreSQL's answer is a REPEATABLE READ transaction for long reports: from the first query on, the whole transaction sees the same snapshot, and nothing that arrives later changes it. The more careful version adds SERIALIZABLE and DEFERRABLE, which the documentation describes as well suited for long-running reports or backups. The default READ COMMITTED is fine for a quick look, but for any report that takes a while and has to add up, decide the isolation level before letting the agent run it.

A complete example: from "how much did we sell last week" to a number you can trust

Here is the full flow on a fictional shop database. The table and numbers are invented for demonstration; do not treat them as real data. The question: "How many paid orders were there last week?"

The agent's first draft of the SQL might look like this:

SELECT count(*) FROM orders WHERE created_at >= '2026-11-09';

Under rule 2, you review it first: the status filter is missing (refunded and cancelled orders get counted), the time range has a lower bound but no upper bound ("last week" becomes "everything since 9 November"), and no timezone is stated. Ask the agent to revise:

SELECT count(*) FROM orders
WHERE status = 'paid'
  AND created_at >= '2026-11-09 00:00:00+08'
  AND created_at < '2026-11-16 00:00:00+08';

Under rule 3, run EXPLAIN first: does it use the index on created_at? How far is the estimated row count from the table's actual size? If EXPLAIN shows a sequential scan of a table with hundreds of thousands of rows, pause and ask whether to add an index or split the time range. Under rule 1, execute inside a read-only transaction. Under rule 4, the answer carries the query, the row count, and the time range. And under rule 5, if you discover a batch of orders with the wrong status, write it down and route it through a migration — do not fix it in this session.

Each rule in this example costs only a few dozen seconds, but together they block three real error types: a missing filter, a wrong timezone, and an accidental write. The five rules add up to one sentence: when an agent queries your database, slower is fine — wrong is the problem.

Checklist: paste it into your project

Finally, a checklist you can use as-is by pasting it into CLAUDE.md or AGENTS.md. Before the agent queries the database, confirm these five: one, the connection is read-only and the transaction starts with READ ONLY. Two, the SQL is written out for you to see and confirmed before execution. Three, EXPLAIN is read first; EXPLAIN ANALYZE is for SELECT only, and writes must be wrapped in BEGIN and ROLLBACK. Four, every number carries the full query, the row count, and the time range. Five, writes never happen in an analysis session; they go through a migration or a fix script.

Three more lines cover the "it cannot find anything" case. If the agent says "not found" or "table does not exist", do not let it guess: have it list the tables (for example, SELECT table_name FROM information_schema.tables) and compare — is the table name wrong, or the wrong database connected? If a query fails with a permission error, the read-only account's permissions are misconfigured; fix the permissions, and do not swap in a read-write account to save time. If the agent says "the data looks wrong", suspect stale statistics or a timezone first, and have it pull a few raw rows to look at before believing its explanation.

The checklist has one more benefit that is easy to miss: it makes "checking the numbers" something you can hand over. When a new person joins the project and asks "where did this number come from", you do not have to explain it from memory — point at the query, the rules, and the verification method. The agent's workflow and the humans' workflow share the same guardrails, so everyone on the team computes numbers the same way, and you never end up arguing over "you got 1,284, I got 1,310, which one is right". Consistent definitions matter more than the numbers themselves.

You do not have to adopt all of this at once. Start with rule one — the read-only connection — and you have already blocked the most dangerous mistake. Add one more rule each time you use it, and within a month you have the complete workflow. Guardrails for agents querying your database are not a case of more is better; they are a case of sooner is better.

Views 1Comments 0

Comments (0)

ME
0/1000
Loading comments...

Sources

Browse projectsPublish your project

Related articles

Real photo: a woman in an office sticking colorful sticky notes in rows on a white wall, holding a pink note in her hand
Guide
Stop shimming with margins: Flexbox one-dimensional layout, explained plainly

Flexbox lays things out in one direction at a time, and when it is done right, the nav bar, the card wall, and the cart rows all behave. This guide follows MDN's Basic concepts of flexbox, Aligning items in a flex container, and Controlling ratios of flex items along the main axis: the two axes, containers and items, direction and wrapping, free space, the three flex properties, alignment on both axes, and gaps and auto margins.

Developer WorkflowFrontend EngineeringAI Coding
A developer with glasses types on a laptop at a wooden desk, an external monitor to the left showing C code on a dark background, a small plant on the desk
News
Microsoft's Coding Model Stops Playing Second Fiddle: MAI Code 2 Scores 96% of Opus 5.5 for 5% of the Cost — Read the Three Caveats First

On 17 November 2026, Microsoft launched MAI Code 2 and Code 2 Flash: the larger model has 77 billion active parameters and a one-million-token context, averaging 63.2% across four benchmarks — close to GPT-6.1 Sol and Claude Opus 5.5 — at $0.309 per task. Read the three caveats first: promotional pricing, the lowest score of the three, and no long-horizon work.

Developer WorkflowProduct LaunchModel Updates
A developer types at a dual-monitor workstation; the left screen shows a dark code editor, the right a testing dashboard with green checkmarks and bar charts
News
Did Your Agent Get Better or Worse? AWS Open-Sources UAEF to Measure Agents from Every Framework with One Ruler

On 11 November 2026, AWS open-sourced UAEF: adapters turn traces from LangGraph, LangChain, Strands and Bedrock AgentCore into one format, then 36 metrics across 7 dimensions score them, flagging regressions past 5%. The judge model is hardened too — scores must cite verbatim evidence, and never decide safety.

Developer WorkflowProduct LaunchTesting & Quality