Database Class

\Kotchasan\Database is the core class for database connections and query building. It is used internally by DB and Model.

Creating an Instance

$db = \Kotchasan\Database::create();

// Connect to another connection
$db2 = \Kotchasan\Database::create('secondary');

Configuration

From settings/database.php

// settings/database.php
return [
    'default' => [
        'driver' => 'mysql',
        'host' => 'localhost',
        'database' => 'myapp',
        'username' => 'root',
        'password' => '',
        'prefix' => 'app',
        'charset' => 'utf8mb4'
    ]
];

From Code

\Kotchasan\Database::config([
    'default' => [
        'driver' => 'mysql',
        'host' => 'localhost',
        'database' => 'myapp',
        'username' => 'root',
        'password' => '',
        'prefix' => 'app'
    ]
]);

Query Methods

select() - Create SELECT query

$db = \Kotchasan\Database::create();

$users = $db->select('id', 'name', 'email')
    ->from('user')
    ->where(['status', 1])
    ->fetchAll();

createQuery() - Create QueryBuilder

$db = \Kotchasan\Database::create();
$query = $db->createQuery()
    ->select('*')
    ->from('user');

first() - Retrieve first record

$db = \Kotchasan\Database::create();
$user = $db->first('user');
$user = $db->first('user', ['id', 'name']);

all() - Retrieve all records

$db = \Kotchasan\Database::create();
$users = $db->all('user');
$users = $db->all('user', ['id', 'name']);

get() - Retrieve records with conditions

$db = \Kotchasan\Database::create();
$activeUsers = $db->get('user', ['status' => 1], ['id', 'name']);

selectRaw() - Raw SQL select

$db = \Kotchasan\Database::create();
$result = $db->selectRaw('COUNT(*) as total, DATE(created_at) as date')
    ->from('orders')
    ->groupBy('date')
    ->fetchAll();

insert() / update() / delete()

$db = \Kotchasan\Database::create();

// Insert
$db->insert('user')
    ->values(['name' => 'John', 'email' => 'john@example.com'])
    ->execute();

// Update
$db->update('user')
    ->set(['name' => 'Jane'])
    ->where(['id', 1])
    ->execute();

// Delete
$db->delete('user')
    ->where(['id', 1])
    ->execute();

raw() - Run SQL directly

$db = \Kotchasan\Database::create();
$table = $db->getTableName('user');
$result = $db->raw(
    "SELECT * FROM `{$table}` WHERE status = ?",
    [1]
);

Transaction

Manual

$db = \Kotchasan\Database::create();

$db->beginTransaction();
try {
    $db->insert('orders')->values($order)->execute();
    $db->insert('order_items')->values($items)->execute();
    $db->commit();
} catch (\Exception $e) {
    $db->rollback();
    throw $e;
}

Callback (Recommended)

$db = \Kotchasan\Database::create();

$result = $db->transaction(function ($db) use ($order, $items) {
    $db->insert('orders')->values($order)->execute();
    $orderId = $db->lastInsertId();

    foreach ($items as $item) {
        $item['order_id'] = $orderId;
        $db->insert('order_items')->values($item)->execute();
    }

    return $orderId;
});

Connection Management

connection() - Switch connection

$db = \Kotchasan\Database::create();

$users = $db->all('user');
$logs = $db->connection('logs')->all('access_log');

getConnection() - Get connection instance

$connection = \Kotchasan\Database::getConnection('default');

Debugging & Logging

getLastQuery() - Get last query

$db = \Kotchasan\Database::create();
$db->all('user');

echo $db->getLastQuery();

getLastError() - Get last error

$db = \Kotchasan\Database::create();

try {
    $db->raw('INVALID SQL');
} catch (\Exception $e) {
    echo $db->getLastError();
}

setLogger() - Set Logger

use Kotchasan\Logger\FileLogger;

$logger = new FileLogger(ROOT_PATH.'datas/logs/');
\Kotchasan\Database::setLogger($logger);

createQueryLogger() - Create Query Logger

use Kotchasan\Logger\FileLogger;

$logger = new FileLogger(ROOT_PATH.'datas/logs/');
$queryLogger = \Kotchasan\Database::createQueryLogger($logger);

Cache

configureCache() - Configure Query Cache

\Kotchasan\Database::configureCache([
    'driver' => 'file',
    'path' => ROOT_PATH.'datas/cache/'
], 3600);

setCache() - Set Cache Instance

use Kotchasan\Cache\FileCache;

$cache = new FileCache(ROOT_PATH.'datas/cache/');
\Kotchasan\Database::setCache($cache, 3600);

Utility Methods

getTableName() - Get table name with prefix

$db = \Kotchasan\Database::create();
$tableName = $db->getTableName('user');

getPrefix() - Get table prefix

$db = \Kotchasan\Database::create();
$prefix = $db->getPrefix();

emptyTable() - Truncate table

$db = \Kotchasan\Database::create();
$db->emptyTable('cache');

optimizeTable() - Optimize table

$db = \Kotchasan\Database::create();
$db->optimizeTable('user');

lastInsertId() - Get last insert ID

$db = \Kotchasan\Database::create();
$db->insert('user')->values([...])->execute();
$id = $db->lastInsertId();

Method Reference

Static Methods

Instance Methods

Related Classes

• DB - Utility wrapper for simple CRUD
• Model - Base Model class
• QueryBuilder - Query building