High-Performance Bulk Data Syncing: Mastering Eloquent upsert()

While importing data from external APIs, importing large CSV files, or even while updating inventories on thousands of products, the developer often faces a situation when there is a need to do an "insert or update" operation.

The easiest way to do this is to use a loop through records and use the updateOrCreate() function from Laravel. Although updateOrCreate() serves well for only a few records, the same within a loop on 10,000 records will generate 10,000 to 20,000 queries, which will make your database work very slow and even may cause script timeouts.

In order to overcome such a severe performance issue, Laravel offers a function called upsert(). In this article, we will learn what upsert() does, how it can bulk insert and update data with a single query, how it is different from updateOrCreate(), and some important notes.

The Performance Problem: The updateOrCreate() Loop Trap

Let's look at how developers commonly process batch imports:

// PERFORMANCE KILLER FOR LARGE DATASETS
foreach ($incomingProducts as $item) {
    Product::updateOrCreate(
        ['sku' => $item['sku']], // Query 1: SELECT * WHERE sku = ?
        [
            'name'  => $item['name'],
            'price' => $item['price'],
            'stock' => $item['stock'],
        ] // Query 2: INSERT or UPDATE
    );
}

For an import of 5,000 products, this snippet executes up to 10,000 round-trip database queries. Network latency and query overhead will cause this import to take several minutes, spiking database CPU usage.

The Solution: Eloquent's upsert() Method

Laravel's upsert() method delegates the upsert operation directly to the underlying database engine using native features like MySQL's INSERT ... ON DUPLICATE KEY UPDATE or PostgreSQL's INSERT ... ON CONFLICT DO UPDATE.

Instead of executing thousands of queries, upsert() processes thousands of records in a single SQL statement.

Basic Syntax of upsert()

The upsert() method accepts three arguments:

Model::upsert(
    array $values,          // 1. Array of records to insert/update
    array $uniqueBy,        // 2. Columns that uniquely identify a record
    array $update = null    // 3. Columns that should be updated if record exists
);

Step-by-Step Implementation

Let's walk through an example where we sync product inventory from an external supplier API.

1. Ensure a Unique Index Exists in Your Migration

For upsert() to identify whether a row already exists, the target table must have a primary key or a unique index on the column(s) specified in the second argument ($uniqueBy).

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

return new class extends Migration
{
    public function up(): void
    {
        Schema::create('products', function (Blueprint $table) {
            $table->id();
            $table->string('sku')->unique(); // Unique index required for upsert
            $table->string('name');
            $table->decimal('price', 10, 2);
            $table->integer('stock');
            $table->timestamps();
        });
    }

    public function down(): void
    {
        Schema::dropIfExists('products');
    }
};

2. Running the upsert() Operation

Now, we can pass our entire payload array into Product::upsert():

namespace App\Services;

use App\Models\Product;

class ProductSyncService
{
    public function sync(array $incomingData): void
    {
        // Example batch array of thousands of products
        $products = [
            ['sku' => 'TECH-001', 'name' => 'Mechanical Keyboard', 'price' => 79.99, 'stock' => 50],
            ['sku' => 'TECH-002', 'name' => 'Wireless Mouse',      'price' => 29.99, 'stock' => 120],
            ['sku' => 'TECH-003', 'name' => 'USB-C Monitor Cable', 'price' => 14.50, 'stock' => 300],
        ];

        Product::upsert(
            $products,
            ['sku'],                     // Unique column to identify existing rows
            ['name', 'price', 'stock']   // Columns to update when SKU already exists
        );
    }
}

Under the hood, MySQL executes a single atomic query:

INSERT INTO `products` (`sku`, `name`, `price`, `stock`) 
VALUES 
    ('TECH-001', 'Mechanical Keyboard', 79.99, 50),
    ('TECH-002', 'Wireless Mouse', 29.99, 120),
    ('TECH-003', 'USB-C Monitor Cable', 14.50, 300)
ON DUPLICATE KEY UPDATE 
    `name` = VALUES(`name`),
    `price` = VALUES(`price`),
    `stock` = VALUES(`stock`);

This single statement completes in a few milliseconds, replacing thousands of back-and-forth round trips.

Composite Unique Keys Example

Real-world schemas often determine uniqueness using a combination of multiple columns. For example, a product_variants table might identify a unique variant using both product_id and sku, or a student's semester grade using [student_id, course_id].

1. Composite Unique Migration

Schema::create('course_enrollments', function (Blueprint $table) {
    $table->id();
    $table->foreignId('student_id')->constrained();
    $table->foreignId('course_id')->constrained();
    $table->string('grade')->nullable();
    $table->integer('attendance_percentage')->default(0);
    $table->timestamps();

    // Composite unique index
    $table->unique(['student_id', 'course_id']);
});

2. Upserting with Composite Keys

Pass both composite columns into the second argument:

use App\Models\CourseEnrollment;

CourseEnrollment::upsert(
    [
        ['student_id' => 10, 'course_id' => 101, 'grade' => 'A', 'attendance_percentage' => 95],
        ['student_id' => 12, 'course_id' => 101, 'grade' => 'B+', 'attendance_percentage' => 88],
        ['student_id' => 15, 'course_id' => 102, 'grade' => 'A-', 'attendance_percentage' => 92],
    ],
    ['student_id', 'course_id'], // Matched against composite unique index
    ['grade', 'attendance_percentage']
);

Handling Timestamps with upsert()

Since upsert() is run at the database query level and not hydration of the individual Eloquent model, the Laravel framework cannot automatically handle the created_at and updated_at timestamps unless the user specifies them.

To keep timestamps accurate, add them manually before passing data to upsert():

$now = now();

$records = collect($incomingData)->map(function ($item) use ($now) {
    return [
        'sku'        => $item['sku'],
        'name'       => $item['name'],
        'price'      => $item['price'],
        'created_at' => $now,
        'updated_at' => $now,
    ];
})->toArray();

Product::upsert(
    $records,
    ['sku'],
    ['name', 'price', 'updated_at'] // Update updated_at on existing rows
);

Notice that created_at is in the initial payload (used only when new records are inserted), while only updated_at is included in the third parameter so original creation dates are preserved.

upsert() vs updateOrCreate() Comparison

Feature	upsert()	updateOrCreate()
Query Count	1 single SQL query for the entire batch.	1–2 queries per record ($N \times 2$).
Model Events	❌ Does NOT trigger (`saving`, `saved`, etc.).	✅ Triggers all model events and observers.
Mutators & Casts	❌ Bypasses model mutators/casts.	✅ Applies model mutators and casts.
Database Requirement	Requires an explicit `UNIQUE` or primary index.	Can match on any standard column.
Best For	High-volume imports (100 to 100,000+ rows).	Single record operations with business logic.

Important Things to Remember

📌 Uniform Array Keys: Every record inside the first array must contain the exact same set of keys. If one record has a key that another record lacks, PDO will throw an SQL syntax error.

⚠️ Model Events Do Not Fire: Because upsert() is executed directly on the database engine, Eloquent model observers, events (like creating or updating), and traits (like auto-slug generation) will not run. Prepare all values in PHP before passing them to upsert().

⚡ Chunking Massive Batches: While upsert() is fast, passing 100,000 records in a single query can hit database parameter limits (e.g. MySQL's max_allowed_packet). Use array_chunk() to process very large datasets in batches of 1,000 to 2,000 rows:
```
foreach (array_chunk($hugeDataset, 1500) as $batch) {
    Product::upsert($batch, ['sku'], ['name', 'price', 'stock']);
}
```

Conclusion

The Laravel upsert() function is one of the tools that prove to be very important when working with high-speed database manipulation. With the use of native database mass upsert instead of using slow loop iterations, it is possible to import and synchronize huge amounts of data within seconds.

Thank you for reading this article 😊

For any query, do not hesitate to comment 💬