MonkeysLegion PHP Framework

A flexible PDO-based database abstraction layer supporting MySQL, PostgreSQL, and SQLite with elegant DSN builders and connection management along with PSR-16 cache adapters.

Features

✅ Multi-Database Support: MySQL, PostgreSQL, SQLite
✅ DSN Builders: Fluent API for building connection strings
✅ Host Fallback: Automatic localhost fallback for unreachable hosts
✅ Connection Health Checks: isConnected() and isAlive() methods
✅ Type Safety: Full enum support and strict typing
✅ Factory Pattern: Flexible connection creation
✅ Docker Ready: Built-in support for containerized environments
✅ High-Performance Caching: Array, FileSystem, and Redis cache adapters
✅ Concurrency Protection: Lock-based stale-while-revalidate pattern
✅ Automatic Cleanup: Intelligent cache maintenance and monitoring
✅ PSR Compatible: Follows PSR-16 caching standards

1 · Installation

composer require monkeyscloud/monkeyslegion-database

Nothing else to install—just PHP 8.4+ and the appropriate PDO extension (pdo_mysql, pdo_pgsql, or pdo_sqlite).

This is the config referance:

return [
    // The default cache driver to use: 'file', 'redis', or 'memory'
    'default' => $_ENV['CACHE_DRIVER'] ?? 'file',
    
    // Available cache drivers. Keys must match the CacheType enum values.
    'drivers' => [
        // File-based cache configuration
        'file' => [
            // Directory for cache files (optional, defaults to system temp)
            'directory' => $_ENV['CACHE_FILE_DIRECTORY'] ?? '/path/to/cache',
            // Optional: auto-cleanup settings, lock expiration, etc.
            // 'auto_cleanup' => [
            //     'enabled' => $_ENV['CACHE_FILE_AUTO_CLEANUP_ENABLED'] ?? true,
            //     'probability' => $_ENV['CACHE_FILE_AUTO_CLEANUP_PROBABILITY'] ?? 1000,
            //     'interval' => $_ENV['CACHE_FILE_AUTO_CLEANUP_INTERVAL'] ?? 3600,
            // ],
            // 'lock_expiration' => $_ENV['CACHE_FILE_LOCK_EXPIRATION'] ?? 30,
        ],
        
        // Redis cache configuration
        'redis' => [
            'host' => $_ENV['CACHE_REDIS_HOST'] ?? '127.0.0.1',
            'port' => $_ENV['CACHE_REDIS_PORT'] ?? 6379,
            // 'auth' => $_ENV['CACHE_REDIS_AUTH'] ?? 'your_password', // optional
            // 'database' => $_ENV['CACHE_REDIS_DATABASE'] ?? 0,           // optional
            // 'timeout' => $_ENV['CACHE_REDIS_TIMEOUT'] ?? 2.0,          // optional
            // 'prefix' => $_ENV['CACHE_REDIS_PREFIX'] ?? 'myapp:',      // optional namespace prefix
        ],
        
         // In-memory array cache (no config needed)
        'memcached' => [
            // No options required
        ],
    ],
];

2 · Creating a Connection

2.1 Direct Connection Creation (Legacy Style)

use MonkeysLegion\Database\MySQL\Connection as MySQLConnection;

$config = [
    'host'     => '127.0.0.1',
    'port'     => 3306,
    'database' => 'monkeys_app',  // Note: changed from 'dbname'
    'username' => 'root',         // Note: changed from 'user'  
    'password' => '',             // Note: changed from 'pass'
    'charset'  => 'utf8mb4',
    'options'  => [
        PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    ],
];

$conn = new MySQLConnection($config);   // lazy-connects on first query

2.2 Using ConnectionFactory (Recommended)

use MonkeysLegion\Database\Factory\ConnectionFactory;

$config = [
    'default' => 'mysql',
    'connections' => [
        'mysql' => [
            'host' => '127.0.0.1',
            'port' => 3306,
            'database' => 'monkeys_app',
            'username' => 'root',
            'password' => '',
            'charset' => 'utf8mb4',
            'options' => [
                PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
                PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
            ]
        ]
    ]
];

// Create connection using the 'default' type from config
$conn = ConnectionFactory::create($config);

// Or specify connection type explicitly
$conn = ConnectionFactory::createByType('mysql', $config);

2.3 Config Keys

Key	Required	Default	Notes
`host`, `database`, `username`	✓	n/a	Standard database creds
`password`	✓	''	Empty string for no password
`port`	✗	3306/5432	Database specific
`charset`	✗	utf8mb4	Sets appropriate charset
`options`	✗	reasonable PDO defaults	Merge/override freely

3 · Everyday API

use MonkeysLegion\Database\ConnectionInterface;

/** @var ConnectionInterface $conn */
$conn = ConnectionFactory::create($config);

// All familiar methods still work
$conn->pdo();           // Returns underlying PDO instance
$conn->run($sql, $params);           // Prepares & executes
$conn->fetchAll($sql, $params);      // Returns all rows
$conn->fetchOne($sql, $params);      // Returns single row
$conn->column($sql, $params);        // Returns single column value

4 · Transactions

$conn->pdo()->beginTransaction();
try {
    $conn->run('INSERT INTO orders ...', $orderData);
    $conn->run('INSERT INTO invoices ...', $invoiceData);
    $conn->pdo()->commit();
} catch (\Throwable $e) {
    $conn->pdo()->rollBack();
    throw $e;
}

5 · Convenience Helpers

// Connection health checks (NEW)
$isConnected = $conn->isConnected();  // Check if PDO instance exists
$isAlive = $conn->isAlive();          // Check if connection is responsive

// Auto-reconnect after "MySQL server has gone away"  
$healthy = $conn->ping();             // Returns true if connection is healthy

// Dump last statement for debugging
$sql = $conn->lastQuery();            // e.g. "SELECT * FROM users WHERE id = ?"

// Simple pagination helper
[$rows, $total] = $conn->paginate(
    'SELECT * FROM posts WHERE status = :status',
    ['status' => 'published'],
    page: 3,
    perPage: 20,
);

6 · Integrating with DI

Legacy Way (Direct Connection)

// Before - single MySQL connection
MonkeysLegion\Database\Connection::class => fn() =>
    new Connection(require base_path('config/database.php')),

New Way (Factory Pattern with Interface)

// Register interface with factory (recommended)
MonkeysLegion\Database\ConnectionInterface::class => fn() =>
    ConnectionFactory::create(require base_path('config/database.php')),

// Or for dynamic switching based on environment
MonkeysLegion\Database\ConnectionInterface::class => function() {
    $config = require base_path('config/database.php');
    $type = env('DB_CONNECTION', 'mysql');
    
    return ConnectionFactory::createByType($type, $config);
},

// You can still register specific implementations if needed
MonkeysLegion\Database\MySQL\Connection::class => fn() =>
    ConnectionFactory::createByType('mysql', require base_path('config/database.php')),

7 · Working with QueryBuilder

use MonkeysLegion\Query\QueryBuilder;

/** @var QueryBuilder $qb */
$qb = new QueryBuilder($conn);  // Accepts any ConnectionInterface implementation

$posts = $qb->select('*')
            ->from('posts')
            ->where('author_id', '=', 42)
            ->orderBy('published_at', 'DESC')
            ->limit(10)
            ->fetchAll(App\Entity\Post::class);

8 · Multi-Database Support

8.1 Supported Databases

use MonkeysLegion\Database\Factory\ConnectionFactory;
use MonkeysLegion\Database\Types\DatabaseType;

$config = [
    'default' => 'mysql',
    'connections' => [
        'mysql' => [
            'host' => 'localhost',
            'database' => 'myapp',
            'username' => 'root',
            'password' => 'secret',
            'charset' => 'utf8mb4'
        ],
        'postgresql' => [
            'host' => 'localhost', 
            'database' => 'myapp',
            'username' => 'postgres',
            'password' => 'secret'
        ],
        'sqlite' => [
            'file' => '/path/to/database.sqlite'
            // or 'memory' => true for in-memory database
        ]
    ]
];

// Create different connections
$mysql = ConnectionFactory::createByType('mysql', $config);
$pgsql = ConnectionFactory::createByType('postgresql', $config); 
$sqlite = ConnectionFactory::createByType('sqlite', $config);

// Or using enums
$mysql = ConnectionFactory::createByEnum(DatabaseType::MYSQL, $config);

8.2 DSN Builders

use MonkeysLegion\Database\DSN\MySQLDsnBuilder;
use MonkeysLegion\Database\DSN\PostgreSQLDsnBuilder; 
use MonkeysLegion\Database\DSN\SQLiteDsnBuilder;

// MySQL DSNs
$dsn = MySQLDsnBuilder::localhost('myapp')->build();
$dsn = MySQLDsnBuilder::docker('myapp', 'db')->build();

// PostgreSQL DSNs
$dsn = PostgreSQLDsnBuilder::localhost('myapp')->build();
$dsn = PostgreSQLDsnBuilder::create()
    ->host('localhost')
    ->port(5432)
    ->database('myapp')
    ->sslMode('require')
    ->build();

// SQLite DSNs  
$dsn = SQLiteDsnBuilder::inMemory()->build();
$dsn = SQLiteDsnBuilder::fromFile('/path/to/db.sqlite')->build();

9 · Cache System

The package includes a high-performance PSR-16 compatible cache system with multiple adapters. All cache adapters implement CacheItemPoolInterface and follow the same consistent API.

9.1 Quick Start - Basic Cache Usage

use MonkeysLegion\Database\Factory\CacheFactory;

// 1. Create cache instance
$config = require base_path('config/cache.php');
$cache = CacheFactory::create($config);

// 2. Basic save and retrieve
$item = $cache->getItem('user_profile_123');
$item->set(['name' => 'John Doe', 'email' => 'john@example.com']);
$item->expiresAfter(3600); // 1 hour
$cache->save($item);

// 3. Retrieve later
$cachedItem = $cache->getItem('user_profile_123');
if ($cachedItem->isHit()) {
    $userData = $cachedItem->get(); // Returns the cached data
    echo $userData['name']; // "John Doe"
} else {
    // Cache miss - regenerate data
    $freshData = fetchUserFromDatabase(123);
    $cachedItem->set($freshData)->expiresAfter(3600);
    $cache->save($cachedItem);
}

9.2 Cache Factory Configuration

// config/cache.php
return [
    'default' => $_ENV['CACHE_DRIVER'] ?? 'file',
    
    'drivers' => [
        'file' => [
            'directory' => $_ENV['CACHE_FILE_DIRECTORY'] ?? '/var/cache/app',
        ],
        
        'redis' => [
            'host' => $_ENV['CACHE_REDIS_HOST'] ?? '127.0.0.1',
            'port' => $_ENV['CACHE_REDIS_PORT'] ?? 6379,
            'auth' => $_ENV['CACHE_REDIS_AUTH'] ?? null,
            'database' => $_ENV['CACHE_REDIS_DATABASE'] ?? 0,
            'prefix' => $_ENV['CACHE_REDIS_PREFIX'] ?? 'app:',
        ],
        
        'memory' => [
            // No configuration needed for array cache
        ],
    ],
];

use MonkeysLegion\Database\Factory\CacheFactory;

// Create cache instances
$cache = CacheFactory::create($config); // Uses 'default' from config
$fileCache = CacheFactory::createByType('file', $config);
$redisCache = CacheFactory::createByType('redis', $config);
$memoryCache = CacheFactory::createByType('memory', $config);

9.3 Complete Cache Usage Patterns

1. Basic Save and Retrieve

// Save data to cache
$item = $cache->getItem('api_response');
$item->set(['data' => $apiData, 'timestamp' => time()]);
$item->expiresAfter(300); // 5 minutes
$cache->save($item);

// Retrieve data
$cachedItem = $cache->getItem('api_response');
if ($cachedItem->isHit()) {
    return $cachedItem->get(); // Returns the cached array
}

// If cache miss, you get a CacheItem that's not a hit
// You can still use it to save new data
$cachedItem->set($freshData)->expiresAfter(300);
$cache->save($cachedItem);

2. Working with Cache Items

// Different expiration methods
$item = $cache->getItem('session_data');
// Or
$item = new \MonkeysLegion\Database\Cache\Items\CacheItem('session_data');

// Expire after seconds
$item->expiresAfter(3600); // 1 hour

// Expire at specific timestamp
$item->expiresAt(new DateTime('tomorrow noon'));

// Never expire (until cache clears)
$item->expiresAfter(null);

// Set the value
$item->set(['user_id' => 123, 'roles' => ['admin']]);

// Save to pool
$cache->save($item);

3. Batch Operations

// Get multiple items at once
$keys = ['user_1', 'user_2', 'user_3'];
$items = $cache->getItems($keys);

foreach ($items as $key => $item) {
    if ($item->isHit()) {
        echo "{$key}: " . json_encode($item->get()) . "\n";
    }
}

// Save multiple items efficiently
$cache->saveDeferred($item1);
$cache->saveDeferred($item2); 
$cache->saveDeferred($item3);
$cache->commit(); // All saved in single operation

// Delete multiple items
$cache->deleteItems(['temp_data_1', 'temp_data_2']);

9.4 Advanced Cache Features

1. Cache Invalidation

// Clear specific items
$cache->deleteItem('user_123');
$cache->deleteItems(['user_123', 'user_456']);

// Clear by prefix (FileSystem and Redis adapters)
$cache->clearByPrefix('user_'); // Removes user_123, user_456, etc.

// Clear everything (use with caution!)
$cache->clear();

// Check if item exists without fetching
$hasItem = $cache->hasItem('some_key');

2. Cache Statistics and Monitoring

// Get performance metrics
$stats = $cache->getStatistics();

echo "Cache Performance:\n";
echo "Hit Ratio: {$stats['hit_ratio']}%\n";
echo "Total Operations: {$stats['total_operations']}\n";
echo "Hits: {$stats['hits']}, Misses: {$stats['misses']}\n";

// Reset for new monitoring period
$cache->resetStatistics();

9.5 Adapter-Specific Features

FileSystem Cache

use MonkeysLegion\Database\Cache\Adapters\FileSystemAdapter;

$cache = new FileSystemAdapter('/path/to/cache');

// Configure automatic cleanup
$cache->configureAutoCleanup(
    enabled: true,
    probability: 100,    // 1% chance to cleanup on each operation
    interval: 3600       // Full cleanup every hour
);

// Manual cleanup
$stats = $cache->runFullCleanup();
echo "Freed {$stats['total_freed_bytes']} bytes\n";

Redis Cache

use MonkeysLegion\Database\Cache\Adapters\RedisCacheAdapter;

$cache = new RedisCacheAdapter(
    RedisCacheAdapter::createConnection('localhost', 6379),
    'app:' // Key prefix
);

// Check Redis health
if ($cache->isConnected()) {
    echo "Redis is healthy\n";
}

// Get Redis server info
$info = $cache->getConnectionInfo();

Array Cache (Memory)

use MonkeysLegion\Database\Cache\Adapters\ArrayCacheAdapter;

$cache = new ArrayCacheAdapter();

// Perfect for testing - no persistence
$item = $cache->getItem('test');
$item->set('value');
$cache->save($item);

// Clear everything (resets the array)
$cache->clear();

10 · Extending

10.1 Query-level events (metrics / logging)

use MonkeysLegion\Database\ConnectionInterface;

// Works with any connection implementing the interface
$conn->on('beforeQuery', static function (string $sql, array $params): float {
    return microtime(true);
});

$conn->on('afterQuery', static function (string $sql, array $params, $start) {
    $t = (microtime(true) - $start) * 1000;
    printf("[db] %.1f ms – %s\n", $t, $sql);
});

10.2 Custom fetch modes (DTOs / JSON columns)

trait JsonAwareFetch
{
    public function fetchAllJson(string $sql, array $params = []): array
    {
        return array_map(
            fn ($row) => new UserSummary(
                id:      (int) $row['id'],
                name:    $row['name'], 
                profile: json_decode($row['profile_json'], true, flags: JSON_THROW_ON_ERROR),
            ),
            $this->fetchAll($sql, $params)  // Uses interface method
        );
    }
}

final class MyMySQLConnection extends MySQLConnection
{
    use JsonAwareFetch;
}

10.3 Read/Write splitting

For high-traffic apps you can point writes to the primary but reads to replicas by overriding pdo():

final class SplitConnection implements ConnectionInterface
{
    private ConnectionInterface $writer;
    private ConnectionInterface $reader;

    public function __construct(array $writerConfig, array $readerConfig)
    {
        // Create *single* connections directly, not via "connections" wrapper
        $this->writer = ConnectionFactory::create($writerConfig);
        $this->reader = ConnectionFactory::create($readerConfig);
    }

    /**
     * Default PDO is the writer (safe choice).
     */
    public function pdo(): PDO
    {
        return $this->writer->pdo();
    }

    /**
     * Explicit read access.
     */
    public function readPdo(): PDO
    {
        return $this->reader->pdo();
    }

    /**
     * READ helper → always replica
     */
    public function fetchAll(string $sql, array $params = [], ?string $into = null): array
    {
        return $this->reader->fetchAll($sql, $params, $into);
    }

    /**
     * WRITE helper → always master
     */
    public function execute(string $sql, array $params = []): int
    {
        return $this->writer->execute($sql, $params);
    }

    /**
     * Proxy every other method to the correct side.
     */
    public function fetch(string $sql, array $params = [], ?string $into = null): mixed
    {
        return $this->reader->fetch($sql, $params, $into);
    }

    public function lastInsertId(): string|int
    {
        return $this->writer->lastInsertId();
    }

    public function beginTransaction(): void
    {
        $this->writer->beginTransaction();
    }

    public function commit(): void
    {
        $this->writer->commit();
    }

    public function rollBack(): void
    {
        $this->writer->rollBack();
    }
}

11 · Connection Management

$connection = ConnectionFactory::create($config);

// Health monitoring
function healthCheck(ConnectionInterface $connection): array
{
    return [
        'connected' => $connection->isConnected(),
        'alive' => $connection->isAlive()
    ];
}

healthCheck($connection); // ['connected' => false, 'alive' => false]

// Get PDO instance (triggers connection)
$pdo = $connection->pdo();

healthCheck($connection); // ['connected' => true, 'alive' => true]

// Disconnect when needed
$connection->disconnect();

12 · Host Fallback

MySQL and PostgreSQL connections automatically fall back to localhost if primary host is unreachable:

// If 'db' host fails, automatically tries 'localhost'
'mysql' => [
    'host' => 'db',  // Docker service name
    'database' => 'myapp',
    'username' => 'root',
    'password' => 'secret'
]

Database