PHP code example of monkeyscloud / monkeyslegion-database

    
        
<?php
require_once('vendor/autoload.php');

/* Start to develop here. Best regards https://php-download.com/ */

    

monkeyscloud / monkeyslegion-database example snippets

use MonkeysLegion\Database\Connection\ConnectionManager;

// From a config array — the simplest setup
$manager = ConnectionManager::fromArray([
    'default' => [
        'driver'   => 'mysql',
        'host'     => '127.0.0.1',
        'port'     => 3306,
        'database' => 'myapp',
        'username' => 'root',
        'password' => 'secret',
    ],
]);

// Get the default connection (lazy — no PDO until first use)
$conn = $manager->connection();

// Execute queries
$conn->execute('INSERT INTO users (name) VALUES (:name)', [':name' => 'Alice']);
$stmt = $conn->query('SELECT * FROM users WHERE id = :id', [':id' => 1]);
$user = $stmt->fetch();

use MonkeysLegion\Database\Config\{DatabaseConfig, DsnConfig};
use MonkeysLegion\Database\Connection\Connection;
use MonkeysLegion\Database\Types\DatabaseDriver;

$dsn = new DsnConfig(
    driver: DatabaseDriver::MySQL,
    host: 'localhost',
    port: 3306,
    database: 'myapp',
);

$config = new DatabaseConfig(
    name: 'primary',
    driver: DatabaseDriver::MySQL,
    dsn: $dsn,
    username: 'root',
    password: 'secret',
);

$conn = new Connection($config);
$conn->connect();

// PDO is available
$pdo = $conn->pdo();

$manager = ConnectionManager::fromArray([
    'test' => [
        'driver' => 'sqlite',
        'memory' => true,
    ],
]);

$conn = $manager->connection();
$conn->execute('CREATE TABLE tests (id INTEGER PRIMARY KEY, name TEXT)');

$manager = ConnectionManager::fromArray([
    'primary' => [
        'driver'   => 'mysql',
        'host'     => 'primary-db.internal',
        'database' => 'myapp',
        'username' => 'root',
        'password' => 'secret',
        'read'     => [
            'strategy' => 'round_robin',  // round_robin | random | least_connections
            'sticky'   => true,           // reads go to primary after a write
            'replicas' => [
                ['host' => 'replica-1.internal', 'database' => 'myapp'],
                ['host' => 'replica-2.internal', 'database' => 'myapp'],
            ],
        ],
    ],
]);

// Reads go to a replica
$users = $manager->read()->query('SELECT * FROM users');

// Writes go to primary
$manager->write()->execute('INSERT INTO users (name) VALUES (:n)', [':n' => 'Bob']);

// After a write with sticky enabled, reads also go to primary
$manager->markWritePerformed();
$freshUser = $manager->read()->query('SELECT * FROM users WHERE id = :id', [':id' => 1]);

// Reset sticky at request boundary
$manager->resetSticky();

$conn = $manager->connection();

// Callback-based (recommended) — auto commit/rollback
$result = $conn->transaction(function ($c) {
    $c->execute('INSERT INTO orders (user_id, total) VALUES (:u, :t)', [':u' => 1, ':t' => 99.99]);
    $c->execute('UPDATE inventory SET stock = stock - 1 WHERE product_id = :p', [':p' => 42]);
    return 'order_placed';
});

// Manual control
use MonkeysLegion\Database\Types\IsolationLevel;

$conn->beginTransaction(IsolationLevel::RepeatableRead);
try {
    $conn->execute('...');
    $conn->commit();
} catch (\Throwable $e) {
    $conn->rollBack();
    throw $e;
}

$conn = $manager->connection(); // No database connection yet
echo $conn->getName();          // "default" — no connection needed
echo $conn->getDriver()->label(); // "MySQL / MariaDB" — still no connection

$conn->query('SELECT 1');       // NOW the connection is established

use MonkeysLegion\Database\Config\PoolConfig;

$manager = ConnectionManager::fromArray([
    'default' => [
        'driver'   => 'mysql',
        'host'     => 'localhost',
        'database' => 'myapp',
        'username' => 'root',
        'password' => 'secret',
        'pool'     => [
            'min_connections'      => 2,
            'max_connections'      => 20,
            'idle_timeout'         => 300,   // seconds
            'max_lifetime'         => 3600,  // seconds
            'health_check_interval' => 30,
            'validate_on_acquire'  => true,
        ],
    ],
]);

$stats = $manager->stats();
foreach ($stats as $name => $poolStats) {
    echo "{$name}: {$poolStats->active} active, {$poolStats->idle} idle\n";
    echo "  Utilization: " . round($poolStats->utilization() * 100) . "%\n";
    echo "  Exhausted: " . ($poolStats->isExhausted() ? 'YES' : 'no') . "\n";
}

use MonkeysLegion\Database\Connection\ConnectionPool;
use MonkeysLegion\Database\Config\{DatabaseConfig, DsnConfig, PoolConfig};

$pool = new ConnectionPool($config, new PoolConfig(minConnections: 3, maxConnections: 20));
$pool->warmUp(); // opens 3 connections eagerly

use MonkeysLegion\Database\Exceptions\{
    DuplicateKeyException,
    ForeignKeyViolationException,
    DeadlockException,
    ConnectionLostException,
    SyntaxException,
};

try {
    $conn->execute('INSERT INTO users (email) VALUES (:e)', [':e' => '[email protected]']);
} catch (DuplicateKeyException $e) {
    // Unique constraint violated
    echo "Duplicate on: {$e->constraintName}, column: {$e->duplicateColumn}";
    echo "SQL: {$e->debugSql}"; // Interpolated SQL for debugging

} catch (ForeignKeyViolationException $e) {
    echo "FK: {$e->constraintName} → {$e->referencedTable}";

} catch (DeadlockException $e) {
    if ($e->canRetry) {
        // Safe to retry — deadlocks are always retryable
        retry($e->retryAttempt + 1);
    }

} catch (ConnectionLostException $e) {
    echo "Lost after {$e->uptimeBeforeLoss}s, retryable: " . ($e->retryable ? 'yes' : 'no');

} catch (SyntaxException $e) {
    // Never retryable
    echo "SQL error near '{$e->nearToken}' at position {$e->errorPosition}";
}

use MonkeysLegion\Database\Support\SchemaIntrospector;

// Get all tables
$tables = SchemaIntrospector::listTables($conn);
// ['users', 'orders', 'products', ...]

// Check table existence (case-insensitive, underscore-tolerant)
$exists = SchemaIntrospector::tableExists($conn, 'users'); // true

// Resolve actual table name (handles case/underscore differences)
$name = SchemaIntrospector::resolveTableName($conn, 'orderItems'); // 'order_items'

// Get columns for a table
$columns = SchemaIntrospector::listColumns($conn, 'users');
// ['id', 'email', 'name', ...]

// Check if a specific column exists
$has = SchemaIntrospector::columnExists($conn, 'users', 'email'); // true

// Results are statically cached — repeated calls are free
SchemaIntrospector::clearCache(); // flush after migrations

use MonkeysLegion\Database\Config\{DatabaseConfig, DsnConfig, PoolConfig, ReadReplicaConfig};
use MonkeysLegion\Database\Types\DatabaseDriver;

$config = new DatabaseConfig(
    name: 'primary',
    driver: DatabaseDriver::MySQL,
    dsn: new DsnConfig(
        driver: DatabaseDriver::MySQL,
        host: 'db.example.com',
        port: 3306,
        database: 'production',
        charset: 'utf8mb4',
    ),
    username: 'appuser',
    password: 'secret',
    pdoOptions: [
        PDO::ATTR_TIMEOUT => 10,
    ],
    timezone: 'UTC',
    pool: new PoolConfig(
        minConnections: 2,
        maxConnections: 20,
    ),
);

// Or from array (backward-compatible)
$config = DatabaseConfig::fromArray('primary', [
    'driver'   => 'mysql',
    'host'     => 'db.example.com',
    'port'     => 3306,
    'database' => 'production',
    'username' => 'appuser',
    'password' => 'secret',
    'timezone' => 'UTC',
    'pool'     => ['max_connections' => 20],
]);

$conn->execute('INSERT INTO users (name) VALUES (:n)', [':n' => 'Alice']);
$id = $conn->lastInsertId();   // '1'

// PostgreSQL — pass the sequence name
$pgId = $conn->lastInsertId('users_id_seq');

use MonkeysLegion\Database\Support\RetryHandler;
use MonkeysLegion\Database\Support\RetryConfig;

$result = RetryHandler::withRetry(
    operation: function () use ($conn): int {
        return $conn->execute(
            'UPDATE inventory SET stock = stock - 1 WHERE id = :id',
            [':id' => 42],
        );
    },
    config: new RetryConfig(
        maxAttempts: 4,
        baseDelayMs: 20,
        multiplier:  2.0,
        maxDelayMs:  500,
        jitter:      true,
    ),
);

use Monolog\Logger;
use Monolog\Handler\StreamHandler;

$log = new Logger('db');
$log->pushHandler(new StreamHandler('php://stderr'));

// On ConnectionManager — set hook propagates to all open connections
$manager->logger = $log;

// On a single Connection directly
$conn->logger = $log;

use MonkeysLegion\Database\Contracts\ConnectionEventDispatcherInterface;

$manager->eventDispatcher = $myDispatcher; // propagates to all open connections
$conn->eventDispatcher    = $myDispatcher; // or on a single connection

use MonkeysLegion\Database\Support\HealthChecker;

$result = HealthChecker::check($conn);

echo "Healthy: " . ($result->healthy ? 'yes' : 'no') . "\n";
echo "Latency: {$result->latencyMs}ms\n";
if ($result->reason) {
    echo "Reason: {$result->reason}\n";
}

$conn = $manager->connection();

// Check state
$conn->isConnected();  // false — lazy, not connected yet
$conn->isAlive();      // false

// Force connection
$conn->connect();
$conn->isConnected();  // true
$conn->isAlive();      // true

// Property hooks
$conn->queryCount;     // 0
$conn->uptimeSeconds;  // 0.001...

$conn->query('SELECT 1');
$conn->queryCount;     // 1

// Reconnect (new PDO, reset counters)
$conn->reconnect();
$conn->queryCount;     // 0

// Disconnect
$conn->disconnect();
$conn->uptimeSeconds;  // 0.0

$manager = ConnectionManager::fromArray([
    'mysql'    => ['driver' => 'mysql', 'host' => 'mysql-host', 'database' => 'app', ...],
    'postgres' => ['driver' => 'pgsql', 'host' => 'pg-host', 'database' => 'analytics', ...],
    'sqlite'   => ['driver' => 'sqlite', 'memory' => true],
]);

// Access by name
$app = $manager->connection('mysql');
$analytics = $manager->connection('postgres');
$cache = $manager->connection('sqlite');

// Switch default
$manager->setDefaultConnection('postgres');
$conn = $manager->connection(); // now returns postgres

// Disconnect specific
$manager->disconnect('mysql');

// Disconnect all
$manager->disconnectAll();

use MonkeysLegion\Database\Contracts\ConnectionManagerInterface;
use MonkeysLegion\Database\Connection\ConnectionManager;

// Register
ConnectionManagerInterface::class => fn() => ConnectionManager::fromArray(
    RE id = :id',
        [':id' => $id],
    );
    return $stmt->fetch() ?: [];
}