Aurora Dsql Pdo Pgsql Laravel Package

Getting Started

Prerequisites

• PHP 8.1+ with the pdo_pgsql extension (php -m | grep pdo_pgsql).
• An Aurora DSQL cluster. aws dsql create-cluster --region us-east-1, then take the endpoint from the output — it looks like abc123def456.dsql.us-east-1.on.aws.
• AWS credentials resolvable by the default provider chain (env vars locally, task/instance role in ECS/EC2, execution role in Lambda). DSQL auth is IAM, not password-based; there is no static DB password to put in .env.
• composer require awslabs/aurora-dsql-pdo-pgsql.

Look at these first, in this order

1. README.md — confirm the exact namespace and constructor signature. This package is young (2 stars, 0 dependents), so do not assume the API shape from memory; copy the example verbatim once before adapting it.
2. composer.json — check the pinned aws/aws-sdk-php range. That constraint is the main thing that will bite you on an existing app with its own SDK requirement.
3. The tests directory — the token-generation path is the only part that is non-obvious. Everything else is stock PDO/pgsql. Reading the test that builds a connection tells you more than the README about how credentials and SSL are resolved.
4. src/ — if the package is a thin wrapper (it likely is: sign a SigV4 presigned URL, set it as the password, force sslmode=require, hand off to PDO), decide early whether you want the dependency at all versus ~30 lines against Aws\DSQL\DSQLClient directly.

First use case: prove auth before you prove the app

Do not start by wiring this into config/database.php. Start with the smallest possible script that connects, runs SELECT 1, and reads now():

$pdo = new PDO(
    "pgsql:host={$endpoint};port=5432;dbname=postgres;sslmode=require",
    'admin',
    $token, // generated immediately before this line
    [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]
);

If that works, every later problem is a Laravel wiring problem, not a DSQL problem. If it fails, isolate with the CLI before touching PHP:

aws dsql generate-db-connect-admin-auth-token \
  --hostname abc123.dsql.us-east-1.on.aws --region us-east-1
PGPASSWORD="$(...)" psql "host=... user=admin dbname=postgres sslmode=require"

Once the psql round-trip is green, move to Laravel. Laravel's stock pgsql driver works fine; the only thing you need to inject is a freshly-minted token as the password. The cleanest fit is a custom connector rather than a config hack:

// app/Database/DsqlConnector.php
class DsqlConnector extends PostgresConnector
{
    public function connect(array $config)
    {
        $config['password'] = app(DsqlTokenProvider::class)->token($config['host']);
        return parent::connect($config);
    }
}

// AppServiceProvider::boot()
DB::extend('dsql', fn () => new DsqlConnector());

Then 'driver' => 'dsql' in config/database.php, with host, database, username, and sslmode => 'require'. Keeping PostgresConnector as the parent means query grammar, schema builder, and Eloquent all keep working — you are only changing how the password is obtained.

Implementation Patterns

Generate the token at connect time, never at boot time. This is the single most important pattern. Tokens are short-lived (on the order of 15 minutes). Anything that resolves a token during container boot, config caching, or a singleton that outlives a connection will break in production and work fine in php artisan tinker. Wrap token generation in a DsqlTokenProvider service that signs on every connect() call, and take the signing cost as given — it is a local HMAC, not a network call.

class DsqlTokenProvider
{
    public function token(string $host, string $user = 'admin'): string
    {
        $client = new DSQLClient(['region' => config('services.dsql.region'), 'version' => 'latest']);

        return $user === 'admin'
            ? $client->generateDbConnectAdminAuthToken(['Host' => $host, 'ExpiresIn' => 900])
            : $client->generateDbConnectAuthToken(['Host' => $host, 'ExpiresIn' => 900]);
    }
}

If you are opening many connections per second, cache the token in Cache::remember() keyed on host:user:region with a TTL of ~10 minutes (always shorter than ExpiresIn, and account for clock skew). Measure before adding this — signing is cheap, and a stale-cache bug is worse than the CPU you save.

Treat every write transaction as retryable. DSQL uses optimistic concurrency control; conflicting transactions abort with SQLSTATE 40001 rather than blocking. Laravel's DB::transaction($callback, $attempts) already retries on concurrency errors for Postgres, so this often works out of the box:

DB::transaction(function () use ($order) {
    // keep it short, keep it free of side effects
}, attempts: 5);

The rule that follows: never put an external side effect inside a transaction (no queued job dispatch without afterCommit, no HTTP call, no S3 write). A retried transaction will run that side effect twice. DB::afterCommit() is not optional here, it is load-bearing.

Schema design: no sequences, no foreign keys. Laravel's $table->id() emits bigserial, which will fail. Use UUIDs:

$table->uuid('id')->primary()->default(DB::raw('gen_random_uuid()'));

Replace ->foreignId()->constrained() with a plain ->uuid('order_id')->index() and enforce referential integrity in the application layer or via a periodic reconciliation job. ->timestamps(), ->string(), ->json(), indexes, and most of the schema builder are fine. Before writing a migration, run it against a real cluster — a local Postgres will happily accept DDL that DSQL rejects, so a passing local test suite proves nothing about schema compatibility.

Do not put hot single-row tables on DSQL. Laravel's database session driver, database cache driver, database queue driver, and Cache::lock() all funnel contention onto a small number of rows. On an OCC database that means a retry storm under any real concurrency. Use Redis (or DynamoDB) for sessions, cache, and queues, and reserve DSQL for the relational workload it is actually good at. SELECT ... FOR UPDATE semantics are also not a safe assumption — verify before you build anything on lockForUpdate().

Read/write split and connection hygiene. Keep PDO::ATTR_PERSISTENT off. A persistent connection survives across requests, and while the token is only needed at connect time, a reused connection can be one the cluster has already reaped — you get opaque "server closed the connection unexpectedly" errors rather than a clean reconnect. Laravel's DB::purge() and DB::reconnect() are your tools for long-running workers; wrap queue job execution so a dead connection triggers a reconnect instead of failing the job.

Testing strategy. There is no local emulator. Two tiers: unit tests against plain Postgres (fast, but permissive — it will not catch DSQL incompatibilities), and a CI job that creates a throwaway DSQL cluster, runs the migration suite plus a smoke test, and tears it down. The second tier is the one that matters, and it is the reason to keep the schema surface small.

Gotchas and Tips

Auth failures immediately after provisioning are usually propagation, not code. Newly created clusters, IAM roles, and database roles take time to become usable. If psql with a freshly generated CLI token fails right after create-cluster, wait and retry before debugging your PHP.

Clock skew breaks SigV4. Presigned tokens are rejected if the signer's clock is more than a few minutes off. Containers and CI runners are the usual culprits. If auth works locally and fails in a container, check date -u before anything else.

sslmode=require is not negotiable. If you find yourself adding sslmode=disable to make something work, you are debugging the wrong layer.

Prepared statements are worth an explicit test. PDO_PGSQL issues server-side PREPAREs when emulation is off. With connection multiplexing in front of the database, named prepared statements can outlive their session or be routed to a different backend. If you see intermittent "prepared statement does not exist" errors, PDO::ATTR_EMULATE_PREPARES => true is the escape hatch — but confirm against your actual deployment topology rather than cargo-culting it.

lastInsertId() will return nothing useful. No sequences means no last-insert ID. Every insert that needs its generated key must use RETURNING, which Eloquent does by default for ->create() on a UUID default — but raw DB::insert() calls will silently give you 0 or ''. Grep for insertGetId and lastInsertId in the codebase before migrating.

Watch transaction size and duration. DSQL caps transaction size and lifetime. Bulk imports and DB::table(...)->insert($tenThousandRows) need chunking. A chunk()-based seeder is not premature optimization here; it is a correctness requirement.

Unsupported features to check before you commit. Foreign keys, sequences/SERIAL, LISTEN/NOTIFY, advisory locks, temporary tables, and most extensions are unavailable or restricted. gen_random_uuid() is built in and fine. Anything you reach for out of Postgres habit — pg_cron, dblink, pg_trgm — verify against the actual cluster.

Error codes are your debugging interface. Log $e->getCode() (the SQLSTATE) alongside the message. 40001 means retry, 28P01/28000 means token or role problem, 08006 means the connection was reaped. Three codes cover 90% of incidents, and the message text alone will not distinguish them reliably.

Extension points worth planning for. Three seams carry most of the value: the token provider (swap in Secrets Manager or a sidecar-issued token without touching the connector), the connector (add statement logging, query timing, or a SET on connect), and a transaction-retry wrapper (as middleware, a base repository class, or a DB::transaction helper with a jittered backoff). Build these as your own classes rather than subclassing package internals — with 2 stars and no dependents, the internals will move.

Vendor risk is real and should shape the design. awslabs is where AWS publishes experimental work, and a package with this little adoption has no implicit support contract. Pin the exact version in composer.json, keep the DSQL-specific code confined to the connector and token provider, and confirm that falling back to Aws\DSQL\DSQLClient plus stock PDO is a one-file change. If the package turns out to be a thin wrapper, that fallback may be worth taking preemptively — it removes a dependency and leaves you on the same SDK you already ship.