pimbay-php/sequence-number-pdo

PDO implementation of the Atomic UPSERT Engine for the PimBay Number Sequence Stack (MySQL, MariaDB, PostgreSQL, SQLite).

v1.0.0 2026-05-18 03:30 UTC

Requires

• php: >=8.3
• ext-pdo: *
• pimbay-php/sequence: ^1.0

Requires (Dev)

• ext-pcntl: *
• ext-shmop: *
• friendsofphp/php-cs-fixer: ^3.95
• phpbench/phpbench: ^1.6
• phpstan/phpstan: ^2.0
• phpunit/phpunit: ^11.0
• pimbay/sequence-number-sql: ^1.0

Suggests

• ext-pcntl: Required for parallel concurrency tests
• ext-shmop: Required for parallel concurrency tests

Unlicense

• Jan Sarmir

• mysql
• sqlite
• postgresql
• pdo
• sequence
• mariadb
• atomic
• upsert

README

The pimbay-php/sequence-number-pdo package provides the core PDO-based engine for atomic, persistent number sequence generation and schema management. It supports MySQL, MariaDB, PostgreSQL, and SQLite, ensuring reliable and collision-free sequence operations in high-concurrency environments.

This package serves as the backbone for database-driven number sequencing within the PimBay Sequence Stack, offering a flexible and extensible architecture through its AdapterInterface.

Why not AUTO_INCREMENT?

AUTO_INCREMENT and PostgreSQL SEQUENCE give you a unique number — but they don't give you control over grouping, naming, or initial values.

sequence-number-pdo lets you maintain multiple independent sequences in a single table:

$sequence->nextNumber('invoice', '2026');     // → 1, 2, 3 ...
$sequence->nextNumber('invoice', '2025');     // → independent sequence
$sequence->nextNumber('order', 'proforma');   // → another independent sequence

Each sequence is identified by a (group, name) pair and increments atomically — safe for concurrent requests without application-level locking.

Key Features

• Atomic UPSERT Engine: Implements native SQL UPSERT logic (ON DUPLICATE KEY UPDATE for MySQL/MariaDB, ON CONFLICT for PostgreSQL) for robust, collision-free sequence increments.
• MariaDB RETURNING Adapter: Dedicated MariadbAdapter using native RETURNING clause (MariaDB 10.5+) — cleaner and faster than the MySQL approach.
• Pessimistic Locking: SelectForUpdateAdapter and SqliteAdapter for scenarios requiring explicit transaction-based locking with retry logic.
• Extensible Adapter Architecture: AdapterInterface allows easy integration of custom or third-party database adapters (e.g. MSSQL via pimbay-php/sequence-number-pdo-mssql).
• DriverDetectingAdapter: Automatically selects the appropriate adapter at runtime based on the PDO driver name. Useful for libraries that don't know the database upfront. Throws PdoSequenceException for unsupported drivers — no silent fallback.
• Schema Management: SchemaManager provides database-specific CREATE TABLE SQL for MySQL, MariaDB, PostgreSQL and SQLite, verified 1:1 against pimbay/sequence-number-sql.
• SQL Constants: Every adapter exposes its SQL as a public const — verifiable against the canonical pimbay/sequence-number-sql repository.
• Consistent Error Handling: Wraps underlying PDO exceptions into PdoSequenceException for unified error management.
• Reference Benchmarks: Includes comprehensive benchmarks across MySQL, MariaDB, PostgreSQL, and SQLite — serves as the performance baseline for the entire PimBay Sequence Stack.

Installation

composer require pimbay-php/sequence-number-pdo

Usage

NumberSequence

The NumberSequence is the main entry point for interacting with sequences. You inject a AdapterInterface implementation into its constructor.

<?php

declare(strict_types=1);

use PimBay\Sequence\Number\Pdo\NumberSequence;
use PimBay\Sequence\Number\Pdo\Adapter\MysqlAdapter;
use PimBay\Sequence\Number\Pdo\SchemaManager;

// Assume $pdo is an initialized PDO connection (e.g., MySQL)
$pdo = new PDO('mysql:host=localhost;dbname=test', 'user', 'password');
$tableName = 'my_sequences';

// Ensure the sequence table exists
$schemaManager = new SchemaManager($pdo);
$schemaManager->createTable($tableName);

// Create an adapter for MySQL
$mysqlAdapter = new MysqlAdapter($pdo, $tableName);

// Initialize the number sequence service
$numberSequence = new NumberSequence($mysqlAdapter);

// Get the next number for a group and name
$nextInvoiceNumber = $numberSequence->nextNumber('invoice', '2024', 1000, ['department' => 'sales']);
echo "Next Invoice Number: " . $nextInvoiceNumber . "\n"; // → 1000

$nextInvoiceNumber = $numberSequence->nextNumber('invoice', '2024');
echo "Next Invoice Number: " . $nextInvoiceNumber . "\n"; // → 1001

// Get the current value without incrementing
$currentOrder = $numberSequence->getCurrent('invoice', '2024');
echo "Current Invoice Value: " . $currentOrder->currentValue . "\n"; // → 1001

DriverDetectingAdapter (Automatic Adapter Selection)

For libraries that don't know the database upfront, DriverDetectingAdapter automatically selects the correct adapter based on the PDO driver name.

<?php

declare(strict_types=1);

use PimBay\Sequence\Number\Pdo\NumberSequence;
use PimBay\Sequence\Number\Pdo\Adapter\DriverDetectingAdapter;
use PimBay\Sequence\Number\Pdo\SchemaManager;

$pdo = new PDO('pgsql:host=localhost;dbname=test', 'user', 'password');
$tableName = 'my_sequences';

$schemaManager = new SchemaManager($pdo);
$schemaManager->createTable($tableName);

// Automatically picks PostgresAdapter for pgsql driver
$adapter = new DriverDetectingAdapter($pdo, $tableName);
$numberSequence = new NumberSequence($adapter);

$nextNumber = $numberSequence->nextNumber('report', 'daily', 1);
echo "Next Report Number: " . $nextNumber . "\n";

MariaDB and DriverDetectingAdapter

PDO reports mysql as the driver name for both MySQL and MariaDB connections — there is no way to distinguish them without an extra query. For this reason, DriverDetectingAdapter always resolves to MysqlAdapter on MariaDB.

To use the optimized MariadbAdapter (which uses the native RETURNING clause available since MariaDB 10.5+), inject it explicitly:

use PimBay\Sequence\Number\Pdo\Adapter\MariadbAdapter;

$adapter = new MariadbAdapter($pdo, $tableName);
$sequence = new NumberSequence($adapter);

SchemaManager

The SchemaManager class helps in setting up the necessary database tables.

<?php

declare(strict_types=1);

use PimBay\Sequence\Number\Pdo\SchemaManager;

$pdo = new PDO('mysql:host=localhost;dbname=test', 'user', 'password');
$schemaManager = new SchemaManager($pdo);

// Create the default sequence table
$schemaManager->createTable();

// Create a custom-named sequence table
$schemaManager->createTable('my_custom_sequences');

Performance Benchmarks

Benchmarks were run across PHP 8.3, 8.4, and 8.5 with MySQL 5.7, MySQL 8.0, MariaDB 10, MariaDB 11, PostgreSQL 15, and PostgreSQL 16 — OPcache enabled, Xdebug off. Each DB + adapter combination runs in an isolated container with a restart between runs to eliminate cache effects.

• PHP 8.5 delivers 4–5× lower latency for MariaDB and PostgreSQL compared to PHP 8.3/8.4 — JIT improvements reduce overhead from ~2ms to ~430–520μs.
• MariaDB and PostgreSQL on PHP 8.5 — ~430–520μs Native adapter median. On PHP 8.3/8.4 — ~2ms.
• Native adapter outperforms SelectForUpdate by 25–35% on PHP 8.5, 15–20% on PHP 8.3/8.4.
• MySQL 8.0 — ~1.6ms on PHP 8.3 and 8.5 (InnoDB durability overhead dominates). PHP 8.4 shows anomalous ~8.5ms in Docker — MySQL 5.7, MariaDB, PostgreSQL unaffected.
• MySQL 5.7 — ~600μs on PHP 8.5, ~3.8ms on PHP 8.3/8.4.
• MariaDB Native (RETURNING) vs MySQL Native (LAST_INSERT_ID) — no measurable difference in Docker environment; network round-trip dominates.

For production use, PHP 8.5 + MariaDB or PostgreSQL with the Native adapter is the recommended combination.

Use Case	Recommended
Highest throughput	PHP 8.5 + MariaDB 11/10 or PostgreSQL 16/15 + Native adapter (~430–520μs)
Best consistency	PHP 8.5 + PostgreSQL 15 Native — lowest variance

Full benchmark results and interactive charts

SQL Reference

All SQL used by this library is documented and versioned in the canonical SQL repository:

pimbay/sequence-number-sql

Each adapter exposes its SQL as a public constant for testing and verification:

MysqlAdapter::UPSERT_SQL
PostgresAdapter::UPSERT_SQL
SelectForUpdateAdapter::SELECT_SQL
SelectForUpdateAdapter::INSERT_SQL
SelectForUpdateAdapter::UPDATE_SQL

Test Matrix

PHP	MySQL 8.0	MySQL 5.7	MariaDB 11	MariaDB 10	PostgreSQL 16	PostgreSQL 15	SQLite
8.3	✅	✅	✅	✅	✅	✅	✅
8.4	✅	✅	✅	✅	✅	✅	✅
8.5	✅	✅	✅	✅	✅	✅	✅

Contribution

Contributions are welcome! Please ensure that your code adheres to the existing coding standards and all tests pass. Open an issue or submit a pull request for any new features or bug fixes.

License

Public domain — Unlicense

Created by Jan Sarmir · No conditions · No copyright