DALT.PHP
Framework Deep Dive3. Container & DB

Database Connection

How database connections are created and managed

Connecting to Data

Every web app needs to store data. DALT.PHP supports two database drivers:

  • SQLite - File-based, zero setup (default)
  • PostgreSQL - Production-ready, powerful

Let's see how connections are created and managed.

The Database Configuration

Configuration lives in config/database.php:

return [
    'database' => [
        'driver' => $_ENV['DB_DRIVER'] ?? 'sqlite',
        'host' => $_ENV['DB_HOST'] ?? '127.0.0.1',
        'port' => $_ENV['DB_PORT'] ?? 5432,
        'dbname' => $_ENV['DB_NAME'] ?? 'dalt_php_app',
        'username' => $_ENV['DB_USERNAME'] ?? 'postgres',
        'password' => $_ENV['DB_PASSWORD'] ?? '',
        'charset' => $_ENV['DB_CHARSET'] ?? 'utf8',
        'database' => $_ENV['DB_DATABASE'] ?? base_path('database/app.sqlite'),
    ]
];

Why Environment Variables?

$_ENV['DB_DRIVER'] ?? 'sqlite'

This reads from .env file:

DB_DRIVER=sqlite
DB_DATABASE=database/app.sqlite

Benefits:

  • ✅ Different settings per environment (dev/staging/prod)
  • ✅ Secrets not in code (passwords, hosts)
  • ✅ Easy to change without editing code

The ?? operator:

  • If $_ENV['DB_DRIVER'] exists → use it
  • If not → use 'sqlite' (default)

The DatabaseManager

The DatabaseManager class handles driver-specific setup:

class DatabaseManager
{
    private $config;
    private $database;

    public static function create($config)
    {
        $manager = new self($config);
        return $manager->getDatabase();
    }
    
    public function __construct($config)
    {
        $this->config = $config;
        $this->setupDatabase();
    }

    private function setupDatabase()
    {
        $driver = $this->config['driver'] ?? 'sqlite';

        switch ($driver) {
            case 'sqlite':
                $this->setupSQLite();
                break;
            case 'pgsql':
                $this->setupPostgreSQL();
                break;
            default:
                throw new \RuntimeException("Unsupported database driver: {$driver}");
        }

        $this->database = new Database($this->config);
        $this->ensureTablesExist();
    }

    public function getDatabase()
    {
        return $this->database;
    }
}

The Creation Flow

1. Static factory method

public static function create($config)
{
    $manager = new self($config);
    return $manager->getDatabase();
}

This is a "factory pattern" - a method that creates objects.

Why static?

  • Can be called without an instance
  • Clean API: DatabaseManager::create($config)

2. Constructor does the work

public function __construct($config)
{
    $this->config = $config;
    $this->setupDatabase();
}

Steps:

  1. Store config
  2. Setup driver-specific things
  3. Create Database instance
  4. Run migrations if needed

Driver Setup

Different databases need different preparation:

private function setupDatabase()
{
    $driver = $this->config['driver'] ?? 'sqlite';
    
    switch ($driver) {
        case 'sqlite':
            $this->setupSQLite();
            break;
        case 'pgsql':
            $this->setupPostgreSQL();
            break;
    }
}

SQLite Setup

private function setupSQLite()
{
    if (isset($this->config['database']) && !str_starts_with($this->config['database'], '/')) {
        $this->config['database'] = BASE_PATH . $this->config['database'];
    }

    $dbPath = $this->config['database'];
    $dbDir = dirname($dbPath);

    if (!is_dir($dbDir)) {
        mkdir($dbDir, 0755, true);
    }

    if (!file_exists($dbPath)) {
        touch($dbPath);
    }
}

What this does:

1. Make path absolute

if (!str_starts_with($this->config['database'], '/')) {
    $this->config['database'] = BASE_PATH . $this->config['database'];
}

Converts database/app.sqlite → /full/path/to/database/app.sqlite

2. Create directory

$dbDir = dirname($dbPath);
if (!is_dir($dbDir)) {
    mkdir($dbDir, 0755, true);
}

If database/ folder doesn't exist, create it.

3. Create empty file

if (!file_exists($dbPath)) {
    touch($dbPath);
}

SQLite needs an actual file to exist.

PostgreSQL Setup

private function setupPostgreSQL()
{
}

PostgreSQL doesn't need file setup - it's a server.

The Database Class

This wraps PHP's PDO (PHP Data Objects):

class Database
{
    protected $connection;
    protected $statement;
    
    public function getConnection(): PDO
    {
        return $this->connection;
    }
    
    public function __construct($config)
    {
        $dsn = $this->buildDsn($config);
        $username = $config['username'] ?? null;
        $password = $config['password'] ?? null;

        try {
            $this->connection = new PDO($dsn, $username, $password, [
                PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
                PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            ]);
        } catch (PDOException $e) {
            throw new \RuntimeException(
                "Database connection failed...\n" .
                "Driver: {$config['driver']}\n" .
                "DSN: {$dsn}\n" .
                "Error: " . $e->getMessage()
            );
        }
    }
}

What is PDO?

PDO is PHP's standard database interface. It works with many databases:

  • SQLite
  • PostgreSQL
  • SQL Server
  • Oracle

Benefits:

  • Same API for all databases
  • Prepared statements (security)
  • Error handling
  • Transactions

The Constructor Breakdown

1. Build DSN

$dsn = $this->buildDsn($config);

DSN = Data Source Name. It's a connection string.

2. Get credentials

$username = $config['username'] ?? null;
$password = $config['password'] ?? null;

SQLite doesn't need these, but PostgreSQL does.

3. Create PDO connection

$this->connection = new PDO($dsn, $username, $password, [
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
]);

Options explained:

  • PDO::FETCH_ASSOC - Return rows as associative arrays

    ['id' => 1, 'title' => 'Hello']
// Instead of: [0 => 1, 1 => 'Hello', 'id' => 1, 'title' => 'Hello']

  • PDO::ERRMODE_EXCEPTION - Throw exceptions on errors

    // Instead of returning false, throw an exception
// Makes errors easier to catch and debug

4. Catch connection errors

catch (PDOException $e) {
    throw new \RuntimeException(
        "Database connection failed...\n" .
        "Driver: {$config['driver']}\n" .
        "DSN: {$dsn}\n" .
        "Error: " . $e->getMessage()
    );
}

This provides helpful error messages:

Database connection failed...
Driver: pgsql
DSN: pgsql:host=127.0.0.1;port=5432;dbname=myapp
Error: SQLSTATE[08006] Connection refused

Building the DSN

The DSN format is different for each driver:

private function buildDsn($config)
{
    $driver = $config['driver'] ?? 'sqlite';
    
    switch ($driver) {
        case 'pgsql':
            return sprintf(
                'pgsql:host=%s;port=%s;dbname=%s',
                $config['host'],
                $config['port'],
                $config['dbname']
            );
            
        case 'sqlite':
            return 'sqlite:' . $config['database'];
            
        default:
            throw new \Exception("Unsupported database driver: {$driver}");
    }
}

DSN Examples

PostgreSQL:

pgsql:host=127.0.0.1;port=5432;dbname=dalt_php_app

SQLite:

sqlite:/full/path/to/database/app.sqlite

Auto-Migration

After creating the database, the manager checks if tables exist:

private function ensureTablesExist()
{
    try {
        $this->database->query("SELECT 1 FROM users LIMIT 1");
    } catch (\Exception $e) {
        $msg = strtolower($e->getMessage());
        if (str_contains($msg, 'no such table') || str_contains($msg, 'does not exist')) {
            $this->runMigrations();
        } else {
            throw $e;
        }
    }
}

How It Works

1. Try to query users table

$this->database->query("SELECT 1 FROM users LIMIT 1");

If this succeeds, tables exist.

2. If it fails, run migrations

catch (\Exception $e) {
    $this->runMigrations();
}

This is "auto-migration" - first time you run the app, it creates tables automatically.

Why check users specifically?

  • It's a core table every app needs
  • Simple indicator that migrations ran
  • If it exists, assume others do too

Binding to the Container

Remember from Part 3.1, the database is bound during bootstrap:

// framework/Core/bootstrap.php

$container->bind('Core\Database', function () use ($dbConfig) {
    return DatabaseManager::create($dbConfig['database']);
});

What happens:

  1. Closure captures $dbConfig
  2. When resolved, calls DatabaseManager::create()
  3. DatabaseManager:
    • Sets up driver
    • Creates Database instance
    • Runs migrations if needed
  4. Returns the Database object

Usage anywhere:

$db = App::resolve(Database::class);

Connection Lifecycle

App Starts
    ↓
Bootstrap runs
    ↓
Container binds Database (recipe only)
    ↓
... app runs ...
    ↓
Controller needs database
    ↓
App::resolve(Database::class)
    ↓
Container calls the closure
    ↓
DatabaseManager::create()
    ↓
Setup driver (create files, etc.)
    ↓
new Database($config)
    ↓
new PDO($dsn, ...)
    ↓
Connection established
    ↓
Check if tables exist
    ↓
Run migrations if needed
    ↓
Return Database instance
    ↓
Controller uses it

Key Takeaways

  1. Config uses environment variables - Different per environment
  2. DatabaseManager handles setup - Driver-specific preparation
  3. Database wraps PDO - Standard PHP database interface
  4. DSN format varies by driver - Connection string
  5. Auto-migration on first run - Convenient for development

What's Good Here

✅ Supports multiple databases
✅ Clear error messages on connection failure
✅ Auto-migration for convenience
✅ Environment-based configuration
✅ Simple enough to understand and modify

Design Note

Features like connection pooling and retry logic are intentionally omitted. The container already provides singleton caching (one connection per request), which is sufficient for a learning framework. These optimizations would add complexity without teaching new concepts.

Next, explore how queries are executed and results are fetched.

Container Basics

Understanding dependency injection and the service container

Query Execution

How SQL queries are executed safely with prepared statements

On this page

Connecting to DataThe Database ConfigurationWhy Environment Variables?The DatabaseManagerThe Creation FlowDriver SetupSQLite SetupPostgreSQL SetupThe Database ClassWhat is PDO?The Constructor BreakdownBuilding the DSNDSN ExamplesAuto-MigrationHow It WorksBinding to the ContainerConnection LifecycleKey TakeawaysWhat's Good HereDesign Note