sfaut/masquerade

Batch MySQL/MariaDB upserts (INSERT ... ON DUPLICATE KEY UPDATE) with automatic chunking and primary-key protection, via PDO.

v1.0.0 2026-08-30 13:15 UTC

Requires

• php: ^8.1
• ext-pdo: *
• ext-pdo_mysql: *

Requires (Dev)

• pestphp/pest: ^5.1

MIT

• Sébastien

• mysql
• pdo
• batch
• mariadb
• upsert
• bulk-insert

Published on Packagist.org on 2026-08-30 13:26 UTC

README

A small, dependency-free PHP library for writing large amounts of data to a MySQL/MariaDB database efficiently and safely, without hand-rolling batching, placeholder generation, or conflict handling every time you need to import or synchronize data.

PSR-4 root namespace sfaut\Masquerade\, no dependency on any framework or application code -- usable as a standalone package. Currently contains Batch\Upsert.

Why

Importing or synchronizing bulk data (a product catalog, a CSV export, a paginated API feed, a nightly sync job) usually means writing the same boilerplate every time: chunk the records so a single query doesn't blow up, build a multi-row INSERT, decide what happens when a record already exists (skip it? overwrite it? merge it?), and make sure a primary key never gets clobbered by accident. Batch\Upsert packages that boilerplate into a single class with sensible, hard-to-misuse defaults.

Batch\Upsert

Inserts records in batches, with automatic update on key conflict (INSERT ... AS new ON DUPLICATE KEY UPDATE ...). A single class covers both use cases : a batch that never hits a conflict behaves like a plain insert, so there's no need for a separate "pure insert" class and another "upsert" class.

What the class guarantees on its own

• The table's primary key is never rewritten -- provided the table actually has one. At construction, Upsert queries information_schema.KEY_COLUMN_USAGE to find the PK columns, and excludes them from the UPDATE clause. Without that, a conflict detected via a secondary UNIQUE index (not the PK) would update the existing record found through that index -- and if the PK were part of the SET, its value would be silently overwritten with the inserted record's, potentially breaking foreign keys pointing at the old value. Result: nothing to configure, the class protects itself. (Constructing an Upsert therefore immediately fires 1 query -- it isn't a class to instantiate "just in case", let alone use.)

  Upsert never requires a primary key to run -- nothing throws either way. It just behaves differently depending on what the table actually has :

  • With a PRIMARY KEY : the guarantee above applies. This is the intended, safe use case.

  • Without one : the guarantee becomes meaningless, silently.

    • No key at all (no PK, no UNIQUE index) : every add() behaves as a plain INSERT -- conflicts are never detected, and duplicates accumulate freely.

    • A UNIQUE index but no PK : conflicts via that index do trigger an UPDATE, but with no PK left to exclude, every column -- including whatever should have been protected -- ends up in the SET.

• Real binding, no concatenation. Values go through PDO placeholders (?), never through a hand-escaped value inserted into the SQL string. null is handled natively by PDO. Identifiers (schema, table, column names) are quoted with backticks, doubling any backtick they contain.

• Prepared statement cached, re-prepared only if the batch size changes (so at most 2 times over the lifetime of an instance : once for full batches, once for the last, partial, batch) -- no thousands of useless prepare() calls on a large import.

• No column list to supply. Columns are determined automatically from the keys of the very first record added. Every subsequent record must carry at least the same keys (any extra key is ignored), otherwise a RuntimeException is thrown for the missing one -- rather than a silently misaligned record.

Requirements

• PHP 8.1+ (constructor property promotion, readonly properties).

• MySQL or MariaDB, via a PDO connection.

• No primary key is required to run -- but the target table needs a real PRIMARY KEY (not just a UNIQUE index) for the primary-key protection described above to actually protect anything ; see "What the class guarantees on its own".

add() vs addAll()

• add(array $record): void -- adds one record, triggers an automatic flush() once chunk is reached. A final manual flush() is still needed at the end for the remainder.

• addAll(iterable $records): void -- adds a whole collection (array or Generator) at once, and flushes the remainder on its own at the end. No call to flush() is needed after addAll().

All the examples below import the same thing -- a shop.product catalog (sku primary key, name, price_cents) -- from simplest to most involved, so only what actually changes between them stands out.

Example -- a first import, record by record

The simplest possible use : add records one by one, then flush the (here, single) batch by hand.

<?php

declare(strict_types=1);

require_once '/path/to/vendor/autoload.php';

use sfaut\Masquerade\Batch\Upsert;

$upsert = new Upsert($pdo, schema: 'shop', table: 'product');

$upsert->add(['sku' => 'WIDGET-1', 'name' => 'Widget', 'price_cents' => 1_999]);
$upsert->add(['sku' => 'GADGET-1', 'name' => 'Gadget', 'price_cents' => 2_999]);

$upsert->flush(); // Don't forget the remainder after the last add()

Running this again with a changed price updates the matching product (on its unique sku) in place instead of duplicating it.

Example -- importing a whole catalog at once

addAll() is a shortcut over add() for a full collection : it flushes the remainder on its own, no trailing flush() needed. $catalog_feed here is an already-loaded array of raw records straight from a CSV export, reshaped to the column names product expects :

$upsert = new Upsert($pdo, schema: 'shop', table: 'product');

$upsert->addAll(array_map(
    fn (array $product) => [
        'sku' => $product['sku'],
        'name' => $product['title'],
        'price_cents' => $product['price_cents'],
    ],
    $catalog_feed,
));

Example -- large catalog, custom batch size

By default chunk is 5,000 records ; an automatic flush() triggers on its own as soon as a batch is full, no need to think about it -- raise it for a bigger catalog. Mind the server's max_allowed_packet though : a chunk too high, combined with wide records, can build a single multi-row INSERT that exceeds it -- lower chunk if that happens :

$upsert = new Upsert($pdo, 'shop', 'product', chunk: 20_000);

$upsert->addAll($large_catalog_feed); // Automatic flush() every 20,000, plus the remainder

Example -- streamed source (Generator), without loading everything into memory

Upsert never keeps more than one batch in memory at a time, so it combines well with a source that isn't an already fully loaded array -- for instance importing the catalog page by page from a paginated supplier API :

function fetch_products(Client $api): Generator
{
    $page = 1;

    do {
        $response = $api->get('/products', ['page' => $page++]);

        foreach ($response['products'] as $product) {
            yield [
                'sku' => $product['sku'],
                'name' => $product['title'],
                'price_cents' => $product['price_cents'],
            ];
        }
    } while ($response['has_more']);
}

$upsert = new Upsert($pdo, 'shop', 'product');

$upsert->addAll(fetch_products($api));

echo "Done.";

Example -- record-by-record control

add() remains available directly for when something else needs to happen between two additions (addAll() is just a shortcut built on top of it) -- here, logging progress on each product. Unlike addAll(), a manual flush() is still needed at the end :

$upsert = new Upsert($pdo, 'shop', 'product');

foreach ($catalog_feed as $product) {
    log_progress($product);

    $upsert->add([
        'sku' => $product['sku'],
        'name' => $product['title'],
        'price_cents' => $product['price_cents'],
    ]);
}

$upsert->flush(); // Remainder

Limitations

• MySQL/MariaDB only (relies on INSERT ... ON DUPLICATE KEY UPDATE and information_schema.KEY_COLUMN_USAGE) ; no PostgreSQL/SQLite support.

• A table without a PRIMARY KEY gets no protection : a conflict via a UNIQUE index (if any) overwrites every column, unprotected -- see "What the class guarantees on its own".

• All records passed to a given instance must carry at least the columns of the first record added (any extra key is silently ignored).

• No check against the server's max_allowed_packet : a chunk too large, combined with wide records, can build an INSERT that exceeds it and fails.