PDO — PHP Data Objects, DSN strings, connection options

Intermediate5 min read·fw-06-001

sql

Concept

PDO (PHP Data Objects) is PHP's database abstraction layer. It provides a consistent API across different database engines (MySQL, PostgreSQL, SQLite, SQL Server) through driver-specific extensions.

DSN (Data Source Name): The connection string. Format varies by driver:

MySQL: mysql:host=localhost;port=3306;dbname=mydb;charset=utf8mb4
PostgreSQL: pgsql:host=localhost;port=5432;dbname=mydb
SQLite: sqlite:/path/to/database.db or sqlite::memory: for in-memory

PDO constructor: new PDO(string $dsn, ?string $username, ?string $password, array $options).

Key options (PDO::* constants):

PDO::ATTR_ERRMODE: Error reporting mode.
- PDO::ERRMODE_EXCEPTION: Throw PDOException on error (always use this).
- PDO::ERRMODE_SILENT: Silently fail (default, dangerous).
PDO::ATTR_DEFAULT_FETCH_MODE: PDO::FETCH_ASSOC (default to associative arrays), PDO::FETCH_OBJ, PDO::FETCH_CLASS.
PDO::ATTR_EMULATE_PREPARES: false = use native prepared statements (safer, more accurate types).
PDO::ATTR_PERSISTENT: Persistent connections (shared across requests — advanced, rarely needed).
PDO::MYSQL_ATTR_INIT_COMMAND: SQL to run after connection (e.g., SET NAMES utf8mb4).

Connection errors: If the connection fails, PDO throws PDOException regardless of ERRMODE. Always catch on connection.

Code Example

php

<?php
// Basic PDO connection
$dsn = 'mysql:host=127.0.0.1;port=3306;dbname=myapp;charset=utf8mb4';

$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION, // throw exceptions on errors
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,       // default to assoc arrays
    PDO::ATTR_EMULATE_PREPARES   => false,                   // use native prepared statements
    PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci",
];

try {
    $pdo = new PDO($dsn, 'root', 'secret', $options);
} catch (\PDOException $e) {
    // Wrap so the password isn't in the stack trace
    throw new \RuntimeException('Database connection failed: ' . $e->getMessage(), 0, $e);
}

// PostgreSQL
$pgDsn = 'pgsql:host=127.0.0.1;port=5432;dbname=myapp';
$pg    = new PDO($pgDsn, 'postgres', 'secret', [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,
]);

// SQLite (great for testing — no server needed)
$sqlite = new PDO('sqlite::memory:', null, null, [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
]);

// SQLite from file
$sqliteFile = new PDO('sqlite:' . __DIR__ . '/database.db');

// Verify connection works
$version = $pdo->query('SELECT VERSION()')->fetchColumn();
echo "Connected: MySQL {$version}\n";

PreviousMiddleware priority orderingMiddleware Pipeline from Scratch NextConnection class — wrapping PDO with a fluent APIDatabase Connection & PDO Wrappers