Advantages of Using SOQL Builder in Salesforce

In this article I will summarize the reasons why you need to start using a SOQL builder from today.

The Pains in the Past

Before going to the topic, let’s revisit when a SOQL builder is in absence. Probably you have seen many times developers were using string concatenation to build dynamic SOQLs.

public List<Account> findAccounts(
    List<Id> accountIds, String countryName,
    Decimal minAmount, Decimal maxAmount) {

    String query = 'SELECT ' +
        '    Name, BillingCountry, BillingState, ' +
        '    FORMAT(CONVERTCURRENCY(AnnualRevenue)), ' +
        '    (SELECT Name FROM Contacts) ' +
        'FROM Account ' +
        'WHERE ' +
        '    Id IN :accountIds ' +
        '    AND (AnnualRevenue >= USD' + minAmount +
        '        AND AnnualRevenue <= USD'  + maxAmount +
        '    ) ' +
        '    AND BillingCountry = \'' + countryName + '\' ' +
        'ORDER BY AnnualRevenue DESC NULLS LAST';
    return Database.query(query);
}

Pain Points:

1. Developers have to deal with + signs to break string into fragments. When filtering conditions changed, it raises another pain to combine and split the string fragments again.
2. Developers waste times on finding a missing space or unpaired parenthesis inside these string fragments.
3. Developers have to add the binding variable names into the SOQL themselves, such as :accountIds.
4. Developers may introduce SOQL injection risks, when concatenate strings from user inputs, such as countryName.
5. The concatenated strings can be hardly read. All syntaxes displayed in the same red color.

It would be cleaner if written in a native SOQL as below, however:

1. It provides no reusability. For example, the where condition can not be reused for combining with other criteria to form a new query.
2. Date and currency literals have to be hard coded as USD1000.

SELECT 
    Name, BillingCountry, BillingState,
    FORMAT(CONVERTCURRENCY(AnnualRevenue)),
    (SELECT Name FROM Contacts) 
FROM Account 
WHERE 
    Id IN :accountIds
    AND (AnnualRevenue >= USD1000 AND AnnualRevenue <= USD2000) 
    AND BillingCountry = :countryName
ORDER BY AnnualRevenue DESC NULLS LAST

A Delight Future

A sophisticated SOQL builder can change these experiences totally, and more. It should possess the following characteristics:

1. The writing experience should be close to a native SOQL, and no need to learn new things.
2. Help developers follow best practices, prevent them entering wrong inputs with strong types.
3. Enhance the query syntax with new functionalities developers ever missed.

Apex Query

ApexQuery (github link) is a library designed and built with these pain points and good characteristics in mind. Let use it to build the same query method and then examine how well it improves the coding experiences.

Apex Query

1. Strong types are enforced, no commas and spaces to deal with, and manipulate these statements are much safer.
2. Variables can be passed into the filtering conditions directly, i.e. accountIds.
3. SOQL injection risks are prevented by the library, no need to handle it specifically.
4. Support the missing comparison operator between, etc.
5. Every statement fragments can be constructed standalone, passed around for modification to boost its reusability.

public List<Account> findAccounts(
    List<Id> accountIds, String countryName,
    Decimal minAmount, Decimal maxAmount) {

    Query.SelectBy selectBy = selectBy()
        .add(Account.Name, Account.BillingCountry, Account.BillingState)
        .add(FORMAT(CONVERT_CURRENCY(Account.AnnualRevenue)))
        .add('Contacts', Query.of(Contact.SObjectType).selectBy('Name'));

    Query.Filter filter = andx()
        .add(inx(Account.Id, accountIds))
        .add(between(Account.AnnualRevenue, USD(minAmount), USD(maxAmount)))
        .add(eq(Account.BillingCountry, countryName));

    Query.OrderBy orderBy = orderBy()
        .add(Account.AnnualRevenue).descending().nullsLast();

    return (List<Account>) Query.of(Account.SObjectType)
        .selectBy(selectBy)
        .filterBy(filter)
        .orderBy(orderBy)
        .run();
}

Summary

The ApexQuery (github link) contains a lot of features waiting you to explore, and give suggestions. For example, all query clauses are value objects, which makes ApexQuery even more powerful. Particularly when combined with a test mockup library such as Apex Test Kit BDD (github wiki) and a dependency injection library Apex DI (github link). I will introduce these topics in another time. Thanks for the reading.