SQL Whisperer: 10 Battle-Tested AI Prompts for PostgreSQL and MongoDB
Every developer knows the scenario: you're staring at a sluggish query, or trying to design a schema that won't haunt you in six months, or wrestling with a MongoDB aggregation pipeline that resembles a plate of spaghetti. AI tools like ChatGPT, Claude, or specialized assistants can be a lifesaver—but only if you know how to ask. The difference between a generic, useless answer and a production-ready solution often comes down to the prompt. This isn't about magic; it's about structured communication. Here are 10 practical prompts I've refined through real projects, from schema design to query optimization, with concrete examples for both PostgreSQL and MongoDB.
1. Schema Design: From Requirements to Normalized DDL
When to use: You're starting a new feature or a greenfield project. You have a rough idea of the data, but need a solid, normalized schema with proper constraints.
The prompt:
Act as a senior database architect. I'm building a [describe your application, e.g., a multi-tenant SaaS for project management]. Here are the core entities and their relationships: [list them, e.g., users, projects, tasks, comments]. Design a PostgreSQL schema that:
- Uses appropriate data types (e.g., UUID for IDs, timestamptz for times)
- Implements normalization (3NF) unless there's a strong reason not to
- Includes primary keys, foreign keys with ON DELETE rules, and unique constraints
- Adds indexes for foreign keys and common query patterns (e.g., WHERE clauses)
- Includes a comment for each table explaining its purpose
Output the full DDL as a single SQL block.
Example usage: For the project management app, the AI might return:
CREATE TABLE users (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
email TEXT UNIQUE NOT NULL,
name TEXT NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE TABLE projects (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
owner_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
name TEXT NOT NULL,
description TEXT,
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE TABLE tasks (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
project_id UUID NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
assignee_id UUID REFERENCES users(id) ON DELETE SET NULL,
title TEXT NOT NULL,
status TEXT NOT NULL DEFAULT 'todo',
due_date DATE
);
CREATE INDEX idx_tasks_project_id ON tasks(project_id);
CREATE INDEX idx_tasks_assignee_id ON tasks(assignee_id);
Why it works: By specifying normalization level, data types, and constraints, you get a production-ready DDL instead of a vague outline. The AI acts as an architect, not just a code generator.
2. Translating Business Logic into SQL: Natural Language to Query
When to use: You need to extract data that requires joins, subqueries, or window functions, but the SQL isn't immediately obvious.
The prompt:
You are a SQL expert. Given the following schema (tables and columns): [paste schema]. Write a PostgreSQL query that answers: [describe the business question in plain English]. The query should:
- Use explicit JOINs (INNER/LEFT) with aliases
- Use window functions (ROW_NUMBER, LAG, etc.) if applicable
- Be optimized for performance (avoid unnecessary subqueries if a JOIN is better)
- Include comments explaining each step
Return only the SQL, no explanations.
Example usage: With the schema above, you ask: "Find the top 3 most recent tasks in each project, including the assignee's name and project name." The AI returns:
WITH ranked_tasks AS (
SELECT t.*, p.name AS project_name, u.name AS assignee_name,
ROW_NUMBER() OVER (PARTITION BY t.project_id ORDER BY t.created_at DESC) AS rn
FROM tasks t
LEFT JOIN projects p ON t.project_id = p.id
LEFT JOIN users u ON t.assignee_id = u.id
)
SELECT project_name, title, status, assignee_name, created_at
FROM ranked_tasks
WHERE rn <= 3
ORDER BY project_name, created_at DESC;
Why it works: The prompt forces the AI to use modern SQL features and explicitly ties the query to your schema, avoiding generic or incorrect column names.
3. Optimizing a Slow Query: EXPLAIN ANALYZE in the Loop
When to use: You have a query that's painfully slow, and you need to understand why and fix it.
The prompt:
I have a slow PostgreSQL query. Here is the query: [paste query]. And here is the output of `EXPLAIN (ANALYZE, BUFFERS)` : [paste output].
Analyze the execution plan and identify the bottleneck. Then:
1. Explain what's causing the slowness in simple terms (e.g., seq scan vs index scan, hash join vs nested loop).
2. Suggest 2-3 concrete improvements: index additions, query rewrites, or configuration tweaks.
3. Provide the modified query and the DDL for any new indexes.
4. Explain how each improvement would affect the plan (e.g., 'this will change the seq scan to an index scan').
Example usage: Suppose you have a query that filters a large orders table by customer_id and order_date. The EXPLAIN shows a sequential scan because there's no index. The AI suggests:
CREATE INDEX idx_orders_customer_date ON orders(customer_id, order_date DESC);
And rewrites the query to use a covering index if possible.
Why it works: The prompt grounds the AI in actual execution data. It's not guessing—it's analyzing real numbers. This is the essence of performance tuning.
4. Generating Realistic Test Data (with a Twist)
When to use: You need to populate a database for testing, but you want realistic data that respects constraints.
The prompt:
Generate a PostgreSQL script that inserts [N] rows of realistic test data into the following tables: [list tables and columns]. Requirements:
- Use `generate_series` for sequential data where appropriate
- Use `random()` and `md5()` to create realistic-looking strings (e.g., emails, names)
- Respect foreign keys (use subqueries to pick random IDs)
- For dates, use `now() - (random() * interval '365 days')` to get random dates within the last year
- Output the script with BEGIN/COMMIT transaction wrapper
Example usage: For the project management schema, the AI generates a script that inserts 100 users, 50 projects, and 500 tasks with random assignments.
Why it works: It forces the AI to use PostgreSQL-specific functions, making the data generation fast and efficient.
5. Writing a Complex MongoDB Aggregation Pipeline
When to use: You need to transform or analyze data in MongoDB, and the aggregation pipeline is multi-stage.
The prompt:
Act as a MongoDB expert. I have a collection called `orders` with documents like: [paste sample document]. Write an aggregation pipeline that answers: [describe the business question]. The pipeline should:
- Use `$match` early to filter documents
- Use `$group` with `_id` for grouping and `$sum`, `$avg`, `$min`, `$max` as needed
- Use `$project` to reshape documents and `$sort` for ordering
- Use `$lookup` for joining with other collections (e.g., `customers`)
- Explain each stage in a comment
Return the pipeline as a JavaScript array.
Example usage: For an e-commerce dataset, you ask: "Calculate the total revenue per customer per month, only for orders with status 'completed'." The AI might produce:
[
{ $match: { status: 'completed' } },
{ $addFields: { month: { $dateToString: { format: '%Y-%m', date: '$orderDate' } } } },
{ $group: { _id: { customerId: '$customerId', month: '$month' }, totalRevenue: { $sum: '$amount' } } },
{ $sort: { '_id.month': 1, totalRevenue: -1 } }
]
Why it works: The prompt enforces best practices (early $match, efficient $group) and ensures the pipeline is syntactically correct for MongoDB's engine.
6. Designing MongoDB Indexes for Your Queries
When to use: Your MongoDB queries are getting slow, and you need to decide which indexes to create.
The prompt:
Given the following MongoDB queries (list them with their filter fields and sort order), recommend indexes. For each index:
- Provide the exact `createIndex` command
- Explain why this index helps (e.g., covers the query, uses equality then range)
- Mention any trade-offs (e.g., index size, write performance)
Also consider compound indexes for queries that filter on multiple fields.
Example usage: For the orders collection, if you frequently query by customerId and sort by orderDate, the AI recommends:
db.orders.createIndex({ customerId: 1, orderDate: -1 })
Why it works: It makes the AI think about real query patterns and index design principles, not just syntax.
7. Migrating Between Databases (e.g., MySQL to PostgreSQL)
When to use: You're moving a legacy database to a modern one, and you need help translating schema and queries.
The prompt:
I'm migrating from [source DB] to [target DB]. Here is a table definition in [source]: [paste DDL]. Convert it to [target] syntax, taking into account:
- Data type differences (e.g., AUTO_INCREMENT vs SERIAL, TEXT vs VARCHAR)
- Constraint syntax (e.g., ON UPDATE CASCADE)
- Any quirks (e.g., backticks vs double quotes)
Also, convert the following queries: [paste queries]. Ensure they use [target] functions and semantics.
Example usage: Converting a MySQL ENGINE=InnoDB table to PostgreSQL might yield:
CREATE TABLE users (
id SERIAL PRIMARY KEY,
email VARCHAR(255) UNIQUE NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
Why it works: The prompt specifies the exact differences to handle, preventing common migration pitfalls.
8. Automating Routine Tasks with Stored Procedures
When to use: You need to encapsulate business logic in the database, and you want to generate a stored procedure or function.
The prompt:
Write a PostgreSQL function that does the following: [describe the logic, e.g., 'when a new order is inserted, update the customer's total_spent']. Use PL/pgSQL. The function should:
- Use `NEW` to reference the inserted row
- Handle errors with `EXCEPTION` blocks if necessary
- Be transaction-safe
- Include a comment explaining the logic
Provide the full `CREATE OR REPLACE FUNCTION` statement.
Example usage: The AI generates a trigger function and the trigger itself.
Why it works: It leverages the AI's knowledge of PL/pgSQL, which is not trivial to write from scratch.
9. Tuning PostgreSQL Configuration (postgresql.conf)
When to use: Your PostgreSQL instance is underperforming, and you suspect configuration issues.
The prompt:
I have a PostgreSQL 15 instance with [specs: RAM, CPU, disk type, workload description]. Here is my current `postgresql.conf` (relevant parts): [paste]. Suggest changes to improve performance. For each change:
- Explain why it helps (e.g., increasing `shared_buffers` reduces disk I/O)
- Provide the recommended value and how to compute it (e.g., 25% of RAM for shared_buffers)
- Note any risks or monitoring needed
Also mention any settings that might be causing problems (e.g., `fsync=off`).
Example usage: For a 16GB RAM server, the AI might suggest shared_buffers = 4GB, work_mem = 64MB, and effective_cache_size = 12GB.
Why it works: It grounds the advice in your actual environment and forces the AI to explain the reasoning, so you can make informed decisions.
10. Explaining a Query Plan in Plain English
When to use: You have a complex EXPLAIN output and want to understand what the database is doing.
The prompt:
Here is the EXPLAIN output for a query: [paste output]. Explain it in simple terms for a junior developer:
- What is the overall approach (e.g., nested loop join, hash join, seq scan)?
- Which operations are the most expensive (high rows or cost)?
- Are there any red flags (e.g., seq scans on large tables)?
- What would you recommend to improve it?
Use analogies if helpful.
Example usage: The AI might explain a hash join as "like using a lookup table instead of checking every row."
Why it works: It turns a cryptic output into actionable insights, which is invaluable for teaching and debugging.
Putting It All Together: A Practical Workflow
These prompts aren't isolated tricks—they form a workflow. Start with schema design (Prompt 1), use natural language for initial queries (Prompt 2), and when performance issues arise, dive into EXPLAIN (Prompt 3) and configuration (Prompt 9). For MongoDB, use Prompts 5 and 6 to handle aggregates and indexes. The key is to be specific: give the AI context, constraints, and examples. The more you invest in the prompt, the better the output.
AI tools are now part of every developer's toolkit. The difference between a junior and a senior is often the ability to ask the right question. With these prompts, you're not just getting answers—you're getting production-ready solutions that respect database best practices. Try them on your next project and see the difference.
Now, go query something interesting!
Comments