Connecting with PDO

PDO is PHP's database abstraction layer for MySQL/MariaDB, PostgreSQL, SQLite and more. Configure it to throw exceptions (the default since PHP 8.0), return associative arrays, and use real (not emulated) prepared statements where supported. Read credentials from environment variables, never from code. In PHP-FPM, each worker keeps its own connection per request; use a pooler like PgBouncer or ProxySQL at scale rather than persistent connections. PHP 8.4 adds driver-specific subclasses (Pdo\Mysql, Pdo\Pgsql) via PDO::connect().

<?php
function createPdo(): PDO
{
    $dsn = sprintf('pgsql:host=%s;port=%d;dbname=%s', getenv('DB_HOST'), (int) getenv('DB_PORT'), getenv('DB_NAME'));

    return new PDO($dsn, getenv('DB_USER'), getenv('DB_PASSWORD'), [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        PDO::ATTR_EMULATE_PREPARES => false,
    ]);
}

// MySQL: always set the charset in the DSN
// 'mysql:host=db;dbname=shop;charset=utf8mb4'

Common pitfalls

• Using utf8 instead of utf8mb4 with MySQL (no emoji / 4-byte characters).
• Echoing PDOException messages to users — they can contain hostnames or credentials.
• Opening a new connection per query instead of reusing one per request.

Key points

• ERRMODE_EXCEPTION and FETCH_ASSOC
• Credentials from environment
• utf8mb4 for MySQL

Further reading: PHP manual: PDO connections