Multi-DB Drivers

Miko ORM supports multiple database systems through a unified driver interface. Switch between MySQL, PostgreSQL, SQLite, and SQL Server with minimal code changes.

---

Supported Databases

Database	Driver Class	Default Port
MySQL	MySqlDriver	3306
PostgreSQL	PostgreSqlDriver	5432
SQLite	SqliteDriver	N/A
SQL Server	SqlServerDriver	1433

---

Driver Factory

Use DriverFactory to create database drivers dynamically.

use Miko\Database\Drivers\DriverFactory;

// Create driver by type
$driver = DriverFactory::create('mysql', [
    'host' => 'localhost',
    'database' => 'myapp',
    'username' => 'root',
    'password' => ''
]);

// Get PDO connection
$pdo = $driver->connect();

// Get DSN string
$dsn = $driver->getDsn();

---

Driver Interface

All drivers implement DriverInterface:

interface DriverInterface
{
    /**
     * Create PDO connection
     */
    public function connect(): PDO;
    
    /**
     * Get DSN string for connection
     */
    public function getDsn(): string;
    
    /**
     * Get default port for this database
     */
    public function getDefaultPort(): int;
    
    /**
     * Quote identifier (table/column name)
     */
    public function quoteIdentifier(string $identifier): string;
    
    /**
     * Get database-specific SQL for operations
     */
    public function getCreateTableSql(string $table, array $columns): string;
}

---

MySQL Configuration

use Miko\Database\Drivers\MySqlDriver;

$driver = new MySqlDriver([
    'host' => 'localhost',
    'port' => 3306,
    'database' => 'myapp',
    'username' => 'root',
    'password' => 'secret',
    'charset' => 'utf8mb4',
    'collation' => 'utf8mb4_unicode_ci',
    'prefix' => '',
    'strict' => true,
    'engine' => 'InnoDB'
]);

$pdo = $driver->connect();

// DSN: mysql:host=localhost;port=3306;dbname=myapp;charset=utf8mb4

MySQL-Specific Features

// Full-text search
$results = User::whereRaw("MATCH(Name, Bio) AGAINST(? IN BOOLEAN MODE)", ['john*'])->get();

// JSON column queries (MySQL 5.7+)
$users = User::whereRaw("JSON_EXTRACT(settings, '$.theme') = ?", ['dark'])->get();

---

PostgreSQL Configuration

use Miko\Database\Drivers\PostgreSqlDriver;

$driver = new PostgreSqlDriver([
    'host' => 'localhost',
    'port' => 5432,
    'database' => 'myapp',
    'username' => 'postgres',
    'password' => 'secret',
    'schema' => 'public',
    'sslmode' => 'prefer'
]);

$pdo = $driver->connect();

// DSN: pgsql:host=localhost;port=5432;dbname=myapp

PostgreSQL-Specific Features

// Array columns
$users = User::whereRaw("? = ANY(tags)", ['admin'])->get();

// JSONB queries
$users = User::whereRaw("settings->>'theme' = ?", ['dark'])->get();

// Full-text search with tsvector
$results = Article::whereRaw("to_tsvector('english', content) @@ plainto_tsquery('english', ?)", ['search term'])->get();

---

SQLite Configuration

use Miko\Database\Drivers\SqliteDriver;

// File-based database
$driver = new SqliteDriver([
    'database' => '/path/to/database.sqlite'
]);

// In-memory database
$driver = new SqliteDriver([
    'database' => ':memory:'
]);

$pdo = $driver->connect();

// DSN: sqlite:/path/to/database.sqlite

SQLite-Specific Features

// Enable foreign keys (disabled by default)
$pdo->exec('PRAGMA foreign_keys = ON');

// WAL mode for better concurrency
$pdo->exec('PRAGMA journal_mode = WAL');

// JSON functions (SQLite 3.38+)
$users = User::whereRaw("json_extract(settings, '$.theme') = ?", ['dark'])->get();

---

SQL Server Configuration

use Miko\Database\Drivers\SqlServerDriver;

$driver = new SqlServerDriver([
    'host' => 'localhost',
    'port' => 1433,
    'database' => 'myapp',
    'username' => 'sa',
    'password' => 'secret',
    'charset' => 'UTF-8',
    'encrypt' => true,
    'trust_server_certificate' => false
]);

$pdo = $driver->connect();

// DSN: sqlsrv:Server=localhost,1433;Database=myapp

SQL Server-Specific Features

// TOP instead of LIMIT
$users = User::take(10)->get();
// Generates: SELECT TOP 10 * FROM users

// OFFSET FETCH for pagination (SQL Server 2012+)
$users = User::skip(20)->take(10)->get();
// Generates: SELECT * FROM users ORDER BY Id OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY

---

Using with DbContext

class AppDbContext extends DbContext
{
    protected function getConfig(): array
    {
        return [
            'host' => $_ENV['DB_HOST'] ?? 'localhost',
            'port' => (int) ($_ENV['DB_PORT'] ?? 3306),
            'database' => $_ENV['DB_NAME'] ?? 'myapp',
            'username' => $_ENV['DB_USER'] ?? 'root',
            'password' => $_ENV['DB_PASS'] ?? '',
            'charset' => 'utf8mb4'
        ];
    }
}

getConfig() may include 'driver' => 'mysql'|'pgsql'|'sqlite'|'sqlsrv' (MySQL is the default). getConnection() uses the matching DbConfig factory. You can also pass a ready Connection into new AppDbContext($connection) or Model::setConnection($connection).

Environment-Based Configuration

# .env file
DB_DRIVER=mysql
DB_HOST=localhost
DB_PORT=3306
DB_NAME=myapp
DB_USER=root
DB_PASS=secret

---

Connection Pooling

Two pools exist:

1. Named connections — Miko\Database\ConnectionPool\ConnectionPool + Miko\Database\DatabaseManager (see Connection & Pool).
2. Static ORM pool — Miko\Database\ORM\ConnectionPool for one config array:

use Miko\Database\ORM\ConnectionPool;

// Configure pool
ConnectionPool::configure(
    minSize: 5,
    maxSize: 100,
    timeoutSeconds: 30,
    idleTimeoutSeconds: 300
);

// Set database config
ConnectionPool::setConfig([
    'driver' => 'mysql',
    'host' => 'localhost',
    'database' => 'myapp',
    'username' => 'root',
    'password' => ''
]);

// Acquire connection
$connection = ConnectionPool::acquire();

// Use connection
$result = $connection->query("SELECT * FROM users");

// Release back to pool
ConnectionPool::release($connection);

// Or use callback (auto-release)
$users = ConnectionPool::use(function($connection) {
    return $connection->query("SELECT * FROM users")->fetchAll();
});

Pool Status

$status = ConnectionPool::getStatus();

print_r($status);
// [
//     'enabled' => true,
//     'pool_size' => 5,
//     'in_use' => 2,
//     'min_size' => 5,
//     'max_size' => 100,
//     'total_created' => 7,
//     'total_acquired' => 150,
//     'total_released' => 148
// ]

echo ConnectionPool::getStatusString();
// "Pool: 5 available, 2 in use (max: 100), created: 7"

---

Database-Agnostic Queries

Write queries that work across all databases.

// Use QueryBuilder methods instead of raw SQL
$users = User::query()
    ->where('IsActive', true)
    ->where('Age', '>=', 18)
    ->orderBy('Name')
    ->take(10)
    ->get();

// Pagination works on all databases
$result = User::paginate(20, 1);

// Aggregates work on all databases
$count = User::count();
$avg = User::avg('Age');
$sum = Order::sum('TotalAmount');

---

Switching Databases

// Development: SQLite
if ($_ENV['APP_ENV'] === 'development') {
    $driver = 'sqlite';
    $config = ['database' => ':memory:'];
}

// Testing: SQLite
elseif ($_ENV['APP_ENV'] === 'testing') {
    $driver = 'sqlite';
    $config = ['database' => '/tmp/test.sqlite'];
}

// Production: MySQL
else {
    $driver = 'mysql';
    $config = [
        'host' => $_ENV['DB_HOST'],
        'database' => $_ENV['DB_NAME'],
        'username' => $_ENV['DB_USER'],
        'password' => $_ENV['DB_PASS']
    ];
}

$db = new AppDbContext($driver, $config);

---

Best Practices

Practice	Description
Use QueryBuilder	Avoid raw SQL for database portability
Test on target database	Some features are database-specific
Use connection pooling	Improves performance in production
Configure timeouts	Prevent hanging connections
Use transactions	Ensure data integrity across all databases

// Portable code
$users = User::where('IsActive', true)
    ->whereBetween('Age', 18, 65)
    ->orderBy('CreatedDate', 'desc')
    ->paginate(20, 1);

// Database-specific (avoid if possible)
$users = User::whereRaw("YEAR(CreatedDate) = ?", [2024])->get();
// MySQL: YEAR()
// PostgreSQL: EXTRACT(YEAR FROM ...)
// SQLite: strftime('%Y', ...)
// SQL Server: YEAR()