Connecting to a Database with PDO

A web application almost always stores its data in a database: accounts, products, orders… In PHP, the modern and standard way to access it is PDO (PHP Data Objects). PDO offers the same API whatever the database management system (DBMS): MySQL/MariaDB, PostgreSQL, SQLite, SQL Server… Each DBMS is handled by a driver (pdo_mysql, pdo_pgsql, pdo_sqlite). You can check which ones are installed with php -m.

The DSN: the database’s address

You create a connection by instantiating the PDO class with a DSN (Data Source Name), then optionally a user, a password and options.

// In-memory SQLite: ideal for learning and for tests
$pdo = new PDO('sqlite::memory:');

// SQLite in a file
$pdo = new PDO('sqlite:' . __DIR__ . '/app.sqlite');

// MySQL / MariaDB: always specify charset=utf8mb4
$pdo = new PDO('mysql:host=127.0.0.1;port=3306;dbname=boutique;charset=utf8mb4', 'app', 'secret');

// PostgreSQL
$pdo = new PDO('pgsql:host=127.0.0.1;port=5432;dbname=boutique', 'app', 'secret');

The essential options

$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION, // an SQL error throws an exception
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,       // rows as associative arrays
    PDO::ATTR_EMULATE_PREPARES   => false,                  // real prepared statements (MySQL)
];
$pdo = new PDO($dsn, $user, $password, $options);

Since PHP 8.0, the default error mode is already ERRMODE_EXCEPTION, but writing it explicitly makes the code readable and robust. Without exceptions, a faulty query would simply return false, and the error would go unnoticed.

Handling a connection failure

try {
    $pdo = new PDO($dsn, getenv('DB_USER'), getenv('DB_PASSWORD'), $options);
} catch (PDOException $e) {
    error_log($e->getMessage());          // we log the details
    http_response_code(500);
    exit('Service temporarily unavailable.'); // neutral message for the user
}

The message of a PDOException can reveal the host or database name: you log it, you don’t display it. Likewise, the password must never appear in versioned code: read it from an environment variable (getenv(), $_ENV) or a .env file ignored by Git.

Running a first statement

For a statement with no parameter and no result (creating a table, for example), exec() is enough. It returns the number of affected rows.

$pdo->exec('CREATE TABLE produits (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    nom TEXT NOT NULL,
    prix REAL NOT NULL
)');

Good to know: since PHP 8.4, PDO::connect($dsn, ...) returns a driver-specific subclass (Pdo\Sqlite, Pdo\Mysql…). new PDO(...) remains valid and is what you will encounter most often. PHP 8.5, however, deprecates driver-specific constants declared on PDO (for example PDO::SQLITE_ATTR_OPEN_FLAGS): use those of the subclass (Pdo\Sqlite::ATTR_OPEN_FLAGS, Pdo\Mysql::ATTR_...).

In summary: a correct DSN, explicit options, credentials outside the code and discreet error handling. This is the foundation of the rest of the course.