Driver-specific PDO classes

suggest change

Until PHP 8.4 every PDO connection was an instance of the same PDO class, whatever database it talked to. Features that only one driver supports were bolted onto that class with prefixed names: PDO::sqliteCreateFunction(), PDO::pgsqlCopyFromArray(), PDO::MYSQL_ATTR_INIT_COMMAND and so on. These methods existed on every PDO object but only worked for one driver, and IDEs and static analysers couldn't tell which ones were valid.

PHP 8.4 added a subclass of PDO for each bundled driver:

Class Driver DSN prefix
Pdo\Sqlite pdo_sqlite sqlite:
Pdo\Mysql pdo_mysql mysql:
Pdo\Pgsql pdo_pgsql pgsql:
Pdo\Firebird pdo_firebird firebird:
Pdo\Odbc pdo_odbc odbc:
Pdo\Dblib pdo_dblib dblib:, sybase:, mssql:

It also added a static factory, PDO::connect(), that looks at the DSN and returns an object of the right subclass. The driver-specific methods and constants now live on the subclasses under their plain names (Pdo\Sqlite::createFunction(), Pdo\Mysql::ATTR_INIT_COMMAND), and PHP 8.5 deprecated the old prefixed versions on PDO.

The examples were run with PHP 8.5 and SQLite. Each class exists only when its driver extension is loaded. The MySQL and PostgreSQL examples were not run because no database server was available, so they show no output.

For PDO basics see Basic PDO Connection and Retrieval, PDO connecting to MySQL/MariaDB server and PostgreSQL. The separate SQLite3 extension is covered in SQLite3.

PDO::connect() returns the subclass

new PDO(...) still works exactly as before and still returns a plain PDO object. To get a driver-specific object, call PDO::connect() with the same arguments:

<?php
$db = PDO::connect('sqlite::memory:');
echo get_class($db), "\n";
var_dump($db instanceof PDO);

$plain = new PDO('sqlite::memory:');
echo get_class($plain), "\n";
var_dump(method_exists($plain, 'createFunction'));

Output:

Pdo\Sqlite
bool(true)
PDO
bool(false)

PDO::connect() takes the same $dsn, $username, $password and $options parameters as the constructor. The result is still a PDO, so prepare(), query(), transactions and everything else work as usual. You also get the extra methods.

If you already know which database you are using, you can call the subclass directly, either with new or with its own connect():

$db = new Pdo\Sqlite('sqlite:/var/data/app.db');
$db = Pdo\Sqlite::connect('sqlite:/var/data/app.db');

The DSN must match the class. If it doesn't, you get an exception before any connection is attempted:

<?php
try {
    Pdo\Mysql::connect('sqlite::memory:');
} catch (PDOException $e) {
    echo $e->getMessage(), "\n";
}

Output:

Pdo\Mysql::connect() cannot be used for connecting to the "sqlite" driver, either call Pdo\Sqlite::connect() or PDO::connect() instead

Pdo\Sqlite

Pdo\Sqlite has the same functions you could already register through PDO::sqlite*(), plus a few that used to exist only in the SQLite3 extension.

createFunction()

createFunction(string $function_name, callable $callback, int $num_args = -1, int $flags = 0) makes a PHP callable available as an SQL function. Pass Pdo\Sqlite::DETERMINISTIC when the function always returns the same result for the same input. SQLite can then use it in indexes and optimise repeated calls.

<?php
$db = Pdo\Sqlite::connect('sqlite::memory:');
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$db->createFunction(
    'slugify',
    fn(string $s): string => trim(preg_replace('/[^a-z0-9]+/', '-', strtolower($s)), '-'),
    1,
    Pdo\Sqlite::DETERMINISTIC,
);

$db->exec("CREATE TABLE posts (title TEXT)");
$db->exec("INSERT INTO posts VALUES ('Hello, World!'), ('PHP 8.4 is out')");

foreach ($db->query("SELECT title, slugify(title) AS slug FROM posts") as $row) {
    echo "{$row['title']} => {$row['slug']}\n";
}

Output:

Hello, World! => hello-world
PHP 8.4 is out => php-8-4-is-out

createAggregate()

An aggregate function like SUM() or COUNT() needs two callbacks. The step callback is called for every row with the context (the value it returned last time, null on the first row), the row number and the column values. The finalize callback turns the context into the result:

<?php
$db = Pdo\Sqlite::connect('sqlite::memory:');
$db->exec("CREATE TABLE orders (customer TEXT, amount INTEGER)");
$db->exec("INSERT INTO orders VALUES ('ann', 30), ('bob', 10), ('ann', 50), ('bob', 20), ('ann', 40)");

// median(): step collects values, finalize computes the result
$db->createAggregate(
    'median',
    function (?array $ctx, int $rownum, $value): array {
        $ctx ??= [];
        $ctx[] = $value;
        return $ctx;
    },
    function (?array $ctx, int $rownum): float {
        sort($ctx);
        $n = count($ctx);
        return $n % 2 ? $ctx[intdiv($n, 2)] : ($ctx[$n / 2 - 1] + $ctx[$n / 2]) / 2;
    },
    1,
);

$sql = "SELECT customer, median(amount) AS med FROM orders GROUP BY customer";
foreach ($db->query($sql)->fetchAll(PDO::FETCH_KEY_PAIR) as $customer => $median) {
    echo "$customer: $median\n";
}

Output:

ann: 40
bob: 15

createCollation()

A collation decides how ORDER BY and comparisons sort text. The callback works like the one for usort(): it returns a negative number, zero or a positive number. strnatcmp() gives "natural" ordering, where img2 comes before img10:

<?php
$db = Pdo\Sqlite::connect('sqlite::memory:');
$db->createCollation('NATSORT', strnatcmp(...));

$db->exec("CREATE TABLE files (name TEXT)");
$db->exec("INSERT INTO files VALUES ('img12.png'), ('img10.png'), ('img2.png'), ('img1.png')");

$binary  = $db->query("SELECT name FROM files ORDER BY name")->fetchAll(PDO::FETCH_COLUMN);
$natural = $db->query("SELECT name FROM files ORDER BY name COLLATE NATSORT")->fetchAll(PDO::FETCH_COLUMN);
echo implode(' ', $binary), "\n";
echo implode(' ', $natural), "\n";

Output:

img1.png img10.png img12.png img2.png
img1.png img2.png img10.png img12.png

Don't name a collation NATURAL: that is an SQL keyword (NATURAL JOIN), and the query fails with a syntax error.

openBlob()

openBlob(string $table, string $column, int $rowid, ?string $dbname = "main", int $flags = Pdo\Sqlite::OPEN_READONLY) opens a BLOB column as a PHP stream, so you can read or write part of a large value without loading all of it into memory. This method is new in Pdo\Sqlite. Before, it was only available in the SQLite3 extension.

<?php
$db = Pdo\Sqlite::connect('sqlite::memory:');
$db->exec("CREATE TABLE images (id INTEGER PRIMARY KEY, data BLOB)");
// reserve 16 zero bytes; a blob can't change size through openBlob()
$db->exec("INSERT INTO images (id, data) VALUES (1, zeroblob(16))");

$w = $db->openBlob('images', 'data', 1, 'main', Pdo\Sqlite::OPEN_READWRITE);
fwrite($w, 'PNG-HEADER');
fclose($w);

$r = $db->openBlob('images', 'data', 1);   // read-only by default
fseek($r, 4);
var_dump(fread($r, 6));
echo bin2hex(stream_get_contents($r, -1, 0)), "\n";
fclose($r);

Output:

string(6) "HEADER"
504e472d484541444552000000000000

The stream can't grow or shrink the BLOB. To store a value of a different size, first reserve the space with zeroblob(n) in an INSERT or UPDATE.

loadExtension(string $name) is also new. It loads a compiled SQLite extension such as a spatial or full-text module. PHP must be built with extension loading enabled, and sqlite3.extension_dir must be set in php.ini.

Opening a database read-only

The open flags are now class constants. Pdo\Sqlite::ATTR_OPEN_FLAGS must be passed in the $options array, because the flags are used when the file is opened:

<?php
$rw = Pdo\Sqlite::connect('sqlite:app.db');
$rw->exec("CREATE TABLE IF NOT EXISTS notes (body TEXT)");
$rw->exec("INSERT INTO notes VALUES ('hello')");

$ro = Pdo\Sqlite::connect('sqlite:app.db', options: [
    Pdo\Sqlite::ATTR_OPEN_FLAGS => Pdo\Sqlite::OPEN_READONLY,
]);
echo $ro->query("SELECT count(*) FROM notes")->fetchColumn(), "\n";
try {
    $ro->exec("INSERT INTO notes VALUES ('nope')");
} catch (PDOException $e) {
    echo $e->getMessage(), "\n";
}

Output:

1
SQLSTATE[HY000]: General error: 8 attempt to write a readonly database

setAuthorizer() (PHP 8.5)

setAuthorizer(?callable $callback) installs a callback that SQLite calls while it compiles each statement. The callback gets an action code (from SQLite's C API, for example 20 for SQLITE_READ or 9 for SQLITE_DELETE) and up to four strings that describe the action. For SQLITE_READ those are the table and the column. It returns one of three values:

This is useful when you run SQL you don't fully trust, such as queries written by users in a reporting tool:

<?php
$db = Pdo\Sqlite::connect('sqlite::memory:');
$db->exec("CREATE TABLE users (name TEXT, password_hash TEXT)");
$db->exec("INSERT INTO users VALUES ('ann', 'x1'), ('bob', 'x2')");

// action codes come from SQLite: 20 is SQLITE_READ, 9 is SQLITE_DELETE
$db->setAuthorizer(function (int $action, ?string $a, ?string $b, ?string $db, ?string $trigger): int {
    if ($action === 20 && $a === 'users' && $b === 'password_hash') {
        return Pdo\Sqlite::IGNORE;   // column reads as NULL
    }
    if ($action === 9) {
        return Pdo\Sqlite::DENY;     // statement fails to prepare
    }
    return Pdo\Sqlite::OK;
});

foreach ($db->query("SELECT name, password_hash FROM users")->fetchAll() as $row) {
    echo $row['name'], ' ', var_export($row['password_hash'], true), "\n";
}

try {
    $db->exec("DELETE FROM users");
} catch (PDOException $e) {
    echo $e->getMessage(), "\n";
}

$db->setAuthorizer(null);   // remove the authorizer
echo $db->exec("DELETE FROM users"), " rows deleted\n";

Output:

ann NULL
bob NULL
SQLSTATE[HY000]: General error: 23 not authorized
2 rows deleted

Transaction mode and EXPLAIN (PHP 8.5)

PHP 8.5 added more SQLite attributes. They exist only as Pdo\Sqlite constants, with no PDO::SQLITE_* version:

<?php
$db = Pdo\Sqlite::connect('sqlite::memory:', options: [
    Pdo\Sqlite::ATTR_TRANSACTION_MODE => Pdo\Sqlite::TRANSACTION_MODE_IMMEDIATE,
]);
var_dump($db->getAttribute(Pdo\Sqlite::ATTR_TRANSACTION_MODE) === Pdo\Sqlite::TRANSACTION_MODE_IMMEDIATE);

$db->exec("CREATE TABLE t (id INTEGER PRIMARY KEY, v TEXT)");
$db->exec("CREATE INDEX t_v ON t (v)");

$stmt = $db->prepare("SELECT id FROM t WHERE v = ?");
$stmt->setAttribute(Pdo\Sqlite::ATTR_EXPLAIN_STATEMENT, Pdo\Sqlite::EXPLAIN_MODE_EXPLAIN_QUERY_PLAN);
$stmt->execute(['x']);
foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $row) {
    echo $row['detail'], "\n";
}
var_dump($stmt->getAttribute(Pdo\Sqlite::ATTR_READONLY_STATEMENT));

Output:

bool(true)
SEARCH t USING COVERING INDEX t_v (v=?)
bool(true)

Transactions themselves are covered in Database Transactions with PDO.

Pdo\Pgsql

Pdo\Pgsql gets the pgsql* methods under their plain names. It also has escapeIdentifier() and setNoticeCallback(), which PDO never had:

Method Replaces
copyFromArray($table, $rows, $separator, $nullAs, $fields) PDO::pgsqlCopyFromArray()
copyFromFile($table, $filename, ...) PDO::pgsqlCopyFromFile()
copyToArray($table, ...) PDO::pgsqlCopyToArray()
copyToFile($table, $filename, ...) PDO::pgsqlCopyToFile()
getNotify($fetchMode = PDO::FETCH_DEFAULT, $timeoutMilliseconds = 0) PDO::pgsqlGetNotify()
getPid() PDO::pgsqlGetPid()
lobCreate(), lobOpen($oid, $mode), lobUnlink($oid) PDO::pgsqlLOB*()
escapeIdentifier($name) new
setNoticeCallback(?callable $callback) new

The copy* methods use PostgreSQL's COPY protocol, which bulk-loads rows much faster than many INSERTs. Each row is one string with columns separated by $separator. In PHP 8.5 copyFromArray() also accepts any iterable, so a generator can stream rows without building a big array.

This example was not run (no PostgreSQL server):

<?php
$pg = PDO::connect('pgsql:host=localhost;dbname=app', 'app', 'secret');
// $pg is a Pdo\Pgsql

$pg->exec('CREATE TEMP TABLE visits (path text, hits int)');

function rows(): Generator
{
    yield "/\t120";
    yield "/about\t7";
    yield "/blog\t42";
}
$pg->copyFromArray('visits', rows());            // iterable since PHP 8.5
print_r($pg->copyToArray('visits'));             // ["/\t120\n", "/about\t7\n", "/blog\t42\n"]

// "order" is a reserved word; escapeIdentifier() quotes it safely
$col = $pg->escapeIdentifier('order');           // "order" with double quotes
$pg->exec("ALTER TABLE visits ADD COLUMN $col int");

// RAISE NOTICE messages go to the callback instead of being lost
$pg->setNoticeCallback(function (string $message): void {
    error_log("pg notice: $message");
});
$pg->exec("DO $$ BEGIN RAISE NOTICE 'cache warmed'; END $$");

// LISTEN / NOTIFY
$pg->exec('LISTEN jobs');
$pg->exec("NOTIFY jobs, 'job-42'");
$note = $pg->getNotify(PDO::FETCH_ASSOC, 1000);  // ['message' => 'jobs', 'pid' => ..., 'payload' => 'job-42']

Pdo\Pgsql::ATTR_DISABLE_PREPARES replaces PDO::PGSQL_ATTR_DISABLE_PREPARES, and Pdo\Pgsql::TRANSACTION_IDLE, TRANSACTION_INTRANS and the other TRANSACTION_* constants replace PDO::PGSQL_TRANSACTION_*.

Pdo\Mysql

MySQL gets one new method, getWarningCount(), which returns the number of warnings from the last statement (what SHOW WARNINGS would list). The many PDO::MYSQL_ATTR_* connection options are now Pdo\Mysql::ATTR_*.

This example was not run (no MySQL server):

<?php
$my = PDO::connect('mysql:host=localhost;dbname=app;charset=utf8mb4', 'app', 'secret', [
    Pdo\Mysql::ATTR_INIT_COMMAND       => "SET time_zone = '+00:00'",
    Pdo\Mysql::ATTR_USE_BUFFERED_QUERY => false,   // stream large result sets
    Pdo\Mysql::ATTR_SSL_CA             => '/etc/ssl/certs/ca.pem',
]);
// $my is a Pdo\Mysql

// in non-strict mode, out-of-range values are clamped and produce a warning
$my->exec("SET SESSION sql_mode = ''");
$my->exec("CREATE TEMPORARY TABLE t (n TINYINT)");
$my->exec("INSERT INTO t VALUES (1000)");
echo $my->getWarningCount(), "\n";               // 1

You can use the new constants with new PDO() as well. They have the same values as the old ones, so the following is the warning-free way to write PHP 8.5 code that still creates plain PDO objects:

$db = new PDO($dsn, $user, $pass, [Pdo\Mysql::ATTR_INIT_COMMAND => "SET NAMES utf8mb4"]);

Type-hinting the subclass

The biggest practical gain is in function signatures. Code that needs a driver-specific feature can now say so in its type declaration, and get a clear TypeError instead of a "call to undefined method" error at some later point:

<?php
final class Slugs
{
    public function __construct(private Pdo\Sqlite $db)
    {
        $db->createFunction('slug', fn($s) => strtolower(str_replace(' ', '-', $s)), 1, Pdo\Sqlite::DETERMINISTIC);
    }

    public function of(string $title): string
    {
        return $this->db->query('SELECT slug(' . $this->db->quote($title) . ')')->fetchColumn();
    }
}

echo (new Slugs(PDO::connect('sqlite::memory:')))->of('Driver Specific PDO'), "\n";

try {
    new Slugs(new PDO('sqlite::memory:'));
} catch (TypeError $e) {
    echo $e->getMessage(), "\n";
}

Output:

driver-specific-pdo
Slugs::__construct(): Argument #1 ($db) must be of type Pdo\Sqlite, PDO given, called in .../slugs.php on line 18

Note the second call: a SQLite connection made with new PDO() is a plain PDO, not a Pdo\Sqlite. If you start type-hinting subclasses, change the place where connections are made to use PDO::connect().

Code that works with any database keeps the PDO type and can check for the subclass when it wants to use an optional feature:

<?php
function describe(PDO $db): string
{
    return match (true) {
        $db instanceof Pdo\Sqlite => 'SQLite ' . $db->getAttribute(PDO::ATTR_SERVER_VERSION),
        $db instanceof Pdo\Mysql  => 'MySQL, ' . $db->getWarningCount() . ' warnings',
        $db instanceof Pdo\Pgsql  => 'PostgreSQL, backend pid ' . $db->getPid(),
        default                   => get_class($db) . ' (' . $db->getAttribute(PDO::ATTR_DRIVER_NAME) . ')',
    };
}

echo describe(PDO::connect('sqlite::memory:')), "\n";
echo describe(new PDO('sqlite::memory:')), "\n";

Output:

SQLite 3.53.4
PDO (sqlite)

instanceof with a class that doesn't exist (because that driver isn't loaded) is simply false, so this works even when only some of the drivers are installed.

Extending a driver class

You can extend the subclasses to add your own helpers. connect() called on your class returns an instance of your class:

<?php
class AppDb extends Pdo\Sqlite
{
    public function tables(): array
    {
        return $this->query("SELECT name FROM sqlite_master WHERE type = 'table' ORDER BY name")
            ->fetchAll(PDO::FETCH_COLUMN);
    }
}

$db = AppDb::connect('sqlite::memory:');
echo get_class($db), "\n";
$db->exec("CREATE TABLE users (id INTEGER)");
$db->exec("CREATE TABLE posts (id INTEGER)");
print_r($db->tables());

try {
    AppDb::connect('mysql:host=localhost;dbname=app');
} catch (PDOException $e) {
    echo $e->getMessage(), "\n";
}

Output:

AppDb
Array
(
    [0] => posts
    [1] => users
)
AppDb::connect() cannot be used for connecting to the "mysql" driver, either call Pdo\Mysql::connect() or PDO::connect() instead

There is one restriction: if your class extends PDO directly (class MyPdo extends PDO), MyPdo::connect() throws the same "cannot be used for connecting" exception for every driver. PDO::connect() would have to return a Pdo\Sqlite, and a Pdo\Sqlite is not a MyPdo. Existing PDO subclasses keep working with new MyPdo(...). To get the new features, extend the driver class.

Deprecated in PHP 8.5

PHP 8.5 deprecated the driver-prefixed methods and constants on PDO. They still work, but each use emits an E_DEPRECATED warning that names the replacement:

<?php
$db = new PDO('sqlite::memory:');
$db->sqliteCreateFunction('double', fn($x) => $x * 2, 1, PDO::SQLITE_DETERMINISTIC);
echo $db->query('SELECT double(21)')->fetchColumn(), "\n";

Output:

Deprecated: Constant PDO::SQLITE_DETERMINISTIC is deprecated since 8.5, use Pdo\Sqlite::DETERMINISTIC instead in .../deprecated.php on line 3

Deprecated: Method PDO::sqliteCreateFunction() is deprecated since 8.5, use Pdo\Sqlite::createFunction() instead in .../deprecated.php on line 3
42

The rename follows a simple pattern: drop the driver prefix and move the name to the subclass.

Deprecated (8.5) Replacement (8.4+)
PDO::sqliteCreateFunction() Pdo\Sqlite::createFunction()
PDO::sqliteCreateAggregate() Pdo\Sqlite::createAggregate()
PDO::sqliteCreateCollation() Pdo\Sqlite::createCollation()
PDO::SQLITE_DETERMINISTIC Pdo\Sqlite::DETERMINISTIC
PDO::SQLITE_ATTR_OPEN_FLAGS Pdo\Sqlite::ATTR_OPEN_FLAGS
PDO::SQLITE_OPEN_READONLY (and _READWRITE, _CREATE) Pdo\Sqlite::OPEN_READONLY
PDO::SQLITE_ATTR_READONLY_STATEMENT Pdo\Sqlite::ATTR_READONLY_STATEMENT
PDO::SQLITE_ATTR_EXTENDED_RESULT_CODES Pdo\Sqlite::ATTR_EXTENDED_RESULT_CODES
PDO::pgsqlCopyFromArray() and the other pgsql*() methods Pdo\Pgsql::copyFromArray() and so on
PDO::PGSQL_ATTR_DISABLE_PREPARES Pdo\Pgsql::ATTR_DISABLE_PREPARES
PDO::MYSQL_ATTR_INIT_COMMAND and all MYSQL_ATTR_* Pdo\Mysql::ATTR_INIT_COMMAND and so on
PDO::FB_ATTR_*, PDO::ODBC_*, PDO::DBLIB_ATTR_* Pdo\Firebird::ATTR_*, Pdo\Odbc::*, Pdo\Dblib::ATTR_*

The methods are only on Pdo\Sqlite and Pdo\Pgsql, so to migrate method calls you have to create connections with PDO::connect() (or the subclass). The constants can be switched right away: they work with new PDO() too.

If a library must support PHP versions older than 8.4, pick the constant at runtime:

$initCommand = PHP_VERSION_ID >= 80400
    ? Pdo\Mysql::ATTR_INIT_COMMAND
    : PDO::MYSQL_ATTR_INIT_COMMAND;

Summary

Feedback about page:

Feedback:
Optional: your email if you want me to get back to you:



Table Of Contents