PHP
Efficiently Insert or Update Records with Eloquent Upsert
Leverage Laravel Eloquent's `upsert` method to atomically insert new records or update existing ones based on a unique key, significantly reducing database operations and improving performance for bulk data handling.
<?php
namespace App\Models;
use Illuminate\Database\Eloquent\Model;
class Product extends Model
{
protected $fillable = ['sku', 'name', 'price', 'stock'];
// Assuming 'sku' is a unique identifier in the database table
}
// --- Usage ---
// Define the data to upsert
$productsToUpsert = [
['sku' => 'PROD001', 'name' => 'Laptop Pro', 'price' => 1200.00, 'stock' => 50],
['sku' => 'PROD002', 'name' => 'Wireless Mouse', 'price' => 25.00, 'stock' => 200],
// This product exists, so it will be updated
['sku' => 'PROD001', 'name' => 'Laptop Pro Max', 'price' => 1250.00, 'stock' => 45] // Updated name, price, stock
];
// Perform the upsert operation
// 1. Array of records to insert/update
// 2. Columns that should be used to uniquely identify records (e.g., primary key, unique index)
// 3. Columns that should be updated if a matching record is found
App\Models\Product::upsert(
$productsToUpsert,
['sku'], // Unique by 'sku'
['name', 'price', 'stock'] // Update these columns if 'sku' matches
);
echo "Upsert operation completed.
";
// Verify the changes
$product1 = App\Models\Product::where('sku', 'PROD001')->first();
$product2 = App\Models\Product::where('sku', 'PROD002')->first();
echo "
Product PROD001: " . $product1->name . ", Price: " . $product1->price . ", Stock: " . $product1->stock . "
";
echo "Product PROD002: " . $product2->name . ", Price: " . $product2->price . ", Stock: " . $product2->stock . "
";
// Example of upserting a single record (can also be done with an array containing one record)
App\Models\Product::upsert(
[['sku' => 'PROD003', 'name' => 'Mechanical Keyboard', 'price' => 99.99, 'stock' => 100]],
['sku'],
['name', 'price', 'stock']
);
echo "
Single record upserted (PROD003).
";
$product3 = App\Models\Product::where('sku', 'PROD003')->first();
echo "Product PROD003: " . $product3->name . ", Price: " . $product3->price . ", Stock: " . $product3->stock . "
";
How it works: The `upsert` method in Eloquent provides a highly efficient way to insert records that do not exist or update records that do, all in a single database query. It takes three arguments: an array of values to insert or update, an array of columns that should be used to determine uniqueness (e.g., `['sku']`), and an array of columns that should be updated if a matching record is found. This is particularly useful for bulk operations like synchronizing data, importing files, or handling concurrent requests, as it prevents race conditions and significantly reduces the number of database roundtrips compared to separate `firstOrCreate` or `updateOrCreate` calls.