System.QueryException: List has no rows for assignment to SObject

You're staring at a stack trace from production. A user clicked Save. The page broke. The log says System.QueryException: List has no rows for assignment to SObject, and you can already feel that this one will be the kind of fix that takes ten minutes but the kind of post-mortem that takes an hour.

What the platform is actually telling you

Apex lets you assign a SOQL result directly to an SObject variable. The compiler trusts that the query returns exactly one row.

Account a = [SELECT Id, Name FROM Account WHERE Name = 'Acme' LIMIT 1];

When the query matches zero rows, Apex has nothing to assign. SObject variables are reference types, but they're not nullable in the assignment-from-query sense. The runtime can't put "no record" into a declared Account slot, so it throws QueryException: List has no rows for assignment to SObject.

When the query matches two or more rows, you get a different exception: System.QueryException: assignment to SObject from non-singleton list. Same exception class, different message. The single-row contract is strict on both ends.

This is the only language feature in Apex where the return type of an expression depends on the surrounding context. The same SOQL query is a List<Account> if you write List<Account> hits = [...], and a single Account if you write Account a = [...]. The shorthand is convenient when you know the row exists. It's a footgun when you don't.

The broken example

Walk through this Apex class, which fetches a setting record and returns its threshold:

public class CaseEscalationService {
    public static Integer thresholdForRegion(String region) {
        Escalation_Setting__c setting = [
            SELECT Threshold__c
            FROM Escalation_Setting__c
            WHERE Region__c = :region
            LIMIT 1
        ];
        return Integer.valueOf(setting.Threshold__c);
    }
}

In a sandbox where Escalation_Setting__c has rows for 'NA', 'EMEA', and 'APAC', this works. The unit tests pass. The code review approves.

The first time someone calls thresholdForRegion('LATAM') in production, the trigger that wrapped it logs an unhandled System.QueryException: List has no rows for assignment to SObject and the case save fails. The user sees a generic "unexpected error" page. The integration that was driving the bulk import retries the same record 600 times before its circuit breaker trips. Now you have a Slack thread.

Why this is especially nasty

Three reasons make this error a recurring resident of error pages instead of a one-time learning experience.

It only fails when the data isn't there. Sandboxes always have the test data you seeded. Tests always have the Test.startTest() block you handcrafted. The failure mode is "real users in real orgs with real edge cases." That's exactly the worst place for a runtime exception.

The error message reads as if it's about the list. Junior developers see "List has no rows for assignment to SObject" and start checking their List<Account> variables, none of which are part of the bug. The actual culprit is the unwrapped Account a = [...] line, which doesn't look like a list at all.

It survives refactors. The pattern is so compact that engineers copy it across classes without thinking about whether each context can guarantee the row exists. Five years of org tenure later, dozens of methods share the same defect.

The fix

Always materialize the query into a List, check isEmpty(), then index. The List form returns an empty list (a valid, non-null value) instead of throwing, which lets you decide what to do.

List<Escalation_Setting__c> hits = [
    SELECT Threshold__c
    FROM Escalation_Setting__c
    WHERE Region__c = :region
    LIMIT 1
];
if (hits.isEmpty()) {
    return null;
}
return Integer.valueOf(hits[0].Threshold__c);

That's the mechanical fix. There are three judgment calls to make on top of it.

Decide what "no rows" means semantically. If a missing setting is legitimately a "no escalation applies" case, returning null (or a default like Integer.MAX_VALUE) is appropriate. If a missing setting means a config defect that should halt the operation, throwing an explicit error is better.

if (hits.isEmpty()) {
    throw new IllegalArgumentException(
        'No Escalation_Setting__c configured for region "' + region + '". '
      + 'Add a record via Setup or contact #platform-config.'
    );
}

A clear message in the error log saves an on-call engineer twenty minutes of triage compared to the cryptic stock exception.

Push the check up the call stack when possible. If five callers all need the same row, query once at the top of the trigger context and pass the resolved record down. The deepest method shouldn't be the one discovering that the org is misconfigured.

Don't use [SELECT ... LIMIT 1][0] to dodge the issue. Indexing an empty list throws System.ListException: List index out of bounds: 0, which is a different exception with a different remediation. You traded one failure for another. The pattern looks clever and reads as obviously broken to anyone who has hit it before.

The fixed example

Here's the same class with the bug removed and the design tightened:

public class CaseEscalationService {
    public class MissingEscalationSettingException extends Exception {}

    public static Integer thresholdForRegion(String region) {
        if (String.isBlank(region)) {
            throw new IllegalArgumentException('region is required');
        }
        List<Escalation_Setting__c> hits = [
            SELECT Threshold__c
            FROM Escalation_Setting__c
            WHERE Region__c = :region
            LIMIT 1
        ];
        if (hits.isEmpty()) {
            throw new MissingEscalationSettingException(
                'No Escalation_Setting__c configured for region "' + region + '"'
            );
        }
        return Integer.valueOf(hits[0].Threshold__c);
    }
}

Three improvements rolled in: an input-validation guard, an empty-result guard, and a domain-specific exception class so callers can catch the missing-config case without catching every other QueryException.

Where this shows up beyond direct assignment

The same family of failure hides in any place that an SObject reference is assumed non-null:

• record.SomeField after a Database.query call returned an empty list and you took result[0] without checking.
• Account a = recordMap.get(someId); where the map is empty for that id, followed by a.Name.
• Map-based lookups: Map<Id, Account> byId = new Map<Id, Account>([SELECT Id, Name FROM Account WHERE ...]); then byId.get(someId).Name when the id wasn't in the result set.

All of these share the lesson: SObject references in Apex can be null, and dereferencing null is its own exception (NullPointerException), so guard every access that depends on an external lookup.

Testing the empty-list path

Write a test that explicitly does not seed the row. The default test context in Apex sees no production data (the platform isolates test transactions), so a test that runs thresholdForRegion('LATAM') without inserting a corresponding setting record will trigger the empty path:

@isTest
static void thresholdForRegion_throwsWhenSettingMissing() {
    Test.startTest();
    try {
        CaseEscalationService.thresholdForRegion('LATAM');
        System.assert(false, 'Expected MissingEscalationSettingException');
    } catch (CaseEscalationService.MissingEscalationSettingException ex) {
        System.assertNotEquals(null, ex.getMessage());
    }
    Test.stopTest();
}

A test like this catches the bug at deploy time, before it reaches production. It also documents the contract: "this method throws when the setting is missing, on purpose."

Closely related errors

All four are symptoms of the same broad defect class: assuming an external lookup found something. The fixes share a pattern: query into a List, check size, then proceed.

How this pattern gets into a codebase

The single-row assignment form predates much of modern Apex idiom. In early versions of the language, it read as the natural way to write "fetch one record by id," and a generation of Trailhead modules used it in introductory examples without flagging the failure mode. Engineers who learned Apex that way carry the habit forward.

The pattern survives modern code review for a quiet reason. It's syntactically compact, semantically familiar to anyone who has used a singleton-find method in another framework, and reads as legible English. The reviewer's eye skips past it. The reviewer's brain does not pattern-match it against "this is a runtime exception waiting for a missing row," because the failure case is invisible at the call site.

You can find every instance of the bug in your org with a simple grep against your repository. Look for any line that matches the shape SObjectName variableName = [SELECT without a surrounding List< declaration. Run the search across your team's repos before the next release. Every match is a candidate for the List-and-check rewrite.

Defensive habits that prevent this class of bug

Three habits help, independent of the specific error.

Treat external lookups as fallible. SOQL, Map lookups, REST callouts, custom-metadata reads, Custom Settings reads. All of them can return nothing. The Apex compiler doesn't enforce a null-check, so you have to remember. Build the muscle of pairing every external read with a "what happens when this is empty?" comment in your head.

Prefer Map<Id, SObject> for record-by-id access. When you need to look up records by Id within a transaction, querying into a Map and using map.get(id) returns null for missing keys without throwing. This shifts the failure into a NullPointerException if you forget to guard, which is louder and easier to catch in QA than a QueryException deep inside a method.

Wrap config reads in a service class. If the same Escalation_Setting__c is read from twenty places, create one class that owns the read and exposes a typed method like Optional<Integer> thresholdForRegion(String region). The empty-result handling lives once. Callers can't bypass it.

Database.query and the empty-result family

Dynamic SOQL via Database.query(queryString) always returns a List<SObject>, so it can't trigger the single-row exception directly. It can still trigger the family of failures if you take [0] on the result without checking:

List<SObject> hits = Database.query(
    'SELECT Id FROM ' + objectName + ' WHERE Name = :name LIMIT 1'
);
SObject hit = hits[0]; // ListException if the dynamic query returned zero rows.

The fix is the same: check isEmpty() before indexing. Dynamic SOQL doesn't buy you out of the guard, it just changes which exception class fires when you skip it.

A related trap is Database.queryWithBinds (introduced to replace string-concatenated bind variables for safety). It has the same return-type behavior as Database.query: always a list, always requires the size check before indexing.

When the error fires in batch and async contexts

Batch Apex start() methods that return Database.QueryLocator cleanly tolerate empty result sets. The batch run completes with zero execute() invocations, which is unusual but not exceptional. The error only fires if your code inside execute() or in a separate method does the single-row assignment.

Queueable Apex and @future methods are exactly as exposed as synchronous code. The exception thrown inside async work surfaces in the Apex Job log under Setup, often without a user-visible trace. Add a try/catch around the query if the work is critical, log the failure to a custom object, and notify your on-call channel. Silent failures in queues are the worst kind of silent failure.

Static analysis can catch most occurrences

PMD's Apex ruleset and the Salesforce Code Analyzer both flag the single-row assignment pattern. If you run them in CI on every pull request, you catch new instances at PR time, before they merge. Retrofitting the check on a legacy codebase is a one-time cleanup that pays off forever.

For teams without CI tooling, the Developer Console's "Code Coverage" view can highlight Apex methods with low coverage on their empty-data path. Methods that show 100% coverage from a test that only seeded a populated org are exactly where this bug lives.

Further reading from Salesforce

• Apex Developer Guide: SOQL and SOSL Queries
• Apex Developer Guide: Exception Class and Built-In Exceptions
• Apex Developer Guide: Working with SObjects
• Trailhead: Apex Testing Module
• Apex Developer Guide: Querying Data Using SOQL