DbContext & MikoSet

DbContext is the main entry point for database operations, similar to Entity Framework's DbContext. MikoSet provides a fluent interface for querying entity collections.

DbContext Overview

Creating a DbContext

use Miko\Database\ORM\DbContext;
use Miko\Database\ORM\MikoSet;
use Miko\Core\Config;

class AppDbContext extends DbContext
{
    public MikoSet $Users;
    public MikoSet $Products;
    public MikoSet $Orders;
    public MikoSet $Categories;

    protected function getConfig(): array
    {
        return [
            'host' => Config::env('DB_HOST_LOCAL', 'localhost'),
            'port' => (int) Config::env('DB_PORT', 3306),
            'database' => Config::env('DB_DATABASE_LOCAL', 'myapp'),
            'username' => Config::env('DB_USERNAME_LOCAL', 'root'),
            'password' => Config::env('DB_PASSWORD_LOCAL', ''),
            'charset' => 'utf8mb4'
        ];
    }
}

Sets are created from public MikoSet properties. Model class is resolved from the property name (Users → User in App\Models\, Models\, or the global namespace). configure(), setConnection() and onModelCreating() do not exist. MikoSet constructor takes only the model class: new MikoSet(User::class).

Using DbContext

Basic Usage

// Create context instance
$db = new AppDbContext();

// Auto-migration: Create tables if not exist
$db->ensureCreated();

// Access entities via MikoSet
$users = $db->Users->all();
$user = $db->Users->find(1);
$activeUsers = $db->Users->where('IsActive', true)->get();

Database Operations

// Get connection
$connection = $db->getConnection();

// Raw query
$results = $connection->query("SELECT * FROM users WHERE IsActive = 1");

// Scalar value
$count = $connection->scalar("SELECT COUNT(*) FROM users");

MikoSet Methods

MikoSet provides a fluent interface for querying model collections.

Retrieval Methods

// Get all records
$users = $db->Users->all();

// Find by ID
$user = $db->Users->find(1);

// Find or throw exception
$user = $db->Users->findOrFail(1);

// Find multiple by IDs
$users = $db->Users->findMany([1, 2, 3]);

// Get first record
$user = $db->Users->first();

// Get first or throw
$user = $db->Users->firstOrFail();

Query Builder Methods

// Where conditions
$users = $db->Users
    ->where('IsActive', true)
    ->where('Role', 'admin')
    ->get();

// Where with operator
$users = $db->Users
    ->where('Age', '>=', 18)
    ->where('CreatedDate', '>', '2024-01-01')
    ->get();

// Or where
$users = $db->Users
    ->where('Role', 'admin')
    ->orWhere('Role', 'moderator')
    ->get();

// Grouped where / orWhere (parentheses)
$users = $db->Users
    ->where('IsActive', true)
    ->where(function ($q) {
        $q->where('Role', 'admin')
          ->orWhere('Role', 'moderator');
    })
    ->get();

// Where in
$users = $db->Users
    ->whereIn('Id', [1, 2, 3, 4, 5])
    ->get();

// Where between
$users = $db->Users
    ->whereBetween('Age', 18, 65)
    ->get();

// Where null / not null
$users = $db->Users->whereNull('DeletedAt')->get();
$users = $db->Users->whereNotNull('EmailVerifiedAt')->get();

// Where like
$users = $db->Users
    ->whereLike('Name', 'john')
    ->get();

Ordering & Limiting

// Order by
$users = $db->Users
    ->orderBy('Name')
    ->get();

$users = $db->Users
    ->orderBy('CreatedDate', 'desc')
    ->get();

// Latest / Oldest
$latestUser = $db->Users->latest()->first();
$oldestUser = $db->Users->oldest()->first();

// Limit & Offset
$users = $db->Users
    ->take(10)
    ->skip(20)
    ->get();

Aggregates

$count = $db->Users->count();
$activeCount = $db->Users->where('IsActive', true)->count();

$totalSales = $db->Orders->query()->sum('TotalAmount');
$avgAge = $db->Users->query()->avg('Age');
$minPrice = $db->Products->query()->min('Price');
$maxPrice = $db->Products->query()->max('Price');

$hasUsers = $db->Users->query()->exists();
$hasAdmin = $db->Users->where('Role', 'admin')->exists();

Pagination

$result = $db->Users->query()->paginate(10, 1); // per page, page number

echo $result['current_page'];
echo $result['last_page'];
echo $result['total'];

foreach ($result['data'] as $user) {
    echo $user->Name;
}

Chunking

$db->Users->query()->chunk(100, function($users) {
    foreach ($users as $user) {
        // Process user
    }
});

$db->Users->query()->chunkById(100, function($users) {
    foreach ($users as $user) {
        // Process user
    }
});

Model-First Migration

DbContext automatically creates tables based on Model definitions.

Define Model Schema

class User extends Model
{
    use HasTimestamps, SoftDeletes;
    
    protected static string $table = 'users';
    protected string $primaryKey = 'Id';
    
    /**
     * Define table schema for auto-migration
     */
    protected static function defineSchema(TableBuilder $table): void
    {
        $table->id('Id');
        $table->string('Name', 100);
        $table->string('Email', 100)->unique();
        $table->string('Password', 255);
        $table->boolean('IsActive')->default(true);
        $table->string('Role', 50)->default('user');
        $table->integer('Age')->nullable();
        $table->timestamps();  // CreatedDate, UpdatedDate
        $table->softDeletes(); // DeletedAt
    }
}

Auto-Create Tables

$db = new AppDbContext();

// Creates all tables defined in models
$db->ensureCreated();

// Output:
// - Created table: users
// - Created table: products
// - Created table: orders
// - Created table: categories

Transactions

Using DbContext Transactions

use Miko\Database\ORM\Transaction;

$db = new AppDbContext();

Transaction::run(function () use ($db) {
    $user = $db->Users->create([
        'Name' => 'John Doe',
        'Email' => 'john@example.com'
    ]);

    $db->Orders->create([
        'UserId' => $user->Id,
        'TotalAmount' => 100.00
    ]);
});

DbContext has no beginTransaction() / commit() / rollback(). Use Miko\Database\ORM\Transaction. Call Transaction::setConnection($connection) or DbConfig::mysql(...)->connect() first so the helper has a connection.

Using Transaction Helper

use Miko\Database\ORM\Transaction;

Transaction::run(function() use ($db) {
    $user = $db->Users->create(['Name' => 'John']);
    $db->Orders->create(['UserId' => $user->Id]);
});

Complete Example

use Miko\Database\ORM\DbContext;
use Miko\Database\ORM\MikoSet;
use Miko\Core\Config;

class AppDbContext extends DbContext
{
    public MikoSet $Users;
    public MikoSet $Products;
    public MikoSet $Orders;
    public MikoSet $OrderItems;

    protected function getConfig(): array
    {
        return [
            'host' => Config::env('DB_HOST_LOCAL', 'localhost'),
            'port' => (int) Config::env('DB_PORT', 3306),
            'database' => Config::env('DB_DATABASE_LOCAL', 'myapp'),
            'username' => Config::env('DB_USERNAME_LOCAL', 'root'),
            'password' => Config::env('DB_PASSWORD_LOCAL', ''),
            'charset' => 'utf8mb4'
        ];
    }
}

Usage:

use Miko\Database\ORM\Transaction;

$db = new AppDbContext();
$db->ensureCreated();

$user = $db->Users->create([
    'Name' => 'John Doe',
    'Email' => 'john@example.com',
    'Password' => password_hash('secret', PASSWORD_DEFAULT)
]);

$products = $db->Products
    ->where('IsActive', true)
    ->where('Stock', '>', 0)
    ->orderBy('Name')
    ->paginate(20, 1); // per page, page

Transaction::run(function () use ($db, $user, $cartItems) {
    $order = $db->Orders->create([
        'UserId' => $user->Id,
        'OrderNumber' => 'ORD-' . time(),
        'TotalAmount' => 0
    ]);

    $total = 0;
    foreach ($cartItems as $item) {
        $db->OrderItems->create([
            'OrderId' => $order->Id,
            'ProductId' => $item['product_id'],
            'Quantity' => $item['quantity'],
            'Price' => $item['price']
        ]);
        $total += $item['quantity'] * $item['price'];
    }

    $order->TotalAmount = $total;
    $order->save();
});