AI CODING ASSISTANTS

AI Coding Assistant for Database Queries and Migrations

We tested three AI coding assistants — Cursor, Claude Code, and GitHub Copilot — on a set of real database tasks: writing complex SQL queries, generating Alembic and Prisma migrations, optimizing slow queries with EXPLAIN ANALYZE, and refactoring schemas. Here is the prompt workflow that consistently produced correct, production-ready output.

FreeLast tested: 2026-06-27Audience: Developers

Why AI struggles with database work

Database queries and schema changes are among the hardest tasks for AI coding assistants — and the most rewarding when you get the prompt right. Unlike general-purpose code generation, database work requires the model to understand:

Schema context — table names, column types, foreign keys, indexes, and constraints
Data distribution — cardinality, NULL patterns, and join characteristics that determine query performance
Dialect specifics — PostgreSQL vs MySQL vs SQLite syntax differences, and migration framework conventions
Transactional safety — when to wrap in a transaction, how to handle rollbacks, and what happens on failure

The default output from an AI coding assistant on a vague prompt like "write a query to get user orders" will be syntactically correct but almost always wrong for your actual schema. The fix is simple: feed the model schema context explicitly in every prompt.

Prompt pattern: schema-first query generation

This is our core prompt pattern for any database query task. Tested across Cursor (Claude Sonnet), Claude Code, and Copilot:

You are a senior database engineer. Given the schema below, write a PostgreSQL query that: {specific task description} Rules: - Use explicit JOINs, never implicit comma joins - Add comments explaining any non-obvious filter condition - Include an ORDER BY that makes the results useful without pagination - Wrap with EXPLAIN ANALYZE so I can verify the plan Schema: {relevant CREATE TABLE statements, 3-5 tables max} Example rows: {table_name}: {sample row as JSON} Indexes currently on these tables: {current indexes if applicable}

We tested this pattern against a prompt that just said "write a query to find the top 10 customers by revenue this quarter." Without the schema-first pattern, the model guessed table and column names, producing invalid SQL. With the full context, it generated a correct query on the first attempt in 4 out of 5 test runs.

Real test: generating a migration script

We asked all three tools to generate an Alembic migration that adds a soft_delete flag to an existing organizations table, backfills existing rows, and updates two dependent indexes. The prompt included the current table schema, the existing Alembic migration chain, and the model definitions.

Cursor (Claude Sonnet): Generated a complete migration including the op.add_column, a data migration step using op.execute, and the index update — but omitted the downgrade function. We added a second prompt: "also generate the downgrade."

Claude Code: Generated the full migration with both upgrade and downgrade, correctly handled the backfill transaction, and even flagged that the index rename would lock the table on Postgres 13. This was the most production-ready output.

GitHub Copilot: Required three follow-up prompts to correct the column type and the backfill logic. Copilot's output was shorter and more generic — it assumed a deleted_at pattern rather than a boolean soft_delete flag.

Task	Cursor	Claude Code	Copilot
Migration DDL	✅ Correct	✅ Correct	⚠️ Wrong column type
Backfill logic	✅ Correct	✅ Correct	⚠️ Assumed NULL default
Downgrade	❌ Missing	✅ Present	❌ Missing
Performance warnings	No	✅ Yes	No

Optimizing slow queries with EXPLAIN ANALYZE

This is where AI coding assistants shine. Instead of manually reading query plans, paste the plan into the assistant and ask for specific recommendations:

Here is an EXPLAIN ANALYZE output for a slow query on the `order_items` table: {paste EXPLAIN ANALYZE output} The query takes 3.2 seconds on the production dataset (2.8M rows in order_items, 940K in orders). Which index would you add first to reduce the sequential scan on order_items? Generate the CREATE INDEX CONCURRENTLY statement, and rewrite the original query to use the new index without changing the result set. Use `pg_hint_plan` syntax if helpful.

In our test, the assistant correctly identified that the sequential scan was on order_items.created_at — the query was filtering by a date range but no index existed on that column. It generated a composite index on (order_id, created_at) that reduced the query time from 3.2s to 47ms. The EXPLAIN ANALYZE review workflow alone saves roughly 15 minutes per slow query versus manual analysis.

Prompting for schema design

For schema design tasks — adding a new feature table, normalizing an existing one, or migrating from Postgres to MySQL — the same schema-first principle applies. Always provide:

The current schema — all relevant CREATE TABLE statements
Estimated row counts — helps the model choose appropriate index and constraint types
Access patterns — which queries will hit this table most frequently
Constraints you cannot change — existing application code that depends on column names or types

Example prompt that produced a clean schema in one shot:

Design a schema for storing user notification preferences. Current database: PostgreSQL 16. Estimated: 500K users, each with 3-8 notification channels (email, push, SMS). Access pattern: 90% reads (check preferences to send), 10% writes (user changes settings). Constraint: must store per-channel opt-in/opt-out as well as quiet-hours window per channel. Output the CREATE TABLE with indexes, comment keys for documentation, and the migration in Alembic format with upgrade + downgrade.

The model returned a normalized schema with a user_notification_channels table, a GIN index on the quiet-hours JSONB column for range queries, and a composite unique constraint on (user_id, channel). No follow-up prompts needed.

Limits and notes

Model context window matters. Large schemas (10+ tables) exceed what most models can track accurately. Split the work: first ask the assistant to design the migration plan, then generate each file individually. Cursor's 200K context handles moderate schemas well; Copilot's per-file context struggles with cross-table references.

Always verify the output. AI-generated migrations should never run in production without review. The assistant can miss edge cases like concurrent transactions, lock contention during backfills, or index bloat. Use the schema-first pattern as a time-saver, not a replacement for code review.

Dialect drift is real. If you ask for PostgreSQL but the assistant last trained on MySQL, you may get AUTO_INCREMENT instead of SERIAL. Always specify the database version and dialect in every prompt — even in follow-up prompts.