# Write SQL and database migrations with AI

URL: https://agentvera.dev/en/blog/write-sql-with-ai/ · Published: 2026-10-27

How to write SQL and migrations with AI coding agents safely: give the schema, use a read-only user, check EXPLAIN plans, add indexes and test migrations on a copy.

**In short:**

- AI agents write good SQL when they see the real schema, the query plan and the table sizes. Give them a read-only database user, not your admin credentials.
- Ask for migrations in small, reversible steps (expand, migrate data, contract), use non-blocking index builds, and run every migration on a copy of the data first.
- Database MCP servers such as Supabase’s have a read-only mode. Never point an agent at production with write access; generate the SQL, review it, then apply it yourself.

**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:

```bash
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:

```text
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:

```sql
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:

```sql
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:

```text
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:

```sql
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:

| Risky | Safer |
| --- | --- |
| `CREATE INDEX` on a large table | `CREATE INDEX CONCURRENTLY` |
| Adding a foreign key in one step | `ADD CONSTRAINT ... NOT VALID`, then `VALIDATE CONSTRAINT` in a separate step |
| `SET NOT NULL` on a large table | Add a `CHECK (col IS NOT NULL) NOT VALID`, validate it, then set NOT NULL |
| Changing a column type | Add a new column, backfill, switch, drop the old one |
| One huge `UPDATE` for a backfill | Batches of a few thousand rows |

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:

```sql
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.

| ORM | Generate a migration | Apply | See the SQL |
| --- | --- | --- | --- |
| Prisma | `npx prisma migrate dev --name add_invoice --create-only` | `npx prisma migrate deploy` | the `migration.sql` file it creates |
| Drizzle | `npx drizzle-kit generate` | `npx drizzle-kit migrate` | the SQL files in your migrations folder |
| Django | `python manage.py makemigrations` | `python manage.py migrate` | `python manage.py sqlmigrate app 0004` |
| Rails | `bin/rails generate migration AddFullNameToUsers` | `bin/rails db:migrate` | `db/schema.rb` and the migration file |

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:

```text
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](/en/glossary/mcp/ "What is 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:

```bash
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](/en/blog/best-mcp-servers-for-coding/ "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`:

```markdown
## 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](/en/glossary/prompt-injection/ "What is 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](/en/blog/ai-coding-agent-security/ "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](/en/features/database-manager/ "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](/en/features/db-guard/ "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](/en/features/mcp-manager/ "MCP manager"):** set up a database MCP server once and it attaches to your agents on every launch.
- **[Code review](/en/features/code-review/ "Code review"):** a second model reviews the migration and query code the agent wrote when its turn ends.

[Download AgentVera](/en/download/ "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](/en/blog/prompts-for-ai-coding-agents/ "Prompts for AI coding agents") covers context, plans and done criteria in more depth.

## Frequently asked questions

**Can AI write correct SQL?** Often, if it sees your actual schema and data shape. Most wrong queries come from guessed column names, missing joins or unknown NULLs. Give the agent the schema, a few sample rows and the expected result, and have it run the query against a development database before you trust it.

**Is it safe to connect a coding agent to my database?** To a development or staging database with a read-only user, yes, with care. To production with write access, no. An agent can misread a request, follow an injected instruction in data, or run a DELETE without a WHERE. Keep writes behind a migration you review and apply yourself.

**How do I give an AI agent my database schema?** Point it at the schema file your ORM keeps (schema.prisma, Drizzle schema files, Django models) or dump the structure only, for example with pg_dump --schema-only. A read-only database MCP server also lets the agent list tables and columns itself.

**Should the AI write the migration or the ORM?** Let the ORM generate the migration from your model change, then have the agent review the generated SQL for locks, data loss and missing steps. Write raw SQL by hand (or by agent) only for what the ORM can’t express, such as concurrent index builds or data backfills.

**What is the expand and contract pattern?** A way to change a schema without downtime. First expand: add the new column or table alongside the old one. Then migrate: deploy code that writes both and backfill old rows. Finally contract: once nothing reads the old structure, remove it in a later migration.

---

**About AgentVera.** AgentVera is a desktop app for macOS, Windows and Linux, built by Solviera Teknoloji. It runs 18 AI coding agents (Claude Code, Codex, Gemini CLI, Copilot, Cursor, Antigravity, opencode, Hermes, Qwen Code, Kimi Code, Kiro, Amp, Droid, Goose, Vibe, Crush, Aider, Cline) side by side, per project, in real terminals with the user's own CLI installs and logins. The free plan has unlimited agents. Download: https://agentvera.dev/en/download/ · Pricing: https://agentvera.dev/en/pricing/