DML in Apex

What is DML?

DML stands for Data Manipulation Language. It allows you to manipulate rows or records in database, one at a time or in batches. Read and Write operation on salesforce database achieved by SOQL queries and DML Operations respectively. Apex supports following DML operations insert, update, upsert, merge, delete, undelete and convertLead. Basic concepts of DML operations are similar to those of other databases but, Salesforce introduces it’s own set of features like transactional integrity and governor limits.

What are Transactions in Apex?

It represents a set of operations executed as a single unit. If error occur in one operation, the entire transaction is rolled back. Transactions ensure data integrity and consistency by following the principles of ACID (Atomicity, Consistency, Isolation and Durability). In simple terms, we can understand transaction as operations listed under one debug log count as single transaction in context of particular user. Transactions in apex are subject to governor limits imposed by Salesforce platform to ensure efficient resource usage and system performance.

DML Statements Vs Database Methods

Apex offers you a flexibility to perform data operations. There are two ways DML Statements and Database methods.

DML Statements	Database Methods
No partial record processing allowed. If DML Operation fails it returns exception without processing further records.	It allows partial record processing by setting allOrNone parameter to false. Remainder DML operation still succeed if single record or multiple fails.
If DML Operation fails it returns the exception and the entire transaction rollbacks.	It returns a result object array which contains status of each operation and errors encountered. If allOrNone set to true exception will occur.
DML Statements are specific to manipulating records. It is a subset of Database Operations.	Database methods involves transaction control statements, dynamic query methods, and many more.
convertLead operation not present in DML Statements	convertLead operation present in Database methods.

Insert

To insert a new record no need to provide Id value. Salesforce create a unique Id for each record. In below example debug statement at line number 5 prints account without Id. After inserting account record Id generates automatically. If we attempt to provide Id value manually it will cause System.DmlException: INVALID_FIELD_FOR_INSERT_UPDATE, cannot specify id in an insert call exception.

Account acc = new Account();
acc.Name = 'Dancing Rabbit';
acc.Industry = 'Entertainment';
acc.Type = 'Other';
System.debug(acc);
insert acc;
System.debug(acc);

USER_DEBUG [5]|DEBUG|Account:{Name=Dancing Rabbit, Industry=Entertainment, Type=Other}
USER_DEBUG [7]|DEBUG|Account:{Name=Dancing Rabbit, Industry=Entertainment, Type=Other, Id=0012w000029Ui1qAAC}

Update

To perform Update operation we need to identify the record first. It is used to modify the existing record. It is necessary to provide Id in update call otherwise it will cause System.DmlException: MISSING_ARGUMENT, Id not specified in an update call.

List<Account> accList = [SELECT Id, Name, Industry,
                         Type, BillingCity
                         FROM Account 
                         WHERE Name='Dancing Rabbit'
                         LIMIT 1];
System.debug(accList);
if(accList != null && !accList.isEmpty()){
    Account acc = accList[0];
    System.debug(acc);
    acc.BillingCity = 'New York';
    System.debug(acc);
    update acc;
}

USER_DEBUG [6]|DEBUG|(Account:{Id=0012w000029Ui1qAAC, Name=Dancing Rabbit, Industry=Entertainment, Type=Other})
USER_DEBUG [9]|DEBUG|Account:{Id=0012w000029Ui1qAAC, Name=Dancing Rabbit, Industry=Entertainment, Type=Other}
USER_DEBUG [11]|DEBUG|Account:{Id=0012w000029Ui1qAAC, Name=Dancing Rabbit, Industry=Entertainment, Type=Other, BillingCity=New York}

Please note it is not a good practice to hardcode the record Id in a apex code because, salesforce generates unique Ids in different environments. Id present in sandboxes won’t be available in production which leads to unexpected behavior and exceptions. If hardcoded Id is false or not available in environment then it will throw System.DmlException INVALID_CROSS_REFERENCE_KEY, invalid cross reference id: [] exception.

Upsert

Upsert operation is use to either insert or update a records in one call. The decision to update or insert a record depend on record Id or custom external field or standard field with idLookup attribute set to true these fields uses as a key to identify records. Three scenarios are there, if key is not present insert records, if key is present update the record and if key present multiple times then exception will occur and record neither inserted nor updated.

List<Account> accList = [SELECT Id, Name, Industry,
                         Type, BillingCity
                         FROM Account 
                         WHERE Name='Dancing Rabbit'
                         LIMIT 1];
Account acc = new Account();
if(accList != null && !accList.isEmpty()){
    acc = accList[0];
    acc.BillingCity = 'New Jersey';
}
List<Account> accToBeUpserted = new List<Account>{new Account(Name='Teasing Turtle',Industry='Entertainment')};
accToBeUpserted.add(acc);
System.debug(accToBeUpserted);
upsert accToBeUpserted; 
System.debug(accToBeUpserted);

USER_DEBUG [15]|DEBUG|(Account:{Name=Teasing Turtle, Industry=Entertainment}, Account:{Id=0012w000029Ui1qAAC, Name=Dancing Rabbit, Industry=Entertainment, Type=Other, BillingCity=New Jersey})
USER_DEBUG [17]|DEBUG|(Account:{Name=Teasing Turtle, Industry=Entertainment, Id=0012w000029UijYAAS}, Account:{Id=0012w000029Ui1qAAC, Name=Dancing Rabbit, Industry=Entertainment, Type=Other, BillingCity=New Jersey})

Delete

Delete operation is used to remove the record from database. In Salesforce, records not deleted permanently it move to Recycle bin for 15 days. After 15 days records cannot be restore back from Recycle bin. Delete DML operation supports cascading deletions. If parent object deleted child objects deleted automatically as long as child record can be deleted.

List<Account> accList = [SELECT Id, Name, Industry,
                         Type, BillingCity
                         FROM Account 
                         WHERE Name='Dancing Rabbit'
                         LIMIT 1];
System.debug(accList);
delete accList;
System.debug([SELECT Id, Name, Industry,
              Type, BillingCity
              FROM Account 
              WHERE Name='Dancing Rabbit'
              LIMIT 1]);

USER_DEBUG [6]|DEBUG|(Account:{Id=0012w000029Ui1qAAC, Name=Dancing Rabbit, Industry=Entertainment, Type=Other, BillingCity=New Jersey})
USER_DEBUG [8]|DEBUG|()

After record has been deleted the same query is not returning a record.

Undelete

Undelete operation restores the deleted record from Recycle Bin.

Account acc = [SELECT Id
               FROM Account 
               WHERE Name='Teasing Turtle'
               LIMIT 1];
insert(new Contact(LastName='Anderson',AccountId=acc.Id));
System.debug(acc+' *Contact* '+[SELECT Id, LastName FROM Contact WHERE AccountId=:acc.Id]);
delete acc;
System.debug(acc+' *Contact* '+[SELECT Id, LastName FROM Contact WHERE AccountId=:acc.Id]);
Account[] accList = [SELECT Id, Name
                        FROM Account
                        WHERE Name='Teasing Turtle'
                        ALL ROWS];
System.debug(acc+' *Contact* '+[SELECT Id, LastName FROM Contact WHERE AccountId=:acc.Id]);
undelete accList;
System.debug(acc+' *Contact* '+[SELECT Id, LastName FROM Contact WHERE AccountId=:acc.Id]);

USER_DEBUG [6]|DEBUG|Account:{Id=0012w000029UijYAAS} *Contact* (Contact:{Id=0032w00001OrDqJAAV, LastName=Anderson})
USER_DEBUG [8]|DEBUG|Account:{Id=0012w000029UijYAAS} *Contact* ()
USER_DEBUG [13]|DEBUG|Account:{Id=0012w000029UijYAAS} *Contact* ()
USER_DEBUG [15]|DEBUG|Account:{Id=0012w000029UijYAAS} *Contact* (Contact:{Id=0032w00001OrDqJAAV, LastName=Anderson})

Database Method

List<Account> accList = new List<Account>();
for(Integer i = 0; i < 3 ; i++){
    Account acc = new Account(Name='gig'+i , Industry='Other');
    accList.add(acc);
}
accList.add(new Account(Name='Test2'));
Database.SaveResult[] results = Database.insert(accList,false);

for (Database.SaveResult sr : results) {
    if (sr.isSuccess()) {
        // Operation was successful, so get the ID of the record that was processed
        System.debug('Account inserted successfully. Account ID: ' + sr.getId());
    }
    else {
        // Operation failed, so get all errors                
        for(Database.Error err : sr.getErrors()) {
            System.debug('ERROR '+err.getStatusCode() + ': ' + err.getMessage());
            System.debug('Account fields that affected this error: ' + err.getFields());
        }
    }
}

USER_DEBUG [12]|DEBUG|Account inserted successfully. Account ID: 0012w000029Ul8aAAC
USER_DEBUG [12]|DEBUG|Account inserted successfully. Account ID: 0012w000029Ul8bAAC
USER_DEBUG [12]|DEBUG|Account inserted successfully. Account ID: 0012w000029Ul8cAAC
USER_DEBUG [17]|DEBUG|ERROR FIELD_CUSTOM_VALIDATION_EXCEPTION: Please enter Industry
USER_DEBUG [18]|DEBUG|Account fields that affected this error: (Industry)

Bulk DML Operation

Bulkification a best practice that ensures code can properly handle more than one record at a time. Consume least amount of resources, less chance of hitting governor limits, reduces transaction execution time. Following are some of the governor limits related to DML operations: 150 DML Statements are allowed per transaction and 10,000 rows can be processed in DML Operations per transaction.

List<Account> accList = [SELECT Id, Industry FROM Account WHERE Industry=null];
for(Account acc : accList){
    acc.Industry = 'Other';
    update acc;
}

Number of DML statements: 3 out of 150
Number of DML rows: 3 out of 10000

In the above example, DML statement is written inside a for loop which cause DML statement to execute N times. N is number of rows retrieved in query. In our example three records are present in a list hence, DML performs 3 times and each time on a single record this practice will lead to poor performance and exceeding governor limits.

List<Account> accList = new List<Account>();
for(Account acc : [SELECT Id FROM Account WHERE Type=null]){
    Account updatedAccount = new Account(Id = acc.Id, Type = 'Other');
    accList.add(updatedAccount);
}
if(!accList.isEmpty()){
    update accList;
}

Number of DML statements: 1 out of 150
Number of DML rows: 4 out of 10000

Above example shows bulkified DML Operation as records which needs to update stored inside a collection and DML operation perform outside the for loop. Hence, DML performs only once on N number of records. In our example N is 4.

Best Practices to Perform DML Operation

1. Bulkify your code: Always process your records in a bulk to respect governor limits. Use collections wisely to handle multiple records efficiently. Avoid using SOQL queries and DML statements inside for loop.
2. Query Optimization: Fetch only necessary fields using SOQL queries to minimize execution time. Utilize WHERE clause effectively to filter out the irrelevant records. Consider using relationship queries to fetch related records in single queries. With DML on sObject It is best to construct new instances and only update the fields you wish to modify without querying other fields.
3. Error handling: Implement robust error handling mechanisms using try-catch blocks to handle exceptions gracefully. Log errors for debugging purposes and provide meaningful error messages to users when appropriate.
4. Governor Limits Awareness: Stay mindful of Salesforce governor limits, including limits on DML statements, SOQL queries, CPU time, heap size, and other resource limits. Design your code to avoid hitting these limits, and implement strategies to handle limit exceptions when they occur.
5. Asynchronous Processing: Consider using asynchronous processing mechanisms such as Batch Apex, Queueable Apex, or @future methods for long-running or bulk data operations.
6. Security Considerations: Ensure that your code adheres to Salesforce security best practices, including field-level security, object-level security, and sharing rules. Use Schema methods to check whether user have necessary permissions or not. Use with sharing keyword to enforce sharing rules of current user.