AI-Generated SOQL: Surprisingly Good, Occasionally Dangerous

A few months ago I was reviewing a pull request from a developer on my team — I'll call him Daniel — and the SOQL in it looked immaculate. Correct child-to-parent traversal, relationship names spelled right, fields I'd have had to look up in Workbench myself. Claude had written most of it, and it was better formatted than anything I produce by hand at 5pm on a Thursday. It also would have taken the org down.

The query was correct in every sense that a language model can check. It was wrong in the one sense it can't. That gap — between a query that reads perfectly and a query that survives production — is the whole story of AI-generated SOQL, and it's worth being precise about where the line sits.

Where AI Actually Shines With SOQL

I'll say the positive part plainly, because I think a lot of architects are too proud to admit it: for query syntax, I trust the model more than I trust myself.

It never gets the relationship name wrong. It knows that the child relationship from Account to Contact is Contacts but the one from Account to a custom object might be Invoices__r, and it knows which is which because it read the schema I gave it. It writes aggregate queries with GROUP BY and HAVING without me reaching for the reference. It remembers date literals like LAST_N_DAYS:30 and THIS_FISCAL_QUARTER. It handles polymorphic lookups on Task.WhatId with a TYPEOF clause that I'd have had to think about.

That's real time saved. Half of writing SOQL by hand is tab-switching to confirm an API name, and the model has effectively eliminated that step. For a straightforward relationship query, it's excellent:

SELECT Id, CaseNumber, Subject, Account.Name, Contact.Email,
       (SELECT Id, CommentBody FROM CaseComments ORDER BY CreatedDate DESC LIMIT 5)
FROM Case
WHERE AccountId = :accountId AND Origin = 'Web'
ORDER BY CreatedDate DESC
LIMIT 200

There's nothing wrong with that query. Bind variable, selective filter, a bounded LIMIT, a child subquery that's capped. If the model hands me this, I merge it. The problem is that the model hands you the good version and the dangerous version with exactly the same confidence.

The Query That Looked Perfect and Wasn't

Back to Daniel's PR. The query fed a Lightning component that showed a support manager their team's open cases. Here's roughly what it was:

SELECT Id, Subject, Status, Priority, Account.Name, Owner.Name
FROM Case
WHERE Status != 'Closed'
ORDER BY LastModifiedDate DESC

In the sandbox, this returned in about a second. The sandbox had 40-odd Cases. Production had roughly 6.8 million, accumulated over nine years of a support operation that never deleted anything. The filter Status != 'Closed' is a negative operator, and negative operators can't use an index. Neither can NOT IN, nor LIKE with a leading wildcard. So the database did a full table scan across 6.8 million rows, the query blew past the timeout, and the component just spun.

We caught it in a UAT sandbox that had a realistic data copy, which is the only reason it didn't reach production. The model had no way to know any of this. It optimised for correctness — "open cases" genuinely means "status is not closed" — and correctness is the wrong target when the table has millions of rows.

The honest ending is that we didn't redesign the feature. We changed the filter to drive off an indexed, selective field — the owner's queue and a LastModifiedDate window — and added a hard LIMIT. It's not elegant. A manager with a genuinely huge backlog still can't see all of it in one view, and we decided that was acceptable rather than build pagination we didn't have time for. It's been fine for a year. I've stopped feeling bad about it. If you want the longer version of how I structure queries to stay indexed, I wrote that up separately in SOQL query patterns that won't kill your governor limits.

It Doesn't Know Your Data Distribution

This is the core limitation, and it's structural, not something a better model fixes. The query optimiser's decisions depend on facts that live in your org and nowhere else: how many rows each table holds, how skewed the data is, which fields carry custom indexes, whether a picklist value covers 2% of records or 80%.

A filter is only selective if it returns a small enough slice of the table — as a rule of thumb, under 10% of rows for a custom index, and the threshold tightens as the table grows. The model can't see any of that. It can't run the Query Plan tool. It doesn't know that your Type__c field has a custom index or that 90% of your Accounts share one region value, which makes filtering on that region worse than useless.

So I've stopped expecting the model to reason about performance and started treating that as my job exclusively. When it produces a query against a table I know is large, I check the plan myself before it goes anywhere near a scheduled batch or a controller. The model writes the query. I own whether it's selective.

Security Is the Silent Omission

The other thing AI leaves out, almost every time, is enforcement. Ask for a query and you get a query — raw, running in system mode, no field-level security, no sharing, and if you asked for dynamic SOQL, quite possibly built by string concatenation that's wide open to injection.

None of that is the model being careless. It's answering the question you asked. But "give me a query of contacts by email" and "give me a query of contacts by email that respects the running user's permissions and isn't injectable" are different requests, and the model only does the second if you say so.

These days I ask for user-mode enforcement and bind variables by default:

// User-mode enforces FLS and object permissions for the running user
List<Contact> matches = [
    SELECT Id, Name, Email
    FROM Contact
    WHERE Email = :searchEmail
    WITH USER_MODE
];

// If the query has to be dynamic, bind — never concatenate user input
String q = 'SELECT Id, Name FROM Contact WHERE LastName = :searchName';
List<Contact> results = Database.queryWithBinds(
    q,
    new Map<String, Object>{ 'searchName' => userSuppliedName },
    AccessLevel.USER_MODE
);

Even with the instruction in the prompt, I read every generated query for this specifically, because it's the failure that passes code review — it works, the tests go green, and the hole only shows up in a security review or a penetration test. The enforcement rules are subtle enough that I keep a whole separate piece on them in CRUD/FLS enforcement in Apex. The short version: never assume the generated query enforces anything.

How I Actually Prompt For SOQL Now

The quality of what the model gives back is almost entirely a function of the context I give it. A bare "write me a SOQL query for open opportunities" gets a plausible guess. The version that gets me a production-ready query includes three things.

First, the schema — the actual field API names, and critically, which fields are indexed and roughly how large the table is. Second, the execution context: is this running in a trigger, a batch selector, a controller, a loop? That changes everything about the limits I care about. Third, the enforcement mode, stated up front.

A prompt that gets good output reads more like a spec than a question:

Object: Case (~6.8M rows in prod).
Indexed fields: OwnerId, CreatedDate, custom index on Region__c.
Context: batch Apex start() method, needs to be selective.
Enforce user mode. Return open cases for a given owner in the last 90 days.

Given that, the model produces something I can actually use, because I've handed it the facts it can't otherwise see. This is the same discipline that makes the model useful for Apex generally — I go into more detail on the workflow in how I use Claude Code to write Apex faster. SOQL is just the part where the cost of a bad guess is highest.

When I Don't Let It Near the Query

There's a category of query I still write entirely by hand, and I don't apologise for it. Batch selectors against tables over a million rows. Anything with data skew I know about. Dynamic SOQL that takes user input and feeds a shared component. For those, the query isn't the hard part — the hard part is the reasoning about volume, selectivity, and blast radius, and that reasoning is precisely what the model can't do.

I'll also say the uncomfortable thing: the model's fluency is part of the danger. A query that's slightly wrong and clearly wrong gets caught. A query that's beautifully formatted, syntactically flawless, and quietly non-selective slides through review because it looks like the work of someone who knew what they were doing. Reviewers relax when the code is clean. That's the trap, and if you want a second pair of eyes on the queries feeding your high-volume paths, that's the kind of review I do.

The tooling has genuinely changed how I write SOQL. I look up fewer API names, I write cleaner joins, and I ship simple queries faster than I did two years ago. What hasn't changed is that a query which reads perfectly and a query which survives 6.8 million rows are not always the same query. AI closed the gap on the first one. It hasn't touched the second.