php 29 lines · 6 steps

Bulk-inserting users with batched PDO upserts

Chunk a large array into batches and upsert each with a single prepared multi-row INSERT.

Explained by highlit
Intermediate batching bulk-insert prepared-statements upsert database
1public function importUsers(array $users): int
2{
3 $inserted = 0;
4 
5 foreach (array_chunk($users, 500) as $batch) {
6 $placeholders = [];
7 $values = [];
8 
9 foreach ($batch as $user) {
10 $placeholders[] = '(?, ?, ?)';
11 $values[] = $user['name'];
12 $values[] = $user['email'];
13 $values[] = $user['created_at'] ?? date('Y-m-d H:i:s');
14 }
15 
16 $sql = sprintf(
17 'INSERT INTO users (name, email, created_at) VALUES %s '
18 . 'ON DUPLICATE KEY UPDATE name = VALUES(name)',
19 implode(', ', $placeholders)
20 );
21 
22 $stmt = $this->pdo->prepare($sql);
23 $stmt->execute($values);
24 
25 $inserted += $stmt->rowCount();
26 }
27 
28 return $inserted;
29}
01 / 01
STEP 01

Walkthrough

Space play ←→ step click any line
Three takeaways
  1. 1Chunking huge datasets keeps each SQL statement within safe placeholder and packet limits.
  2. 2A single multi-row INSERT is far faster than one query per record.
  3. 3ON DUPLICATE KEY UPDATE turns an insert into an idempotent upsert against unique keys.

Related explainers

php 
<?php
 
final class MarkdownParser
{

Building a small Markdown-to-HTML parser in PHP

state-machine parsing closures
Intermediate 10 steps
ruby 
namespace :counter_cache do
  desc "Recalculate comments_count for posts after a backfill"
  task warm_post_comments: :environment do
    scope = Post.where(comments_count: nil).or(Post.where("comments_count < 0"))

Backfilling counter caches in a Rake task in Rails

counter-cache batching rake-tasks
Intermediate 6 steps
php 
<?php
 
namespace App\Console\Commands;
 

Releasing stale document locks in Laravel

artisan-command transactions row-locking
Intermediate 6 steps
php 
<?php
 
namespace App\Providers;
 

Subdomain multi-tenancy routing in Laravel

multi-tenancy service-container route-binding
Advanced 7 steps
php 
<?php
 
namespace App\Broadcasting;
 

Authorizing presence channels in Laravel

broadcasting authorization presence-channels
Intermediate 3 steps
php 
<?php
 
namespace App\Http\Middleware;
 

Resolving the current team in Laravel middleware

middleware multi-tenancy cookies
Intermediate 8 steps

Share this explainer

Here's the card — post it anywhere.

Bulk-inserting users with batched PDO upserts — share card
Made with highlit — turn any snippet into a walkthrough like this in about a minute.
Explain your code