Database Development Patterns

A reporting query that scanned a few thousand rows in staging now sequentially scans 40 million in production and times out the dashboard. A “quick” column rename ships a migration that takes an ACCESS EXCLUSIVE lock and freezes every write for ninety seconds. And an ORM relation you added last week quietly fires one query per row, so the orders page makes 500 round-trips to render. These are the database failures that don’t show up until real data and real concurrency arrive — and they’re where a coding agent is genuinely useful, because the fix is a recognizable pattern, not invention.

This recipe shows the prompts that get the AI to read an actual EXPLAIN ANALYZE plan, propose a safe expand/contract migration, and collapse an N+1 into one batched query. The anchor stack is PostgreSQL with Drizzle ORM, but the prompts work just as well with Prisma or raw SQL.

What You’ll Walk Away With

• A prompt that pastes a real EXPLAIN (ANALYZE, BUFFERS) plan and gets back targeted index recommendations, not generic advice.
• A zero-downtime column-rename prompt using the expand/contract pattern, so writes never block.
• A prompt that turns an N+1 ORM access pattern into a single batched query.
• The three-tool workflow for schema work (Cursor, Claude Code, Codex) and how the Postgres MCP server changes it.
• A “When This Breaks” checklist for the bad advice AI models give on database work.

Designing a schema across the three tools

Schema generation is the one task where all three tools do roughly the same thing — read your existing tables, match the conventions, propose new ones. The difference is the surface you drive it from.

Cursor

Open your existing schema file (src/db/schema.ts) so Agent mode has it in context, then Cmd/Ctrl + I:

Following the Drizzle conventions in @src/db/schema.ts, add orders and order_items tables. Include created_at/updated_at with defaults, a deleted_at for soft delete, a foreign key from order_items to orders with ON DELETE CASCADE, and an index on orders(user_id, created_at DESC) for our most common lookup.

Review the diff before accepting, and use a Checkpoint so you can revert the whole schema change if the index choice is wrong.

Claude Code

Let Claude Code read the schema and the migration history itself:

claude "Read src/db/schema.ts and the latest files in drizzle/, then add orders and \
order_items tables matching our conventions: timestamps, soft delete, FK with cascade, \
and an index on orders(user_id, created_at DESC). Generate the Drizzle migration too."

Add --permission-mode plan to have it propose the schema and migration before writing anything.

Codex

Run the change non-interactively so it lands as one reviewable commit:

codex exec --ask-for-approval on-request \
"Add orders and order_items tables to src/db/schema.ts following our Drizzle conventions \
(timestamps, soft delete, FK cascade, index on orders(user_id, created_at DESC)) and \
generate the migration."

Codex Cloud is the better fit when the migration is risky: it runs the task in an isolated worktree and opens a PR, so you can review the generated SQL before it touches a shared branch.

Note

For query work specifically, a Postgres MCP server changes the loop: instead of pasting an EXPLAIN plan by hand, the agent connects read-only to your database and pulls the plan, table stats, and existing indexes itself. Reach for a maintained one — Postgres MCP Pro (crystaldba/postgres-mcp) is purpose-built for this, with index-tuning and EXPLAIN-plan tools; Neon and Supabase ship their own if you’re on those platforms. (Avoid the long-archived @modelcontextprotocol/server-postgres — it’s deprecated and was the subject of a documented SQL-injection case study.) Configure it once and the integration is identical across Cursor, Claude Code, and Codex — point it at a read replica or a connection-limited role, never your primary with write credentials.

Optimizing a slow query from its real plan

The single biggest upgrade to AI query tuning is refusing to let it guess. A model handed only the SQL will pattern-match (“add an index on the WHERE columns”) and is often wrong. A model handed the actual execution plan can see the sequential scan, the row-count misestimate, and the spilled sort — and recommend the index that matches.

Tip

Copy-paste prompt — index recommendation from a real plan:

Here is a slow query and its EXPLAIN (ANALYZE, BUFFERS) output on Postgres 16.

SELECT o.id, o.total, u.email
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.status = 'pending'
  AND o.created_at > NOW() - INTERVAL '30 days'
ORDER BY o.created_at DESC
LIMIT 50;

[paste the full EXPLAIN ANALYZE output here]

Tell me:

1. Which node is the bottleneck and why (cite the actual vs. estimated rows and the buffers read).
2. The single most effective index to add, written as a CREATE INDEX CONCURRENTLY statement.
3. Whether a partial index (WHERE status = 'pending') wins here, given the selectivity.
4. The expected plan change after the index — which scan should replace the seq scan.

Do not suggest more than two indexes, and justify each against the plan.

The discipline that makes this reliable: ask for CREATE INDEX CONCURRENTLY explicitly (a plain CREATE INDEX locks writes on a large table), and demand justification against the plan. If the AI recommends four indexes “to be safe,” push back — every index is a write-time tax, and the model has no idea about your write volume unless you tell it.

Zero-downtime migrations: the expand/contract pattern

Renaming a column with ALTER TABLE ... RENAME COLUMN looks harmless and is fine on small tables, but on a hot table the lock plus any dependent view rebuild can stall production. The safe pattern is expand/contract: add the new column, backfill, dual-write from the app, switch reads, then drop the old column in a later deploy. The AI knows this pattern — your job is to make it produce all the steps, not just the rename.

Tip

Copy-paste prompt — expand/contract column rename:

We need to rename users.full_name to users.display_name on a table with ~20M rows and constant write traffic, with zero downtime, using Drizzle migrations on Postgres. Produce the migration sequence as separate, independently deployable steps:

1. Expand: add display_name (nullable, no default to avoid a full rewrite) and a trigger that copies full_name -> display_name on insert/update.
2. Backfill: a batched UPDATE (e.g. 5,000 rows per batch by id range) so we never lock the whole table; show the loop or the batching SQL.
3. Contract: after the app reads display_name, drop the trigger and full_name in a final migration.

For each step, note exactly what lock it takes and whether it’s safe to run during peak traffic. Flag any step that would take an ACCESS EXCLUSIVE lock.

Watch for the model collapsing this into a single RENAME “because it’s simpler.” It is simpler, and it’s the thing that pages you at 2am. The batched backfill is the part most often missing — without it, one big UPDATE rewrites every row under a lock and you’ve reintroduced the downtime you were avoiding.

Killing an N+1 with a single batched query

ORMs make N+1 effortless to write: iterate a list, touch a relation, and you’ve fired one query per element. The fix is to load the children in one query keyed by parent ID. Give the AI the offending code and the model, and it will reshape the access pattern.

Tip

Copy-paste prompt — collapse N+1 into one query:

This Drizzle code triggers an N+1 — one query per order to load its items:

const orders = await db.select().from(ordersTable).where(eq(ordersTable.userId, userId));
for (const order of orders) {
  order.items = await db.select().from(itemsTable).where(eq(itemsTable.orderId, order.id));
}

Rewrite it to load all items in a single query using inArray(itemsTable.orderId, ids), then group them in memory by orderId so each order still ends up with its items array. Preserve the exact return shape the callers expect. Then show the two SQL statements this issues (one for orders, one for items) and confirm it’s O(2), not O(n+1).

The verification step — “show the two SQL statements” — matters because some AI rewrites swap an N+1 for a single query with a Cartesian join, which loads the same row data N times over the wire. Two clean statements grouped in memory is the shape you want.

When This Breaks

Caution

Bad advice AI models give on database work — catch these in review:

• Index spam. Asked to “optimize,” models add indexes on every WHERE/ORDER BY column. Each one slows writes and bloats storage. Insist on one or two, justified against the actual plan.
• CREATE INDEX without CONCURRENTLY. A plain index build locks the table against writes. On any large, live table, demand CONCURRENTLY (and remember it can’t run inside a transaction block).
• Non-concurrent / single-shot migrations. The model proposes a bare RENAME or a non-batched UPDATE that locks a hot table. Require the expand/contract steps and a batched backfill, and ask it to name the lock each step takes.
• Hallucinated extensions and functions. Watch for invented Postgres extensions or functions (a plausible-sounding pg_fastsearch, a made-up aggregate). Verify anything unfamiliar in the Postgres docs before running it.
• String-interpolated SQL. Generated query builders sometimes concatenate user input straight into SQL. Every value must be parameterized — reject anything that interpolates into the query text.
• Ignoring write performance. A schema tuned purely for reads (heavy denormalization, many indexes) can cripple your write path. Tell the AI your read/write ratio so it balances both.

What’s Next

• API patterns — idempotent endpoints, cursor pagination, and OpenAPI generation on top of this schema.
• Serverless patterns for running these databases in cloud and edge environments.