irdb/api/src/Infrastructure/Db/ConnectionFactory.php

<?php

declare(strict_types=1);

namespace App\Infrastructure\Db;

use Doctrine\DBAL\Connection;
use Doctrine\DBAL\DriverManager;
use InvalidArgumentException;

/**
 * Builds a Doctrine DBAL Connection from the api's `db` settings.
 *
 * For SQLite we apply the four PRAGMAs the spec mandates on each new
 * connection (WAL journaling, normal sync, busy timeout, foreign keys on).
 */
final class ConnectionFactory
{
    /**
     * @param array{
     *     driver: string,
     *     sqlite_path: string,
     *     mysql_host: string,
     *     mysql_port: int,
     *     mysql_database: string,
     *     mysql_username: string,
     *     mysql_password: string
     * } $settings
     */
    public function __construct(private readonly array $settings)
    {
    }

    public function create(): Connection
    {
        $driver = $this->settings['driver'];

        if ($driver === 'sqlite') {
            $path = $this->settings['sqlite_path'];
            if ($path === ':memory:') {
                $params = [
                    'driver' => 'pdo_sqlite',
                    'memory' => true,
                ];
            } else {
                $params = [
                    'driver' => 'pdo_sqlite',
                    'path' => $path,
                ];
            }

            $connection = DriverManager::getConnection($params);
            self::applySqlitePragmas($connection);

            return $connection;
        }

        if ($driver === 'mysql') {
            $params = [
                'driver' => 'pdo_mysql',
                'host' => $this->settings['mysql_host'],
                'port' => $this->settings['mysql_port'],
                'dbname' => $this->settings['mysql_database'],
                'user' => $this->settings['mysql_username'],
                'password' => $this->settings['mysql_password'],
                'charset' => 'utf8mb4',
            ];

            return DriverManager::getConnection($params);
        }

        throw new InvalidArgumentException(sprintf('Unsupported DB_DRIVER: %s', $driver));
    }

    private static function applySqlitePragmas(Connection $connection): void
    {
        $connection->executeStatement('PRAGMA journal_mode = WAL');
        $connection->executeStatement('PRAGMA synchronous = NORMAL');
        $connection->executeStatement('PRAGMA busy_timeout = 5000');
        $connection->executeStatement('PRAGMA foreign_keys = ON');
    }
}