Write SQL and database migrations with AI

To write SQL with AI safely, give the agent your real schema and a read-only database user, make it show you the query plan, and keep every migration small, reversible and tested on a copy of the data before it goes near production. An AI coding agent is fast at writing queries and migrations. The risk is not that it writes bad SQL now and then, it’s that a bad statement runs where it can do damage.

This guide covers how to prompt for queries, how to check them with EXPLAIN, how to ask for indexes, how to write migrations that don’t lock or lose data, how ORMs fit in, and how to connect an agent to a database through MCP without giving it the keys to production. Examples use PostgreSQL; the ideas carry over to MySQL and SQLite.

Start with the schema, not the question

The most common reason an agent writes a wrong query is that it guessed: a column called user_id that is really account_id, a status stored as an integer, a soft-delete flag it didn’t know about. Give it the schema first.

Where to get it:

• ORM schema files. prisma/schema.prisma, your Drizzle schema files, Django models.py, Rails db/schema.rb. The agent can read these directly; point to them in the prompt.
• A structure-only dump. pg_dump --schema-only writes the tables, columns, indexes and constraints without any data:

pg_dump --schema-only --no-owner "$DEV_DATABASE_URL" > schema.sql

• A read-only database connection through an MCP server, so the agent can list tables and inspect columns itself (more on this below).

Then add what a schema doesn’t say: rough table sizes, which columns are often NULL, and what the business terms mean. “An active customer is one with an order in the last 90 days” saves a wrong guess.

A prompt for a query:

Using schema.sql: write a PostgreSQL query that returns, for each country,
the number of active customers (an order in the last 90 days) and their
total order value this year. Exclude soft-deleted customers
(deleted_at IS NOT NULL). orders has about 20M rows, customers 400k.
Run it on the dev database, show me the first 10 rows and the EXPLAIN plan.

Use a read-only user for the agent

Before an agent runs anything against a database, give it its own user that can’t change data. In PostgreSQL 14 and later, the built-in pg_read_all_data role makes this short:

CREATE ROLE agent_ro LOGIN PASSWORD 'change-me';
GRANT pg_read_all_data TO agent_ro;
ALTER ROLE agent_ro SET default_transaction_read_only = on;
ALTER ROLE agent_ro SET statement_timeout = '30s';

pg_read_all_data grants read access to all tables, without write rights. The two ALTER ROLE lines add belt and braces: transactions start read-only, and a runaway query is cancelled after 30 seconds. On older versions or for tighter access, grant CONNECT, USAGE on the schema and SELECT on the tables you want instead.

Point this user at a development or staging database with realistic data, not at production. If you need production numbers, use a read replica or a recent anonymized copy.

Read the query plan

A query that returns the right rows can still take minutes. Ask the agent to run EXPLAIN on anything that touches a large table and explain the plan to you:

EXPLAIN (ANALYZE, BUFFERS)
SELECT c.country, count(DISTINCT c.id)
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE o.created_at > now() - interval '90 days'
  AND c.deleted_at IS NULL
GROUP BY c.country;

EXPLAIN alone shows the plan the database would use. ANALYZE actually runs the query and adds real timings and row counts. That matters: EXPLAIN ANALYZE on an UPDATE or DELETE really changes data. Wrap those in a transaction you roll back, or only run them on a copy.

What to ask the agent to look for:

• Sequential scans on big tables where you filter on a few rows.
• Estimated vs actual rows far apart, which hints at stale statistics (ANALYZE tablename refreshes them).
• Sorts or hashes spilling to disk.
• Nested loops over many rows where a hash join would be cheaper.

A good follow-up prompt: “Explain this plan in plain words. Which step takes the most time and why? Propose one change and show the new plan.”

Ask for indexes, with the trade-off

Agents like to suggest indexes. Each one speeds up some reads and slows down every write to that table, and takes disk space. Ask for the reasoning:

The query above does a sequential scan on orders. Suggest at most one
index, explain which queries it helps and what it costs on writes,
and show the EXPLAIN plan with it on the dev database.

On a busy table, build the index without blocking writes:

CREATE INDEX CONCURRENTLY idx_orders_customer_created
  ON orders (customer_id, created_at);

CONCURRENTLY takes longer but doesn’t block inserts and updates while it runs. It can’t run inside a transaction block, which matters because many migration tools wrap each migration in a transaction; check how your tool handles it (most have an option to turn the transaction off for one migration). If a concurrent build fails, it leaves an invalid index behind that you need to drop and rebuild.

Write migrations that are safe to run

A migration runs against live data, often while the app is serving traffic. These are the patterns to ask your agent for, and to check in its output.

Expand, migrate, contract

Don’t rename or drop in one step. To rename users.name to users.full_name:

1. Expand: add full_name as a nullable column. Deploy code that writes both columns.
2. Migrate: backfill full_name from name in batches. Switch reads to full_name.
3. Contract: in a later release, once nothing reads name, drop it.

Each step can be deployed and rolled back on its own. A single ALTER TABLE ... RENAME COLUMN breaks every running instance of the old code the moment it runs.

Avoid long locks

Some DDL takes a lock that blocks reads or writes for as long as it runs. Ask the agent to flag these and use the safer form:

Since PostgreSQL 11, adding a column with a constant default doesn’t rewrite the table, so that one is usually cheap. Also set a lock timeout at the top of the migration, so it fails fast instead of queuing behind a long query and blocking everything behind it:

SET lock_timeout = '5s';

Make it reversible

Every migration should have a way back, either a down migration or a written rollback step. Ask for it explicitly: “Write the up and down migration, and tell me which steps can’t be undone without data loss.” Dropping a column can’t be reversed by a down migration; the data is gone. That’s another reason to drop in a later, separate step, after a backup.

Test it on a copy

Run the migration on a copy of the data before production: a staging database restored from a recent backup, or a local database with production-sized fake data. Check three things: it finishes, how long it takes, and that the app still works against the new schema. Then run the down migration and check that too.

Using an ORM: let it generate, have the agent review

If you use an ORM, the safest flow is: change the model, let the ORM generate the migration, and have the agent review the generated SQL.

Prisma’s --create-only writes the migration without applying it, so you can edit the SQL first, for example to add CONCURRENTLY or split a step. Drizzle also has drizzle-kit push, which applies schema changes directly without migration files; that’s handy for local prototyping, but use generated migrations for anything shared.

A review prompt that works with any of these:

Read the new migration in prisma/migrations/. For each statement, tell me:
does it lock the table, can it lose data, how long will it take on a
table with 20M rows, and is there a safer form? Don't change any files.

Connecting an agent to a database with MCP

An MCP server lets the agent query the database itself: list tables, run a SELECT, read a plan. That’s useful, and it’s also where the risk is, so start read-only.

Supabase runs a hosted MCP server. As of October 2026, adding read_only=true to its URL runs every query as a read-only Postgres user, and project_ref ties it to one project:

claude mcp add --transport http supabase \
  "https://mcp.supabase.com/mcp?project_ref=<your-project-ref>&read_only=true"

Supabase’s own guidance is to connect a production project only when the task really needs production data, and then with project scoping and read-only mode on; to keep manual approval of tool calls on; and to review results before acting on them, because protections against prompt injection aren’t foolproof.

Plain PostgreSQL: use a maintained Postgres MCP server with your agent_ro user in the connection string. Best MCP servers for coding lists options and their read-only settings. The old reference Postgres server from the MCP project is archived.

The rules worth writing into your CLAUDE.md or AGENTS.md:

## Database
- The database MCP server is read-only. Never ask for write access.
- Schema changes go through migrations in prisma/migrations, never direct DDL.
- Never run UPDATE or DELETE without a WHERE clause.
- Run new queries with EXPLAIN on the dev database before proposing them.

Never give an agent write access to production

This is the one rule not to bend. Data rows can contain text that reads like instructions (a support ticket, a user bio), and an agent reading them may follow them; that’s prompt injection. A misunderstood request can turn into a DELETE with the wrong filter. With a read-only user, the worst case is a slow query. With write access, it’s an outage or lost data.

The safe split: the agent writes and tests SQL against development data; you review it; a migration tool or a person applies it to production. AI coding agent security covers sandboxing and permissions more broadly.

Doing it in AgentVera

AgentVera is a desktop app for macOS, Windows and Linux that runs Claude Code, Codex and other CLI agents side by side. A few features are built for exactly this work:

• Database manager: PostgreSQL, MySQL/MariaDB and SQLite connections in the same window as your agents, with an editable table view, a SQL editor with EXPLAIN, a table designer that shows the DDL before applying it, and an ER diagram. Connections marked read-only are opened read-only on the server side too, and a connection marked as production is shown in red with changes asking for confirmation first. You can let agents use a connection through the built-in agentvera-db tool: access is off by default, starts in read-only mode when you turn it on, agents never see the password, and every query they run is logged. The database manager is on the Pro and Team plans.
• DB Guard: when an agent finishes a turn, changed migration files are scanned for risks such as DROP, TRUNCATE, UPDATE or DELETE without WHERE, CREATE INDEX without CONCURRENTLY, new constraints and missing down steps. With a production-like connection selected, risk is scaled by real table sizes. You can try the migration on a shadow database inside a transaction that is always rolled back, or send the findings back to the agent to fix.
• MCP manager: set up a database MCP server once and it attaches to your agents on every launch.
• Code review: a second model reviews the migration and query code the agent wrote when its turn ends.

Download AgentVera and try it on a development database.

Wrapping up

Give the agent the real schema and a read-only user on development data. Ask it to run EXPLAIN and explain the plan, to justify every index, and to write migrations in small, reversible expand-and-contract steps with non-blocking DDL. Let your ORM generate migrations and the agent review them, test each one on a copy, and keep production writes in your own hands. Clear instructions make all of this easier; how to write prompts for AI coding agents covers context, plans and done criteria in more depth.