When to Use upsert() or insert() in Laravel

Laravel provides powerful tools for database operations, including the methods upsert() and insert(). Both are designed to handle bulk operations, but their use cases differ significantly. In this blog, we’ll explore when to use upsert() or insert() in Laravel, complete with examples, scenarios, and best practices.

What is upsert() in Laravel?

The upsert() method was introduced in Laravel 8.x to allow a combination of insert and update operations in a single query. It works as follows:

• If the record doesn’t exist, it inserts the record.
• If the record exists (based on a unique constraint), it updates the specified columns.

Syntax:

DB::table('table_name')->upsert($values, $uniqueBy, $updateColumns);

$values: An array of data to be inserted or updated.

$uniqueBy: Columns that define a unique constraint (e.g., id or email).

$updateColumns: Columns to update if a record already exists.

What is insert() in Laravel?

The insert() method allows you to add new records to a database. It is a simple, straightforward operation that inserts the provided data into the specified table. Unlike upsert(), it doesn’t handle conflicts or update existing records.

Syntax:

DB::table('table_name')->insert($values);

$values: An array of data to be inserted.

Key Differences Between upsert() and insert()

When to Use upsert()

1. Avoid Duplicate Records

Use upsert() when you need to insert new records but avoid duplicating existing ones. For instance, if you’re syncing data from an external source, you can rely on upsert() to manage duplicates.

Example:

DB::table('users')->upsert(
    [
        ['id' => 1, 'name' => 'John Doe', 'email' => 'john@example.com'],
        ['id' => 2, 'name' => 'Jane Smith', 'email' => 'jane@example.com']
    ],
    ['id'], // Unique constraint
    ['name', 'email'] // Columns to update if a conflict occurs
);

Here:

• If id = 1 exists, the name and email will be updated.
• If id = 2 doesn’t exist, a new record will be inserted.

2. Bulk Insert with Updates

When working with large datasets, some records may already exist in the database. Instead of checking each record individually, use upsert() to handle the insert and update operations in one query.

Scenario:

Updating product inventory while adding new products.

Example:

DB::table('products')->upsert(
    [
        ['sku' => 'A123', 'name' => 'Product A', 'stock' => 50],
        ['sku' => 'B456', 'name' => 'Product B', 'stock' => 30]
    ],
    ['sku'], // Unique constraint
    ['stock'] // Columns to update
);

3. Conflict Resolution

If your table has unique constraints (e.g., on email or sku), upsert() resolves conflicts automatically by updating the conflicting records.

When to Use insert()

1. No Conflicts Expected

Use insert() when you’re certain that the records you’re adding do not already exist in the database. For instance, adding new user accounts to a system.

Example:

DB::table('users')->insert([
    ['name' => 'Alice Johnson', 'email' => 'alice@example.com'],
    ['name' => 'Bob Brown', 'email' => 'bob@example.com']
]);

2. Insert-Only Operations

If you want to ensure that only new records are added, and you don’t care about updating existing records, insert() is the appropriate method.

Scenario:

Logging events or tracking user activities.

Example:

DB::table('activity_logs')->insert([
    ['user_id' => 1, 'action' => 'login', 'timestamp' => now()],
    ['user_id' => 2, 'action' => 'logout', 'timestamp' => now()]
]);

3. Error on Conflict

If you want Laravel to throw an error when a unique constraint is violated (e.g., duplicate email), insert() ensures the operation stops without updating any records.

Best Practices for Using upsert() and insert()

Optimize Your Database:

• Add proper indexing to columns used in upsert() or insert() to speed up lookups and updates.

Validate Your Data:

• Always validate data before performing bulk operations to ensure data integrity.

Handle Large Datasets:

• For very large datasets, split the data into smaller chunks using array_chunk() to avoid memory overload.

Example:

$chunks = array_chunk($dataArray, 500);

foreach ($chunks as $chunk) {
    DB::table('users')->upsert($chunk, ['id'], ['name', 'email']);
}

Wrap Operations in Transactions:

• Use database transactions to ensure consistency, especially when performing multiple related operations.

Example:

DB::transaction(function () use ($dataArray) {
    DB::table('users')->upsert($dataArray, ['id'], ['name', 'email']);
    DB::table('logs')->insert(['action' => 'Bulk Upsert', 'timestamp' => now()]);
});

Conclusion

Choosing between upsert() and insert() in Laravel depends on your use case:

• Use upsert() for scenarios where you need to insert new records and update existing ones simultaneously, such as syncing data or managing inventories.
• Use insert() when you need to add new, unique records without handling conflicts or updates.

By leveraging the appropriate method, you can optimize your Laravel applications for performance and reliability.