How to Use AI for Database Query Generation (SQL & NoSQL)

Writing queries is one of the highest-value uses of AI — joins, aggregations, and pipeline stages are exactly the kind of repetitive logic AI handles well. But query generation is also where AI hallucinates the most, inventing column names and functions that do not exist. The fix is rigorous schema-grounding.

1. Introduction

Whether you are writing a join across five tables in PostgreSQL or an aggregation pipeline in MongoDB, the same principle holds: AI needs to know exactly what your data looks like before it can query it. This tutorial walks through the schema-first prompt pattern that produces queries you can paste straight into your database client, for both SQL and NoSQL stacks.

2. The Concept Explained

A query has three parts: the shape of the data (schema), the question being asked (intent), and the flavour of database (dialect). Skipping any of these forces the AI to guess. Skipping all three is why "write me a query for sales" tends to return SQL that references a sales table you do not have.

Think of yourself as the human translator. You know the data; the AI knows the language. You hand it the dictionary (schema), say what you want to say (intent), and tell it which language to translate into (dialect). Skip any of those and you get nonsense, no matter how fluent the AI sounds.

3. The Problem Without This Technique

The shortcut is to describe the query in plain English and hope for the best. The AI then hallucinates plausible-sounding column names that almost never match your schema.

write a sql query to get the top 5 customers by total purchase
amount in the last 30 days

4. The Solution

Dialect: PostgreSQL 15

Tables (only relevant columns shown):

customers (
  id            uuid PRIMARY KEY,
  display_name  text NOT NULL,
  email         text NOT NULL,
  created_at    timestamptz NOT NULL
)

orders (
  id            uuid PRIMARY KEY,
  customer_id   uuid REFERENCES customers(id),
  status        text CHECK (status IN ('pending','paid','refunded')),
  total_cents   integer NOT NULL,
  placed_at     timestamptz NOT NULL
)

Goal: return the top 5 customers by total of `paid` order amounts in the
last 30 days. Include customer id, display_name, total spend in dollars
(2 decimal places), and order count.

Constraints:
- Use `now() - interval '30 days'` for the window
- Only `status = 'paid'` orders count
- Order by total spend desc, then by display_name asc as tie-breaker
- Return at most 5 rows
- Use a CTE if it improves readability
- Parameterise nothing — return the literal query I can paste into psql

Database: MongoDB 7 aggregation pipeline (using the Node driver, but I just
want the pipeline array I can paste in)

Collection: orders
Sample document:
{
  _id: ObjectId,
  customerId: ObjectId,
  status: "paid" | "pending" | "refunded",
  totalCents: 4250,
  placedAt: ISODate,
  customer: { displayName: string, email: string }   // denormalised
}

Goal: top 5 customers by total spend of `paid` orders in the last 30 days.

Output an aggregation pipeline (array of stages) that returns documents like:
  { customerId, displayName, totalDollars (number, 2 decimals), orderCount }

Sort by totalDollars desc, then displayName asc. Limit 5.

5. Step-by-Step Breakdown

1. State the dialect and version. PostgreSQL 15, MySQL 8, MongoDB 7, DynamoDB. Function names and syntax differ.
2. Paste the schema or a sample document. Tables and columns for SQL; one realistic document for document stores. Include types and constraints.
3. State the question in one sentence. "Top 5 customers by spend in last 30 days" is sharper than three paragraphs of explanation.
4. Pin down the edge rules. Tie-breakers, which statuses count, what time window, currency handling.
5. Specify the output shape. Column names, formatting, ordering, limits. This is the contract the query must meet.
6. Ask for an EXPLAIN review afterwards. Once the query runs, follow up: "Here is the EXPLAIN output — can you suggest an index that would speed this up?"

Tip: For production queries, always ask the AI to flag any operations that could scan the entire table. This catches missing indexes and accidental cartesian joins before they hit your database.

6. Practice Exercises

7. Key Takeaways