Write SQL With AI: Generate and Optimize Queries Safely

AI is useful for translating a reporting question into a first SQL draft. It is much less reliable at guessing what your data means. A query can execute successfully while duplicating revenue, excluding unpaid customers, or comparing timestamps in incompatible time zones.

The safest workflow separates semantic correctness from performance. First establish exactly which rows and aggregates answer the question. Then use execution evidence to improve the query without quietly changing its meaning. The assistant proposes; your fixtures, database, and review process decide.

Define the question as a data contract

Write the business question in language that includes a grain, time window, and inclusion rules. “Monthly revenue” is incomplete. Does revenue mean invoices raised, payments settled, or recognised amounts? Are refunds negative transactions? Which currency and reporting time zone apply?

Specify one row per customer, invoice, day, or another entity before generating SQL. This exposes a common source of errors: joining a parent table to multiple child rows and then summing a parent-level amount. The result looks reasonable until a customer with several related records appears.

Give the assistant the SQL dialect, relevant table definitions, primary keys, foreign keys, nullability, and useful indexes. Do not provide an entire production dump. A small, sanitised schema and synthetic examples are usually enough to reason about the query structure.

State what the answer must not do. Perhaps archived accounts must remain visible, or cancelled orders must never contribute revenue. These negative requirements are easy to lose in a broad prompt. The AI coding assistants directory can help you find integrations, but schema access and database permissions matter more than conversational polish.

Ask for assumptions before executable SQL

Start with an explanation of the proposed joins and filters. Ask the assistant to identify missing definitions instead of filling gaps silently. Explicit assumptions are easier to review than a long query whose business logic is hidden inside nested expressions.

A compact prompt can establish that boundary:

Dialect: PostgreSQL.
Goal: one row per customer, including customers with no settled payments.
Window: January 2026 in UTC, start inclusive and end exclusive.
Input: customers(id), payments(customer_id, amount, status, paid_at).
First list missing assumptions and explain the join strategy.
Then propose parameterised SQL. Do not execute anything.
Include fixtures for zero payments, two payments, and a boundary timestamp.

Follow up with real column types and decisions about currencies, refunds, and null amounts. Do not treat this abbreviated schema as sufficient for a financial report. Ask the assistant to explain how each predicate affects customers with no matching payments.

Use parameterised queries through your database driver instead of interpolating user-supplied strings. The OWASP SQL injection prevention guidance explains why escaping and generated query text are not substitutes for binding values. Dynamic identifiers require a separate allowlist strategy; a value parameter cannot safely stand in for an arbitrary table name.

Build fixtures that expose wrong answers

Test the draft against a small database where you can calculate expected results manually. Include an empty table, a parent with no children, multiple matching children, null values, and records just outside the reporting window. Add duplicate-looking business events with distinct identifiers if your data permits them.

Check join cardinality explicitly. Count rows before and after each join, and inspect which keys repeat. A DISTINCT clause may conceal a faulty join rather than repair it. Aggregating a child table before joining can be appropriate, but only when that aggregation matches the intended grain.

Pay attention to null semantics. Filtering a nullable column after a left join can remove the unmatched rows you intended to retain. Comparisons with null do not behave like ordinary equality. Ask the assistant to explain a specific fixture row through every stage rather than merely declaring the query correct.

Consult the PostgreSQL SQL documentation for the actual syntax and semantics when using PostgreSQL. Other databases differ in date functions, grouping rules, casts, and optimisation behaviour. Translating function names does not automatically preserve results.

Save these fixtures as regression tests. They become especially valuable when the next optimisation changes a join, introduces a common table expression, or moves a filter across an aggregation boundary.

Read plans before requesting optimisation

Once results are correct, capture a baseline with representative data and parameters. Record duration, returned rows, database version, relevant indexes, and whether the cache was warm. A query on twenty fixture rows cannot predict the behaviour of a production-sized table.

Use the database's plan tools and read the distinction between estimated and actual rows. Large estimation errors can point to stale statistics, skewed values, or correlated columns. An assistant can explain a plan excerpt, but it should not prescribe indexes without understanding the workload.

The PostgreSQL EXPLAIN guide describes plan interpretation and the consequences of ANALYZE. Remember that EXPLAIN ANALYZE actually executes the statement. Use a safe staging environment, read-only roles where appropriate, and statement timeouts; do not casually run generated write statements under analysis on production.

Ask for one hypothesis at a time: perhaps a selective filter could use an index, or repeated work could be reduced. Compare measured plans after each change. Lower estimated cost alone is not proof of a faster user-facing request, and an additional index imposes storage and write overhead elsewhere.

Integrate the result without losing its meaning

After approving the query, inspect the data shape your application receives. Counts, decimals, and timestamps may arrive as strings depending on the driver. An inferred TypeScript number is not evidence that a large integer or money value can be represented safely.

Use the JSON to TypeScript tool with a sanitised result sample to draft response interfaces, then correct nullable fields and driver-specific scalar types manually. A sample containing only successful payments cannot reveal every permitted null or empty-result shape. Keep runtime validation separate from compile-time types.

Apply least-privilege access to any assistant integration that can contact a database. Prefer schema-only context and read-only staging connections for exploration. Require a human approval boundary before migrations, index creation, or queries that export sensitive records.

Document the query's business definitions alongside its tests. Your development workflow should preserve those decisions during code review so the next developer understands why a seemingly simpler query was rejected. Recheck performance after meaningful data growth, not just after code changes. Optimisation is a workload-specific observation, not a permanent certificate attached to generated SQL.

Key takeaways

• Define the result grain, reporting window, and business rules before requesting SQL.
• Supply a precise dialect and sanitised schema, not production records.
• Verify joins and null handling with deliberately awkward fixtures.
• Optimise from measured execution plans after proving correctness.
• Preserve result types, access controls, and semantic regression tests during integration.