Writing SQL with AI is dangerous in a way that writing other kinds of code is not. A subtly wrong SQL query can silently corrupt data, scan a billion-row table accidentally, or leak information through a UNION you did not intend. The AI does not always know which query plan your database will actually use, does not know your specific indexes, and does not know which innocent-looking JOIN will lock your production replica. And yet, AI-generated SQL is often genuinely good — fast to produce, idiomatic, and usable after review. The difference between getting value from AI SQL and causing an incident is all in the workflow. This guide covers how to write SQL with AI safely, the schema-first prompting pattern that dramatically improves quality, the dry-run discipline that catches disasters, and the specific anti-patterns that turn AI SQL assistance into self-inflicted outages.
Why SQL is a different kind of AI task
Most code runs in your application process, which you can stop, restart, or roll back. SQL runs in your database, which you cannot. A DELETE without a WHERE clause is permanent in seconds. A full-table scan on a production table can slow or stop the whole database. A migration that adds a column can lock a table for hours on a large production system.
AI can produce SQL that looks correct but has these pathologies. The syntax is right; the behaviour is wrong at scale. Unlike application code where a bug often manifests as an error you can catch, SQL bugs often manifest as "it worked but was somehow much worse than expected."
This difference calls for a different workflow. Not "let the AI write it, ship it, iterate" but "have the AI draft it, inspect the plan, verify on staging, ship with care." The extra steps are the price of admission for using AI on production databases.
Schema-first prompting
The single most important practice: always give the AI the schema before asking for a query. This seems obvious but is often skipped.
Without schema context, the AI guesses at table names, column names, and data types. Guesses are sometimes right; often subtly wrong. The resulting query looks plausible but references columns that do not exist or have different types than the AI assumed.
Good pattern: "Here is the schema for the relevant tables: [paste CREATE TABLE statements, or generated schema dumps]. Write a query that [requirement]."
Better pattern: share the schema plus some example rows. "[Schema plus example rows]. Write a query that [requirement]." Example rows help the AI understand data distributions and common patterns.
Best pattern: for important queries, share schema + example rows + index information. "The users table has indexes on (id), (email), and (created_at). The orders table has indexes on (id), (user_id), and (status). [Other context]. Write a query that [requirement] and use indexes effectively."
Each step of added context improves query quality. Skipping these steps produces queries that sometimes work by accident but often have subtle issues.
Always dry-run or EXPLAIN
Before running any non-trivial AI-generated SQL against production data, run EXPLAIN (or your database's equivalent) to see the actual query plan.
EXPLAIN tells you which indexes are used, whether a full table scan is happening, estimated row counts, and the join strategies. AI-generated SQL often looks reasonable but has an awful query plan because of a missing index or a poorly-chosen JOIN order.
A useful prompt pattern after generation: "Here is the query you generated and the EXPLAIN output. Analyse the plan. Is this efficient for a table with 10 million rows? Propose improvements."
Even better: for migrations and data-modifying queries, test on staging with realistic data volumes. A migration that runs in 10 seconds on your dev database may lock a table for an hour on production. Discover this on staging, not on production.
Safe migration patterns
Database migrations are where AI-generated SQL causes the most production incidents. A few patterns that make migrations safer.
Additive-only migrations. Prefer adding columns, tables, or indexes over modifying existing structures. Adding is usually safe; modifying often requires locking and can cause outages.
Two-step destructive changes. To remove a column, first stop writing to it in the application (release 1), then drop the column in a separate migration (release 2). Never do both in one step; rolling back is impossible if the column is already dropped.
Online DDL where possible. Modern databases support some DDL operations without locking (PostgreSQL's CREATE INDEX CONCURRENTLY, MySQL's online DDL). When AI generates a migration, ask specifically for online-compatible variants.
Timeout guards. Wrap long-running migrations in timeouts so a misbehaving migration aborts rather than locking indefinitely.
Staging-first. Run every migration on a realistic staging database before production. The behaviour at scale is often different from the behaviour in dev.
AI is particularly useful here because it knows these patterns. Ask explicitly: "Generate a migration for X that follows safe-migration practices: additive, two-step for destructive, online-compatible, with timeout guards."
Analytics queries and the footgun list
Analytics queries — reports, dashboards, ad-hoc analysis — are where AI SQL is most productive and also where subtle bugs live.
Common analytics-query footguns.
Silent duplication from JOINs. When two tables have a one-to-many relationship, a naive JOIN produces duplicates. AI sometimes produces correct queries and sometimes does not; always check the row counts.
NULL handling. COUNT(column) versus COUNT(*) versus COUNT(DISTINCT column) can give very different results. AI sometimes picks the wrong one.
Date boundary mistakes. "Last month" can be interpreted multiple ways. Always specify exact date ranges.
Aggregation scope. GROUP BY plus HAVING is subtle; AI sometimes produces queries that filter before aggregating when they should filter after.
Timezone handling. Servers often store UTC; users often think in local time. AI needs to know which is which.
For every analytics query AI generates, spot-check the logic: sample a few rows manually and verify the calculations match what the query produces.
Text-to-SQL products vs raw LLMs
Several specialised text-to-SQL products exist (Defog, Vanna, and various features in BI tools). They typically combine an LLM with metadata about your schema, query history, and access controls.
Advantages over raw LLM prompting: they always include schema context, respect access controls, and often learn from past queries. They also handle the dry-run pattern automatically.
Disadvantages: they are purpose-built for database-backed analytics; less flexible than raw LLM access for one-off needs; they cost money per query; configuring them for your specific data model takes investment.
For teams whose primary AI-SQL use is analytical queries on a relatively stable schema, dedicated text-to-SQL products can be worth the investment. For ad-hoc one-off queries, raw LLM prompting with good schema context is usually fine.
ORMs and AI
A middle ground between raw SQL and application code: ORM queries (Prisma, TypeORM, SQLAlchemy, ActiveRecord).
AI is quite good at generating ORM queries. The syntax is usually well-documented, the patterns are standard, and the type safety catches many errors at compile time.
The risk profile is also lower than raw SQL. ORMs generally have sensible defaults, prevent SQL injection, and produce queries that are correct for the described intent even if not always optimal.
For most application code that touches the database, AI-generated ORM queries are safer than AI-generated raw SQL. Use ORM queries for application code; reserve raw SQL for analytics, migrations, and cases where the ORM is inadequate.
When AI-generated SQL should get extra scrutiny
Categories of SQL that deserve particular review.
DELETE and UPDATE statements. Both are destructive if the WHERE clause is wrong. Always double-check. Dry-run with SELECT COUNT first to confirm the scope.
Queries joining many tables. Cartesian explosions are easy; verify row counts.
Queries with subqueries or CTEs that AI generated for you. Complex queries are harder to verify; break them down and check each piece.
Queries against production with no LIMIT. A runaway SELECT on a big table can hurt performance. Always LIMIT during exploration; only remove when you understand the scope.
Any query that takes user input directly. SQL injection is real. Parameterise always, never concatenate. AI usually handles this correctly but occasionally doesn't; verify.
Schema-altering migrations. As covered above, deserve full migration discipline.
Query optimisation with AI
Beyond generating new queries, AI can help optimise slow ones you already have. The workflow.
Step 1: identify the slow query. Database tooling (pg_stat_statements in Postgres, slow query log in MySQL) surfaces candidates.
Step 2: capture the query, its EXPLAIN plan, and the table schema (including indexes and row counts).
Step 3: share with the AI. "This query is slow. Here is the query, the EXPLAIN plan, the table schema, and the relevant indexes. Row counts: [estimates]. Propose specific optimisations. For each, explain the expected performance improvement."
Step 4: apply the proposed optimisation in staging. Re-run EXPLAIN and measure the actual improvement.
Step 5: deploy the winning optimisation, monitor, and iterate.
Common wins: missing indexes, badly-ordered JOINs, unnecessary subqueries that can be converted to JOINs, LIMIT without ORDER BY, correlated subqueries that can be rewritten as aggregations. AI catches most of these quickly when given the right context.
Tools that wrap AI SQL access
Beyond raw LLM prompting, several tools specialise in SQL-AI workflows.
Defog. Schema-aware text-to-SQL. Reads your database metadata and answers natural-language questions with SQL. Used by analytics teams that want ad-hoc exploration without writing SQL by hand.
Vanna. Open-source text-to-SQL with a learning layer; it improves over time as you correct its outputs.
Hex, Mode, Metabase. BI tools with integrated AI features that let analysts ask questions in natural language and get SQL and visualisations back.
DBeaver, DataGrip. SQL IDEs with integrated AI chat that helps you write, explain, and debug queries without leaving the tool.
Claude Code and Cursor. General AI coding tools that handle SQL alongside other code. Less specialised but more flexible.
For teams with heavy SQL workloads, investing in a specialised tool often pays off. For one-off queries and ad-hoc work, general AI coding tools are usually fine.
When to keep a human in the loop
For most day-to-day SQL work, light AI involvement with human review is fine. A few scenarios where human involvement should be heavier.
Production data changes. Any INSERT, UPDATE, or DELETE against production should be reviewed by a human who understands the data, not just approved by the person who prompted the AI.
Queries embedded in customer-facing features. Performance problems reach users. Take the extra time to verify indexes, test at scale, and understand the plan.
Compliance-sensitive queries. Anything touching personal data, financial data, or regulated information deserves human review for compliance implications beyond functional correctness.
New query patterns in hot paths. The first time a pattern gets deployed, monitor its performance carefully. AI may have generated a novel pattern that is slow in practice even if correct in theory.
A real war story: the 30-minute outage from an AI UPDATE
An anonymised story. A developer asked an AI to generate a script to update user preferences in bulk — setting a new default for a recently-added field. The AI produced a clean-looking UPDATE statement. The developer reviewed it casually, saw it was well-formed, and ran it against production.
The statement updated every user — not just the ones who needed the default. The specific user settings of millions of users were overwritten. Customer support lit up within minutes. Rolling back required restoring from a backup and replaying the day's legitimate updates on top, which took most of a weekend.
What went wrong: the AI's UPDATE had an implicit scope that the developer missed on review. The prompt had been ambiguous about whether to update only users without the field set; the AI chose to update everyone, reasonably but incorrectly.
The lessons that stuck with that team afterwards: always SELECT first to verify the scope. Always run against staging. Always have a rollback plan. Never trust an AI-generated UPDATE or DELETE at face value, no matter how clean it looks. These lessons are cheap to learn from someone else's mistake and expensive to learn from your own.
Prompt patterns for SQL tasks
Templates worth saving.
Analytical query template: "Schema: [tables]. Example data: [rows]. Indexes: [list]. Write a query that [requirement]. Explain the query and any assumptions. Estimate the approximate cost at [scale]. Propose an optimised alternative if the naive query would be slow."
Migration template: "Schema: [current]. Change: [describe desired change]. Write a migration that: is additive-only if possible, uses online-compatible DDL where available, includes a rollback script. Explain the performance impact on a table with [N] rows."
Debug template: "Query: [paste]. EXPLAIN output: [paste]. The query is [slow / produces wrong results]. Diagnose the issue and propose a fix."
Bulk-operation template: "Requirement: [describe bulk update]. Schema: [tables]. Write it as a multi-step batched update to avoid locking. Include progress tracking and resumability."
These templates embed the discipline. Using them is faster than remembering to ask about indexes, EXPLAIN, and batching every time.
Read-only access as a default guardrail
A simple but powerful policy: give AI tools read-only database access by default. Write operations require an extra deliberate step.
Most teams grant AI tools full database credentials because it is convenient. That convenience costs you the guardrail. A read-only account cannot UPDATE, cannot DELETE, cannot DROP — and yet can answer almost every analytical question you would ever ask an AI to help with.
For the rare write operations, switch to a write-capable connection manually, review more carefully, and switch back. The two-connection pattern adds ten seconds per write operation and removes an entire category of disaster.
For production write operations, add approval gates. A change-request ticket, a reviewer approval, or an explicit confirmation step before the query executes. Friction is a feature here, not a bug.
The AI SQL failure modes you will encounter
An honest list from real production experience.
Missing indexes in the query plan. AI generates a query that looks reasonable but hits a full table scan because it uses a WHERE clause on an unindexed column. Fix: either add the index or rewrite the query.
Subtly wrong joins. AI produces a query with a JOIN that is technically valid but incorrect for the actual data relationship. Fix: manually verify the join matches the intended semantic.
Dialect confusion. AI knows standard SQL but mixes dialects. PostgreSQL-specific syntax in MySQL queries, or vice versa. Fix: explicitly specify the database in the prompt.
Function availability. AI uses a function that exists in Postgres but not SQLite, or vice versa. Fix: check the function is available in your database.
Window function misuse. ROW_NUMBER, RANK, and partitioning are subtle. AI sometimes produces window functions that run but return wrong results. Fix: verify with a small sample.
Recognising these patterns speeds up review. Teams that have seen each of these once learn to check for them automatically.
AI is excellent at SQL when fed the schema and forced to EXPLAIN plans before running. Without that, it will happily run a full table scan in production, sometimes with correct semantics and sometimes without.
The short version
AI-generated SQL is productive but dangerous. Always provide the schema up front. Always run EXPLAIN before executing non-trivial queries in production. Use safe-migration patterns for schema changes. Prefer ORM queries in application code; reserve raw SQL for analytics and migrations. Watch for the footgun patterns: unsafe JOINs, NULL handling, date boundaries, timezone mismatches. Keep humans in the loop for destructive operations, customer-facing performance-critical queries, and compliance-sensitive work. Done with discipline, AI SQL generation compresses hours of work into minutes. Done without discipline, it compresses hours of work into weekends of incident response and backups restored in anger.