Database Abstraction Layer

The DB class provides a robust database abstraction layer with support for multiple database drivers, connection management, and query building.

Configuration

Database configuration is typically set in your application's configuration file:

SQLite Configuration

'database' => [
    'driver' => 'sqlite',
    'file' => __DIR__ . '/database/sqlite.db',
    'options' => [
        PDO::ATTR_PERSISTENT => true
    ]
]

MySQL Configuration

'database' => [
    'driver' => 'mysql',
    'host' => 'localhost',
    'port' => 3306,
    'name' => 'my_database',
    'user' => 'db_user',
    'password' => 'secure_password',
    'charset' => 'utf8mb4',
    'collation' => 'utf8mb4_unicode_ci',
    'options' => [
        PDO::ATTR_PERSISTENT => true,
        PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES 'utf8mb4'"
    ]
]

PostgreSQL Configuration

'database' => [
    'driver' => 'pgsql',
    'host' => 'localhost',
    'port' => 5432,
    'name' => 'my_database',
    'user' => 'db_user',
    'password' => 'secure_password',
    'options' => [
        PDO::ATTR_PERSISTENT => true
    ]
]

Initialization

Get a database instance using helper functions:

// Get default database connection
$db = database();

// Alternative helper
$db = db();

// Create custom connection
$externalDb = connect_db([
    'driver' => 'mysql',
    'host' => 'external.db.host',
    // ... other config
]);

Core Functionality

Direct PDO Access

// Get PDO instance
$pdo = database()->getPdo();

// Execute raw SQL
$stmt = database()->query('SELECT * FROM users');

// Prepare statement
$stmt = database()->prepare('SELECT * FROM users WHERE id = ?');
$stmt->execute([1]);

Connection Management

// Check driver type
if (database()->isMySQL()) {
    // MySQL specific logic
}

// Reset connection with new config
database()->resetConfig([
    'driver' => 'sqlite',
    'file' => 'new_database.db'
]);

// Force reconnect
database()->resetPdo();

Transactions

try {
    // Start transaction
    database()->beginTransaction();
    
    // Perform operations
    query('accounts')
        ->where('id', 1)
        ->decrement('balance', 100);
        
    query('accounts')
        ->where('id', 2)
        ->increment('balance', 100);
        
    // Commit if successful
    database()->commit();
} catch (Exception $e) {
    // Rollback on error
    database()->rollback();
    throw $e;
}

Query Building

The DB class provides static access to QueryBuilder methods:

Basic Queries

// Select all from table
DB::table('users')->get();

// Select specific columns
DB::table('users')->select('id', 'name')->get();

// Conditional where
DB::table('users')
    ->where('active', 1)
    ->where('age', '>', 18)
    ->get();

Aggregates

// Count records
$count = DB::table('users')->count();

// Get max value
$maxAge = DB::table('users')->max('age');

// Get average
$avgScore = DB::table('results')->avg('score');

Advanced Features

Macros

// Register custom macro
DB::macro('getActiveUsers', function() {
    return $this->table('users')
        ->where('active', 1)
        ->get();
});

// Use macro
$activeUsers = DB::getActiveUsers();

Custom DSN

// Use custom DSN string
$db = new DB([
    'dsn' => 'mysql:unix_socket=/tmp/mysql.sock;dbname=testdb',
    'user' => 'root',
    'password' => ''
]);

Driver-Specific Features

SQLite

// Enable foreign keys (auto-enabled by DB class)
database()->exec('PRAGMA foreign_keys = ON');

// Use in-memory database
$db = new DB([
    'driver' => 'sqlite',
    'file' => ':memory:'
]);

MySQL

// Set collation
$db = new DB([
    'driver' => 'mysql',
    // ... other config
    'collation' => 'utf8mb4_unicode_ci'
]);

Error Handling

try {
    $users = DB::table('users')->where('invalid_column', 'value')->get();
} catch (\PDOException $e) {
    // Handle database errors
    error_log("Database error: " . $e->getMessage());
    return false;
}

Performance Tips

• Use persistent connections for frequently accessed databases
• Enable query caching where appropriate
• Use transactions for bulk operations
• Select only the columns you need
• Add proper indexes to your tables