Introduction
Working with databases is a daily reality for any developer. Whether you're querying relational data in PostgreSQL, handling documents in MongoDB, or caching sessions in Redis, the quality of your prompts can make the difference between hours of debugging and a quick fix. This collection of 15 battle-tested prompts covers real-world scenarios: writing complex queries, optimizing indexes, designing migrations, and troubleshooting performance. Each prompt comes with a concrete example and code snippet, so you can copy, paste, and adapt immediately.
PostgreSQL Prompts
1. Generate a Query with Multiple Joins and Aggregations
Prompt: "Write a PostgreSQL query that joins orders, customers, and order_items tables. Group by customer and month, show total revenue, number of orders, and average order value. Filter for the last 6 months."
Why it works: This prompt forces the AI to handle JOIN logic, GROUP BY with date truncation, and multiple aggregate functions in one query.
Example usage:
SELECT
c.customer_id,
c.name,
DATE_TRUNC('month', o.order_date) AS month,
SUM(oi.quantity * oi.unit_price) AS total_revenue,
COUNT(DISTINCT o.order_id) AS order_count,
AVG(oi.quantity * oi.unit_price) AS avg_order_value
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
WHERE o.order_date >= NOW() - INTERVAL '6 months'
GROUP BY c.customer_id, c.name, DATE_TRUNC('month', o.order_date)
ORDER BY month DESC, total_revenue DESC;
2. Suggest Indexes for a Slow Query
Prompt: "Given a slow query SELECT * FROM logs WHERE user_id = ? AND created_at BETWEEN ? AND ? ORDER BY created_at DESC LIMIT 100, suggest appropriate indexes and show how to create them."
Why it works: It asks for index recommendations based on the WHERE clause and ORDER BY, teaching the developer about composite indexes and index order.
Example usage:
CREATE INDEX idx_logs_user_created ON logs (user_id, created_at DESC);
-- This covers both the filter and the sort
3. Write a Migration Script with Rollback
Prompt: "Write a PostgreSQL migration that adds a status column to the tasks table with a CHECK constraint for values 'pending', 'running', 'done', and 'failed'. Also add a default value and a rollback script."
Why it works: It covers schema changes, constraints, defaults, and migration best practices.
Example usage:
-- Up migration
ALTER TABLE tasks ADD COLUMN status TEXT NOT NULL DEFAULT 'pending';
ALTER TABLE tasks ADD CONSTRAINT chk_status CHECK (status IN ('pending', 'running', 'done', 'failed'));
-- Down migration
ALTER TABLE tasks DROP CONSTRAINT chk_status;
ALTER TABLE tasks DROP COLUMN status;
4. Optimize a Recursive CTE for Hierarchical Data
Prompt: "Optimize this recursive CTE that builds a category tree. The table categories has id, parent_id, and name. It's slow for 100k rows. Suggest indexes and rewrite."
Why it works: Recursive CTEs are tricky; this prompt teaches optimization through indexing and query structure.
Example usage:
-- Add index on parent_id
CREATE INDEX idx_categories_parent ON categories (parent_id);
WITH RECURSIVE category_tree AS (
SELECT id, parent_id, name, 1 AS depth
FROM categories
WHERE parent_id IS NULL
UNION ALL
SELECT c.id, c.parent_id, c.name, ct.depth + 1
FROM categories c
JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT * FROM category_tree ORDER BY depth, name;
5. Troubleshoot Lock Contention
Prompt: "I'm getting deadlocks on UPDATE inventory SET quantity = quantity - 1 WHERE product_id = ?. How can I avoid this using row-level locking and ordering?"
Why it works: It addresses a common production issue with a concrete solution.
Example usage:
-- Always lock rows in the same order
UPDATE inventory SET quantity = quantity - 1
WHERE product_id = ?
AND quantity > 0
AND pg_try_advisory_xact_lock(product_id);
-- Or use SELECT ... FOR UPDATE NOWAIT
MongoDB Prompts
6. Design an Aggregation Pipeline for Time-Series Data
Prompt: "Write a MongoDB aggregation pipeline that groups IoT sensor readings by hour, calculates average temperature and humidity, and outputs a result sorted by time."
Why it works: Aggregation pipelines are central to MongoDB; this prompt covers $match, $group, $sort, and date operators.
Example usage:
db.sensor_readings.aggregate([
{ $match: { timestamp: { $gte: ISODate('2026-01-01') } } },
{ $group: {
_id: { $dateToString: { format: '%Y-%m-%dT%H:00:00Z', date: '$timestamp' } },
avgTemp: { $avg: '$temperature' },
avgHumidity: { $avg: '$humidity' }
} },
{ $sort: { _id: 1 } }
]);
7. Create a Compound Index for Common Queries
Prompt: "My app queries users with filters on email, status, and created_at. Create a compound index that covers both exact match on email and range queries on created_at."
Why it works: It explains the ESR (Equality, Sort, Range) rule for compound indexes.
Example usage:
db.users.createIndex({ email: 1, status: 1, created_at: -1 });
8. Write a Schema Validation Rule
Prompt: "Add a JSON Schema validation to the orders collection that ensures amount is a positive number, status is one of 'pending', 'paid', 'shipped', and items is a non-empty array."
Why it works: Schema validation is underused; this prompt shows how to enforce data integrity.
Example usage:
db.runCommand({
collMod: 'orders',
validator: {
$jsonSchema: {
bsonType: 'object',
required: ['amount', 'status', 'items'],
properties: {
amount: { bsonType: 'number', minimum: 0 },
status: { enum: ['pending', 'paid', 'shipped'] },
items: { bsonType: 'array', minItems: 1 }
}
}
}
});
9. Migrate Data from PostgreSQL to MongoDB
Prompt: "Write a script to migrate users from a PostgreSQL table users (id, name, email, created_at) to a MongoDB collection. Use Python with psycopg2 and pymongo."
Why it works: It covers a common cross-database migration scenario.
Example usage:
import psycopg2
import pymongo
pg_conn = psycopg2.connect('postgresql://...')
mongo_client = pymongo.MongoClient('mongodb://...')
db = mongo_client.myapp
with pg_conn.cursor() as cur:
cur.execute('SELECT id, name, email, created_at FROM users')
for row in cur:
db.users.insert_one({
'old_id': row[0],
'name': row[1],
'email': row[2],
'created_at': row[3]
})
10. Explain the Difference Between $lookup and Embedded Documents
Prompt: "When should I use $lookup instead of embedding documents in MongoDB? Give a concrete example with an orders and customers collection."
Why it works: It clarifies a fundamental design decision.
Example usage:
// Embedding: store customer data inside order (for small, fixed data)
db.orders.insertOne({
order_id: 1,
customer: { name: 'Alice', email: 'alice@example.com' },
items: [...]
});
// $lookup: reference customer ID (for large, dynamic data)
db.orders.aggregate([
{ $lookup: {
from: 'customers',
localField: 'customer_id',
foreignField: 'customer_id',
as: 'customer'
} },
{ $unwind: '$customer' }
]);
Redis Prompts
11. Implement a Rate Limiter with Sorted Sets
Prompt: "Write a Redis-based rate limiter using Sorted Sets that allows 100 requests per minute per user. Show how to add a request and check the current count."
Why it works: Sorted Sets are perfect for sliding window rate limiting.
Example usage:
import redis
r = redis.Redis()
def is_rate_limited(user_id):
key = f'rate_limit:{user_id}'
now = int(time.time() * 1000)
window = 60000 # 1 minute
# Remove old entries
r.zremrangebyscore(key, 0, now - window)
# Count current entries
count = r.zcard(key)
if count >= 100:
return True
r.zadd(key, {f'{user_id}:{now}': now})
r.expire(key, 60)
return False
12. Cache Database Query Results with TTL
Prompt: "Cache the result of a slow PostgreSQL query in Redis with a 5-minute TTL. Use Python with redis-py and psycopg2."
Why it works: Caching is the most common Redis use case.
Example usage:
import redis, psycopg2, json
r = redis.Redis()
pg_conn = psycopg2.connect('postgresql://...')
def get_revenue_report():
cache_key = 'report:revenue'
cached = r.get(cache_key)
if cached:
return json.loads(cached)
with pg_conn.cursor() as cur:
cur.execute('SELECT ...')
result = cur.fetchall()
r.setex(cache_key, 300, json.dumps(result))
return result
13. Use Redis Streams for a Job Queue
Prompt: "Set up a job queue with Redis Streams. Show how to push a job and consume it with a consumer group."
Why it works: Streams are modern and robust for message queues.
Example usage:
import redis
r = redis.Redis()
# Producer
r.xadd('job_stream', {'task': 'send_email', 'to': 'user@example.com'}, maxlen=1000)
# Consumer group
r.xgroup_create('job_stream', 'workers', id='0', mkstream=True)
# Consumer
messages = r.xreadgroup('workers', 'worker1', {'job_stream': '>'}, count=1, block=5000)
14. Implement a Distributed Lock
Prompt: "Write a Redis-based distributed lock using SET NX with TTL and Lua script for safe release."
Why it works: Redlock is complex; this simple pattern works for most cases.
Example usage:
import redis, time
r = redis.Redis()
def acquire_lock(lock_name, acquire_timeout=10):
identifier = str(uuid.uuid4())
end = time.time() + acquire_timeout
while time.time() < end:
if r.set(f'lock:{lock_name}', identifier, nx=True, ex=10):
return identifier
time.sleep(0.1)
return False
def release_lock(lock_name, identifier):
script = '''
if redis.call("get", KEYS[1]) == ARGV[1] then
return redis.call("del", KEYS[1])
else
return 0
end
'''
r.eval(script, 1, f'lock:{lock_name}', identifier)
15. Calculate Real-Time Leaderboard with Sorted Sets
Prompt: "Implement a game leaderboard using Redis Sorted Sets. Show how to add a score, get top 10 players, and get a player's rank."
Why it works: Sorted Sets are perfect for leaderboards.
Example usage:
import redis
r = redis.Redis()
# Add score
r.zadd('leaderboard', {'player1': 1500, 'player2': 2000})
# Get top 10
top = r.zrevrange('leaderboard', 0, 9, withscores=True)
# Get rank
rank = r.zrevrank('leaderboard', 'player1') # 0-based
Conclusion
These 15 prompts cover the most common database tasks you'll face in production: querying, indexing, caching, migration, and concurrency. Start by adapting them to your own projects. For PostgreSQL, focus on indexing strategies and recursive CTEs. For MongoDB, master aggregation pipelines and schema validation. For Redis, get comfortable with sorted sets and streams. Each prompt is a template you can reuse again and again. Save this list and refer to it when you hit a database challenge — it will save you hours of searching and debugging.
Comments