sfaut / masquerade

v1.1.1 2026-09-13 18:23 UTC

Requires

• php: ^8.3
• 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-09-13 18:29 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, which generates its SQL through a vendor dialect resolved from the connection.

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.

    Should the records carry nothing but PK columns -- a junction table's, typically -- the UPDATE clause is left with nothing to assign : it is dropped, and the statement stays a plain INSERT, which fails on conflict. Provisional behaviour, meant to be replaced by a dedicated Batch\Insert carrying an explicit conflict policy.

  • 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.3+ (typed class constants, readonly properties).

• MySQL 8.0.19+ or MariaDB 10.3.3+, via a PDO connection. The dialect is deduced from the connection ; see "Dialects" below.

• 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

Dialects

The SQL itself belongs to a dialect, resolved from the connection at construction time. MySQL and MariaDB do not spell the conflict clause the same way -- MySQL names the inserted row through an alias, MariaDB through the VALUE() function, which it introduced precisely to disambiguate it from the VALUES table value constructor:

-- MySQL
INSERT INTO `shop`.`product` (`sku`, `name`) VALUES (?, ?)
AS new ON DUPLICATE KEY UPDATE `name` = new.`name`

-- MariaDB
INSERT INTO `shop`.`product` (`sku`, `name`) VALUES (?, ?)
ON DUPLICATE KEY UPDATE `name` = VALUE(`name`)

You normally have nothing to do about this. MariaDB announces itself under the mysql PDO driver name, so the two are told apart by the server version string, read from the connection handshake at no cost:

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

Pass one explicitly to force a vendor, or to skip the version introspection the deduction performs:

use sfaut\Masquerade\Dialects\MariaDb;

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

An unsupported driver, or a server older than its dialect's floor, raises a RuntimeException at construction rather than failing later on a syntax error.

See src/Dialects/README.md to add a vendor.

Limitations

• MySQL 8.0.19+ and MariaDB 10.3.3+ only (both rely on INSERT ... ON DUPLICATE KEY UPDATE and information_schema.KEY_COLUMN_USAGE) ; no PostgreSQL, DuckDB or SQLite support yet.

• 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.