You know the drill: a query that takes forever, an index that does nothing, a schema that makes you cringe. Databases are powerful, but they're also unforgiving. The difference between a smooth operation and a late-night firefighting session often comes down to the details. That's where AI prompts come in. They're not magic, but they're the next best thing: a way to get expert-level advice, instantly, for free. This guide is your cheat sheet—ten specific, copy-paste-ready prompts for SQL, PostgreSQL, and MongoDB that will help you optimize queries, design better schemas, and debug like a pro. No fluff, just what works.
Why Prompts for Databases?
Before we dive in, let's be clear: these prompts are not a replacement for learning. They're a force multiplier. They help you explore options, understand trade-offs, and get unstuck. For example, instead of staring at a slow query, you can ask for a detailed execution plan analysis. Instead of guessing at index columns, you can ask for a recommendation based on your workload. The key is to provide as much context as possible—schema, query, and what you've already tried. The more specific you are, the better the response.
The Prompt Arsenal: 10 Prompts to Use Today
Here are ten prompts, organized by task. Each one includes an example of how to use it. Remember: treat the AI's output as a starting point. Always test, always verify.
1. The SQL Query Whisperer (SQL)
Prompt: "I'm working with a database that has the following schema: [INSERT SCHEMA]. I need to write a query that [describe the task]. The database engine is [MySQL/PostgreSQL/etc.]. Please provide the SQL query, and explain how it works step by step. Also, suggest an alternative approach if one exists."
Why it works: This prompt forces the AI to understand your schema and your goal before writing code. It also asks for an explanation, which helps you learn and verify the logic.
Example: "I have a 'users' table (id, name, email, created_at) and an 'orders' table (id, user_id, amount, created_at). I need to find the top 5 users by total order amount in the last month. The database is PostgreSQL." The AI will generate a query with a JOIN, a GROUP BY, and a LIMIT, and explain why it uses a LEFT JOIN or INNER JOIN depending on whether you want users with zero orders.
2. The Execution Plan Decoder (SQL)
Prompt: "I have a slow query in [PostgreSQL/MySQL]. Here's the execution plan (EXPLAIN ANALYZE output): [PASTE OUTPUT]. The query is: [YOUR QUERY]. Please analyze the plan and tell me what's causing the slowdown. Be specific: which steps are expensive, and what indexes or changes would help?"
Why it works: This prompt gives the AI real data to work with. You're not asking for generic advice; you're asking for a diagnosis of a specific problem.
Example: Paste an EXPLAIN ANALYZE output showing a Seq Scan on a large table. The AI will point out the full table scan and suggest an index, or a change in the WHERE clause to use an existing index.
3. The Index Architect (SQL)
Prompt: "I have the following table: [SCHEMA]. The workload is [describe: heavy reads, heavy writes, mixed]. I see these slow queries: [QUERY 1], [QUERY 2]. Based on this, what indexes should I create? Please provide the exact CREATE INDEX statements, and explain the trade-offs (e.g., index size, write performance)."
Why it works: This prompt focuses on a specific pain point and asks for actionable DDL. It also forces the AI to consider the trade-offs, which is crucial for real-world decisions.
Example: For a table with millions of rows and a query that filters on status and created_at, the AI might recommend a composite index on (status, created_at) and explain why a single-column index on status is less effective.
4. The Schema Designer (SQL)
Prompt: "I'm designing a database for [describe the application]. The core entities are [list them]. I'm using [PostgreSQL/MySQL]. Please propose a normalized schema (3NF) and then a denormalized alternative for read-heavy workloads. Include table definitions with data types, primary keys, and foreign keys. Explain the pros and cons of each approach."
Why it works: This prompt asks for both normalization and denormalization, giving you a full range of options. It also asks for explanations, which is essential for making informed decisions.
Example: For an e-commerce platform, the AI might propose a normalized schema with separate tables for users, orders, and products, then a denormalized one with an order_summary table for fast dashboard queries.
5. The Query Explainer (SQL)
Prompt: "Here's a complex SQL query: [PASTE QUERY]. Please explain what it does in plain English, step by step. If there are any subqueries or joins, break those down too. Also, point out any potential pitfalls or edge cases that might trip someone up."
Why it works: This is great for learning or for debugging a query someone else wrote. It helps you understand the logic and spot errors.
Example: A query with multiple CTEs and a window function might be hard to parse. The AI will break it down into logical steps and explain what each CTE does.
6. The PostgreSQL Performance Tuner (PostgreSQL)
Prompt: "I'm running PostgreSQL 14 on a server with [CPU, RAM, disk]. My workload is [OLTP/OLAP/mixed]. Here are my current settings: [PASTE from postgresql.conf]. What settings should I change to improve performance for my workload? Please be specific with the parameter names and values, and explain why each change helps."
Why it works: This prompt leverages the AI's knowledge of PostgreSQL internals and gives you concrete, actionable advice.
Example: The AI might suggest increasing shared_buffers to 25% of RAM, setting effective_cache_size to 50%, and adjusting work_mem for sorts, explaining the rationale behind each.
7. The PostgreSQL Index Advisor (PostgreSQL)
Prompt: "I have a slow query in PostgreSQL: [QUERY]. The table is [SCHEMA]. Can you recommend an index that would speed it up? Please use the pg_stat_user_indexes and pg_stat_all_tables views to analyze usage if possible. Provide the CREATE INDEX statement and explain how it helps."
Why it works: This prompt asks for an index recommendation based on a specific query, and it suggests using system views for data-driven analysis.
Example: For a query with WHERE user_id = $1 ORDER BY created_at DESC, the AI might suggest a btree index on (user_id, created_at DESC).
8. The MongoDB Schema Modeler (MongoDB)
Prompt: "I'm designing a MongoDB schema for [describe the application]. The main entities are [entities] and their relationships are [describe]. The read/write ratio is [X:Y]. Please propose a document schema, including embedded documents and references where appropriate. Provide sample documents and explain the trade-offs."
Why it works: MongoDB schema design is very different from relational. This prompt helps you leverage the flexibility of the document model.
Example: For a blog platform, the AI might suggest embedding comments within a post document for small comment counts, or using a separate comments collection if comments are huge and frequently accessed independently.
9. The MongoDB Query Optimizer (MongoDB)
Prompt: "I have a MongoDB collection with the following structure: [SCHEMA]. My query is: [QUERY]. It's slow. Can you use explain() to analyze it? Here's the output: [PASTE]. What indexes would help? Please provide the createIndex commands and explain how they improve performance."
Why it works: This prompt uses the same pattern as the SQL one—give the AI real data and ask for specific actions.
Example: The AI might see a COLLSCAN in the explain output and suggest a compound index on the fields used in the query's filter and sort.
10. The Data Model Refactorer (MongoDB)
Prompt: "I have a MongoDB collection with documents like this: [SAMPLE]. My application now needs to [new requirement]. Should I embed or reference? Please suggest a schema change, provide the migration script (using the MongoDB shell or a driver), and explain the impact on queries."
Why it works: This prompt helps you evolve your schema as needs change, which is a common real-world scenario.
Example: If you're adding a feature that requires frequent updates to a subdocument that's currently embedded, the AI might recommend moving it to a separate collection.
Putting It All Together: A Real-World Case
Let's say you have a PostgreSQL database for an e-commerce site. Your orders query is slow. You'd use prompt #2 to analyze the execution plan. The AI identifies a sequential scan on the orders table. Then you use prompt #3 to get index recommendations. You create a composite index on (user_id, created_at). The query speeds up dramatically. That's the power of using these prompts in sequence.
Final Thoughts
These prompts are your starting point. The more context you provide, the better the output. But don't stop there. Use the explanations to learn, and always test the suggestions in a safe environment. Databases are complex, but with the right questions, you can unlock their full potential. Now go optimize something!
If you're looking for a structured way to master these skills, consider checking out the courses at asibiont.com — they offer hands-on learning with AI assistance.
Comments