SQL prompts for writing, explaining and optimizing queries

The best SQL prompts give the model your schema, your dialect and a clear question, then ask it to prove the answer. Below are 13 SQL prompts for ChatGPT, Claude, Claude Code and Codex that cover writing queries, explaining them, making them fast, designing schemas and running migrations without downtime.

These go deeper than the general coding prompts, which already include one prompt each for a basic query, a migration and a first data model. Nothing here repeats those.

How to use these prompts

Copy a prompt, replace the blanks in double braces, or keep the default after the colon. {{dialect:PostgreSQL}} means PostgreSQL unless you type something else.

Three habits make AI much better at SQL:

• Paste the real schema. Use \d table_name in psql, SHOW CREATE TABLE in MySQL, or your migration files. Include indexes and constraints. A model that has to guess column names will guess wrong and sound sure about it.
• Say how big the tables are. A query that is fine on 10,000 rows can time out on 50 million. Rough row counts change the advice.
• Never paste real customer data. Make up three to five rows with the same shape, including the awkward cases: nulls, duplicates, time zones.

In an agent such as Claude Code or Codex, you can point at your migration folder instead of pasting the schema. Give the agent a read-only database role if it can run queries at all.

How do you get AI to write a correct SQL query?

1. Write a query and prove it works

The fastest way to catch a wrong join is a tiny test with known answers. This prompt makes the model build one.

Schema:
{{schema}}

Write a {{dialect:PostgreSQL}} query that answers: {{question}}

Then:
1. List every assumption you made (how nulls, duplicates, ties and time
   zones are handled).
2. Write a self-contained test: a WITH clause of 5 to 8 sample rows that
   includes the edge cases, the query run against it, and the exact result
   I should expect.
3. Point out anything in my question that could mean two different things.

2. Window functions for analytics

Running totals, rankings, top N per group and period-over-period changes are where most hand-written SQL goes wrong.

Using {{dialect:PostgreSQL}} window functions, write a query on this schema:

{{schema}}

Goal: {{goal:top 3 products by revenue in each category, per month}}

Explain the PARTITION BY, ORDER BY and frame clause you chose and why. Say
what happens with ties and with groups that have fewer rows than the limit.
Show the same result written without window functions only if it is
simpler.

3. Translate SQL between dialects

For moving queries or views from one database to another.

Convert this {{from:MySQL}} SQL to {{to:PostgreSQL}}.

{{sql}}

Use idiomatic {{to:PostgreSQL}} functions, not workarounds. List every
change you made in a table: original, converted, reason. Flag anything that
behaves differently between the two, such as string comparison and
collation, date math, integer division, NULL ordering, or LIMIT with ties.

How do you get AI to explain a SQL query?

4. Explain a long query in plain language

For the 200-line report query nobody wants to touch.

Explain this {{dialect:PostgreSQL}} query to a developer who knows SQL but
has never seen this database.

{{sql}}

Go through it one CTE or subquery at a time: what rows go in, what comes
out, and why that step exists. Then describe the final result in one
sentence, as a business question. Finally list anything that looks like a
bug or a silent assumption (an inner join that drops rows, a missing
DISTINCT, a filter that excludes nulls).

5. Read a query plan

Query plans are dense. This prompt turns one into a short list of what matters.

Here is the output of EXPLAIN (ANALYZE, BUFFERS) for a slow query, plus the
query itself.

Query:
{{sql}}

Plan:
{{plan}}

Explain the plan from the innermost node outward in plain language. Point to
the node where most of the time goes. Call out any row estimate that is off
from the actual rows by 10x or more and what usually causes that. End with
the two changes most likely to make it faster.

EXPLAIN ANALYZE runs the statement for real. The PostgreSQL EXPLAIN docs say to wrap an INSERT, UPDATE or DELETE in BEGIN; and ROLLBACK; if you don't want it to change your data.

How do you make a slow SQL query faster with AI?

6. Speed up a slow query

Give it everything: the query, the plan, the indexes and the sizes. Asking for options with trade-offs stops it from adding an index for every problem.

This {{dialect:PostgreSQL}} query takes {{current_time:4 seconds}} and needs
to run in under {{target_time:200 ms}}.

Query:
{{sql}}

Tables and indexes:
{{schema}}

Row counts: {{row_counts}}
Plan (EXPLAIN ANALYZE): {{plan}}

Give up to three options, from least to most invasive: rewrite the query,
add or change an index, or change the schema. For each, show the change,
explain why it helps using the plan, and name the cost (write overhead,
disk, code changes). Do not change what the query returns.

7. Review the indexes on a table

Indexes pile up over the years. This finds the ones you are missing and the ones you are paying for but never use.

Here is a {{dialect:PostgreSQL}} table with all of its indexes, and the
queries that hit it most often.

Table and indexes:
{{schema}}

Most common queries:
{{queries}}

The table has about {{rows:10 million}} rows and gets
{{write_load:about 50 writes per second}}.

1. Which queries are not well served by any index, and what index would fix
   each one (column order matters, explain yours)?
2. Which indexes are redundant (covered by another index) or unused by
   these queries?
3. Give the SQL to add and drop indexes in a way that does not block writes
   on a live table.

For the last step on PostgreSQL, expect CREATE INDEX CONCURRENTLY. Per the CREATE INDEX docs, a standard index build blocks inserts, updates and deletes until it finishes, while the concurrent build does not, but it takes longer and cannot run inside a transaction block.

8. Fix the SQL your ORM generates

When the app is slow and the cause is a query you never wrote by hand.

My {{orm:Prisma}} code below is slow. Here is the code and the SQL it
produces (from the query log).

Code:
{{code}}

Generated SQL:
{{sql}}

Explain what the ORM is doing, including any N+1 pattern (one query per
row in a loop). Rewrite the ORM code so it issues fewer, better queries. If
the ORM can't express the efficient version cleanly, show a raw SQL version
and how to call it safely with parameters.

How do you design a database schema with AI?

9. Review an existing schema

For a schema that grew one feature at a time. It catches problems while they are still cheap to fix.

Review this {{dialect:PostgreSQL}} schema for an app that
{{app:sells subscriptions to small teams}}.

{{schema}}

Check for: missing foreign keys, missing NOT NULL or UNIQUE constraints,
wrong types (money in floats, dates in text, timestamps without time zone),
columns that store lists or JSON that should be tables, tables that mix two
concepts, and naming that is inconsistent.

Rank findings by how much damage they could do to data. For each, give the
fix as SQL and say whether it is safe to run on a live table.

10. Add constraints without breaking existing data

Adding a constraint to a table that already has bad rows fails halfway. This prompt finds the bad rows first.

I want the database to enforce this rule:
{{rule:every order has a customer, and quantity is greater than zero}}.

Schema:
{{schema}}

1. Write queries that find existing rows that break the rule, so I can
   clean them up first.
2. Write the constraints (CHECK, FOREIGN KEY, UNIQUE or NOT NULL) that
   enforce it.
3. Show how to add them on a large live {{dialect:PostgreSQL}} table with
   the least locking, for example adding the constraint as NOT VALID and
   validating it in a second step.

That two-step pattern comes from the ALTER TABLE docs: with NOT VALID, adding the constraint does not scan the table, and the later VALIDATE CONSTRAINT takes only a SHARE UPDATE EXCLUSIVE lock.

How do you plan a safe database migration with AI?

11. Plan a zero-downtime schema change

Renaming a column or splitting a table while the app is live takes several deploys, not one migration. This prompt lays them out.

I need to {{change:rename users.name to users.full_name}} in
{{dialect:PostgreSQL}} without downtime. The app is
{{app:a Node.js API with several instances}} and deploys take about
{{deploy_time:10 minutes}}.

Plan it as an expand and contract change:
- Each step in order, labeled "migration" or "code deploy"
- What old and new code must both handle at each step
- How to backfill existing rows in batches, with the SQL
- How to verify each step before moving on
- How to roll back at each step

12. Review a migration before it runs

Paste the migration your framework generated. This catches the line that would lock a busy table for minutes.

Review this migration before I run it on production.

{{migration}}

Database: {{dialect:PostgreSQL}}. Largest table it touches has about
{{rows:20 million}} rows. The app stays live.

For each statement: does it rewrite the table, block reads, or block
writes, and for roughly how long at this size? Flag anything that should be
split into separate steps or run outside a transaction. Check that the down
migration really undoes the up migration. Give me a corrected version.

13. Generate realistic test data

For seeding a dev database or load testing a query with something closer to real volume.

Write a {{dialect:PostgreSQL}} script that fills this schema with realistic
fake data:

{{schema}}

Volumes: {{volumes:1,000 customers, 20,000 orders, 60,000 order items}}.
Respect every foreign key and constraint. Make the data uneven like real
data: a few customers with many orders, some with none, dates spread over
{{period:two years}}, some nulls where columns allow them. Use
generate_series or similar set-based SQL instead of row-by-row loops, and
make it re-runnable.

Which of these prompts should become a skill?

The query plan reader and the migration review are the ones teams run every week, so they are the first candidates. Turn one into a skill and Claude Code or Codex will apply it whenever you ask about a slow query or open a migration file. Paste the prompt into the SKILL.md generator to get a valid SKILL.md, with each blank turned into an instruction.

If you want a ready-made rule set instead, the Postgres best practices skill gives an agent rules for indexes, row level security, connection pooling and locking. It pairs well with prompts 6, 7 and 12. For reviewing the application code around your queries, see the code review prompts.

To keep these one keystroke away, save them in Kipt. The {{dialect:PostgreSQL}} blanks work as written, tags like sql keep them together, and ⌘K finds "plan" or "migration" in a second. Your library lives as Markdown in a private GitHub repo you own.