Database Connection

VelvetCMS supports SQLite, MySQL, and PostgreSQL through a unified connection layer. Connections are created lazily-the database isn't touched until you actually run a query.

Configuration #

Database settings live in config/db.php, merged with user/config/db.php:

return [
    'default' => 'sqlite',
    
    'connections' => [
        'sqlite' => [
            'driver' => 'sqlite',
            'database' => storage_path('data/database.sqlite'),
        ],
        
        'mysql' => [
            'driver' => 'mysql',
            'host' => '127.0.0.1',
            'port' => 3306,
            'database' => 'velvet',
            'username' => 'root',
            'password' => '',
            'charset' => 'utf8mb4',
        ],
        
        'pgsql' => [
            'driver' => 'pgsql',
            'host' => '127.0.0.1',
            'port' => 5432,
            'database' => 'velvet',
            'username' => 'postgres',
            'password' => '',
        ],
    ],
];

Getting the Connection #

// Via container
$db = app('db');

// Direct PDO access (connects if needed)
$pdo = app('db')->getPdo();

Running Queries #

For simple queries, use the connection directly:

$db = app('db');

// SELECT returns array of rows
$users = $db->query('SELECT * FROM users WHERE active = ?', [1]);

// INSERT/UPDATE/DELETE returns affected row count
$count = $db->statement('UPDATE users SET active = ? WHERE id = ?', [0, 123]);

All queries use prepared statements automatically-you can't accidentally create SQL injection vulnerabilities.

Transactions #

Wrap multiple operations in a transaction:

$db->beginTransaction();

try {
    $db->statement('INSERT INTO orders (user_id, total) VALUES (?, ?)', [$userId, $total]);
    $orderId = $db->lastInsertId();
    
    foreach ($items as $item) {
        $db->statement(
            'INSERT INTO order_items (order_id, product_id, qty) VALUES (?, ?, ?)',
            [$orderId, $item['product_id'], $item['qty']]
        );
    }
    
    $db->commit();
} catch (\Exception $e) {
    $db->rollback();
    throw $e;
}

Or use the callback form for automatic commit/rollback:

$result = $db->transaction(function ($db) use ($userId, $items) {
    $db->statement('INSERT INTO orders ...', [...]);
    $orderId = $db->lastInsertId();
    // ... more queries
    return $orderId;
});

If the callback throws an exception, the transaction is rolled back automatically.

Checking Tables #

if ($db->tableExists('pages')) {
    // Table exists
}

Driver Detection #

$driver = $db->getDriver(); // 'sqlite', 'mysql', or 'pgsql'

if ($driver === 'sqlite') {
    // SQLite-specific logic
}

Query Builder #

For complex queries, use the fluent query builder:

$posts = $db->table('posts')
    ->where('status', '=', 'published')
    ->orderBy('created_at', 'desc')
    ->limit(10)
    ->get();

See Query Builder for full documentation.

Multi-Tenancy #

When tenancy is enabled with database isolation, each tenant gets its own database (SQLite) or prefixed tables. The connection handles this transparently-you don't need to change your code.

Error Handling #

Connection failures throw RuntimeException. Query errors throw PDOException. Both include the underlying error message for debugging.

try {
    $db->query('SELECT * FROM nonexistent_table');
} catch (\PDOException $e) {
    // Handle query error
}