pimbay/sequence-number-sql

Centralized SQL snippets for atomic number sequence operations (UPSERT, pessimistic locking) and table schema definitions across MySQL, MariaDB, PostgreSQL, MSSQL, SQLite, Oracle and IBM DB2.

v1.0.0 2026-05-15 07:28 UTC

Unlicense

• Jan Sarmir

• database
• sql
• mysql
• sqlite
• postgresql
• mssql
• oracle
• locking
• sequence
• mariadb
• snippets
• db2
• upsert

README

Centralized SQL snippets for atomic number sequence operations across MySQL, MariaDB, PostgreSQL, MSSQL, SQLite, Oracle and IBM DB2. Part of the PimBay Sequence Stack.

Purpose

Decouples SQL from PHP adapter code. PDO adapters load snippets via file_get_contents(), replace the {table} placeholder, bind ? positional parameters and execute. No PHP code — SQL and documentation only.

Installation

composer require pimbay/sequence-number-sql

SQL Snippets

UPSERT (sql/upsert/)

Atomic insert-or-increment in a single statement. Available for databases that support returning the new value without a follow-up query.

File	Database	Strategy
mysql.sql	MySQL	ON DUPLICATE KEY UPDATE + LAST_INSERT_ID()
mariadb.sql	MariaDB 10.3+	ON DUPLICATE KEY UPDATE + LAST_INSERT_ID()
mariadb-returning.sql	MariaDB 10.5+	ON DUPLICATE KEY UPDATE RETURNING
postgres.sql	PostgreSQL	ON CONFLICT DO UPDATE RETURNING
mssql.sql	MSSQL	MERGE WITH (HOLDLOCK) OUTPUT

Oracle and IBM DB2 do not support atomic UPSERT with single-statement value retrieval — use pessimistic locking instead.

Pessimistic locking (sql/pessimistic-locking/)

Transaction-based locking. Required for SQLite, Oracle and IBM DB2. Available as an alternative for all other databases.

File	Databases	Notes
select_for_update.sql	MySQL, MariaDB, PostgreSQL, Oracle	Standard FOR UPDATE
select_for_update_db2.sql	IBM DB2	FOR UPDATE WITH RS — explicit isolation level
select_with_lock.sql	MSSQL	WITH (UPDLOCK, ROWLOCK)
select.sql	SQLite	Plain SELECT — file-level lock provides isolation
insert.sql	All	INSERT when sequence does not exist
update.sql	All	UPDATE existing sequence — sets updated_at explicitly

Schema (schema/)

CREATE TABLE definitions for each database.

File	Database
mysql.sql	MySQL
mariadb.sql	MariaDB
postgres.sql	PostgreSQL
sqlite.sql	SQLite
mssql.sql	MSSQL
oracle.sql	Oracle
db2.sql	IBM DB2

Usage

$sql = file_get_contents(__DIR__ . '/vendor/pimbay/sequence-number-sql/sql/upsert/mysql.sql');
$sql = str_replace('{table}', $tableName, $sql);
$stmt = $pdo->prepare($sql);
$stmt->execute([$group, $name, $initialValue, $metadata]);

The {table} placeholder must always be replaced before prepare(). Never pass table names from user input.

Documentation

• Schema — column types and database-specific strategy overview
• UPSERT — MySQL
• UPSERT — MariaDB
• UPSERT — PostgreSQL
• UPSERT — MSSQL
• Pessimistic Locking — SQLite, Oracle, IBM DB2 and fallback for all databases

License

Public domain — Unlicense

Created by Jan Sarmir · No conditions · No copyright