Reading Writes and Transactions Safely

The boss does not always ask for reports. Sometimes she asks for changes.

"Bump every Food expense by 5% to reflect the new tax. And while you're at it, delete the test rows from when we were debugging."

The agent writes two statements: an UPDATE and a DELETE. The agent says they look fine. You almost let them run against your live Neon database. Then you remember Chapter 21 talking about rollback discipline, and you decide to wrap them in a transaction so you can look at them first.

That instinct just saved you from deleting a third of your real data. The DELETE statement was missing its WHERE clause.

This lesson teaches you to read writes before they hit the table, and to wrap them in a rollback while you read.

Key Terms for This Lesson

• INSERT: Adds new rows.
• UPDATE: Changes existing rows. Always check the WHERE clause: without one, every row in the table changes.
• DELETE: Removes rows. Always check the WHERE clause: without one, the table is emptied.
• ON CONFLICT: A clause on INSERT that says what to do when the new row collides with an existing one. DO NOTHING skips. DO UPDATE merges.
• BEGIN / COMMIT / ROLLBACK: A transaction wraps multiple statements into one atomic unit. COMMIT makes the changes permanent. ROLLBACK undoes everything in the transaction.

The Most Dangerous Reading Habit

A write that is missing its WHERE clause is the most expensive single bug in this entire chapter. It does not crash. It does not warn. It quietly affects every row in the table.

-- DELETES EVERY ROW IN THE TABLE.
DELETE FROM expenses;

-- UPDATES EVERY ROW IN THE TABLE.
UPDATE expenses SET amount = amount * 1.05;

Both of these are valid SQL. The database will run them. If you run them on a live Neon database without a backup, the data is gone in milliseconds.

The reading habit you build in this lesson is simple: when you see UPDATE or DELETE, your eyes go straight to the WHERE clause. If there is no WHERE, you stop reading and start asking. The default answer for "is this what we want?" is no.

The Rollback Wrapper

For any write you have not personally reviewed, run it inside a transaction so you can undo it.

BEGIN;

UPDATE expenses
SET amount = amount * 1.05
WHERE category = 'Food';

-- Look at the affected row count and a few sample rows.
SELECT id, category, amount FROM expenses WHERE category = 'Food' LIMIT 5;

ROLLBACK;

What happens:

1. BEGIN opens a transaction.
2. The UPDATE runs and modifies rows in memory. The database sees the changes.
3. The SELECT shows you what the changed rows look like.
4. ROLLBACK undoes everything since BEGIN. The table is exactly as it was.

You read. You verified. Nothing is permanent. When you are satisfied, you replace ROLLBACK with COMMIT and run the same block again to apply the change for real.

This is the seatbelt. Put it on every time the agent hands you a mutation you have not yet read line by line.

Reading an INSERT

INSERT INTO expenses (user_id, category, amount, date)
VALUES (1, 'Food', 52.50, '2026-04-15');

Three things to read:

1. Target table: expenses.
2. Columns: user_id, category, amount, date (in that exact order).
3. Values: 1, 'Food', 52.50, '2026-04-15'. In the same order as the columns.

If the columns and values are not in the same order, the agent has shipped a bug. If a column is missing from the column list but the schema requires it (NOT NULL with no default), the INSERT will fail at runtime with a constraint error.

Reading an INSERT ... ON CONFLICT

INSERT INTO users (email, name) VALUES ('bob@example.com', 'Bob')
ON CONFLICT (email) DO NOTHING;

Translation in plain English: "Insert a new user. If a user already exists with this email, do nothing." This is the idempotent insert pattern. Run it twice and you get one row, not two.

The cousin form is DO UPDATE:

INSERT INTO users (email, name) VALUES ('bob@example.com', 'Robert')
ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name;

Translation: "Insert a new user. If the email already exists, update the existing row's name to the new value." This is the upsert. You will see it in agent-produced migrations and idempotent worker jobs.

You do not need to write either of these. You need to recognise them and know what they do when you see them.

Reading an UPDATE

UPDATE expenses
SET amount = amount * 1.05
WHERE category = 'Food';

Three things:

1. Target: rows in expenses.
2. Change: amount becomes 1.05 times its old value.
3. Filter: only rows where category is exactly 'Food'.

Now contrast it with this one.

UPDATE expenses
SET amount = amount * 1.05;

Same change. No filter. Every row in the table goes up by 5%. If the agent ships an UPDATE without a WHERE, treat it like a fire alarm.

Reading a DELETE

DELETE FROM expenses
WHERE id IN (101, 102, 103);

Three rows deleted by primary key. Safe and surgical.

DELETE FROM expenses
WHERE created_at < '2024-01-01';

Deletes every row created before 2024. The intent might be correct. The risk is real. Run it inside a rollback wrapper first and confirm the affected row count.

DELETE FROM expenses;

No WHERE. Empties the table. Refuse this query. If the agent says "this was on purpose," ask for TRUNCATE expenses instead and confirm with a human before running either.

PRIMM-AI+ Practice: The Food Tax Bump

Predict [AI-FREE]

The agent produced these two statements for the boss's request.

UPDATE expenses
SET amount = amount * 1.05
WHERE category = 'Food';

DELETE FROM expenses
WHERE description ILIKE '%test%';

Before running, write down:

• How many rows the UPDATE will touch (it depends on your data, but the number should be the count of Food expenses).
• Whether the UPDATE has a WHERE clause and what it scopes to.
• How many rows the DELETE will touch (expenses in any category whose description contains "test", case-insensitive).
• Whether the DELETE has a WHERE clause.
• Your confidence score from 1 to 5.

Run

Wrap both statements in a transaction.

BEGIN;

UPDATE expenses SET amount = amount * 1.05 WHERE category = 'Food';
DELETE FROM expenses WHERE description ILIKE '%test%';

SELECT category, COUNT(*) FROM expenses GROUP BY category;

ROLLBACK;

Ask Claude Code to run this block against your Neon database. Make sure the agent includes the ROLLBACK at the end. If the agent tries to COMMIT, stop it.

The output should show you the affected counts and a category summary. Then the ROLLBACK undoes both writes and the table is back to its original state.

Investigate

Write your own explanation:

1. The transaction made both writes visible only inside the session.
2. The SELECT after the writes let you see what the table would look like if the changes were committed.
3. The ROLLBACK threw away both writes. The table is unchanged.
4. If you wanted to make the changes permanent, you would replace ROLLBACK with COMMIT and run the block again.

Then ask the agent:

1. "How many rows did the UPDATE affect, and how many did the DELETE affect?"
2. "What would happen if I changed category = 'Food' to category ILIKE 'food'? Why is the second one safer?"
3. "What would I need to add to make this block idempotent, so running it twice does the same thing as running it once?"

Modify

Change one requirement: bump Food by 5%, but only for expenses logged in the last 90 days. Predict the new WHERE clause. Ask the agent for the change. Read the new UPDATE. Confirm the WHERE clause grew an AND date >= ... condition and nothing else moved.

Make [Mastery Gate]

Write a one-sentence brief that involves a controlled UPDATE or DELETE on the Budget Tracker. Hand it to the agent. Ask for the statement and the rollback wrapper.

Read the statement. Confirm the WHERE clause matches your intent. Run the block. Verify the affected row count. Roll back. Approve or reject.

The gate passes when you can refuse at least one missing-WHERE write and explain in business terms what would have happened if you had let it run.

Try With AI

Prompt 1: Missing-WHERE Drill

I'm going to paste three UPDATE statements. For each one, tell me
whether you would run it and why. If you would refuse, propose the
one-line fix.

A) UPDATE expenses SET amount = amount * 1.05 WHERE category = 'Food';
B) UPDATE expenses SET amount = amount * 1.05;
C) UPDATE users SET email = 'admin@example.com';

What you're learning: B and C are missing-WHERE bugs. C would set every user's email to the same string. The drill builds the reflex that an UPDATE without a WHERE clause is always rejected on first read.

Prompt 2: Idempotent Insert

Write an INSERT for a new category called 'Travel' that is idempotent:
running it twice should produce one row, not two. Use the ON CONFLICT
pattern. Explain in plain English what each line does.

What you're learning: Recognition of ON CONFLICT DO NOTHING. You will see this pattern in worker code that must be safe to retry.

Prompt 3: Rollback Drill

Write me a transaction that updates every Food expense to be 5% higher,
shows me the affected row count, and then rolls back. After I see the
rollback work, I will rerun it with COMMIT instead. Do not run anything
yet, just show me the block.

What you're learning: The full rollback rhythm. Read first, run in a transaction, inspect, roll back, then commit when you are sure. This is the single most useful safety habit in the chapter.

Checkpoint

• I read every UPDATE and DELETE for a WHERE clause before running.
• I can wrap a mutation in BEGIN; ... ROLLBACK; and inspect the affected rows.
• I can recognise ON CONFLICT DO NOTHING as an idempotent insert and ON CONFLICT DO UPDATE as an upsert.
• I rejected at least one missing-WHERE write during practice.

Flashcards Study Aid