How to Write SQL With AI Without Corrupting Data

Playbook

SQL is where an AI's confident guess can quietly ruin your day. A model that invents a column name gives you a broken query — annoying, but harmless, it just errors. A model that writes UPDATE users SET plan = 'free' and forgets the WHERE clause just changed every user in your database, and there's no undo button on production. The gap between "helpful SQL assistant" and "data-loss incident" is a single missing line. So the rules here are stricter than anywhere else in AI coding: never guess a column, always look before you write, and treat every destructive statement as a loaded weapon.

Step 1: Schema-first — never let it guess a column

The most common way AI SQL goes wrong isn't dramatic, it's mundane: the model assumes a column is called user_id when it's actually account_id, or that created is a date when it's a Unix timestamp. Every one of those assumptions is a bug or a subtly wrong result. The fix is simple and non-negotiable: give the agent the real schema before you ask for a query. Paste the CREATE TABLE statements, or the output of describing the tables involved. Now it's reading your actual columns and types instead of imagining plausible ones. A query written against the real schema is right or clearly wrong; a query written against a guessed schema can be confidently, silently wrong — which is far worse.

Step 2: EXPLAIN before and after

Once you have a query that returns the right answer, check that it's not secretly murdering your database. Run EXPLAIN (or EXPLAIN ANALYZE) on it. This shows you how the database plans to execute the query — whether it's using an index or scanning the entire table. A query that works fine on your 100-row dev database can take thirty seconds and lock a table on the million-row production one, because it's doing a full scan. If EXPLAIN shows a sequential scan on a big table, ask the agent to suggest an index or rewrite. Then EXPLAIN again to confirm the fix actually changed the plan. Same discipline as any optimization: prove the improvement, don't assume it.

Step 3: Destructive operations require an explicit OK and a backup

This is the rule that saves you from a genuinely bad day. Any statement that can lose or alter data — DROP, DELETE, TRUNCATE, or an UPDATE — gets special handling, no exceptions:

• Back up first. A dump of the affected table, or at minimum a snapshot, taken before you run anything destructive. This is your undo button, and it only exists if you make it in advance.
• Never run a bare DELETE or UPDATE. A WHERE clause is mandatory. A destructive statement with no WHERE hits every row. If the agent produces one without a WHERE, treat it as a red flag, not a suggestion.
• Preview with a SELECT. Before an UPDATE or DELETE, run the same WHERE as a SELECT COUNT(*). If you're about to delete 40,000 rows when you expected 12, you'll see it before it happens instead of after.
• You run destructive statements, not the agent. An agent can draft the SQL. The human reads it, confirms the row count, and presses enter. Never hand an agent unsupervised execution against a database that holds data you'd cry about losing.

The copy-paste SQL prompt

Here's the prompt we use to get correct, safe SQL. It bakes in schema-first and the destructive-op guardrail so the agent can't skip them:

I need a SQL query. Follow these rules strictly.

SCHEMA (this is the source of truth — do not invent columns):
<paste your CREATE TABLE statements or DESCRIBE output>

TASK: <what you want the query to do>

RULES:
- Use only columns that appear in the schema above. If you need
  a column that isn't there, STOP and ask.
- For any DELETE or UPDATE, include an explicit WHERE clause and
  first give me a matching SELECT COUNT(*) so I can preview how
  many rows it touches.
- For any DROP / TRUNCATE / destructive change, do NOT write it
  as ready-to-run. Flag it, explain the blast radius, and remind
  me to back up first.
- Then show EXPLAIN for the final query so I can check it uses
  an index and isn't a full table scan.

Step 4: Test on a copy, then run for real

Run any non-trivial query against a copy of the database first — a staging clone or a restored dump. This catches the wrong-column and wrong-row-count mistakes in a place where they cost nothing. Only when it behaves correctly on the copy do you run it against production, and even then, wrap risky writes in a transaction so you can ROLLBACK if the row count surprises you. A transaction you can roll back is a second undo button on top of your backup. Belt and suspenders is the right amount of caution when the downside is permanent data loss.

Where AI SQL genuinely shines

None of this means AI is bad at SQL — it's excellent at it, once you've fenced the danger. It's great at turning a plain-English question into a correct SELECT across joins you'd have to think about, at spotting why a query is slow, at writing the window function you half-remember, and at translating a query between Postgres and MySQL dialects. Read-only work — reports, analytics, exploration — is low-risk and high-value; lean into it. It's specifically the writes and the schema changes that demand the ceremony above.

SQL with AI is safe when you keep the schema real, the plan checked, and the destructive statements on a leash. Guess nothing, back up everything, and run the writes yourself. Since a slow query is often the real performance villain, optimizing code with AI pairs directly with the EXPLAIN step, and because databases are exactly where a hallucinated shortcut hurts most, keep the AI coding security checklist next to your keyboard.