SOQL (Salesforce object query language) and DML (Data manipulation language) are the languages used in Salesforce to read and modify records, respectively. In this blog post, we’ll explore how to use them in Apex, including best practices to prevent hitting governor limits. This will be particularly helpful to developers who are new to Salesforce development, or those who need a refresher on these topics.

Multitenancy in Salesforce

Before diving into SOQL and DML, it’s important to first understand how your records are stored in Salesforce. The database that contains your records behind the scenes does not actually contain a SQL table for each of your standard and custom objects. The database stores records in a multitenant database, a database that stores data and metadata from multiple tenants in a standardized way. The database contains generic tables to store standard and custom object records.

I particularly like the image below from a past Dreamforce session that helps to illustrate how records from different tenants are stored:

I also like the explanation of multitenancy in the Multitenant architecture wiki page and the Best Practices for Deployments with Large Data Volumes guide.

As you can imagine, a Salesforce developer cannot use SQL to access these tables directly for security reasons. Instead, you read data writing SOQL queries that the query optimizer translates transparently into the correspondent SQL queries. Unlike with other database languages, data cannot be modified using SOQL. We’ve got another language to do that, which is DML.

Example data model

To illustrate how SOQL and DML work, we’ll use a pair of custom objects in the code examples shown later in this post. We’ll have a Species__c custom object to represent plant species, and we’ll have a Plant__c custom object to represent plants that belong to a certain species.

In Salesforce, you relate objects using lookup or master-detail relationships. These fields are equivalent to what is known in other database languages as a foreign key, a field that references another object. This means that the Plant__c object will have a lookup relationship with Species__c (its parent).

Reading data with SOQL

Let’s start by talking about SOQL. The following is a simple SOQL query which we use to read records for a custom object called Plant__c:

Let’s examine the different keywords used in the query:

• SELECT — used to indicate the fields that you want to retrieve. The only one that is returned by default is the Id field. You can SELECT FIELDS(STANDARD) to retrieve all the standard fields for which the user has permissions. Take a look at all the available options for the function and the APIs in which they’re supported.
• FROM — the main table from which we want to retrieve the records
• WHERE — used to specify filter conditions
• ORDER BY — used to order the results by a specific field
• LIMIT — to limit the number of results returned. Tip: using LIMIT 1 let’s you assign the result in Apex to a single record variable. Only use this option if you are 100% certain the record will always be there.

The following is the result for this query in an org in which I created some sample data:

You can execute queries for debugging purposes on the Developer Console, or preferably in VSCode after having installed the Salesforce Extension pack. In VSCode, there are different options to do so. The preferred and easiest option is to use SOQL Builder, a tool to visually build and run queries. You can also execute a written query by selecting it in the text or entering it inline in the command bar.

However, where you’ll normally use queries in production is in your Apex code. Standard and custom objects (also other types of objects, such as external objects) have an equivalent data type in Apex. For instance, having a Species__c object implies there is a Species__c class that represents objects of that type and that contains all the Species__c object fields. There’s also a generic sObject class that you can use for any object type. You can construct these types yourself or use them to store the records returned by a SOQL query:

This is how we call our SOQL query from Apex, enclosing it into square brackets and assigning the returned value to a List<sObject> or List<ConcreteObject__c> variable:

In the previous query, we specified all the values we wanted to use, such as the acquisition date to filter from or the record number to limit. But what if don’t know those values in advance? In that case, you can bind Apex variables using a colon (:) as follows:

The result in this case would be exactly the same, but the acquisitionDate variable would be evaluated at runtime.

Note: In Apex, be sure to include WITH SECURITY_ENFORCED (see docs) in your queries, to enforce field- and object-level security permissions checking.

Bringing related records with relationship queries

Let’s now discuss how you can incorporate related records data in your SOQL queries, This is probably the most noticeable difference with other query languages. In SOQL, you execute queries against a single table, and then you have mechanisms to bring related data as part of the query. In order to do that, the objects need to have a relationship. Concretely, you can:

• Query a child and bring its parent data (child-to-parent query)
• Query a parent and bring its children data (parent-to-child query)

This means that in SOQL you can make joins as in other query languages, but using a different syntax and always based on a foreign key (relationship). This design decision was taken to increase performance at the time when the query optimizer translates the queries into SQL.

Child-to-parent relationship queries

These kind of queries allow you to execute the main query on a child object and bring its parent data as part of the result.

To be able to reference the parent on the SELECT part, we’ll need to use the API name that represents the relationship with the parent. In our case, as it’s a custom object, it will be the name of the relationship field substituting __c by __r. For standard objects, just use the relationship field API name.

This is the result that I got for my org:

If you want to learn about standard object relationships, take a look at the different app data models on the Salesforce Architects site.

Parent-to-children relationship queries

These kinds of queries allow you to execute the main query on a parent object and bring its children’s data as part of the result.

To be able to reference the list of children on the SELECT part, we’ll need to use the child relationship name defined in the relationship field plus __r, or just the child relationship name for standard objects. In this case, as the parent can have multiple children, we’ll need to use a subquery.

For instance, in our case, we can bring all our species with their child plants:

The result:

That is, I have two Jasmine plants, one Pothos, and two Aloe Veras on my terrace!

To handle the result in Apex, bear in mind that the returned relationship field will contain a list of children:

Doing more with relationship fields

There’s a lot more that we can do in SOQL. For instance, we could use parent relationship fields on the WHERE part to filter by parent field value(s):

Or we could also use a subquery on the WHERE part to filter by related children:

Constructing queries at runtime with dynamic SOQL

So far, we’ve used static SOQL to execute our queries. But sometimes, you need to construct the query at runtime (in the form of a String) and then run it as a dynamic query. This is how you do it:

Static queries are preferred when possible as they’re evaluated at compile time, preventing bad constructed queries and SOQL injection attacks. To prevent SOQL injection in dynamic queries, always bind user-supplied value using the colon notation.

Leveraging the power of aggregate queries

Aggregate queries allow you to roll up and summarize records. For instance, I can count the number of records that match a specific condition, such as the number of Aloe Vera plants I have:

Or I can use the AVG() function to calculate the average max temperature values that my species support:

This is what I obtained as a result:

Aggregate queries become even more powerful when using GROUP BY. For instance, this is how I calculate the average max temperature values that my species support, grouped by species type (ornamental or edible):

In this case, obtaining:

Modifying records with DML

Next, let’s take a look at how you manipulate records in Apex. For this purpose, instead of using SOQL, we use DML, our Data Manipulation Language. Thanks to the use of DML for the modification of records, we can prevent SOQL injection flaws associated with these operations.

With DML, you can insert, update, upsert, delete, and even undelete and merge records by using those keywords before a record or list of records to manipulate. Here are some examples:

Exception handling and transaction control for DML operations

Exception handling is an important aspect to bear in mind when performing DML operations. If a DML operation fails, a DMLException is thrown by the Apex runtime. This can happen, for instance, when the sObject to modify contains wrong values, or when a validation on a trigger is not fulfilled. If exceptions are not handled, Salesforce automatically rolls back all the DML operations that occurred during that transaction.

You can handle exceptions and perform full or partial rollbacks yourself using Apex’s transaction control mechanisms.

In this case, if the Species__c record was inserted correctly but the Plant__c record creation failed, the Species__c record will still be created. Salesforce’s automatic rollback didn’t happen; instead, we manually rolled back the database to the state it had after the Species__c record was created. By doing that, we rolled back any changes performed by the triggers and automations that executed during the plant insert operation before the failure happened. That way, we maintained the integrity of the data.

Modifying records at runtime with dynamic DML

In the same way that you construct dynamic queries, you can create records dynamically and then manipulate them using DML. This option can be interesting when you don’t know the name of the object or the fields to modify in advance.

Governor limits and bulkification

Lastly, let’s cover an important concept that Salesforce developers must understand and take into account when writing code. The concept of multitenancy does not only apply to the database. Your whole Salesforce instance is running in an infrastructure securely shared with other tenants. That’s why Salesforce prevents your custom code from monopolizing resources and — the thing I like most about Salesforce development — you need to write optimal code, because if you don’t do it, you’ll easily hit governor limits! Some governor limits that apply to SOQL and DML operations are:

• You can execute at most 100 SOQL queries within a transaction
• You can execute at most 150 DML statements in a transaction

This means that to minimize the number of SOQL queries and DML operations that your code executes within a transaction, it’s a best practice to:

• Always perform those operations over collections of records instead of over single records (what we call “bulkification”)
• Never, never, never execute SOQL or DML within a loop
• Use SOQL for loops (more efficient) when possible

Tip: your catch blocks won’t be able to handle your governor limit exceptions, and they’ll always behave as unhandled exceptions, resulting in a full rollback.

SOSL

On a final note, there’s another language used to construct search-based text queries called SOSL (Salesforce Object Search Language), however, we won’t cover SOSL in this blog post.

Resources and next steps

In this blog post, you’ve learned about the most important aspects that a Salesforce developer must know to start writing Apex code that works with Salesforce records. We’ve seen how SOQL works to read records and how to modify records using DML.

Some related blog posts:

• Basic SOQL Relationship Queries
• SOQL for the SQL Developer

And if you prefer to get hands on, head to Trailhead and tackle the following modules:

• SOQL for Admins
• Transform SQL Queries to SOQL Queries in a Lightning App
• Database & .NET Basics
• Apex Basics & Database

Authors

Alba Rivas works as a Principal Developer Advocate at Salesforce. She currently focuses on Lightning Web Components and Slack development. You can follow her on Twitter @AlbaSFDC.