nexphant/database

Query builder, connection management, pooling, and query coalescing for SQLite, MySQL, and PostgreSQL.

Install

composer require nexphant/database

Connection

Initialize a database connection by passing a configuration array:

use Nexphant\Database\DB;

$db = DB::connect([
    'driver' => 'sqlite',
    'database' => 'storage/app.db',
]);

// Or MySQL connection:
$db = DB::connect([
    'driver' => 'mysql',
    'host' => '127.0.0.1',
    'port' => 3306,
    'database' => 'my_app',
    'username' => 'root',
    'password' => 'secret',
]);

Query Builder

DB::table() provides fluent query builder operations:

// Select records
$users = DB::table('users')->where('active', '=', 1)->get();

// Insert records
DB::table('users')->insert([
    'name' => 'Alice',
    'email' => 'alice@example.com',
]);

// Update records
DB::table('users')->where('id', '=', 1)->update(['name' => 'Bob']);

// Delete records
DB::table('users')->where('id', '=', 1)->delete();

Additional builder methods:

Method	Description
select(array $columns)	Select specific columns.
where, orWhere, whereIn, whereNull, whereNotNull	Add predicates and bindings.
join, leftJoin	Add table joins.
groupBy, having, orderBy, limit, offset	Shape grouped and paginated result sets.
get, first, count, exists, value, pluck	Read results.
insert, insertGetId, bulkInsert, upsert	Insert rows.
update, increment, decrement, delete	Mutate rows.
cache(float $seconds), coalesce(bool $enabled = true)	Reuse in-flight or cached identical reads.
lock(?string $lock)	Add driver-specific row locks.

Raw Queries

Execute raw SQL statements with parameter bindings:

$rows = DB::query('SELECT * FROM users WHERE id = ?', [1]);
$affected = DB::execute('UPDATE users SET active = ? WHERE id = ?', [1, 42]);
$count = DB::scalar('SELECT COUNT(*) FROM users');

Transactions

Execute operations within a database transaction:

DB::transaction(function () {
    DB::table('orders')->insert([...]);
    DB::table('inventory')->where('item_id', 12)->decrement('stock');
});

Connection Pool

Configure connection pools for high-concurrency worker environments:

DB::configurePool('default', [
    'min' => 2,
    'max' => 10,
    'driver' => 'mysql',
    'host' => '127.0.0.1',
    'database' => 'my_app',
    'username' => 'root',
    'password' => 'secret',
]);

// Execute query using connection pool automatically
$users = DB::table('users')->get();

Pool helpers:

$conn = DB::pool('default');
DB::releaseToPool($conn);

$rows = DB::withPool('default', function ($conn) {
    return $conn->query('SELECT * FROM users')->rows;
});

$stats = DB::poolStats();

Query Coalescing and Cache

Coalescing deduplicates identical in-flight read queries. Cache TTL keeps the result available for subsequent reads.

DB::coalesce(true);
DB::cache(0.5);

$users = DB::table('users')
    ->where('active', '=', 1)
    ->cache(1.0)
    ->get();

DB::flushCoalescing();

Customization & Configuration

nexphant/database provides rich customization options for connection pooling, read/write splitting, automatic reconnection, health checking, and distributed caching.

1. Advanced Connection Pool Configurations

When calling DB::configurePool(), you can customize the pool parameters using the following keys:

Config Key	Type	Default	Description
pool_max	int	10	Maximum connections allowed in the pool.
pool_min_idle	int	2	Minimum idle connections kept alive in the pool.
pool_idle_timeout	int	300	Timeout in seconds after which idle connections are closed.
pool_max_lifetime	int	3600	Maximum age in seconds of a connection before it is recycled.
pool_health_interval	int	30	Interval in seconds between connection health checks.
pool_acquire_timeout	float	5.0	Timeout in seconds to wait for a connection before failing.
pool_max_wait_queue	int	50	Maximum number of concurrent requests waiting for a connection.

DB::configurePool('default', [
    'driver' => 'mysql',
    'host' => '127.0.0.1',
    'database' => 'my_app',
    'pool_max' => 15,
    'pool_min_idle' => 5,
    'pool_idle_timeout' => 600,
    'pool_max_lifetime' => 7200,
    'pool_health_interval' => 15,
    'pool_acquire_timeout' => 3.0,
    'pool_max_wait_queue' => 100,
]);

2. Read/Write Splitting & Replica Load Balancing

The ReadWriteManager automatically routes write queries to the writer database and read queries to the reader replica(s) in a round-robin fashion.

use Nexphant\Database\Connection\ReadWriteManager;

ReadWriteManager::configure('billing', [
    'driver' => 'mysql',
    'username' => 'app',
    'password' => 'secret',
    'database' => 'billing',
    'write' => [
        'host' => 'primary.mysql.local',
    ],
    'read' => [
        ['host' => 'replica-1.mysql.local'],
        ['host' => 'replica-2.mysql.local'],
    ],
]);

// Automatically selects primary for insert, and replica-1 or replica-2 for select
ReadWriteManager::query('INSERT INTO invoices (amount) VALUES (?)', [100.0], 'billing');
$results = ReadWriteManager::query('SELECT * FROM invoices', [], 'billing');

3. Automatic Reconnect & Health Checks

Configure connection resilience rules for persistent runtime environments:

use Nexphant\Database\Connection\AutoReconnect;
use Nexphant\Database\Connection\HealthChecker;

// Set max retries and delays (ms) for automatic query retries on disconnects
AutoReconnect::configure(maxRetries: 5, retryDelayMs: 200);

// Configure the minimum interval (seconds) between query connection health checks
HealthChecker::configure(interval: 60);

// Execute queries with connection failure auto-recovery:
$result = AutoReconnect::execute('default', function($conn) {
    return $conn->query('SELECT * FROM users');
});

4. Distributed Query Cache

Configure the query cache to use APCu or Redis for sharing cached queries across processes:

use Nexphant\Database\Cache\DistributedQueryCache;
use Nexphant\Database\Cache\QueryCompilationCache;
use Nexphant\Database\Cache\SchemaCache;

// Setup distributed query cache (APCu / Redis)
DistributedQueryCache::configure([
    'driver' => 'redis',
    'host' => '127.0.0.1',
    'port' => 6379,
    'prefix' => 'app:db:cache:',
    'ttl' => 120.0, // default TTL in seconds
    'timeout' => 2.5,
    'password' => 'redis_pass', // optional
    'database' => 1,            // optional
]);

// Configure other caches
QueryCompilationCache::configure(maxSize: 1024); // max compiled SQL queries in cache
SchemaCache::configure(ttl: 300.0);             // table schemas cache TTL (seconds)

5. Advanced Driver Engine & Execution Customizations

For granular control over the underlying connection engine, you can configure engine types, initialization pragmas, and post-connection hooks, or execute custom logic directly on the native driver connection.

Engine Selection & Connection Hooks

When using SQLite, choose between native (SQLite3) or pdo (PDO) engines. Configure custom pragmas and run custom setup logic immediately after establishing the connection:

// 1. SQLite Native Engine Connection with custom pragmas and hook
DB::connect([
    'driver' => 'sqlite',
    'engine' => 'native',
    'database' => 'storage/app.db',
    'sqlite_profile' => 'none',
    'sqlite_pragmas' => [
        'foreign_keys' => 'ON',
        'user_version' => 11,
    ],
    'after_connect' => fn(\SQLite3 $db) => $db->exec('PRAGMA application_id = 99'),
], 'my_sqlite_native');

// 2. SQLite PDO Engine Connection with custom pragmas and hook
DB::connect([
    'driver' => 'sqlite',
    'engine' => 'pdo',
    'database' => 'storage/app.db',
    'sqlite_profile' => 'memory',
    'sqlite_pragmas' => [
        'foreign_keys' => null,
        'user_version' => 12,
    ],
    'after_connect' => fn(\PDO $pdo) => $pdo->exec('PRAGMA application_id = 100'),
], 'my_sqlite_pdo');

Accessing the Native Connection

To run operations directly on the underlying native connection object (SQLite3, PDO, etc.), use DB::native():

$sqlite = DB::native('my_sqlite_native'); // Returns \SQLite3 instance
$pdo = DB::native('my_sqlite_pdo');       // Returns \PDO instance

Direct Execution via Callbacks and Batches

DB::exec() offers flexible execution modes. It can accept a callback to interact directly with the native driver, or a batch array of SQL queries to execute sequentially:

// Callback execution (runs directly on the SQLite3/PDO object)
DB::exec(fn(\SQLite3 $db) => $db->exec('PRAGMA user_version = 21'), connection: 'my_sqlite_native');

// Batch array execution (supports query lists and parametrized prepared statements)
DB::exec([
    'CREATE TABLE items (id INTEGER PRIMARY KEY, name TEXT)',
    'INSERT INTO items (name) VALUES (?)' => ['one'],
], connection: 'my_sqlite_pdo');

Migrations

MigrationRunner runs PHP migration files from a directory:

use Nexphant\Database\MigrationRunner;

$runner = new MigrationRunner(base_path('database/migrations'));

$file = $runner->create('create_users_table');
$runner->up();
$runner->down(steps: 1);
$status = $runner->status();

Migration files return up and down callables.

Optimizers and Diagnostics

Bulk helpers reduce SQL round trips for large write sets:

use Nexphant\Database\Optimizer\BulkInsert;
use Nexphant\Database\Optimizer\BatchUpdate;
use Nexphant\Database\Optimizer\QueryPlanAnalyzer;

(new BulkInsert('users'))
    ->columns(['name', 'email'])
    ->rows($rows)
    ->executeInTransaction();

(new BatchUpdate('users'))
    ->key('id')
    ->rows($rows)
    ->execute();

$analysis = QueryPlanAnalyzer::analyze('SELECT * FROM users WHERE email = ?', [$email]);

Drivers

Supported drivers include PDO, SQLite, MySQLi, and PostgreSQL. Native MySQLi/PostgreSQL drivers implement AsyncDriverInterface for event-loop integration when available.

DB::connect(['driver' => 'sqlite', 'database' => 'storage/app.db']);
DB::connect(['driver' => 'mysql', 'host' => '127.0.0.1', 'database' => 'app']);
DB::connect(['driver' => 'pgsql', 'host' => '127.0.0.1', 'database' => 'app']);

Related

• nexphant/core