DML Statements vs Database Methods in Salesforce

DML stands for Data Manipulation language and it is used to create and modify records in Salesforce. It provides a way to manage records by providing statements like insert, update, merge, delete, and undelete.

• Insert: This statement is used to insert records.
• Update: This is used to update fields
• Delete: Used for deletion of records
• Undelete: Used for undeletion of records
• Upsert: Used to create new records and update existing records within a single statement
• Merge: This statement merges up to three records of the same sObject type into one of the records, deleting the others, and re-parenting any related records.

Don't forget to check out: Files, Attachments And Notes Salesforce Lightning Components | Force.com

On the other side, we also have database methods in Salesforce. Database class methods are another way of working with DML statements which are more flexible than DML statements like insert, update, etc.

DML and database methods do the same exact thing i.e manipulating with records. Now the question arises that if they do the same thing then why do we have the database methods.

The main reason is Database methods are more flexible than DML statements, a bit confusing, isn’t it? No problem, keep going on till the end you will get to know how database methods are more flexible.

1. The very first reason we prefer database methods over DML statements is a Partial update. In DML Partial update is not allowed but in database methods, it is allowed. 

This means if we want to insert 10 records in an object and there is a required field on the object and we are not providing the required field for all of the 10 records which we are trying to insert. 

In the case of DML statements, the operation will not get committed because of the wrong data but in case of database methods, it will successfully insert those records for which required field value is available.

We just have to specify the parameter in the Database method as true or false. True to allow the partial update and false for not allowing the same.

Example: Database.insert(nameOfList, false), where false indicates that partial update is not allowed.

2. In DML we cannot get the list of success and failed records but we can get the same in database methods.

Insert operation using Database method

Public static Database.SaveResult insert(sObject recordToInsert, Boolean allOrNone) 

The optional allOrNone parameter specifies whether the operation allows partial success.  If you specify false for this parameter and a record fails, the remainder of the DML operation can still succeed. 

This method returns a result object that can be used to verify which records succeeded, which failed and why.

Please go through the below example of Database methods:

I have few custom objects available in my org and will perform the database operations on them.

Insert operation 

//Insert operation using Database Method
APEX_Customer__c objCust = new APEX_Customer__c();
objCust.Name = 'Test';
insert objCust;
//Add 2 Invoices
//Create a list which will store invoices
List<APEX_Invoice__c> InvoiceListToInsert = new List<APEX_Invoice__c>();
//First Invoice
APEX_Invoice__c objCustInvoice1 = new APEX_Invoice__c();
objCustInvoice1.APEX_Status__c = 'Pending';
objCustInvoice1.APEX_Amount_Paid__c = 5000;
objCustInvoice1.APEX_Customer__c = objCust.Id;
InvoiceListToInsert.add(objCustInvoice1);
//Second Invoice
APEX_Invoice__c objCustInvoice2 = new APEX_Invoice__c();
objCustInvoice2.APEX_Status__c = 'Paid';
objCustInvoice2.APEX_Amount_Paid__c = 9000;
InvoiceListToInsert.add(objCustInvoice2);
//Database Insert method. We are passing two parameters.
//First: List of the Invoice records we need to insert
//Second: AllorNone
Database.SaveResult[] resultlist = Database.insert(InvoiceListToInsert, false);
//Check which records got successfully inserted and which got failed
for(Database.SaveResult SR : resultlist)
{
    //Successful records
    If(SR.isSuccess())
    {
        System.debug('Successfully inserted these records: '+SR.getID());
    }
    //Failed records
    else
    {
        for(Database.Error SRError : SR.getErrors())
        {
            System.debug('Following error has occured');            
            //Get error status code and message
            System.debug(SRError.getStatusCode()+' : '+SRError.getMessage());           
            //Know which fields are affected
            System.debug('Fields of Invoice object which are affected : '+SRError.getFields());
        }
    }
}

Check out another amazing blog by Udit here: Salesforce REST API Integration With Shopify

Update operation

//Fetch the Invoice records
//Update status field to Paid
List<APEX_Invoice__c> invoicesList = [Select Id, Name, APEX_Status__c, CreatedDate from APEX_Invoice__c];
//Create a list to store records which we are going to update
List<APEX_Invoice__c> listToUpdate = new List<APEX_Invoice__c>();
for(APEX_Invoice__c objInvoice : invoicesList)
{
    If(objInvoice.APEX_Status__c == 'Pending'){
        bjInvoice.APEX_Status__c = 'Paid';
        listToUpdate.add(objInvoice);
    }
}
Database.SaveResult[] SRUpdate = Database.update(listToUpdate, false);
//Iterate through each return result
For(Database.SaveResult SR : SRUpdate){
    If(SR.isSuccess()){
        System.debug('Successfully updated records : ' +SR.getID());
    }    
    else{
        for(Database.Error SRError : SR.getErrors()){
            System.debug('Following error has occured : ');            
            //Get error status code and message
            System.debug(SRError.getStatusCode()+' : '+SRError.getMessage());            
            //Know which fields are affected
            System.debug('Fields of Invoice object which are affected : '+SRError.getFields());
        }
    }
}

I hope you get an idea of the reason why we opt database methods over DML statements. 

Thank you for visiting.

Reference: tutorialspoint, trailhead.salesforce, sqlcourse, salesforce.stackexchange