mpyw/unique-violation-detector
Detects and normalizes unique constraint violation errors across MySQL, PostgreSQL, and SQLite. Inspects PDO exceptions and SQLSTATE codes so your PHP/Laravel app can reliably catch duplicate-key errors and handle retries or upserts without brittle driver-specific checks.
composer require mpyw/unique-violation-detector
This is a micro-package: it answers one question — "is this PDOException a duplicate-key/unique-constraint error, or something else?" — across MySQL, PostgreSQL, SQLite, and SQL Server. There is no service provider, no config file, and no facade in the core package, so nothing is auto-discovered and nothing boots. Read src/ in full before you write any code: it's a handful of classes (DetectorInterface, a Detector, and per-driver detectors for MySQL/Postgres/SQLite/SQLServer), and confirming the exact constructor signature takes two minutes. The 2-star/dependent-free status means the README is the only documentation you get — but it also means the surface area you're depending on is trivially small.
Where to look first: the per-driver detector classes. They encode the error codes you'd otherwise be looking up per database (MySQL 1062, PostgreSQL 23505, SQL Server 2601/2627, SQLite message text). That mapping is the value of the package.
First use case — a genuinely idempotent create:
use Mpyw\UniqueViolationDetector\Detector;
$pdo = DB::connection()->getPdo();
$detector = new Detector($pdo); // driver is read from the PDO instance
try {
DB::table('users')->insert([
'email' => $email,
'name' => $name,
]);
} catch (QueryException $e) {
if ($detector->isUniqueViolation($e->getPrevious())) {
// Someone else won the race — treat it as success, not as a 500.
return User::where('email', $email)->firstOrFail();
}
throw $e;
}
Note the $e->getPrevious(): Laravel's QueryException wraps the PDOException, and the detector operates on the underlying PDO exception, not the wrapper.
firstOrCreatefirstOrCreate is not atomic — it's a SELECT followed by an INSERT, and under concurrency two requests can both pass the SELECT. The correct pattern is: put a real unique index in the database, then insert optimistically and resolve the collision.
final class RegisterUser
{
public function __construct(private Detector $detector) {}
public function __invoke(string $email, string $name): User
{
try {
return User::create(['email' => $email, 'name' => $name]);
} catch (QueryException $e) {
if (! $this->detector->isUniqueViolation($e->getPrevious())) {
throw $e;
}
return User::where('email', $email)->firstOrFail();
}
}
}
Bind the detector as a singleton, per connection, in a service provider:
$this->app->singleton(Detector::class, fn () => new Detector(DB::connection()->getPdo()));
The classic "generate a slug, append -2, -3…" loop is much cheaper when you let the unique index be the arbiter rather than doing a SELECT per candidate:
public function createWithSlug(string $title): Post
{
$base = Str::slug($title);
for ($i = 0; $i < 5; $i++) {
$slug = $i === 0 ? $base : "{$base}-" . ($i + 1);
try {
return Post::create(['title' => $title, 'slug' => $slug]);
} catch (QueryException $e) {
if (! $this->detector->isUniqueViolation($e->getPrevious())) {
throw $e;
}
}
}
throw new RuntimeException("Unable to allocate a slug for [{$base}].");
}
insertOrIgnore() and upsert() silently swallow every integrity error, including foreign-key violations and NOT NULL problems — you lose real bugs. Use the detector to keep the distinction:
DB::transaction(function () use ($rows) {
foreach (array_chunk($rows, 500) as $chunk) {
try {
DB::table('orders')->insert($chunk);
} catch (QueryException $e) {
if (! $this->detector->isUniqueViolation($e->getPrevious())) {
throw $e; // FK / NOT NULL / check constraint — surface it
}
// Fall back to per-row insert so one duplicate doesn't sink 499 good rows
foreach ($chunk as $row) {
try {
DB::table('orders')->insert($row);
} catch (QueryException $inner) {
if (! $this->detector->isUniqueViolation($inner->getPrevious())) {
throw $inner;
}
}
}
}
}
}, 3);
In a worker, a duplicate insert is often transient (a competing job won). Convert it into a retry/release rather than a failed_jobs entry:
public function handle(Detector $detector): void
{
try {
Order::create($this->payload);
} catch (QueryException $e) {
if ($detector->isUniqueViolation($e->getPrevious())) {
$this->release(5);
return;
}
throw $e;
}
}
If you'd rather not thread a Detector through your actions, subclass the connection and expose the check where you catch exceptions. mpyw ships a separate mpyw/laravel-unique-violation-detector wrapper that provides a trait you attach to Illuminate\Database\Connection and that adds an isUniqueViolation(QueryException $e) helper. Either way, register the custom connection via Connection::resolverFor('mysql', ...) in a service provider. This is the cleanest option if you want the check available in exception handlers and middleware, not just in domain actions.
You don't need a real database to test your collision branches — construct a PDOException with the driver payload the detector expects:
$e = new PDOException('Duplicate entry');
$e->errorInfo = ['23000', 1062, 'Duplicate entry for key users_email_unique'];
$this->expectException(QueryException::class);
(new RegisterUser($detector))('[email protected]', 'Bob');
Pair this with one real integration test against each driver you actually ship (MySQL in CI, maybe Postgres too) — that's the test that catches a driver-detector mismatch.
QueryException vs PDOException. The detector takes a PDOException. Always pass $e->getPrevious(), and guard for null — if a custom PDO subclass or a wrapped driver doesn't chain the exception, you'll get a TypeError in your catch block, which is a nasty way to convert a 409 into a 500.
SQLSTATE 23000 is not enough. MySQL reports foreign-key violations, duplicate keys, and NOT NULL violations all as 23000. The whole point of this package is that it also inspects the driver error code (1062 vs 1452 vs 1048). If you're hand-rolling $e->getCode() === '23000', you're already misclassifying bugs as duplicates. Don't regress to that.
PostgreSQL poisons the transaction. In Postgres, once a statement fails, the transaction is aborted — catching the exception and continuing to issue statements on the same connection gets you current transaction is aborted. You must wrap the risky insert in a SAVEPOINT (Laravel's nested DB::transaction() calls create savepoints, and DB::transaction(..., $attempts) retries do too). MySQL/InnoDB does not behave this way, so code that works in dev on MySQL can blow up on Postgres — test the platform you deploy on.
ERRMODE must be exception. PDO must be in PDO::ERRMODE_EXCEPTION (Laravel's default) or you'll get false back instead of a throwable and none of this applies. If a package or a raw new PDO(...) flips the mode, detection silently never runs.
SQLite detection is string-based and therefore the weakest link. PDO's SQLite driver doesn't reliably surface the extended result code (SQLITE_CONSTRAINT_UNIQUE), so the detector ends up matching message text — which has shifted across SQLite versions and can be affected by the bundled library. If SQLite is production for you, add an integration test that actually inserts a duplicate, and consider treating SQLite's broader "UNIQUE constraint failed" text as the contract.
2601 vs 2627 on SQL Server. Both mean "you violated uniqueness" — one for unique indexes, one for unique constraints. Verify the SQL Server detector covers both; if not, extend it rather than editing your call sites.
Multi-connection apps need multiple detectors. The driver is bound at construction. With separate read/write connections, tenant databases, or a second connection to a different engine (MySQL app + Postgres analytics), build one detector per connection — a singleton keyed by nothing in particular is a subtle bug waiting to happen.
Proxies rewrite error codes. Vitess, ProxySQL, PgBouncer in odd modes, and cloud proxies such as RDS Proxy have been known to alter or obscure driver error codes. If detection mysteriously returns false in staging but true locally, log $e->getPrevious()->errorInfo on both sides and diff them before you blame the package.
Debug by dumping the payload. When the detector says "not a unique violation" and you're sure it is, log [$e->getCode(), $e->getPrevious()?->errorInfo, $e->getSql(), $e->getBindings()]. The errorInfo array is the ground truth the detector reads.
Extension point: implement the detector interface. If you add a driver (ClickHouse, a custom PDO wrapper, a test double), implement the package's detector interface with your own code mapping instead of forking. This is the intended seam and it's a single method.
The elephant in the room: 2 stars, 0 dependents. The upside is that the entire value proposition is a ~50-line lookup table with no runtime dependencies, no service provider, and no I/O overhead — the blast radius of it being abandoned is close to zero. The practical mitigation is to vendor it: copy the detector classes into app/Support/ behind a small interface, keep the MIT attribution, and add your own integration tests per driver. You get the domain knowledge (the error-code table, which is the genuinely annoying part to research) without a supply-chain line item. If you keep it as a dependency, pin it exactly and let a test — not the version constraint — be your compatibility guard.
Don't use it where the database can do the work. insertOrIgnore, upsert, and ON CONFLICT DO NOTHING are still the right answer when you genuinely don't care which row wins. Reach for exception-based control flow only when you need the result (fetch the existing row, retry with a new slug, distinguish duplicates from FK errors) or when you need to be portable across drivers with one code path.
How can I help you explore Laravel packages today?