skip to content

In PHP 8.5, how do PDO DSNs for MySQL and PostgreSQL differ, and what does PDO::connect() return that new PDO() does not?

level: middleimportance: should knowfreq 30%

answer

  1. mysql: vs pgsql: prefixes
  2. charset for MySQL, sslmode for PostgreSQL
  3. pgsql DSN credentials win since 8.4
  4. Pdo\Mysql, Pdo\Pgsql, Pdo\Sqlite
  5. PDO::MYSQL_ATTR_* deprecated in 8.5

basics

~20 s

Each DSN starts with its driver prefix and takes driver-specific keys: charset for MySQL, sslmode for PostgreSQL. PDO::connect(), added in 8.4, returns the driver subclass such as Pdo\Mysql or Pdo\Pgsql, while new PDO() returns a generic PDO object.

solid answer

~40 s

Both DSNs are `prefix:key=value;...`, but the keys differ: MySQL uses `mysql:host=...;port=3306;dbname=...;charset=utf8mb4` (or `unix_socket`), while PostgreSQL uses `pgsql:host=...;port=5432;dbname=...;sslmode=require`, and PDO turns its semicolons into spaces, so a semicolon inside a PostgreSQL password is not supported. Credentials placed in a PostgreSQL DSN take priority over the constructor arguments since PHP 8.4; for MySQL the constructor arguments win. `PDO::connect()`, added in 8.4, reads the DSN and returns the matching **driver subclass**, `Pdo\Mysql`, `Pdo\Pgsql` or `Pdo\Sqlite`, while `new PDO()` still returns a plain `PDO`. The subclasses hold the driver-specific constants and methods, such as `Pdo\Mysql::ATTR_INIT_COMMAND` or `Pdo\Pgsql::copyFromArray()`; PHP 8.5 deprecates the old spellings on `PDO` itself.

code

php · 18 lines
php
<?php
declare(strict_types=1);

$dsn = match (getenv('DB_DRIVER')) {
    'mysql' => sprintf('mysql:host=%s;port=3306;dbname=clinic;charset=utf8mb4', getenv('DB_HOST')),
    'pgsql' => sprintf('pgsql:host=%s;port=5432;dbname=clinic;sslmode=require', getenv('DB_HOST')),
};

$pdo = PDO::connect($dsn, getenv('DB_USER'), getenv('DB_PASSWORD'), [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
]);

echo $pdo::class, "\n";   // Pdo\Mysql or Pdo\Pgsql

if ($pdo instanceof Pdo\Pgsql) {
    $pdo->copyFromArray('slot_imports', ["7\t2026-10-01 09:00", "7\t2026-10-01 09:30"]);
}

go deeper

for a junior

Know the mysql: and pgsql: prefixes and the host, port and dbname keys both drivers share.

for a middle

Explain the driver-specific keys, the credential-precedence difference, and what PDO::connect() returns compared with new PDO().

for a senior

Structure a two-database codebase: shared options, per-driver DSNs, driver-specific code typed against the subclasses, and 8.5 deprecations cleared.

for a principal

Judge whether supporting several databases is worth it at all, since SQL dialects and driver behaviour diverge well beyond the DSN.

## One DSN grammar, different keys A PDO DSN is the driver name, a colon, and driver settings. The grammar is shared; the keys are not. A clinic-booking codebase that runs on MySQL for one customer and PostgreSQL for another builds one of these: | | MySQL (`pdo_mysql`) | PostgreSQL (`pdo_pgsql`) | |---|---|---| | prefix | `mysql:` | `pgsql:` | | server | `host`, `port` (3306), or `unix_socket` | `host`, `port` (5432); a directory in `host` means a Unix socket | | database | `dbname` | `dbname` | | encoding | `charset=utf8mb4` | no `charset` key among the documented DSN elements | | TLS | driver options such as `Pdo\Mysql::ATTR_SSL_CA` | `sslmode=require` or stricter | | user/password in DSN | allowed; **constructor arguments take precedence** | allowed; **the DSN takes priority since PHP 8.4** | | separators | `;` | `;` or spaces; PDO rewrites `;` to spaces for libpq | The last row has a consequence: because semicolons become spaces, a PostgreSQL password or database name containing a semicolon cannot be expressed in the DSN. A third driver, SQLite, needs no server at all: `sqlite:/var/data/clinic.sqlite` or `sqlite::memory:` for a throwaway in-memory database in tests. Other DSN forms exist but are rarely right: - an **alias**: a bare name that maps to a `pdo.dsn.<name>` entry in `php.ini`; - the **`uri:` scheme**, which reads the DSN from a file or URL, is **deprecated as of PHP 8.5** because of the risk of DSNs loaded from remote URIs. ## PDO::connect() and the driver subclasses PHP 8.4 added classes per driver, all extending `PDO`: `Pdo\Mysql`, `Pdo\Pgsql`, `Pdo\Sqlite`, `Pdo\Firebird`, `Pdo\Odbc` and `Pdo\Dblib`. You get one in two ways: 1. **`PDO::connect($dsn, $user, $password, $options)`**: a static factory that inspects the DSN prefix and returns the matching subclass, or a generic `PDO` if the driver has none. 2. **`new Pdo\Pgsql($dsn, ...)`**: direct instantiation. A mismatched DSN, such as a `mysql:` DSN passed to `Pdo\Pgsql`, throws `PDOException` ("cannot be used for connecting to the "mysql" driver"). `new PDO($dsn)` keeps its old behaviour and returns a plain `PDO` object, even for a MySQL DSN. ## Why the subclasses matter - **Driver-specific API in the right place.** PostgreSQL's COPY support is `Pdo\Pgsql::copyFromArray()`; SQLite's user functions are `Pdo\Sqlite::createFunction()`. Code that needs them can type-hint `Pdo\Pgsql` and let the type system reject a MySQL connection. - **Driver constants on the driver class.** `Pdo\Mysql::ATTR_INIT_COMMAND`, `Pdo\Mysql::ATTR_USE_BUFFERED_QUERY` and `Pdo\Pgsql::ATTR_DISABLE_PREPARES` replace the old prefixed constants. - **PHP 8.5 deprecations.** The driver-specific constants and methods on the base class, such as `PDO::MYSQL_ATTR_INIT_COMMAND`, `PDO::pgsqlCopyFromArray()` and `PDO::sqliteCreateFunction()`, are deprecated in 8.5 in favour of the subclass versions. ## Telling drivers apart at runtime - `$pdo->getAttribute(PDO::ATTR_DRIVER_NAME)` returns `'mysql'`, `'pgsql'` or `'sqlite'` for any PDO object, including ones created with `new PDO()`. - With `PDO::connect()`, an `instanceof Pdo\Pgsql` check does the same job and also narrows the type for static analysis, so calls such as `copyFromArray()` are known to exist. - Code that branches on the driver should do so in one place, typically the repository or query-builder layer, not throughout the application. ## Tests on SQLite Many teams run fast repository tests against `sqlite::memory:`. That works for simple CRUD but diverges quickly: date functions, casts such as `::date`, upsert syntax and type strictness differ between SQLite, MySQL and PostgreSQL. Tests for driver-specific code should run against the real driver the code targets. ## Putting it together - Build the DSN per driver from configuration; keep the options array (error mode, default fetch mode) identical for both. - Use `PDO::connect()` in new code so the returned object's class tells you which driver you are on. - Keep shared repository code typed against `PDO`, and type only the driver-specific pieces against `Pdo\Mysql` or `Pdo\Pgsql`. - When upgrading to 8.5, replace `PDO::MYSQL_ATTR_*` and similar constants with their `Pdo\...` counterparts to clear the deprecations.

  • What does new Pdo\Pgsql('mysql:host=db;dbname=clinic') do?
    It throws `PDOException`: a driver subclass may only connect with its own driver, and the message tells you to call `Pdo\Mysql` or `PDO` instead. `PDO::connect()` avoids the mismatch by choosing the subclass from the DSN prefix.
  • After upgrading to PHP 8.5, logs show deprecations for PDO::MYSQL_ATTR_INIT_COMMAND. What is the fix?
    Use the constant on the driver class, `Pdo\Mysql::ATTR_INIT_COMMAND`, in the options array. PHP 8.5 deprecated the driver-specific constants and methods on the base `PDO` class in favour of the `Pdo\Mysql`, `Pdo\Pgsql` and `Pdo\Sqlite` subclasses added in 8.4.

saying these in an interview costs you the question

  • new PDO('mysql:...') returns a Pdo\Mysql object in PHP 8.4+
  • charset=utf8mb4 works the same way in a pgsql DSN
  • Constructor credentials always override those in the DSN, for every driver
  • PDO::MYSQL_ATTR_* constants are the current spelling in PHP 8.5
  • Any semicolon-containing password works in a PostgreSQL DSN