Database Queries & Migrations
Prompt queries and migrations with the real schema, the engine, and safety rules so you avoid slow, wrong, or destructive SQL.
TL;DR
- Give the real schema and the exact engine; SQL dialects and ORM APIs differ in ways that matter.
- Require parameterized queries and never interpolate user input into SQL.
- Treat migrations as high-risk: reversible, reviewed, and tested on a copy before production.
Ground In Schema
Paste The TablesGive column names, types, and relations so queries are valid.
orders(id, user_id FK, status,
total_cents, created_at)Name The EngineState Postgres, MySQL, SQLite, and the version.
"PostgreSQL 16" (not just "SQL")Show The IndexesList indexes so the model writes queries that use them.
idx_orders_user_id,
idx_orders_created_atSafe Queries
ParameterizeDemand bound parameters, never string interpolation.
WHERE user_id = $1 -- not + userIdUse The ORM SafelyPrefer the query builder's safe API over raw string building.
db.order.findMany({ where:
{ userId } })Least DataSelect only needed columns; avoid SELECT * in app queries.
"Select id, status, total_cents only."Performance
Index AwarenessAsk which index a query uses and whether it is sargable.
"Which index serves this WHERE?
Is it a range or full scan?"Avoid N+1Request joins or batched loads instead of per-row queries.
"Fetch orders with their items in
one query, not per order."Verify With EXPLAINConfirm the plan against the real database, not the model's claim.
EXPLAIN ANALYZE <query>Migrations With Care
ReversibleAsk for both an up and a down migration.
"Provide up and down; down must
restore the prior state."Avoid LocksFlag operations that lock large tables and request safe alternatives.
"Add the column nullable first;
backfill in batches."Test On A CopyRun on a snapshot before production; back up first.
Dry-run on a copy. Backup.
Then apply.Tips
- Paste the table definitions and indexes so the model writes queries that use them.
- Ask for the query plan reasoning ('which index does this use?') when performance matters.
Warnings
- String-interpolated SQL is an injection hole; demand parameterized queries or the ORM's safe API.
- Migrations can destroy or lock data; never run a model's migration on production unreviewed and unbacked-up.
In Practice
A query request anchored to the real schema and engine, requiring parameterization and index-awareness, with a plan to verify performance against the database rather than trusting the model.
- The schema, engine, and indexes are given so the query is valid and fast.
- Parameterization is required, closing the injection hole.
- The prompt asks which index the query uses, inviting a performance rationale.
- The result is verified with EXPLAIN against the real database.
Engine: PostgreSQL 16.
Schema:
orders(id uuid pk, user_id uuid fk, status text,
total_cents int, created_at timestamptz)
indexes: idx_orders_user_id, idx_orders_created_at
Task: write a query for a user's 20 most recent paid
orders, newest first, returning id, status,
total_cents, created_at.
Rules:
- parameterize user_id (no string interpolation)
- select only the listed columns
- tell me which index the WHERE/ORDER BY uses and
whether a sort is needed
Return the SQL, then I'll verify with EXPLAIN ANALYZE.FAQ
Because SQL is not one language. Postgres, MySQL, and SQLite differ in types, functions, and syntax, and ORMs each have their own API. Without the real schema and engine, the model guesses column names and uses dialect features your database does not have.
Require parameterized queries or the ORM's safe query builder, and state plainly 'never interpolate user input into SQL strings'. Review any query that concatenates values. This is non-negotiable: interpolated input is the classic injection vulnerability.
As the riskiest thing in the chapter. Ask for a reversible migration (an up and a down), request that it avoids long locks on large tables, and test it on a copy of the data first. Never apply a model's migration to production without review and a backup.
It can suggest improvements, but you must give it the schema, the indexes, and ideally the real query plan. Ask it to explain which index a query uses and why, then verify with EXPLAIN against your database rather than trusting the claim.