Bulk Operations

Perform efficient bulk insert, update, upsert, and delete operations on large datasets.

Bulk Methods Summary

Bulk Insert

Insert multiple records in a single query:

use Miko\Database\ORM\BulkOperations;

$users = [
    ['Name' => 'John Doe', 'Email' => 'john@example.com', 'Role' => 'user'],
    ['Name' => 'Jane Doe', 'Email' => 'jane@example.com', 'Role' => 'user'],
    ['Name' => 'Bob Smith', 'Email' => 'bob@example.com', 'Role' => 'admin'],
];

$inserted = BulkOperations::bulkInsert(User::class, $users);
echo "Inserted $inserted records";

With Batch Size

// Insert in batches of 500
$inserted = BulkOperations::bulkInsert(User::class, $largeDataset, 500);

Ignore Duplicates

// Skip records that would cause duplicate key errors
$inserted = BulkOperations::bulkInsert(User::class, $users, 1000, true);

Bulk Update

Update multiple records based on conditions:

// Update all matching records
$updated = BulkOperations::bulkUpdate(
    User::class,
    ['IsActive' => true, 'UpdatedAt' => date('Y-m-d H:i:s')],
    ['Role' => 'user']  // WHERE conditions
);

echo "Updated $updated records";

Update by IDs

// Update specific records by ID
$updated = BulkOperations::bulkUpdateByIds(
    User::class,
    [1, 2, 3, 4, 5],
    ['IsVerified' => true]
);

Bulk Upsert

Insert new records or update existing ones:

$products = [
    ['SKU' => 'PROD-001', 'Name' => 'Product 1', 'Price' => 29.99, 'Stock' => 100],
    ['SKU' => 'PROD-002', 'Name' => 'Product 2', 'Price' => 49.99, 'Stock' => 50],
    ['SKU' => 'PROD-003', 'Name' => 'Product 3', 'Price' => 19.99, 'Stock' => 200],
];

$affected = BulkOperations::bulkUpsert(
    Product::class,
    $products,
    ['SKU'],                    // Unique key columns
    ['Name', 'Price', 'Stock']  // Columns to update on conflict
);

Bulk Delete

Delete multiple records efficiently:

// Delete by IDs
$deleted = BulkOperations::bulkDelete(User::class, [1, 2, 3, 4, 5]);

// Delete by conditions
$deleted = BulkOperations::bulkDeleteWhere(
    User::class,
    ['IsActive' => false, 'Role' => 'guest']
);

// Delete older than date
$deleted = BulkOperations::bulkDeleteWhere(
    Session::class,
    ['ExpiredAt' => ['<', date('Y-m-d H:i:s')]]
);

Using with Model

class User extends Model
{
    // Bulk insert
    public static function bulkCreate(array $records): int
    {
        return BulkOperations::bulkInsert(static::class, $records);
    }
    
    // Bulk update
    public static function bulkActivate(array $ids): int
    {
        return BulkOperations::bulkUpdateByIds(static::class, $ids, ['IsActive' => true]);
    }
}

// Usage
User::bulkCreate($newUsers);
User::bulkActivate([1, 2, 3, 4, 5]);

Performance Comparison

// SLOW: Individual inserts (1000 queries)
foreach ($users as $userData) {
    User::create($userData);
}

// FAST: Bulk insert (1 query)
BulkOperations::bulkInsert(User::class, $users);

// Performance: ~10-50x faster for large datasets

Practical Examples

Import CSV Data

function importUsers(string $csvPath): int
{
    $handle = fopen($csvPath, 'r');
    $header = fgetcsv($handle);
    
    $users = [];
    while (($row = fgetcsv($handle)) !== false) {
        $users[] = array_combine($header, $row);
        
        // Insert in batches of 1000
        if (count($users) >= 1000) {
            BulkOperations::bulkInsert(User::class, $users);
            $users = [];
        }
    }
    
    // Insert remaining
    if (!empty($users)) {
        BulkOperations::bulkInsert(User::class, $users);
    }
    
    fclose($handle);
    return $totalInserted;
}

Sync External Data

function syncProducts(array $externalProducts): void
{
    // Upsert all products
    BulkOperations::bulkUpsert(
        Product::class,
        $externalProducts,
        ['ExternalId'],           // Match by external ID
        ['Name', 'Price', 'Stock', 'UpdatedAt']  // Update these fields
    );
}

Cleanup Old Records

function cleanupOldSessions(): int
{
    return BulkOperations::bulkDeleteWhere(
        Session::class,
        ['ExpiredAt' => ['<', date('Y-m-d H:i:s', strtotime('-30 days'))]]
    );
}

Best Practices

// With transaction
Transaction::run(function() use ($records) {
    BulkOperations::bulkInsert(User::class, $records, 1000);
});