Working with PostgreSQL and MongoDB is like speaking two different languages: SQL is precise and relational, while MongoDB is flexible and document-oriented. Every database engineer knows the pain of writing a schema from scratch, debugging a slow query, or migrating data between these two worlds. What if you could delegate part of this cognitive load to an LLM — not to replace your judgment, but to accelerate it? In this guide, I share 10 prompt templates I use in my own workflow, each with a concrete example and a note on when to apply it. These aren't generic "write a query for me" prompts — they're designed to produce production-ready output that you can validate and adapt.
But first, a word of caution: LLMs are impressive, but they're not oracles. They can hallucinate functions, misremember PostgreSQL version specifics, or subtly break MongoDB aggregation pipelines. Always verify the generated code against official documentation — I've linked the key references throughout. With that in mind, let's turn prompts into a superpower.
1. Schema Generation from a Natural Language Description
When to use: You need a starting point for a new table or collection, and you want to ensure all relevant fields are considered.
The prompt:
Act as a senior database architect. Design a PostgreSQL schema for [describe entity].
Include:
- Table name and column names with data types (use PostgreSQL 15+ conventions)
- Primary key, foreign keys, and indexes with a short justification for each
- Constraints (CHECK, UNIQUE, NOT NULL) that enforce data integrity
- A note on how this table relates to [other table], if applicable
Output as a SQL script with inline comments.
Example: For a "user_account" entity, the LLM might produce a table with id BIGSERIAL PRIMARY KEY, email VARCHAR(255) UNIQUE NOT NULL, a CHECK constraint on status, and a partial index on email WHERE deleted_at IS NULL. You can then review and adjust based on your exact requirements.
2. Converting an ERD Description into MongoDB Collections
When to use: You're moving from a relational model to a document database and need a sensible collection structure.
The prompt:
Convert this relational schema to MongoDB collections for a [use case] workload. For each collection, provide:
- Collection name and document structure (use MongoDB 6.0+ syntax)
- Embedded documents vs. references — explain your choice for each relationship
- Indexes you'd create, including compound or partial indexes
- A realistic sample document (JSON)
Consider that the application does [describe read/write pattern].
Example: Starting from a relational model of users and orders, the LLM might recommend embedding orders in the user document if users rarely exceed 100 orders, or separate collections with a userId reference if orders are frequently accessed independently. It should also suggest an index on userId for the orders collection.
3. Generating a MongoDB Aggregation Pipeline from a Business Question
When to use: You need to extract insights from a collection, but the aggregation stages are complex.
The prompt:
Write a MongoDB aggregation pipeline to [describe business question].
Use the collection [collection_name] with sample documents:
[provide 2-3 sample documents]
Return the pipeline in stages (e.g., $match, $group, $project) with a brief explanation of each stage. Optimize for performance by filtering early.
Example: "Find the total revenue per product category for the last 30 days." The LLM should produce a pipeline with $match on createdAt, $unwind on items, $lookup to join products, $group by category, and $project to shape the output.
4. Detecting and Fixing N+1 Query Problems
When to use: You suspect your application is making too many database round-trips.
The prompt:
Explain the N+1 query problem in [PostgreSQL/MongoDB] with a concrete example using [ORM/ODM if applicable]. Then rewrite the example to avoid N+1 by using [JOINs / $lookup] in a single query.
Compare the two approaches in terms of the number of queries and performance.
Example: In PostgreSQL with an ORM like SQLAlchemy, the LLM might show a loop that fetches users then their orders, and replace it with a single JOIN or selectinload. In MongoDB, it would contrast fetching a list of users and then querying orders by each userId versus a single $lookup.
5. Optimizing a Slow Query with EXPLAIN
When to use: You have a query that's slower than expected, and you want systematic advice.
The prompt:
Here's a PostgreSQL query and its EXPLAIN ANALYZE output:
[query]
[EXPLAIN output]
Analyze the plan. Identify bottlenecks (e.g., sequential scans, missing indexes). Suggest specific indexes, query rewrites, or configuration changes. Provide the revised query and expected improvements.
Example: If the plan shows a sequential scan on a large table, the LLM might suggest a composite index on (user_id, created_at) for a query filtering on both. It should also warn about over-indexing and recommend testing with EXPLAIN (ANALYZE, BUFFERS).
6. Designing an Index Strategy for MongoDB
When to use: You need a plan for indexes that supports your most common queries.
The prompt:
My MongoDB collection [name] has [document count] documents and this schema: [schema].
These are my frequent queries:
1. [query1]
2. [query2]
Recommend an index strategy: which single-field, compound, or partial indexes to create? Explain the trade-offs for each. Also mention any indexes that could be dropped.
Example: For a collection with queries filtering on status and sorting by createdAt, the LLM might suggest a compound index {status: 1, createdAt: -1} and a partial index for active records. It should also advise against an index on high-cardinality fields if not needed.
7. Data Migration Playbook: PostgreSQL to MongoDB
When to use: You're moving from a relational database to a document store and need a step-by-step plan.
The prompt:
Create a step-by-step migration plan from PostgreSQL to MongoDB for [describe schema or use case].
Include:
- Data extraction strategy (e.g., using COPY or a script)
- Transformation rules (e.g., how to handle joins by embedding or referencing)
- Loading approach (e.g., using mongorestore or a bulk write script)
- Data validation after migration (e.g., row count comparison)
- Handling of transactions and rollback
Note any pitfalls like data type differences (e.g., timestamps, numeric precision).
Example: The LLM might recommend using pg_dump to export CSV, then a Python script with pymongo to insert documents, and finally compare counts using SELECT count(*) vs db.collection.countDocuments(). It should also flag that PostgreSQL's numeric type may need conversion to Decimal128 or double.
8. Data Migration Playbook: MongoDB to PostgreSQL
When to use: The reverse direction — you need to flatten documents into relational tables.
The prompt:
Create a migration plan from MongoDB to PostgreSQL for [collection name] with this document structure:
[sample document]
Include:
- Extraction method (e.g., a script using mongodump to BSON)
- Schema mapping: how to flatten nested documents into tables
- Handling of arrays (e.g., separate junction tables or JSONB)
- Loading using COPY or bulk inserts
- Validation and cleanup steps
Example: For a document with an embedded address object, the LLM might suggest creating a users table and an addresses table with a foreign key, or using a JSONB column if the data is rarely queried. It should also address arrays by either using a child table or PostgreSQL's array type.
9. Schema Evolution and Refactoring with AI Assistance
When to use: You need to alter an existing schema without breaking production.
The prompt:
I need to refactor [table/collection] in [PostgreSQL/MongoDB]. Current schema: [schema].
The goal is: [describe change, e.g., "split a column into two" or "change a field type"].
Provide:
- A migration script (ALTER TABLE / updateMany) that is backward-compatible
- Steps to migrate data without downtime (e.g., using triggers or a dual-write pattern)
- How to test the migration in a staging environment
Example: Changing a MongoDB age field from string to number might require a script to update all documents, and the LLM should recommend doing it in batches to avoid locking.
10. Generating Sample Data for Testing
When to use: You need realistic test data for development or load testing.
The prompt:
Generate [N] realistic sample records for a [table/collection] with the following schema:
[schema]
Use [language, e.g., SQL INSERT statements or JSON documents]. Include edge cases like null values, empty strings, and out-of-range dates where appropriate.
Example: For a products table, the LLM might generate 100 rows with varying prices, categories, and stock levels, including a few negative stock values to test validation.
Final Thoughts
These prompts are starting points, not final answers. The best results come when you iterate: run the output, test it, and feed errors back into the conversation. Remember to verify generated code against the official PostgreSQL documentation and MongoDB documentation. With practice, you'll find that these templates save you hours of boilerplate work, letting you focus on the architectural decisions that truly matter.
Now, go ahead and try one of these prompts with your own schema. You'll be surprised how much time you save — and how much you learn about your own data model in the process.
Comments