How to Write and Check SQL with AI

To write and check SQL with AI safely, give the model an approved schema and a precise business question, require parameters instead of string-built values, and keep execution inside a least-privilege read-only sandbox or disposable copy. Review the query plan and reconcile rows, joins, nulls, duplicates, and aggregates against an independently designed fixture before trusting the result.

Do not connect an AI tool directly to a production database as a learning shortcut. The bounded AI workflow applies here, but database access needs stronger identity, data, and side-effect controls.

Key Takeaways

• Define the business question, row grain, schema, and assumptions before generating SQL.
• Keep code and values separate with the database driver’s parameter interface.
• Classify every statement by read/write behavior and possible side effects.
• Use a disposable database or least-privilege read-only identity, not production credentials.
• Inspect the plan, then reconcile results with a synthetic fixture and independent totals.

How do you write and check SQL with AI without giving it control of the database?

Separate drafting from execution. The model may propose a query from schema context, but a reviewed operator or controlled application chooses whether and where it runs. This division keeps an explanation error from becoming a database action.

OWASP recommends prepared statements with parameterized queries as a primary SQL injection defense because they define SQL code separately from supplied values.［1］ Parameterization does not prove that a query answers the right question, but it blocks one major class of code/data confusion.

Use five gates:

Gate	Required evidence
Question	Defined metric, population, time window, and row grain
Draft	Schema-qualified SQL plus explicit assumptions
Safety	Parameters, read/write classification, least-privilege execution identity
Plan	Reviewed operators, estimates, filters, joins, and resource risks
Result	Reconciled fixture rows, totals, duplicates, nulls, and edge cases

The output of one gate becomes input to the next. Do not let a chat interface hide them inside a single “run this query” button.

Step 1: Freeze the question and the expected row grain

Write the question in business terms before sharing schema. “Return one row per active customer with paid invoice total for the previous complete calendar month” is clearer than “show monthly revenue.”

Define:

• one row represents what entity or time bucket;
• which states are included and excluded;
• time zone and inclusive/exclusive boundaries;
• how refunds, cancellations, duplicates, and missing values behave;
• whether the result is a count, sum, snapshot, event history, or latest state;
• an independently calculable small example.

Most expensive SQL mistakes begin as ambiguous grain. Joining invoice rows to multiple status events can multiply money even when every clause is valid SQL. Make the expected uniqueness key explicit.

Keep assumptions in a reviewable list

Ask the model to state assumptions before query text. For example: “invoices.id is unique,” “one customer may have many invoices,” and “timestamps are stored in UTC.” Verify each assumption against schema, constraints, migrations, and maintained documentation.

If the repository is unfamiliar, first explain the relevant code and data path using read-only evidence. Do not infer a database contract from application variable names alone.

Step 2: Share the smallest approved schema context

Provide table and column names, types, keys, relationships, relevant constraints, and a few synthetic rows. Include database dialect and the versioned features your environment supports, but omit real records, credentials, hostnames, connection strings, and sensitive comments.

Prefer a curated schema excerpt over a full production dump. Remove personal data and secret-like defaults. If column names themselves reveal sensitive business information, use an approved local fixture with equivalent relationships.

Tell the model not to invent missing columns, indexes, relationships, or enum values. Its response should have two sections: “query draft” and “unresolved schema questions.” A query that depends on an unresolved relationship is not executable.

Step 3: Require parameterized values and constrained identifiers

Values supplied by users, requests, files, or upstream systems must use the driver’s bind-parameter interface. Do not ask the model to escape strings manually or concatenate them into SQL. OWASP’s query parameterization guidance shows this approach across common languages and database interfaces.［2］

Conceptually, prefer:

SELECT customer_id, SUM(amount) AS paid_total
FROM invoices
WHERE status = :status
  AND paid_at >= :period_start
  AND paid_at < :period_end
GROUP BY customer_id;

The placeholder syntax varies by driver. Verify it in the official documentation for your actual library.

Parameters usually cannot stand in for table names, column names, sort directions, or SQL keywords. If an identifier must vary, map a small approved input set to hard-coded identifiers in application code. Do not pass an arbitrary model-produced identifier directly to the database.

Parameterization and authorization solve different problems

A parameterized query can still expose every customer row to an unauthorized caller. Verify tenant scope, row-level rules, database role, and application authorization separately. The AI privacy risk guide helps decide what data should not enter the prompt or fixture at all.

Step 4: Classify the statement before execution

Do not classify by the first visible word alone. Common table expressions, functions, triggers, stored procedures, extensions, temporary objects, locks, and EXPLAIN ANALYZE can behave differently from a plain read.

Use three practical classes:

Class	Examples	Default handling
Read candidate	Plain SELECT, non-executing plan inspection	Read-only sandbox after review
State or resource effect	Locking reads, temp objects, heavy scans, executing plan analysis	Dedicated environment and explicit limits
Write or administration	INSERT, UPDATE, DELETE, DDL, grants, procedures with effects	Separate change process and human approval

PostgreSQL transactions group multiple steps into an all-or-nothing unit and hide intermediate changes from other transactions until completion.［3］ Transactions help manage atomicity; they do not make an incorrect update acceptable or guarantee that external side effects can be rolled back.

Never rely on a plan to “roll back later” as the primary safety boundary for unreviewed writes. A wrong query may lock resources, trigger work, call volatile functions, or expose data before your intended rollback.

Step 5: Use a disposable or least-privilege read-only environment

The safest learning environment is a local or disposable database populated with synthetic data. A staging copy may still contain sensitive records and may still trigger integrations, so verify its data and side-effect boundaries.

When a real database must be queried, use a dedicated identity with only the required schemas, tables, columns, and operations. Set statement timeouts, row limits where appropriate, resource controls, and logging outside the model’s control.

PostgreSQL supports read-only transaction mode and disallows many data-changing commands in that mode, but its documentation calls this a high-level notion of read-only that does not prevent all writes to disk.［4］ Treat it as one layer, not a universal guarantee. Other databases have different semantics.

The diagram describes an operator workflow. It is not evidence that a specific database, identity, function, or connector is read-only.

Step 6: Inspect the query and plan before reading results

Review selected columns, join keys, filters, null behavior, grouping, ordering, limits, and subqueries. Look for an accidental cross join, a filter placed after a multiplying join, a left join converted to an inner join by a WHERE predicate, or a date boundary that uses the wrong time zone.

Use a non-executing plan command first when your database supports it. Check:

• estimated row counts at each node;
• scans and whether filters are applied early enough;
• join type and join condition;
• sorting, aggregation, materialization, and repeated subplans;
• partition pruning and index assumptions;
• a suspicious gap between expected and estimated cardinality.

In PostgreSQL, EXPLAIN ANALYZE actually executes the query while reporting real row counts and timing.［5］ Do not treat it as a harmless preview. Use it only in an approved environment after classifying the underlying statement and resource risk.

The plan is not a correctness proof. A fast plan can return the wrong rows; a correct query can still be too expensive for production data distribution.

Step 7: Reconcile the result with a purpose-built fixture

Create a small synthetic dataset where you can calculate the answer manually. Include one row for every important rule:

• included and excluded status;
• exact start and end timestamps;
• null and empty value;
• duplicate event or one-to-many relationship;
• customer with no matching child row;
• refund or reversal when relevant;
• unusually large or negative numeric value if allowed.

Before running the generated query, write the expected rows and totals. Then compare:

1. output row count;
2. uniqueness of the intended grain key;
3. per-group subtotals and grand total;
4. records included and excluded;
5. null handling and default values;
6. duplicate sensitivity;
7. deterministic ordering when consumers depend on it.

Ask AI to help enumerate missing cases, but do not let it calculate both the expected result and the query unchecked. Use the AI-assisted test design workflow to build regression fixtures whose oracle is independent of the SQL implementation.

Step 8: Review application integration, not only raw SQL

A safe statement can become unsafe when application code interpolates it, binds values with the wrong type, uses a privileged pool, retries writes, logs sensitive parameters, or exposes unbounded results.

Inspect the final call site:

• Which identity and database does the connection select?
• Are parameters bound through the driver rather than formatted into text?
• Are transaction, timeout, cancellation, and retry rules explicit?
• Are result columns mapped without truncation or type confusion?
• Can errors leak schema or personal data to logs or clients?
• Does pagination use stable ordering?

Apply the pre-execution code review checklist to the integration patch. Raw SQL review does not cover the surrounding permissions and control flow.

Step 9: Handle write queries as a separate change

If the task requires updating data, stop the drafting workflow before execution. Require a reviewed migration or operational runbook, affected-row preview, backup or recovery plan, transaction design, concurrency analysis, authorization, monitoring, and a named approver.

Use immutable inputs and bind approval to the exact query, parameters, target identity, database, and time window. A later query revision invalidates the approval. Do not allow a model to expand the affected scope after preview.

When a query produces unexpected rows or performance, preserve evidence and use the AI debugging workflow. Repeatedly asking for a “better query” without a stable oracle usually trades one hidden assumption for another.

Summary

• Define the business question, row grain, schema, and edge behavior first.
• Use bind parameters for values and allowlisted mappings for variable identifiers.
• Separate drafting from execution and classify all possible effects.
• Prefer synthetic disposable data; otherwise use a least-privilege read-only identity and limits.
• Inspect the plan, then independently reconcile rows, totals, joins, nulls, and duplicates.
• Treat every write as a separately approved change, not an extension of the chat.

FAQ

Can I paste my database schema into an AI tool?

Only if your organization permits it and after removing secrets and sensitive business details. Prefer the smallest approved excerpt or an equivalent synthetic schema.

Does a parameterized query guarantee the SQL is safe?

No. It strongly addresses code/data separation for values, but you must still verify authorization, data exposure, identifiers, query logic, permissions, resource use, and application integration.

Is a read-only database user enough protection?

It is an important layer, not a complete guarantee. Verify database-specific semantics, function behavior, temporary objects, resource effects, accessible data, and the identity actually used by the connection.

Is EXPLAIN the same as EXPLAIN ANALYZE?

No. In PostgreSQL, plain EXPLAIN shows a plan without running the query, while EXPLAIN ANALYZE executes it and reports actual measurements. Check your database’s documentation before use.

How can I detect duplicate rows caused by joins?

Define the expected row grain and uniqueness key, compare row counts before and after each join, and test one-to-many fixtures. Reconcile grouped subtotals with an independent total.

Should I let an AI agent connect directly to production?

Not as a learning or drafting default. Use a disposable database or controlled query interface, least privilege, explicit approval, logging, and independent result checks.

Can a transaction undo every database-related effect?

No. Transaction semantics are database-specific, and external calls, sequences, locks, notifications, or operational impact may not be fully reversed. Review the exact system behavior.

What should I do when the generated query and expected total disagree?

Pause execution, preserve the fixture and results, verify the oracle, then inspect grain, joins, filters, nulls, time boundaries, and duplicates one at a time. Do not change the expected value merely to make the query pass.

---

Further reading:

• Use AI inside a bounded workflow
• Apply AI to a small coding task
• Review AI-generated integration code
• Debug an unexpected query result
• Generate discriminating regression tests
• Minimize sensitive data shared with AI

Disclaimer: This article provides general technical and security guidance, not database administration, legal, compliance, or financial advice. Use qualified reviewers and your organization’s change controls for consequential data systems.

Sources:

1. OWASP Cheat Sheet Series — SQL Injection Prevention — https://cheatsheetseries.owasp.org/cheatsheets/SQL_Injection_Prevention_Cheat_Sheet.html
2. OWASP Cheat Sheet Series — Query Parameterization — https://cheatsheetseries.owasp.org/cheatsheets/Query_Parameterization_Cheat_Sheet.html
3. PostgreSQL Documentation — Transactions — https://www.postgresql.org/docs/current/tutorial-transactions.html
4. PostgreSQL Documentation — SET TRANSACTION — https://www.postgresql.org/docs/current/sql-set-transaction.html
5. PostgreSQL Documentation — Using EXPLAIN — https://www.postgresql.org/docs/current/using-explain.html

Sources checked 24 August 2026.