Kotchasan Framework Documentation

Kotchasan Framework Documentation

Kotchasan Database Usage Guide

EN 05 Feb 2026 06:23

Kotchasan Database Usage Guide

Introduction

The Kotchasan Framework provides a flexible database layer that supports MySQL, PostgreSQL, MSSQL, and SQLite. It focuses on secure queries, clean APIs, and convenient tooling for common tasks.

Table of Contents

  1. Database Configuration
  2. Database Connections
  3. Query Building
  4. Transaction Management
  5. Caching
  6. Performance Optimization
  7. Real-World Examples
  8. Best Practices

Database Configuration

Basic Configuration

use Kotchasan\Database;

Database::config([
    'default' => [
        'driver' => 'mysql',
        'host' => 'localhost',
        'database' => 'myapp_db',
        'username' => 'root',
        'password' => 'password',
        'charset' => 'utf8mb4',
        'collation' => 'utf8mb4_unicode_ci',
        'prefix' => 'app'
    ]
]);

Multiple Database Configuration

use Kotchasan\Database;

Database::config([
    'default' => [
        'driver' => 'mysql',
        'host' => 'localhost',
        'database' => 'main_db',
        'username' => 'root',
        'password' => 'password',
        'charset' => 'utf8mb4',
        'prefix' => 'main'
    ],
    'analytics' => [
        'driver' => 'pgsql',
        'host' => 'analytics.example.com',
        'database' => 'analytics_db',
        'username' => 'analytics_user',
        'password' => 'analytics_pass',
        'charset' => 'utf8'
    ],
    'cache' => [
        'driver' => 'sqlite',
        'database' => ROOT_PATH.DATA_FOLDER.'cache/cache.db',
        'prefix' => 'cache'
    ]
]);

Table Mapping Configuration

use Kotchasan\Database;

Database::config([
    'default' => [
        // ... connection configuration
    ],
    'tables' => [
        'users' => 'members',
        'posts' => 'articles'
    ]
]);

Database Connections

Creating Database Instances

use Kotchasan\Database;

// Use default connection
$db = Database::create();

// Use specific connection
$analyticsDb = Database::create('analytics');
$cacheDb = Database::create('cache');

Connection Status Check

use Kotchasan\Database;

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

if ($connection && $connection->isConnected()) {
    echo "Database connected successfully";
} else {
    echo "Failed to connect to database";
}

Query Building

SELECT Queries

use Kotchasan\Database;

$db = Database::create();

// Basic SELECT
$users = $db->select('id', 'name', 'email')
    ->from('users')
    ->where(['status', 'active'])
    ->orderBy('name')
    ->fetchAll();

// Advanced SELECT
use Kotchasan\Database\Sql;

$userPosts = $db->select(
        'u.name',
        'p.title',
        'p.created_at',
        [Sql::COUNT('p.id'), 'post_count']
    )
    ->from('users u')
    ->leftJoin('posts p', 'p.user_id = u.id')
    ->where(['u.status', 'active'])
    ->where(['p.published', 1])
    ->groupBy('u.id')
    ->having('COUNT(p.id)', '>', 0)
    ->orderBy('u.name')
    ->limit(10)
    ->fetchAll();

INSERT Queries

// Single INSERT
$userId = $db->insert('users')
    ->values([
        'name' => 'John Doe',
        'email' => 'john@example.com',
        'password' => password_hash('secret123', PASSWORD_DEFAULT),
        'status' => 'active',
        'created_at' => date('Y-m-d H:i:s')
    ])
    ->execute();

// Multiple INSERT
$db->insert('users')
    ->rows([
        ['name' => 'Jane Smith', 'email' => 'jane@example.com'],
        ['name' => 'Bob Johnson', 'email' => 'bob@example.com'],
        ['name' => 'Alice Brown', 'email' => 'alice@example.com']
    ])
    ->execute();

UPDATE Queries

use Kotchasan\Database\Sql;

// Basic UPDATE
$db->update('users')
    ->set([
        'name' => 'John Doe (Updated)',
        'updated_at' => date('Y-m-d H:i:s')
    ])
    ->where(['id', 1])
    ->execute();

// Update with calculation
$db->update('accounts')
    ->set([
        'balance' => Sql::raw('balance - 1000')
    ])
    ->where(['id', 1])
    ->execute();

DELETE Queries

// Basic DELETE
$db->delete('users')
    ->where(['status', 'inactive'])
    ->where(['last_login', '<', date('Y-m-d', strtotime('-1 year'))])
    ->execute();

// DELETE with LIMIT
$db->delete('logs')
    ->where(['level', 'debug'])
    ->orderBy('created_at', 'ASC')
    ->limit(1000)
    ->execute();

Transaction Management

Basic Transactions

use Kotchasan\Database;
use Kotchasan\Database\Sql;

$db = Database::create();
$db->beginTransaction();

try {
    // Money transfer
    $db->update('accounts')
        ->set(['balance' => Sql::raw('balance - 1000')])
        ->where(['id', 1])
        ->execute();

    $db->update('accounts')
        ->set(['balance' => Sql::raw('balance + 1000')])
        ->where(['id', 2])
        ->execute();

    // Log transaction
    $db->insert('transactions')
        ->values([
            'from_account' => 1,
            'to_account' => 2,
            'amount' => 1000,
            'type' => 'transfer',
            'created_at' => date('Y-m-d H:i:s')
        ])
        ->execute();

    $db->commit();
    echo "Transfer successful";

} catch (Exception $e) {
    $db->rollback();
    echo "Error occurred: " . $e->getMessage();
}

Transaction Helper

$result = $db->transaction(function ($db) {
    // Create new user
    $userId = $db->insert('users')
        ->values([
            'name' => 'New User',
            'email' => 'newuser@example.com'
        ])
        ->execute();

    // Create profile
    $db->insert('user_profiles')
        ->values([
            'user_id' => $userId,
            'bio' => 'New system user',
            'avatar' => 'default.jpg'
        ])
        ->execute();

    return $userId;
});

echo "Created user ID: " . $result;

Caching

Configure Query Cache

use Kotchasan\Database;

Database::configureCache([
    'driver' => 'file',
    'path' => ROOT_PATH.DATA_FOLDER.'cache/'
], 3600); // default TTL 1 hour

Cache Query Results

$popularPosts = $db->select('*')
    ->from('posts')
    ->where(['views', '>', 1000])
    ->orderBy('views', 'DESC')
    ->limit(10)
    ->cache(1800) // cache for 30 minutes
    ->fetchAll();

Manual Cache Invalidation

$query = $db->select('*')
    ->from('posts')
    ->where(['views', '>', 1000])
    ->cache(1800);

$posts = $query->fetchAll();

$queryCache = Database::getQueryCache();
if ($queryCache) {
    $queryCache->invalidate($query);
}

Performance Optimization

Using Indexes

$explain = $db->raw("EXPLAIN SELECT * FROM users WHERE email = ?", ['user@example.com']);

foreach ($explain as $row) {
    if ($row['key'] === null) {
        echo "Should create index for email column";
    }
}

Table Optimization

use Kotchasan\Database;

$connection = Database::getConnection('default');
if ($connection) {
    $connection->optimizeTable('users');
    $connection->optimizeTable('posts');
    $connection->emptyTable('temp_data');
}

Batch Operations

$users = [];
for ($i = 1; $i <= 1000; $i++) {
    $users[] = [
        'name' => "User {$i}",
        'email' => "user{$i}@example.com",
        'created_at' => date('Y-m-d H:i:s')
    ];
}

$batches = array_chunk($users, 100);
foreach ($batches as $batch) {
    $db->insert('users')->rows($batch)->execute();
}

Real-World Examples

User Management System

use Kotchasan\Database;

class UserManager
{
    private $db;

    public function __construct()
    {
        $this->db = Database::create();
    }

    public function createUser($userData)
    {
        return $this->db->transaction(function ($db) use ($userData) {
            $existing = $db->select('id')
                ->from('users')
                ->where(['email', $userData['email']])
                ->first();

            if ($existing) {
                throw new Exception('Email already exists');
            }

            $userId = $db->insert('users')
                ->values([
                    'name' => $userData['name'],
                    'email' => $userData['email'],
                    'password' => password_hash($userData['password'], PASSWORD_DEFAULT),
                    'status' => 'active',
                    'created_at' => date('Y-m-d H:i:s')
                ])
                ->execute();

            $db->insert('user_profiles')
                ->values([
                    'user_id' => $userId,
                    'bio' => '',
                    'avatar' => 'default.jpg',
                    'created_at' => date('Y-m-d H:i:s')
                ])
                ->execute();

            return $userId;
        });
    }

    public function getUserWithProfile($userId)
    {
        return $this->db->select('u.*', 'p.bio', 'p.avatar')
            ->from('users u')
            ->leftJoin('user_profiles p', 'p.user_id = u.id')
            ->where(['u.id', $userId])
            ->first();
    }

    public function getActiveUsers($page = 1, $limit = 20)
    {
        $offset = ($page - 1) * $limit;

        return $this->db->select('u.*', 'p.avatar')
            ->from('users u')
            ->leftJoin('user_profiles p', 'p.user_id = u.id')
            ->where(['u.status', 'active'])
            ->orderBy('u.created_at', 'DESC')
            ->limit($limit, $offset)
            ->fetchAll();
    }
}

Reporting System

use Kotchasan\Database;
use Kotchasan\Database\Sql;

class ReportManager
{
    private $db;

    public function __construct()
    {
        $this->db = Database::create();
    }

    public function getUserRegistrationReport($startDate, $endDate)
    {
        return $this->db->select(
                'DATE(created_at) as date',
                [Sql::COUNT('*'), 'registrations']
            )
            ->from('users')
            ->where(['created_at', '>=', $startDate])
            ->where(['created_at', '<=', $endDate])
            ->groupBy('DATE(created_at)')
            ->orderBy('date')
            ->fetchAll();
    }

    public function getPopularPosts($limit = 10)
    {
        return $this->db->select(
                'p.*',
                'u.name as author_name',
                [Sql::COUNT('c.id'), 'comment_count']
            )
            ->from('posts p')
            ->leftJoin('users u', 'u.id = p.user_id')
            ->leftJoin('comments c', 'c.post_id = p.id')
            ->where(['p.status', 'published'])
            ->groupBy('p.id')
            ->orderBy('p.views', 'DESC')
            ->limit($limit)
            ->cache(1800)
            ->fetchAll();
    }
}

Best Practices

1. Security

// Always use parameter binding
$user = $db->select('*')
    ->from('users')
    ->where(['email', $email])
    ->first();

// Avoid direct raw SQL
// Bad
$result = $db->raw("SELECT * FROM users WHERE email = '{$email}'");

// Good
$result = $db->raw("SELECT * FROM users WHERE email = ?", [$email]);

2. Error Handling

use Kotchasan\Exception\DatabaseException;

try {
    $result = $db->select('*')->from('users')->fetchAll();
} catch (DatabaseException $e) {
    error_log("Database Error: " . $e->getMessage());
    error_log("SQL: " . $e->getQuery());
    error_log("Bindings: " . print_r($e->getBindings(), true));

    throw new Exception('System error occurred. Please try again.');
}

3. Connection Usage

use Kotchasan\Database;

$mainDb = Database::create('default');
$analyticsDb = Database::create('analytics');
$cacheDb = Database::create('cache');

4. Query Optimization

$users = $db->select('id', 'name', 'email')
    ->from('users')
    ->where(['status', 'active'])
    ->where(['created_at', '>', $lastWeek])
    ->orderBy('id')
    ->limit(100)
    ->fetchAll();

5. Efficient Cache Usage

$categories = $db->select('*')
    ->from('categories')
    ->where(['status', 'active'])
    ->orderBy('sort_order')
    ->cache(3600)
    ->fetchAll();

$db->update('categories')
    ->set(['name' => 'New Name'])
    ->where(['id', 1])
    ->execute();

$queryCache = Database::getQueryCache();
if ($queryCache) {
    $queryCache->invalidateTable('categories');
}

Summary

The Kotchasan database system is flexible and secure. Using QueryBuilder with proper bindings, caching, and transactions will keep your application reliable and maintainable.

For more details, see:

Did you spot an improvement?

Enhance the documentation by contributing updates or translations.

Visit project repository