If you've ever stared at a 200-line SQL query that runs slower than a snail on a coffee break, you know the pain. Or maybe you're new to databases and writing your first JOIN feels like solving a puzzle in the dark. The good news? AI assistants like ChatGPT have become surprisingly good at SQL — but only when you know how to ask. This isn't about magic; it's about crafting precise instructions that turn an AI into your personal SQL tutor, code reviewer, and performance tuner. Below, I've distilled 15 battle-tested prompts that I use in my own workflow, from generating simple queries to dissecting execution plans. Each one is ready to copy, paste, and adapt to your specific database.
1. The Schema-First Query Generator
When to use: You have a clear idea of what data you need, but you're not sure about the exact syntax or table relationships.
The prompt:
Act as a senior SQL developer. Here is my database schema:
{PASTE_SCHEMA}
Write a PostgreSQL query that [describe your goal, e.g., "finds all customers who made more than 3 orders in the last month"].
Include the following:
- Proper JOIN conditions
- Column aliases
- Filtering in the WHERE clause (not HAVING) where possible
- Comments explaining each step
- Use CTEs if the query gets complex
Why it works: By providing the schema, the AI knows valid table and column names, which eliminates guesswork. The explicit instructions force the AI to follow best practices.
Example:
Say you have customers(id, name, created_at) and orders(id, customer_id, amount, created_at). Your prompt: "...finds all customers who made more than 3 orders in the last month." The AI will likely produce a query with a CTE that counts orders per customer, then filters with HAVING, but the prompt's instruction to use WHERE where possible pushes the AI to filter created_at in the CTE itself — a subtle optimization.
2. The Plain-English to SQL Translator
When to use: You think in English, but the database speaks SQL.
The prompt:
Translate the following natural language request into a PostgreSQL query. Follow these rules:
- Use only tables and columns that exist in the schema below.
- If the request is ambiguous, ask for clarification instead of guessing.
- Return the query in a code block, and then explain your reasoning in plain English.
Schema: {PASTE_SCHEMA}
Request: "Show me the top 5 products by revenue in each category for the last quarter, but exclude products with less than 10 units sold."
Why it works: This prompt forces the AI to clarify ambiguity, which is crucial for complex requests. The explanation step helps you learn and verify correctness.
Example: The AI might ask: "Does 'last quarter' mean the previous calendar quarter or the last 3 months from today?" You answer, and it gives you a window function query with ROW_NUMBER() OVER (PARTITION BY category ORDER BY revenue DESC).
3. The Execution Plan Explainer
When to use: Your query is slow, and you need to understand why.
The prompt:
Explain the following PostgreSQL execution plan in simple terms. I'm a developer, not a DBA. Focus on:
1. The most expensive operations (highest cost or time)
2. Whether indexes are being used effectively
3. Any red flags like sequential scans on large tables
4. Specific recommendations to improve performance, including suggested indexes
Here is the EXPLAIN ANALYZE output:
{PASTE_EXPLAIN_ANALYZE_OUTPUT}
Why it works: EXPLAIN ANALYZE gives raw data that's cryptic. This prompt translates it into actionable advice.
Example: You paste a plan showing a Seq Scan on a 10-million-row table. The AI will likely suggest a CREATE INDEX on the column used in the WHERE clause, and explain that the index can turn a full table scan into an index range scan.
4. The Index Design Consultant
When to use: You want to optimize a specific query or set of queries.
The prompt:
Given the following PostgreSQL table schema and query, recommend the best indexes. Consider:
- Query patterns (WHERE, JOIN, ORDER BY, GROUP BY)
- Selectivity of columns
- The trade-off between read and write performance
- Use of covering indexes, partial indexes, or expression indexes if applicable
For each index, provide:
- The exact CREATE INDEX statement
- The reason why it helps
- Potential downsides
Schema: {PASTE_SCHEMA}
Query: {PASTE_QUERY}
Why it works: The AI can reason about index design based on the query, suggesting optimal composite or covering indexes that you might miss.
Example: For a query with WHERE status = 'active' AND created_at > now() - interval '30 days', the AI might suggest a partial index: CREATE INDEX ON orders (created_at) WHERE status = 'active'.
5. The Slow Query Refactorer
When to use: You have a working but slow query, and you want to make it faster without changing the result.
The prompt:
Refactor the following PostgreSQL query for performance. Keep the exact same result set. Apply these techniques where applicable:
- Replace correlated subqueries with JOINs or window functions
- Use EXPLAIN ANALYZE to verify improvements
- Optimize JOIN order
- Avoid functions in WHERE clauses on indexed columns
- Use UNION ALL instead of UNION if duplicates are not a concern
Here is the query:
{PASTE_QUERY}
Show the refactored query and explain each change you made.
Why it works: The prompt provides a checklist of common optimizations, and the explanation helps you learn.
Example: A query using NOT IN (SELECT ...) might be refactored to LEFT JOIN ... WHERE ... IS NULL, which is often faster. The AI will explain why.
6. The SQL Style Formatter
When to use: Your SQL is a mess, and you want a consistent, readable style.
The prompt:
Format the following SQL query according to the SQLFluff style guide (or a style you prefer). Use:
- Uppercase for keywords
- Indentation for readability
- Consistent comma placement (leading commas)
- Alias columns with AS
- Break long lines
Here is the query:
{PASTE_QUERY}
Why it works: AI can reformat code instantly, and you can specify any style guide. This saves time and makes your code more maintainable.
7. The Schema Designer
When to use: You're starting a new project and need a database schema.
The prompt:
Design a PostgreSQL schema for a [describe your project, e.g., "simple e-commerce platform"]. Include:
- Tables with columns, data types, and constraints
- Primary and foreign keys
- Indexes for common queries
- Relationships (one-to-many, many-to-many)
Use best practices like normalization (up to 3NF), but allow denormalization if justified.
Provide the DDL statements.
Why it works: AI can generate a solid starting schema, which you can then refine. It's a great time-saver for prototyping.
8. The Migration Generator
When to use: You need to alter an existing table and want a safe migration script.
The prompt:
Generate a PostgreSQL migration script to [describe change, e.g., "add a column 'email_verified' to the 'users' table with a boolean default false"]. Include:
- ALTER TABLE statement
- Add constraints or indexes if needed
- Data backfill if necessary
- Rollback statement (reverse migration)
- Use a transaction block
Current schema: {PASTE_SCHEMA}
Why it works: It ensures you get a complete migration, including rollback, which is essential for production.
9. The SQL Error Diagnostician
When to use: You have an error message and you're stuck.
The prompt:
I'm getting the following PostgreSQL error:
{PASTE_ERROR_MESSAGE}
Here is my query:
{PASTE_QUERY}
Explain the cause of the error and provide a fixed version.
Why it works: The AI can quickly spot syntax errors, type mismatches, or logic issues.
10. The Data Analysis Assistant
When to use: You want to explore data and get insights.
The prompt:
Given the following database schema, write a PostgreSQL query to answer this business question: {YOUR_QUESTION}
Also, provide a brief analysis of what the result means and suggest possible follow-up queries.
Schema: {PASTE_SCHEMA}
Why it works: It not only gives you the query but also the interpretation, helping you make data-driven decisions.
11. The Window Function Wizard
When to use: You need complex calculations like running totals, moving averages, or ranking.
The prompt:
Write a PostgreSQL query that uses window functions to [describe your goal, e.g., "calculate a running total of sales per month"].
Include:
- The OVER clause with PARTITION BY and ORDER BY
- The appropriate window function (ROW_NUMBER, RANK, SUM, AVG, etc.)
- A frame specification if needed
- An explanation of the logic
Schema: {PASTE_SCHEMA}
Why it works: Window functions are powerful but tricky. This prompt gets a correct implementation with explanation.
12. The CTE vs Subquery Advisor
When to use: You're deciding between a CTE and a subquery for readability and performance.
The prompt:
I have the following query that uses a subquery:
{PASTE_QUERY}
Should I rewrite it as a CTE? Explain the pros and cons in this specific case, and provide the CTE version if beneficial. Consider readability, performance, and whether the subquery is referenced multiple times.
Why it works: This gives you a reasoned recommendation, not just a rewrite.
13. The PostgreSQL Tuning Parameter Advisor
When to use: You need to adjust PostgreSQL server parameters for better performance.
The prompt:
I have a PostgreSQL server with the following settings:
{PASTE_SHOW_CONFIG}
My workload is [describe, e.g., "OLTP with heavy reads"]. Recommend changes to improve performance, such as:
- shared_buffers
- effective_cache_size
- work_mem
- maintenance_work_mem
- max_connections
Explain the rationale and trade-offs for each.
Why it works: AI can suggest baseline values based on your workload and hardware, but always test in a staging environment.
14. The SQL Query Explainer
When to use: You have a complex query written by someone else, and you need to understand it.
The prompt:
Explain the following PostgreSQL query step by step, as if I'm a beginner. Break it down into logical parts:
- What the CTEs do
- What the joins are doing
- What the aggregate functions calculate
- What the final SELECT returns
Then, suggest simplifications if any.
Query:
{PASTE_QUERY}
Why it works: This turns a mysterious query into a learning opportunity.
15. The SQL Unit Tester
When to use: You want to write tests for your SQL logic.
The prompt:
Write a set of unit tests for the following PostgreSQL function or query. Use a testing framework like pgTAP. Include:
- Test cases for typical inputs
- Edge cases (empty sets, nulls, duplicates)
- Assertions for expected results
Here is the code:
{PASTE_CODE}
Why it works: It automates the creation of comprehensive test suites, ensuring your logic is correct.
These prompts aren't just for saving time — they're for learning. By forcing the AI to explain its reasoning, you'll gradually internalize SQL best practices. The next time you face a tricky query, you'll know exactly how to structure your thinking. So go ahead, copy these into your favorite AI assistant, and watch your SQL skills — and your database performance — improve. If you have a prompt that works wonders for you, I'd love to hear about it in the comments!
Comments