Mastering Database Imports: Achieving Upserts with Laravel Excel and Eloquent

As a senior developer, I frequently encounter scenarios where data migration or bulk import processes run into the classic challenge: handling duplicate records. You have a clean CSV file, but when you attempt to import it into your database using standard methods, the system defaults to creating new, unwanted entries instead of intelligently updating existing ones. This is a common hurdle when dealing with data synchronization.

The goal here is to leverage Laravel Excel’s powerful features, specifically Eloquent’s upsert capabilities, to ensure that if a record already exists (based on a set of unique columns), the system performs an update; otherwise, it inserts a new record.

Let's dive into how we can solve this problem effectively using Laravel Excel and Eloquent relationships.

The Challenge with Simple Imports

When you use the ToModel interface along with traits like WithUpserts, the package provides a mechanism to handle bulk operations. However, for an upsert to work correctly, Eloquent needs to know which columns define the uniqueness of the record in your database. If this definition is missing or mismatched, the importer defaults to inserting new rows, leading to data duplication.

Your setup involves a FruitImport class designed to map CSV rows to Fruit models. The key lies in correctly defining the criteria for matching existing records.

Implementing Robust Upserts via uniqueBy()

The solution is to explicitly tell Laravel Excel which combination of fields constitutes a unique entity that should trigger an update if found, rather than an insertion. This is achieved by implementing the uniqueBy() method within your import class.

Consider your table structure:

Field	Type	Key
id	big int un..	Primary Key
color	varchar(12)	Unique identifier component
taste	varchar(12)	Unique identifier component
smell	varchar(12)	Unique identifier component
name	char(20)	Unique identifier component

If you want to ensure that a fruit is uniquely identified by its color, taste, smell, and name combination, you must define these fields in your import class.

Here is the corrected implementation approach:

use Maatwebsite\Excel\Concerns\ToModel;
use Maatwebsite\Excel\Concerns\WithUpserts;
use App\Models\Fruit;

class FruitImport implements ToModel, WithUpserts
{
    /**
     * Define the columns that uniquely identify a record.
     * This tells Laravel where to look for existing records to perform an update (upsert).
     */
    public function uniqueBy()
    {
        // Define the composite key that must match existing database records.
        return ['color', 'taste', 'smell', 'name'];
    }

    /**
     * Map the Excel row data to the Fruit model attributes.
     */
    public function model(array $row)
    {
        // When an existing record matches the uniqueBy criteria, 
        // Eloquent will attempt to update it instead of inserting a new one.
        return new Fruit([
            'color' => $row[0],
            'taste' => $row[1],
            'smell' => $row[2],
            'name' => $row[3],
            'price' => $row[4],
            'supplier_id' => $row[5],
        ]);
    }
}

How This Works Under the Hood

When you use WithUpserts along with a defined uniqueBy() method, Laravel Excel orchestrates the import. For each row read from the CSV, it constructs the unique key based on the fields specified in uniqueBy(). It then queries the database using this composite key.

1. Check Existence: If a record matching the combination of (color, taste, smell, name) is found, Eloquent executes an UPDATE on that existing record.
2. Handle New Data: If no matching record is found, Eloquent executes an INSERT of the new record.

This mechanism ensures data integrity by preventing the creation of duplicate entries based on your defined business rules. This pattern aligns perfectly with robust data handling practices encouraged by modern frameworks like Laravel, which emphasizes clean, efficient database interactions (as seen in official documentation regarding Eloquent functionality).

Conclusion

The transition from simple mass insertion to intelligent upserts requires defining explicit uniqueness constraints during the import process. By correctly implementing the uniqueBy() method in your Laravel Excel importer, you gain full control over how duplicate data is handled—ensuring that existing records are updated rather than duplicated. This approach transforms a potential data corruption issue into a reliable synchronization tool, making bulk operations significantly more robust and scalable.