You're staring at a query that's been running for 47 seconds, the dashboard is timing out, and your boss is tapping their foot. Or maybe you're a product manager who just needs a quick answer from the database but doesn't know a JOIN from a subquery. Either way, you're not alone: research from Stripe and others shows that developers spend roughly 40% of their time on database-related tasks, and much of it is writing and tuning SQL. What if an AI assistant could handle the boilerplate, catch the syntax errors, and even suggest the right index? That's exactly what we'll unlock in this guide—12 practical prompts you can copy, paste, and adapt to go from SQL novice to PostgreSQL pro, with a side trip into MongoDB.
The prompts are organized by task: from generating complex queries and explaining what someone else's query does, to diagnosing slow performance and designing schemas. Each one includes a real-world example and the exact prompt to use. No fluff, just working code and expert commentary. Let's get your database running on autopilot.
1. Write a Complex SQL Query from a Plain-English Description
When to use: You need to extract data that involves multiple tables, aggregations, or window functions, but you're not sure how to structure the SQL. This prompt turns your vague request into a ready-to-run query, and it asks clarifying questions if needed.
The prompt:
Act as a senior SQL developer. Write a PostgreSQL query that does the following: [describe your goal in plain English, e.g., "find the top 3 customers by revenue in the last 6 months, including their total orders and average order value"].
Include:
- The exact tables and columns you assume (e.g., customers(id, name), orders(id, customer_id, total, created_at))
- Proper JOIN conditions, GROUP BY, and ORDER BY
- Use window functions if appropriate
- Add comments to explain each part
- Output only the SQL code, no explanation.
Example:
Suppose you want to see which products are frequently bought together. You'd say:
Write a query to find pairs of products that appear in the same order at least 5 times. Tables: order_items(order_id, product_id), products(id, name). Output the product pair names and the count.
The AI will generate something like this (you'd then verify against your schema):
SELECT p1.name AS product_a, p2.name AS product_b, COUNT(*) AS times_together
FROM order_items oi1
JOIN order_items oi2 ON oi1.order_id = oi2.order_id AND oi1.product_id < oi2.product_id
JOIN products p1 ON oi1.product_id = p1.id
JOIN products p2 ON oi2.product_id = p2.id
GROUP BY p1.id, p2.id
HAVING COUNT(*) >= 5
ORDER BY times_together DESC;
Pro tip: Always specify your actual table names and columns; the AI can't guess them. If you're unsure, ask first: "What tables do you have?"
2. Explain This SQL Query Like I'm Five
When to use: You inherited a messy query or a colleague left a 200-line SQL monster with no comments. This prompt helps you understand it quickly, so you can maintain or fix it.
The prompt:
Act as a SQL expert. Explain the following query step by step, as if to a junior developer. For each part, tell me: what it does, why it's written that way, and any potential pitfalls (e.g., NULL handling, performance issues). Then suggest a simpler alternative if one exists.
[Paste the query here]
Example:
You paste a query that uses a LEFT JOIN and a subquery. The AI will break it down: "First, the subquery in the FROM clause computes the latest order date per customer, then the main query joins that with customers to get their details. The LEFT JOIN ensures customers with no orders are included. A pitfall: if a customer has multiple orders on the same date, you might get duplicates—consider using DISTINCT ON." It might even show you a CTE version that's more readable.
Pro tip: Use this before you attempt to optimize. You can't fix what you don't understand.
3. Find and Fix Slow Queries: EXPLAIN ANALYZE Interpreter
When to use: You have a query that runs too long, and you've run EXPLAIN ANALYZE but the output is cryptic. This prompt turns that output into actionable advice.
The prompt:
Act as a PostgreSQL performance expert. Here is the output of `EXPLAIN (ANALYZE, BUFFERS)` for a slow query. Explain what each part means, identify the bottleneck (e.g., sequential scan, high buffer usage, nested loop), and recommend specific fixes—such as adding an index, rewriting the query, or adjusting PostgreSQL configuration. Provide the exact CREATE INDEX statement if needed.
[Paste the EXPLAIN output]
Example:
You paste output that shows a Seq Scan on orders with a filter. The AI will say: "The sequential scan indicates no index is used. Add an index on orders(customer_id). Also, the filter on status = 'pending' could benefit from a partial index: CREATE INDEX idx_orders_pending ON orders(customer_id) WHERE status = 'pending';" It might also warn about high buffer usage if your shared_buffers is too low.
Pro tip: Always include the table schema in the prompt, because the AI needs to know existing indexes to give accurate advice.
4. Design a Database Schema for a New Project
When to use: You're starting a new application and need a normalized, performant schema. This prompt gives you a complete design with tables, constraints, and indexes.
The prompt:
Act as a database architect. Design a PostgreSQL schema for a [type of application, e.g., e-commerce platform]. Requirements:
- Store users, products, orders, and reviews
- Support search by product name and category
- Need to track inventory
- Expected to grow to millions of rows
Provide:
- CREATE TABLE statements with appropriate data types (use VARCHAR, NUMERIC, TIMESTAMPTZ, etc.)
- Primary keys, foreign keys, and CHECK constraints
- An index strategy, including which columns to index and why (e.g., B-tree, GIN for full-text search)
- A note on partitioning if you think it's needed
- Justify your choices for normalization (3NF) and where you'd denormalize for performance.
Example:
The AI will output a series of CREATE TABLE statements, like:
CREATE TABLE users (
id BIGSERIAL PRIMARY KEY,
email TEXT UNIQUE NOT NULL,
created_at TIMESTAMPTZ DEFAULT now()
);
CREATE TABLE products (
id BIGSERIAL PRIMARY KEY,
name TEXT NOT NULL,
description TEXT,
price NUMERIC(10,2) CHECK (price >= 0),
category_id INT REFERENCES categories(id)
);
CREATE INDEX idx_products_name_trgm ON products USING GIN (name gin_trgm_ops);
It will also explain why gin_trgm_ops helps with fuzzy search.
Pro tip: Mention the scale and traffic—this affects partitioning (e.g., by range on created_at) and indexing choices.
5. Convert a Slow ORM Query to Raw SQL
When to use: Your Django/ActiveRecord/Hibernate query is generating inefficient SQL, or you need to do something the ORM can't express. This prompt translates ORM code to optimized raw SQL.
The prompt:
Act as a database expert. Here is an ORM query that performs poorly: [paste ORM code, e.g., Django queryset or Rails ActiveRecord]. Rewrite it as a single optimized raw SQL statement. Explain why your SQL version is faster (e.g., avoids N+1 queries, uses a single join, pushes down filtering). Also show how to keep the ORM for simple queries but use this raw SQL for the hot path.
Example:
You have a Django queryset like:
Order.objects.filter(user_id=1).select_related('product')
The AI will show you the underlying SQL and suggest a join instead of separate queries. It might then recommend using EXISTS to avoid loading unnecessary fields.
Pro tip: Always test the generated SQL with EXPLAIN ANALYZE to confirm the improvement.
6. Write a Recursive CTE for Hierarchical Data
When to use: You need to query tree-like structures: categories with subcategories, employee hierarchies, or bill-of-materials. Recursive CTEs are tricky, and this prompt helps you nail them.
The prompt:
Act as a PostgreSQL expert. Write a recursive CTE to traverse a [type of hierarchy] stored in table [name] with columns [id, parent_id, name]. I want to get all descendants of a given node [id=...], including the depth level. Also show how to get all ancestors. Include a loop safeguard (e.g., MAXDEPTH) to avoid infinite recursion.
Example:
For a category tree, you'd get:
WITH RECURSIVE category_tree AS (
SELECT id, name, parent_id, 1 AS depth
FROM categories
WHERE id = 1 -- starting node
UNION ALL
SELECT c.id, c.name, c.parent_id, ct.depth + 1
FROM categories c
JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT * FROM category_tree;
The AI will also explain the UNION ALL and the anchor vs. recursive part.
Pro tip: If you're on PostgreSQL 14+, you can use SEARCH and CYCLE clauses for automatic cycle detection—ask the AI to include that.
7. Generate Realistic Test Data for Your Database
When to use: You need sample data for development or testing, but you don't want to use production data. This prompt creates a script to insert realistic rows.
The prompt:
Act as a data engineer. Write a PostgreSQL script to generate 10,000 rows of test data in the `users` table (id, name, email, created_at). Use realistic names and emails (e.g., john.doe@example.com). Vary the created_at over the last year. Use `generate_series` and `random()`. Also generate 100,000 orders that reference these users, with total between 1 and 1000, and created_at after the user's created_at.
Example:
You'll get something like:
INSERT INTO users (name, email, created_at)
SELECT
'user_' || i,
'user'
|| i || '@example.com',
now() - (random() * interval '1 year')
FROM generate_series(1, 10000) AS i;
Plus a more advanced script for orders, ensuring referential integrity.
Pro tip: For testing performance, generate data that matches your production distribution (e.g., some users have many orders). Ask for a skewed distribution.
8. Normalize or Denormalize? Refactor Your Schema
When to use: Your schema has redundant data or you're experiencing slow reads. This prompt analyzes your schema and suggests normalization or denormalization, with trade-offs.
The prompt:
Act as a database normalization expert. Here is my current schema: [list tables and columns, e.g., orders(id, customer_name, customer_email, product_name, product_price)]. I have issues with [update anomalies / slow queries]. Analyze the schema, identify violations of normal forms (1NF, 2NF, 3NF), and propose a normalized design. Then, for my most frequent query [describe], suggest where denormalization might be acceptable for performance, and explain the trade-offs.
Example:
You paste a denormalized orders table. The AI will break it into customers, products, and order_items, showing the FKs. It will then note that for a dashboard that always shows the customer name and product name, a denormalized view or materialized view might be better.
Pro tip: Use this before you start a new feature to avoid future pain.
9. Optimize MongoDB Queries: Indexes and Aggregation Pipelines
When to use: You're working with MongoDB and need to speed up queries or write complex aggregations. This prompt addresses the NoSQL side.
The prompt:
Act as a MongoDB performance expert. I have a collection `orders` with documents like [sample JSON]. My query [describe] is slow. Explain how to check the current query plan with `explain()`, and recommend the proper indexes (e.g., compound, partial, or TTL). Also, write an aggregation pipeline to [goal, e.g., group by customer and calculate total spend per month], using `$match`, `$group`, `$project`, and `$sort`. Explain each stage.
Example:
You provide a sample document:
{ "customer_id": "123", "total": 250, "created_at": ISODate("2026-01-01") }
You'll get advice like: create a compound index on {customer_id: 1, created_at: -1} to speed up the group. The aggregation pipeline would look like:
db.orders.aggregate([
{ $match: { created_at: { $gte: ISODate("2026-01-01") } } },
{ $group: { _id: "$customer_id", totalSpend: { $sum: "$total" } } },
{ $sort: { totalSpend: -1 } },
{ $limit: 10 }
])
Pro tip: Always include the indexes you already have; the AI can't know them.
10. Find and Fix Slow Queries: EXPLAIN ANALYZE Interpreter
When to use: You have a query that runs too long, and you've run EXPLAIN ANALYZE but the output is cryptic. This prompt turns that output into actionable advice.
The prompt:
Act as a PostgreSQL performance expert. Here is the output of `EXPLAIN (ANALYZE, BUFFERS)` for a slow query. Explain what each part means, identify the bottleneck (e.g., sequential scan, high buffer usage, nested loop), and recommend specific fixes—such as adding an index, rewriting the query, or adjusting PostgreSQL configuration. Provide the exact CREATE INDEX statement if needed.
[Paste the EXPLAIN output]
Example:
You paste output that shows a Seq Scan on orders with a filter. The AI will say: "The sequential scan indicates no index is used. Add an index on orders(customer_id). Also, the filter on status = 'pending' could benefit from a partial index: CREATE INDEX idx_orders_pending ON orders(customer_id) WHERE status = 'pending';" It might also warn about high buffer usage if your shared_buffers is too low.
Pro tip: Always include the table schema in the prompt, because the AI needs to know existing indexes to give accurate advice.
10. Find and Fix Slow Queries: EXPLAIN ANALYZE Interpreter
When to use: You have a query that runs too long, and you've run EXPLAIN ANALYZE but the output is cryptic. This prompt turns that output into actionable advice.
The prompt:
Act as a PostgreSQL performance expert. Here is the output of `EXPLAIN (ANALYZE, BUFFERS)` for a slow query. Explain what each part means, identify the bottleneck (e.g., sequential scan, high buffer usage, nested loop), and recommend specific fixes—such as adding an index, rewriting the query, or adjusting PostgreSQL configuration. Provide the exact CREATE INDEX statement if needed.
[Paste the EXPLAIN output]
Example:
You paste output that shows a Seq Scan on orders with a filter. The AI will say: "The sequential scan indicates no index is used. Add an index on orders(customer_id). Also, the filter on status = 'pending' could benefit from a partial index: CREATE INDEX idx_orders_pending ON orders(customer_id) WHERE status = 'pending';" It might also warn about high buffer usage if your shared_buffers is too low.
Pro tip: Always include the table schema in the prompt, because the AI needs to know existing indexes to give accurate advice.
11. Write a Complex SQL Query from a Plain-English Description
When to use: You need to extract data that involves multiple tables, aggregations, or window functions, but you're not sure how to structure the SQL. This prompt turns your vague request into a ready-to-run query, and it asks clarifying questions if needed.
The prompt:
Act as a senior SQL developer. Write a PostgreSQL query that does the following: [describe your goal in plain English, e.g., "find the top 3 customers by revenue in the last 6 months, including their total orders and average order value"].
Include:
- The exact tables and columns you assume (e.g., customers(id, name), orders(id, customer_id, total, created_at))
- Proper JOIN conditions, GROUP BY, and ORDER BY
- Use window functions if appropriate
- Add comments to explain each part
- Output only the SQL code, no explanation.
Example:
Suppose you want to see which products are frequently bought together. You'd say:
Write a query to find pairs of products that appear in the same order at least 5 times. Tables: order_items(order_id, product_id), products(id, name). Output the product pair names and the count.
The AI will generate something like this (you'd then verify against your schema):
SELECT p1.name AS product_a, p2.name AS product_b, COUNT(*) AS times_together
FROM order_items oi1
JOIN order_items oi2 ON oi1.order_id = oi2.order_id AND oi1.product_id < oi2.product_id
JOIN products p1 ON oi1.product_id = p1.id
JOIN products p2 ON oi2.product_id = p2.id
GROUP BY p1.id, p2.id
HAVING COUNT(*) >= 5
ORDER BY times_together DESC;
Pro tip: Always specify your actual table names and columns; the AI can't guess them. If you're unsure, ask first: "What tables do you have?"
12. Generate Realistic Test Data for Your Database
When to use: You need sample data for development or testing, but you don't want to use production data. This prompt creates a script to insert realistic rows.
The prompt:
Act as a data engineer. Write a PostgreSQL script to generate 10,000 rows of test data in the `users` table (id, name, email, created_at). Use realistic names and emails (e.g., john.doe@example.com). Vary the created_at over the last year. Use `generate_series` and `random()`. Also generate 100,000 orders that reference these users, with total between 1 and 1000, and created_at after the user's created_at.
Example:
You'll get something like:
INSERT INTO users (name, email, created_at)
SELECT
'user_' || i,
'user'
|| i || '@example.com',
now() - (random() * interval '1 year')
FROM generate_series(1, 10000) AS i;
Plus a more advanced script for orders, ensuring referential integrity.
Pro tip: For testing performance, generate data that matches your production distribution (e.g., some users have many orders). Ask for a skewed distribution.
You've now got a dozen prompts that cover the most common database tasks. The key is to treat the AI as a junior partner: give it context, verify its output, and iterate. As you get comfortable, you'll find yourself writing complex queries in minutes instead of hours. Remember, even the best prompt won't replace your own understanding of your data model—but it will get you 90% of the way there, and that's a huge win.
If you want to take this further, consider integrating these prompts into your daily workflow with an AI agent like ASI Biont, which can run them against your actual database, execute the SQL, and return the results—all from a natural language request. That's the real autopilot. Start with one prompt today, and soon you'll wonder how you ever worked without it.
Comments