Connecting with PDO

A fresh environment fails before your SQL even runs: someone drops in new PDO('mysql:dbname=shop', 'app', 'secret'), the deploy dies, and the log shows an exception pointing at the constructor line. Three inputs decide whether a PDO connection succeeds — the DSN, the credentials, and the options array — and each has exactly one correct home.

$pdo = new PDO(
    'mysql:host=127.0.0.1;port=3306;dbname=shop;charset=utf8mb4',
    getenv('DB_USER'),
    getenv('DB_PASS'),
    [
        PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        PDO::ATTR_EMULATE_PREPARES   => false,
        PDO::ATTR_TIMEOUT            => 5,
    ]
);

The DSN is one string: driver name, colon, then key=value pairs separated by semicolons. Each driver reads its own keys:

Credentials are the second and third constructor arguments, never part of the DSN — that separation is deliberate, because the DSN appears in error messages and logs while the password does not. The options array is fourth, and putting it there means no query can run against an unconfigured connection: setting attributes afterwards leaves a window where a fast query runs with PDO's defaults.

charset=utf8mb4 earns its place in the DSN. It issues the equivalent of SET NAMES utf8mb4, which decides what the server treats as one character. Leave it out and the server's default applies (latin1 on plenty of older installs), producing failures like SQLSTATE[HY000] [1366] Incorrect string value: '\xF0\x9F\x98\x80' for column 'bio' the first time someone posts an emoji — and quietly mis-sizing any manual escaping that assumes the wrong encoding. The same value belongs in every environment; building it is a sprintf away:

$dsn = sprintf(
    'mysql:host=%s;port=%d;dbname=%s;charset=utf8mb4',
    getenv('DB_HOST') ?: '127.0.0.1',
    (int) (getenv('DB_PORT') ?: 3306),
    getenv('DB_NAME') ?: 'shop'
);

There is no separate connect() call to get wrong: the constructor parses the DSN, loads the driver, opens the socket, authenticates, and applies your options in one shot. An object that came back means all four steps succeeded, and a failure arrives carrying the driver's own message about which step did not.

Failures surface early and specifically. With ERRMODE_EXCEPTION active the constructor itself throws, so new PDO(...) belongs inside the try/catch, not just the queries after it — and the message tells you which layer broke: [1045] Access denied for user 'app'@'localhost' (using password: YES) is a credential problem, [1049] Unknown database 'shop' is a DSN or provisioning problem, [2002] php_network_getaddresses: getaddrinfo for db failed is DNS, [1040] Too many connections is capacity. Learning to read the driver code before touching anything is half the job.

You also owe the project a reason for choosing PDO at all, because "it is popular" dies in review. The honest comparison:

That portability is why composer.json should declare what you use — "require": {"ext-pdo_mysql": "*"} — following the same discipline you applied to packages in Version Constraints and Stability: a missing extension discovered at runtime is a failed deploy, and Composer can catch it at install time instead.

Once the handle exists, the next question is what PDO does when a statement fails — which is a configuration decision you make before the first query.

You did not configure an error mode, so PDO used its default: PDO::ERRMODE_SILENT. Now walk through what that actually costs you.

$stmt = $pdo->query('SELECT total FROM orders WHERE id = 999'); // table was renamed
$rows = $stmt->fetchAll();

In SILENT mode query() returns false and nothing else happens — no exception, no warning, no indication beyond a return value your code never checks. The next line then dies with Error: Call to a member function fetchAll() on bool — a fatal error two files away from the actual cause. Your log says the problem is fetchAll; the truth is a typo in the table name.

The three modes, and what the failing call does:

So the single line that fixes the class of bug is the one already in the constructor:

PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION

Do it at construction rather than with a later setAttribute(). An error mode configured after the connection is a window in which a failed query is silently discarded, and it only takes one early query to lose the data you were about to write.

Exceptions turn error handling into one decision instead of dozens. PDOException carries three layers of detail: getMessage() gives SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'a@b.com' for key 'users_email' in /var/www/app/src/Registration.php:84 — SQLSTATE, driver message, and the exact file and line; getCode() gives the SQLSTATE (HY000 for most driver-level failures); errorInfo() gives the array [SQLSTATE, driver code, driver message], which is what you branch on:

try {
    $pdo->insertUser($email);
} catch (PDOException $e) {
    if (($e->errorInfo[1] ?? null) === '1062') {
        throw new EmailAlreadyRegistered($email); // business rule, not a crash
    }
    throw $e; // everything else keeps its stack trace
}

That distinction — a duplicate key is a 409 response, a lost connection is a 500 and maybe a retry — is what interviewers mean when they ask how you handle database errors. The shape of the answer: exceptions for control flow you expect, one central handler at the front controller for the rest, and never leak SQL text to the client. Catching at the call site buys you context (which operation failed); catching at the boundary buys you a consistent log line and a generic error page.

Two cautions come with the mode. Exceptions do not retry anything — a dead connection throws on every subsequent query until you reconnect — and they do not replace transactions: an exception mid-write leaves the transaction open until something rolls it back, which is exactly the concern Transactions and Concurrency takes up after the CRUD work. The mode also changes what your tests report: with exceptions on, a broken fixture surfaces as a PDOException with the failing statement in its message, instead of an expectation quietly comparing false to false. Finally, remember that ATTR_EMULATE_PREPARES is a separate constructor option with its own default that most teams discover the hard way; it is on by default for pdo_mysql, and the difference only matters once you start sending data through the connection.

A correctly configured handle now behaves predictably when things go wrong. The remaining question is how many of those handles your server is holding at once.

PHP does not run a long-lived application process, and every misconception about PDO connections starts by ignoring that. Under PHP-FPM each request is handled by a worker, and a connection opened during that request is closed when the script ends — unless you asked for persistence. Two costs follow, and they pull in opposite directions.

First, connection overhead. A handshake is TCP plus authentication, often 1–3 ms inside a datacenter — noticeable next to a 0.5 ms indexed query. That produces the classic mistake: opening a connection inside a helper that each query calls.

// wrong: N queries → N handshakes in one request
foreach ($orders as $o) { $pdo = connect(); $pdo->updateStock($o); }

// right: one connection per request, created once
$pdo = connect(); // a factory that reads the DSN from the environment
foreach ($orders as $o) { $pdo->updateStock($o); }

One handle per request is the norm: persistence is per process, so a worker that serves 500 requests opens its connection once if you keep the handle in one place.

Second, capacity. PDO has no cross-worker pooling — every FPM worker holds its own socket. Run 200 workers against MySQL's default max_connections of 151 and the database starts refusing new work with SQLSTATE[HY000] [1040] Too many connections, usually at the exact moment traffic rises. The arithmetic (workers × connections per worker versus server limit) is the first thing to check, and the fixes are operational: bound the worker count, connect once per request, or put ProxySQL, RDS Proxy, or PgBouncer in front so pooling belongs to something designed for it.

That is the honest frame for persistent connections. Setting PDO::ATTR_PERSISTENT => true (the driver's p: connection string) keeps the socket open in the worker after the request ends, so the next request that worker serves skips the handshake. What you save, you also inherit:

$pdo = new PDO($dsn, $user, $pass, [
    PDO::ATTR_PERSISTENT => true,
    PDO::ATTR_ERRMODE    => PDO::ERRMODE_EXCEPTION,
]);

So persistence is justified only where the handshake genuinely dominates and your code holds no session state — and even then you defend it with a cheap revalidation:

try {
    $pdo->query('SELECT 1');
} catch (PDOException $e) {
    if (in_array($e->errorInfo[1] ?? null, ['2006', '2013'], true)) {
        // stale handle: rebuild the connection once and retry
    }
}

The same lifecycle thinking explains deploy-time blips: restarting every worker at once produces a synchronized wave of new handshakes against a database that was not expecting them. The mitigation order teams actually follow — cap pm.max_children so the wave is bounded, stagger worker restarts on a rolling deploy, set a short connect timeout so a saturated database fails fast instead of queueing requests, and retry the connect with backoff, because connecting again is idempotent while replaying an arbitrary write is not. p: is absent from that list on purpose: it changes which requests pay the handshake, never how many the database must accept.

You now have a handle that connects deliberately, fails loudly, and is accounted for in capacity terms. Everything from here flows through it: Prepared Statements and Binding is how data gets into a query without becoming SQL.