Driver-specific PDO classes
suggest changeUntil 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:
Pdo\Sqlite::OKallows the actionPdo\Sqlite::IGNOREmakes a column read asNULL, or skips the actionPdo\Sqlite::DENYmakes the statement fail to prepare
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:
ATTR_TRANSACTION_MODEcontrols whatbeginTransaction()sends:TRANSACTION_MODE_DEFERRED(the default),TRANSACTION_MODE_IMMEDIATEorTRANSACTION_MODE_EXCLUSIVE.IMMEDIATEtakes the write lock at the start of the transaction. If another process holds the lock, you get a "database is locked" error right away, atbeginTransaction(), and not later in the middle of the transaction.ATTR_EXPLAIN_STATEMENT, set on a statement, turns it intoEXPLAINorEXPLAIN QUERY PLANwithout changing the SQL.ATTR_BUSY_STATEMENTtells you whether a statement is still in progress.
<?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
- PHP 8.4 added
Pdo\Sqlite,Pdo\Mysql,Pdo\Pgsql,Pdo\Firebird,Pdo\OdbcandPdo\Dblib, all subclasses ofPDO. PDO::connect($dsn, ...)returns the subclass that matches the DSN.new PDO(...)still returns a plainPDO.Pdo\SqlitehascreateFunction(),createAggregate(),createCollation(),openBlob()andloadExtension(). PHP 8.5 addedsetAuthorizer(),ATTR_TRANSACTION_MODEandATTR_EXPLAIN_STATEMENT.Pdo\Pgsqlhas thecopy*,lob*,getNotify()andgetPid()methods plus the newescapeIdentifier()andsetNoticeCallback().Pdo\MysqlhasgetWarningCount().- Type-hint
Pdo\Sqlite(or another subclass) when code depends on driver features. Useinstanceoffor optional features in driver-agnostic code. - Extend
Pdo\Sqliteand the other driver classes, notPDO, if you wantconnect()to return your class. - PHP 8.5 deprecated
PDO::sqlite*(),PDO::pgsql*()and thePDO::MYSQL_*,PDO::SQLITE_*,PDO::PGSQL_*and other driver constants in favour of the subclass members.