Laravel Dsql Laravel Package

Getting Started

• Install with composer require brefphp/laravel-dsql and confirm the runtime has pdo_pgsql plus AWS credentials allowed to connect to Aurora DSQL (dsql:DbConnect).
• Add a dsql connection in config/database.php and set DB_CONNECTION=dsql. The package registers the DSQL driver and handles IAM auth token generation, so avoid hard-coding passwords.
• Look first at the package config (publish it if available) for retry attempts/backoff and connection options. Then inspect the registered connection class to understand how transactions are retried on DSQL optimistic-concurrency conflicts.
• First use case: run php artisan migrate against DSQL, create a small UUID-keyed model, and exercise a write inside DB::transaction(). Confirm a retry path by running two concurrent updates against the same row and observing the transaction retry rather than a failed request.

Implementation Patterns

• Use Laravel’s normal transaction APIs for writes, but assume the transaction callback can run more than once. Keep callbacks self-contained and avoid external side effects (emails, HTTP calls, file writes) inside them.
• For cache counters, use the database cache store with Cache::increment. As of 1.0.2, increments are re-run after a conflict, so counters are not silently lost, but still avoid ultra-hot single keys.
• For queued batches, use the database batch repository. 1.0.2 fixes job-count updates after conflicts, so Bus::batch() progress should remain accurate under retries.
• Prefer UUID primary keys generated in PHP (Str::uuid()) or DSQL-supported UUID defaults. Do not design around Postgres sequences/SERIAL.
• Treat DSQL as “Postgres-compatible with distributed constraints”: no foreign keys, no SELECT ... FOR UPDATE, no advisory locks, no long-held locks. Model relationships in application code and use optimistic concurrency.
• Keep transactions short and touch as few rows as possible. Contention on the same row or counter is the main source of retries.
• For long-running workers, ensure IAM auth tokens are refreshed. Test queue workers and Octane-style long-lived processes separately from short web requests.
• Integrate with Laravel’s database queue and cache drivers where practical, but avoid database-backed sessions under high write load unless you have measured conflict rates.

Gotchas and Tips

• Upgrade to 1.0.2 if you use database cache increments or job batches. In 1.0.1, retried transactions could skip re-running those operations, leading to undercounted cache values or batch job counts.
• DSQL conflicts surface as serialization/optimistic-concurrency errors, typically SQLSTATE 40001. Log retries and monitor their frequency; a retry storm usually means hot rows or overly broad transactions.
• Retries re-execute the whole transaction callback. Never assume the callback body runs exactly once.
• DSQL does not support foreign keys, sequences/SERIAL, SELECT FOR UPDATE, advisory locks, or LOCK TABLE. Migrations copied from standard Postgres often need rewriting.
• Schema changes can be restricted or asynchronous. Run DDL separately from application traffic and check DSQL’s index/DDL limitations before relying on standard Laravel migration defaults.
• If counters must be exact under heavy contention, avoid a single database row as the bottleneck; shard counters or move aggregation out of the request path.
• Debugging tip: wrap suspicious writes in a transaction, enable query logging, and look for 40001 retries. If a value is missing after a conflict, check whether the operation is one of the patched paths in 1.0.2 or a custom side effect outside the retried callback.
• Extension points: publish and tune the package config for retry limits/backoff, and keep custom connection subclasses small. If you override transaction behavior, preserve the package’s conflict-retry semantics.