DSNs And Connections

A DSN, or Data Source Name, tells PDO which database driver to use and where the database lives. A connection combines the DSN, credentials, and options.

Connection setup is small code with a large blast radius. A wrong character set, missing timeout, hard-coded password, or inconsistent environment value can break every database-backed request.

DSN Examples

Each database driver has its own DSN format.

<?php

declare(strict_types=1);

$dsns = [
    'sqlite_memory' => 'sqlite::memory:',
    'sqlite_file' => 'sqlite:/var/app/app.sqlite',
    'mysql' => 'mysql:host=127.0.0.1;dbname=app;charset=utf8mb4',
    'pgsql' => 'pgsql:host=127.0.0.1;port=5432;dbname=app',
];

foreach ($dsns as $name => $dsn) {
    echo $name . '=' . $dsn . PHP_EOL;
}

// Prints:
// sqlite_memory=sqlite::memory:
// mysql=mysql:host=127.0.0.1;dbname=app;charset=utf8mb4

The DSN should not contain the password. Keep credentials separate.

Connection Factory

A small factory function keeps connection options consistent.

<?php

declare(strict_types=1);

function pdoOptions(): array
{
    return [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        PDO::ATTR_EMULATE_PREPARES => false,
    ];
}

print_r(pdoOptions());

// Output includes PDO option constants as numeric keys.

In a real application, the factory would read configuration from environment variables or the framework config layer, then create new PDO($dsn, $username, $password, $options).

Environment Configuration

Development, test, staging, and production should not share the same database credentials. A common shape is:

<?php

declare(strict_types=1);

function databaseConfig(array $env): array
{
    return [
        'driver' => $env['DB_DRIVER'] ?? 'sqlite',
        'host' => $env['DB_HOST'] ?? '127.0.0.1',
        'database' => $env['DB_DATABASE'] ?? ':memory:',
        'username' => $env['DB_USERNAME'] ?? null,
        'password_set' => isset($env['DB_PASSWORD']),
    ];
}

print_r(databaseConfig(['DB_DRIVER' => 'mysql', 'DB_DATABASE' => 'app']));

// Prints:
// [driver] => mysql
// [database] => app

Do not print real passwords in diagnostics. It is enough to know whether a password was configured.

Timeouts And Failure

Database connections can fail because the server is down, DNS is wrong, credentials are invalid, the database is overloaded, or the connection limit has been reached.

Connection code should:

• Use reasonable timeouts where the driver supports them.
• Throw exceptions instead of hiding errors.
• Avoid exposing raw connection details to users.
• Log enough context for operators without logging secrets.
• Avoid opening new connections repeatedly inside tight loops.

Connection Lifetime

Most PHP web requests are short-lived. A connection is created during the request and closed when the process releases it. Frameworks often manage this for you.

Persistent connections can help in some environments, but they can also preserve session state and consume server connections longer than expected. Treat them as a deliberate operational choice, not a default beginner setting.

What To Check

Before moving on, make sure you can:

• Recognise SQLite, MySQL, and PostgreSQL DSNs.
• Keep credentials separate from DSNs.
• Set PDO options consistently.
• Read database settings from environment-specific config.
• Avoid logging passwords or full secrets.
• Explain why connection counts and timeouts matter.