Connecting & Adapters

A database connection in Pop is a single adapter object. Pop\Db\Db builds one from an array of credentials, and the query builder, the schema builder and Pop\Db\Record all take that object. Five adapters implement one shared interface, so what changes between them is the extension required, the options accepted, and what you catch on failure.

Connecting#

Db::connect() takes the adapter name and the credentials:

use Pop\Db\Db;

$db = Db::connect('mysql', [
    'database' => 'popdb',
    'username' => 'popuser',
    'password' => 'db_password',
    'host'     => '127.0.0.1'
]);

The name is lower-cased and capitalized into a class under Pop\Db\Adapter, so 'mysql' resolves to Pop\Db\Adapter\Mysql. A name with no matching class throws Pop\Db\Exception before anything is opened: Error: The database adapter \Pop\Db\Adapter\Bogus does not exist.

Five shorthand methods skip the adapter string. They take the options array and nothing else:

use Pop\Db\Db;

$db = Db::mysqlConnect(['database' => 'popdb', 'username' => 'popuser', 'password' => 'db_password']);
$db = Db::pgsqlConnect(['database' => 'popdb', 'username' => 'postgres', 'password' => 'db_password']);
$db = Db::sqliteConnect(['database' => '/var/data/my_database.sqlite']);
$db = Db::sqlsrvConnect(['database' => 'popdb', 'username' => 'sa', 'password' => 'secret']);
$db = Db::pdoConnect(['type' => 'mysql', 'database' => 'popdb', 'username' => 'popuser', 'password' => 'db_password']);

host defaults to localhost on every adapter that uses one. The connection is opened in the adapter's constructor, so a bad password fails at the Db::connect() line rather than at the first query — but what you catch is not the same on all five, and code that only catches Pop\Db\Adapter\Exception will miss two of them.

Every adapter also rejects an incomplete options array up front with Error: The proper database credentials were not passed., before it tries to reach the server.

When you would rather test a connection than open one, Db::check() returns true on success and the error message as a string on failure. It catches the driver exception for you, which makes it the right call for a startup gate:

use Pop\Db\Db;

$options = [
    'database' => 'popdb',
    'username' => 'popuser',
    'password' => 'db_password',
    'host'     => '127.0.0.1'
];

$check = Db::check('mysql', $options);

if ($check !== true) {
    throw new \Pop\Db\Adapter\Exception('Error: ' . $check);
}

Db::check() does not protect you from a missing extension either — it catches \Exception, and a missing driver raises an \Error. Db::check('sqlsrv', $options) on a machine with no sqlsrv extension throws Error: Call to undefined function Pop\Db\Adapter\sqlsrv_connect() straight through. Db::isAvailable() is the guard for that question, and Db::getAvailableAdapters() returns the whole map at once:

use Pop\Db\Db;

var_dump(Db::isAvailable('mysql'));       // true where the mysqli extension is loaded
var_dump(Db::isAvailable('sqlsrv'));      // false where the sqlsrv extension is not
var_dump(Db::isAvailable('pdo_pgsql'));   // PDO drivers take a pdo_ prefix

print_r(Db::getAvailableAdapters());

MySQL#

Pop\Db\Adapter\Mysql wraps the mysqli class from the mysqli extension.

use Pop\Db\Db;

$db = Db::mysqlConnect([
    'database' => 'popdb',
    'username' => 'popuser',
    'password' => 'db_password',
    'host'     => '127.0.0.1'
]);

echo $db->getVersion();   // MySQL 8.4.6-0ubuntu3

MySQL is the one adapter whose connection failure isn't a Pop exception. PHP's mysqli driver throws mysqli_sql_exception inside new \mysqli(...), before the adapter inspects connect_error, so catch that as well.

PostgreSQL#

Pop\Db\Adapter\Pgsql wraps the pg_* functions from the pgsql extension. Options beyond the four basics are appended to the connection string as-is.

use Pop\Db\Db;

$db = Db::pgsqlConnect([
    'database' => 'popdb',
    'username' => 'postgres',
    'password' => 'postgres',
    'host'     => '127.0.0.1',
    'sslmode'  => 'prefer'
]);

echo $db->getVersion();   // PostgreSQL 17.6 (Ubuntu 17.6-1build1)

This adapter throws Unable to connect to the database. whatever went wrong. The specifics — FATAL: database "nope_popdb" does not exist against FATAL: role "nosuchuser" does not exist — reach you through a PHP warning from pg_connect(), so read the server log if your handler discards warnings.

SQLite#

Pop\Db\Adapter\Sqlite wraps the SQLite3 class from the sqlite3 extension. There's no host and no credentials — database is a path on disk.

use Pop\Db\Db;

$db = Db::sqliteConnect(['database' => '/var/data/my_database.sqlite']);

echo $db->getVersion();   // SQLite 3.46.1

Create the SQLite file before connecting. The adapter checks file_exists() while reading its options, so touch database.sqlite — or Db::install(), which creates it — comes first.

The file also needs to be writable by the user PHP runs as, and so does the directory holding it — SQLite writes a journal alongside the database, so a writable file in a read-only directory still fails on the first write.

SQL Server#

Pop\Db\Adapter\Sqlsrv wraps the sqlsrv_* functions from Microsoft's sqlsrv extension.

use Pop\Db\Db;

$db = Db::sqlsrvConnect([
    'database' => 'popdb',
    'username' => 'sa',
    'password' => 'secret',
    'host'     => 'localhost'
]);

The sqlsrv extension is a hard requirement for SQL Server, so gate on Db::isAvailable('sqlsrv') first — without the extension loaded, Db::sqlsrvConnect() and Db::check('sqlsrv', ...) raise Error: Call to undefined function Pop\Db\Adapter\sqlsrv_connect().

The query builder also treats SQL Server as a special case: it has no LIMIT clause, so the builder rewrites limits and offsets into a ROW_NUMBER() window, and a limited query with no ORDER BY throws Error: You must set an order by clause to execute a limit clause on the MS SQL Server database.

PDO#

Pop\Db\Adapter\Pdo reaches every driver PDO has rather than a single database, so it's the fallback when the native extension for your database is not available — and the one adapter that needs a type.

use Pop\Db\Db;

$db = Db::pdoConnect([
    'type'     => 'pgsql',
    'database' => 'popdb',
    'username' => 'postgres',
    'password' => 'postgres',
    'host'     => '127.0.0.1'
]);

echo $db->getVersion();   // PDO pgsql 17.6 (Ubuntu 17.6-1build1)
echo $db->getType();      // pgsql

type is checked before the credentials are, and a driver name PDO does not know is reported as missing credentials rather than as a bad driver: Db::pdoConnect(['type' => 'oracle', ...]) throws Error: The proper database credentials were not passed. Check the spelling of type first when you see that message on a config that looks complete.

PDO reports connection failures most usefully — the PDOException is re-thrown as Pop\Db\Adapter\Exception with the SQLSTATE and driver text intact.

Configuring the Connection in an App#

In an application the credentials are data, and they live in a config file that reads them from the environment. kettle pop:init writes this file when you answer yes to its database prompt, with the one default entry:

<?php

return [
    'default' => [
        'database' => $_ENV['DB_DATABASE'],
        'adapter'  => $_ENV['DB_ADAPTER'],
        'username' => $_ENV['DB_USERNAME'],
        'password' => $_ENV['DB_PASSWORD'],
        'host'     => $_ENV['DB_HOST'],
        'type'     => $_ENV['DB_TYPE'],
    ],
    'reports' => [
        'database' => $_ENV['REPORTS_DB_DATABASE'],
        'adapter'  => $_ENV['REPORTS_DB_ADAPTER'],
        'username' => $_ENV['REPORTS_DB_USERNAME'],
        'password' => $_ENV['REPORTS_DB_PASSWORD'],
        'host'     => $_ENV['REPORTS_DB_HOST'],
        'type'     => $_ENV['REPORTS_DB_TYPE'],
    ],
];

adapter is the string Db::connect() takes — mysql, pgsql, sqlite, sqlsrv or pdo — and type is the PDO driver, left empty for the four native adapters. A second entry, keyed however you like, is a second connection; the key becomes the service name it is registered under.

Opening the connection is bootstrap work, so it belongs in load() rather than the config file. The convention is a protected initDb() that walks the entries, gates each with Db::check() and registers the adapter — see Configuration.

Once that has run, the adapter is reachable two ways without passing it around: as a service from the application, and from Pop\Db\Record for any table class bound to it.

use Pop\Db\Record;

$db      = Record::getDb();
$version = $db->getVersion();

Db::getAll() returns every adapter registered with Pop\Db\Db, and Db::db('App\Table\Users') resolves the one a given table class is bound to — the same lookup Record does internally, which is what makes a per-class Users::setDb($db) override work alongside a default connection.

See Also#

• Querying — raw queries, prepared statements and transactions on the adapter this page builds
• Query Builder — portable SQL from the adapter's createSql()
• Records & the ORM — initDb() in full, and binding a connection to a table class
• Applications & Bootstrap — where load() and the service container fit
• pop-db README — the full adapter API surface