README

A high-performance, secure database layer for PHP 8.4+. DBLayer combines a QueryBuilder, reusable repository policies, explicit static table ergonomics, multi-driver execution, and operational controls without requiring a full ORM.

Features

Core Features

Query Builder - Fluent, Laravel-like API
Repository Layer - Reusable table policies, casts, hooks, tenancy, soft deletes, and optimistic locking
TableRepository - Static repository and QueryBuilder ergonomics with explicit infrastructure access
Connection Manager - Tokenized connection pooling, explicit worker warmup, and read replicas
Replica Strategies - random, round_robin, least_latency, weighted
Multi-Driver - MySQL, MariaDB, PostgreSQL, Microsoft SQL Server, SQLite
Security - Multi-layer SQL injection protection
Transactions - Nested transactions with savepoints
Caching - Opt-in CacheLayer 4 query results with scoped identities, tags, and commit-safe invalidation
Profiling - Performance monitoring
System Monitoring - On-demand engine-native status, sessions, long queries, locks, table/index metrics, replication, and maintenance signals
Events - Lifecycle hooks
Telemetry - Query + transaction observability export
Performance diagnostics - Native execution plans and query-shape reports
Pagination - Offset, composite keyset, opaque next/previous cursors, and resumable chunks
Schema & Migrations - Portable type catalog plus explicit driver-specific types, generated/spatial columns, deterministic ledger, dry runs, leases, conditional/stepped execution, rollback/reset/refresh/fresh
Seeding - Explicit transactional seed trees with synchronous nested composition
Relations - Explicit bounded one, many, and many-to-many array projection without ORM behavior
Optional Runwire integration - Host-owned cancellation/deadlines, cooperative retry sleeps, and lazy-runtime propagation without taking over worker lifecycle

Schema UUID/ULID helpers define storage only. Applications may generate portable UUIDv7/ULID values with infocyph/uid, or deliberately configure a driver-specific database expression default. DBLayer does not add an identifier generator to the normal query path.

Performance

Reproducible PHPBench scenarios for relative hot-path comparisons
Connection pooling for reuse
Bounded lazyById() batches and driver-aware unbuffered streaming
Bounded query-log, profiler, telemetry, and local rate-limit state for persistent workers

Security

Automatic parameterization for Query Builder values; explicit raw SQL APIs remain available
Identifier validation & escaping
Operator whitelist
SQL injection pattern detection
Config-driven hardening with TLS policy controls
Rate limiting
Audit logging

Installation

composer require infocyph/dblayer

Quick Start

Basic Configuration

use Infocyph\DBLayer\DB;

// Single connection
DB::addConnection([
    'driver' => 'mysql',
    'host' => 'localhost',
    'port' => 3306,
    'database' => 'myapp',
    'username' => 'root',
    'password' => 'secret',
    'charset' => 'utf8mb4',
    'collation' => 'utf8mb4_unicode_ci',
]);

// Read replicas
DB::addConnection([
    'driver' => 'mysql',
    'read_strategy' => 'round_robin', // random | round_robin | least_latency | weighted
    'read' => [
        ['host' => 'replica1.example.com'],
        ['host' => 'replica2.example.com'],
    ],
    'database' => 'myapp',
    'username' => 'root',
    'password' => 'secret',
]);

Effective Connection Configuration

ConnectionConfig normalizes aliases and applies defaults once. Built-in driver settings are validated before PDO is opened; a recognized setting used with the wrong driver throws instead of being silently ignored.

Scope	Effective keys
All drivers	`database`, `prefix`, `options`, `timeout`, `persistent`, `write`, `read`, replica selection/timing, statement caching, query comments, `sticky`, and SQL `security`
MySQL/MariaDB	`host`, `port`, `username`, `password`, `charset`, `collation`, `unix_socket`, `ssl_ca`, `ssl_cert`, `ssl_key`, `ssl_verify_server_cert`
PostgreSQL	`host`, `port`, `username`, `password`, `charset`, `schema`, `sslmode`
Microsoft SQL Server	`host`, `port`, `username`, `password`, `encrypt`, `trust_server_certificate`, `application_intent`
SQLite	`database`; network, credential, schema, charset, collation, and TLS settings are rejected

MySQL TLS files are translated to Pdo\Mysql::ATTR_SSL_* constructor attributes. collation is applied with the connection initialization command. MySQL does not accept PostgreSQL's sslmode; use the MySQL TLS keys above and set security.require_tls=true when encryption is mandatory.

PostgreSQL charset, schema, timeout, and sslmode are written into the libpq DSN as client_encoding, startup search_path, connect_timeout, and sslmode. Supported SSL modes are disable, allow, prefer, require, verify-ca, and verify-full.

SQL Server uses Microsoft's PDO_SQLSRV driver. encrypt, trust_server_certificate, and application_intent are translated into its connection string; read-replica handles automatically request ReadOnly application intent.

timeout maps to the native connection-time mechanism: PDO timeout attributes for MySQL/SQLite, connect_timeout for PostgreSQL, and LoginTimeout for SQL Server. PDO and native-client versions may still impose driver-specific timeout and persistent-connection semantics.

External Proxies and Database Listeners

DBLayer can target PostgreSQL poolers (PgBouncer/Pgpool-II), MySQL/MariaDB proxies (ProxySQL/MaxScale), and SQL Server availability-group listeners through the normal driver and endpoint configuration. This is expected protocol compatibility; specific proxy modes and HA topologies require deployment tests. DBLayer's pool is local to each PHP worker, and cluster promotion remains owned by the database infrastructure. See the connection deployment guide for PgBouncer session/prepared-statement restrictions, ODBC pooling, the current MultiSubnetFailover limitation, and SQLite file/WAL behavior. The examples guide includes named bootstrap profiles and runnable worker-pooling and SQLite replica-routing demonstrations.

Query Controls

// Per-query timeout budget (milliseconds)
DB::withQueryTimeout(500, function () {
    DB::select('select * from users');
});

// Absolute deadline relative to now (seconds)
DB::withQueryDeadline(0.25, function () {
    DB::select('select 1');
});

// Cooperative cancellation check
DB::withQueryCancellation(
    fn () => false,
    fn () => DB::select('select 1')
);

Persistent Workers and Optional Runwire

Long-running workers should keep one PoolManager per worker generation and use tokenized leases. min_connections creates lazy wrappers; a host-controlled readiness phase can explicitly open distinct database handles:

$manager = DB::poolManager([
    'min_connections' => 2,
    'max_connections' => 10,
]);

$ready = $manager->warmUp('main', target: 2);

When Runwire is installed, the host can lend its active runtime/request/scope to an exclusively owned connection with Connection::withRunwire(). DBLayer composes cancellation/deadlines and lazy iteration with those objects but never starts/stops the host worker or event loop. PDO remains synchronous.

See the DBLayer 6.0 upgrade guide for changed tenancy, cache, transaction, lazy lease, and persistent-worker contracts.

Telemetry

DB::enableTelemetry();

DB::select('select 1');
DB::beginTransaction();
DB::rollBack();

$snapshot = DB::telemetry();      // read buffer
$exported = DB::flushTelemetry(); // read + clear
$shapes = DB::queryShapeReport(); // grouped by parameterized SQL fingerprint

On-Demand Database Monitoring

Monitoring stays under the monitor surface and uses the selected engine's native observational queries. It does not kill sessions, cancel queries, rebuild indexes, vacuum databases, or mutate configuration.

$status = DB::monitor()->status();
$snapshot = DB::monitor()->snapshot();

$slow = DB::monitor()->longRunningQueries(10);
$locks = DB::monitor()->locks();
$indexes = DB::monitor()->indexMetrics();

$analytics = DB::connection('analytics')->monitor()->status();

Detailed monitoring sections can require database-specific privileges. Use snapshot() when partial results are preferable: failing sections are reported in its errors map while other sections continue.

Query Builder

// SELECT
$users = DB::table('users')
    ->select('id', 'name', 'email')
    ->where('active', true)
    ->where('age', '>=', 18)
    ->orderBy('created_at', 'desc')
    ->limit(10)
    ->get();

// INSERT
$id = DB::table('users')->insertGetId([
    'name' => 'John Doe',
    'email' => 'john@example.com',
]);

// UPDATE
DB::table('users')
    ->where('id', $id)
    ->update(['name' => 'Jane Doe']);

// DELETE
DB::table('users')->where('id', $id)->delete();

// Complex queries
$orders = DB::table('orders')->as('o')
    ->joinAs('users', 'u', 'o.user_id', '=', 'u.id')
    ->leftJoinAs('products', 'p', 'o.product_id', '=', 'p.id')
    ->where('o.status', 'completed')
    ->where(function($q) {
        $q->where('o.total', '>', 1000)
          ->orWhere('u.vip', true);
    })
    ->select('o.*')
    ->addSelectAs('u.name', 'user_name')
    ->addSelectAs('p.name', 'product_name')
    ->get();

// Aggregates
$count = DB::table('users')->count();
$total = DB::table('orders')->sum('amount');
$average = DB::table('products')->avg('price');

Repository Layer

use Infocyph\DBLayer\DB;

$users = DB::repository('users');

$all = $users->all();
$one = $users->find(1);
$active = $users->get(fn ($q) => $q->where('active', 1));

Collections and Result Caching

Array results remain the default. Opt into DBLayer collection APIs or bounded lazy transformations explicitly:

$collection = DB::table('users')->where('active', '=', 1)->collect();
$lazy = DB::table('users')->orderBy('id')->lazyCollection(chunkSize: 500);

Query result caching is also explicit. Cache misses are resolved through CacheLayer 4 remember(); writes invalidate conservative table tags only after the surrounding transaction commits.

use Infocyph\CacheLayer\Cache\Cache;

DB::setCache(Cache::memory('application'));

$active = DB::table('users')
    ->where('active', '=', 1)
    ->cacheFor(120)
    ->cacheTags('users')
    ->get();

Transactions, sticky read-after-write state, locking reads, cursors, streaming, unsupported bindings, and unsafe raw shapes bypass shared query caching.

Choosing APIs (DB vs QueryBuilder vs Repository)

Use DB for infrastructure concerns: connections, transactions, retries, telemetry, pooling.
Use DB::table() / QueryBuilder for ad-hoc SQL shaping: joins, CTEs, dynamic filters, reporting.
Use DB::repository() for reusable table-level rules: tenant scope, soft deletes, optimistic locking, hooks, casts.

If the same table rules appear in multiple call sites, move that logic into a repository-oriented class.

TableRepository (Static Queries and Optional Binding)

use Infocyph\DBLayer\Repository\TableRepository;
use Infocyph\DBLayer\Query\QueryBuilder;
use Infocyph\DBLayer\Query\Repository;

final class User extends TableRepository
{
    protected static string $table = 'users';
    protected static ?string $connection = 'main';

    protected static function configureRepository(Repository $repository): Repository
    {
        return $repository->enableSoftDeletes()->setDefaultOrder('id', 'desc');
    }

    protected static function configureQuery(QueryBuilder $query): QueryBuilder
    {
        return $query->where('active', '=', 1);
    }
}

$one = User::find(1);                              // Repository method
$rows = User::where('active', '=', 1)->get();     // Repository-aware query
$stats = DB::stats('main');                        // Infrastructure stays explicit
$reportRows = User::query('reporting')->get();     // Per-call connection override

With InterMix 11.2's enableAutoBind(), a supplied identifier can resolve to a User parameter through the inherited fromBoundValue() method. Direct binding also works without InterMix:

$user = User::fromBoundValue(1);
$user->name = 'Updated'; // memory only
$user->save();          // existing repository write policy
$user->delete();        // existing soft-delete policy

Override protected static string $primaryKey = 'user_key'; for a custom key. Missing or scoped-out rows throw RecordNotFoundException; the host maps that to its HTTP response. Normal static queries build no bound instance or change snapshot. See the binding API and the runnable example.

Transactions

// Automatic transaction
DB::transaction(function() {
    DB::table('accounts')->where('id', 1)->update(['balance' => 900]);
    DB::table('accounts')->where('id', 2)->update(['balance' => 1100]);
    DB::table('transactions')->insert(['amount' => 100]);
});

// Manual transaction
DB::beginTransaction();
try {
    // ... operations
    DB::commit();
} catch (\Exception $e) {
    DB::rollBack();
    throw $e;
}

Testing

composer ic:ci
composer ic:tests
composer ic:test:code
composer ic:test:static
composer ic:test:security
composer ic:release:guard

Test execution is driver-aware:

SQLite runs the base unit and driver-matrix cases. The complete suite also includes mandatory MySQL and PostgreSQL integration tests; configure reachable services and install their PDO extensions before running composer ic:ci or composer ic:release:guard.
MySQL, MariaDB, PostgreSQL, and SQL Server are enabled automatically when their DBLAYER_MYSQL_*, DBLAYER_MARIADB_*, DBLAYER_PGSQL_*, or DBLAYER_MSSQL_* environment variables and matching PDO extensions are available. PHPForge service DSNs (IC_MYSQL_DSN, IC_MARIADB_DSN, IC_POSTGRES_DSN, and IC_MSSQL_DSN) are recognized as CI fallbacks.

So total test count increases when more drivers are available.

Use databases dedicated to testing. For example, after creating dblayer_test and granting the test user access on both servers:

export DBLAYER_MYSQL_HOST=127.0.0.1 DBLAYER_MYSQL_PORT=3306
export DBLAYER_MYSQL_DATABASE=dblayer_test DBLAYER_MYSQL_USERNAME=dblayer
export DBLAYER_MYSQL_PASSWORD=your_test_password
export DBLAYER_PGSQL_HOST=127.0.0.1 DBLAYER_PGSQL_PORT=5432
export DBLAYER_PGSQL_DATABASE=dblayer_test DBLAYER_PGSQL_USERNAME=dblayer
export DBLAYER_PGSQL_PASSWORD=your_test_password
composer ic:ci

Security

DBLayer implements multiple layers of security:

Parameterization - Query Builder values are bound; raw SQL remains explicit
Identifier Validation - Table/column names validated
Operator Whitelist - Only safe operators allowed
Injection Detection - Scans for suspicious patterns
Rate Limiting - Prevents query flooding
Audit Logging - Optional, bounded query logging

Hardening controls:

DB::hardenProduction() sets enabled=true, strict_identifiers=true, require_tls=true.
SecurityMode::OFF is blocked by default (allow explicitly with Security::allowInsecureMode(true)).
security.enabled=false and security.require_tls=false require security.allow_insecure=true.

Requirements

PHP 8.4+
ext-pdo
infocyph/arraykit ^5.3.1, infocyph/cachelayer ^4.0, and psr/log ^3.0.2
infocyph/runwire 2.1.x is optional for host-runtime integration; DBLayer's integration is tested against 2.1.1
infocyph/intermix 11.2+ is optional for automatic repository parameter binding
ext-pdo_mysql (for MySQL and MariaDB)
ext-pdo_pgsql (for PostgreSQL)
ext-pdo_sqlsrv (for Microsoft SQL Server)
ext-pdo_sqlite (for SQLite)

Responsible Disclosure

Do not disclose suspected vulnerabilities in a public issue, discussion or pull request. Follow SECURITY.md and use GitHub private vulnerability reporting.

DBLayer is protected by PHPForge, which provides automated tests, static and taint analysis, dependency auditing, architecture checks and release-readiness gates. Automated controls do not replace responsible disclosure or manual review.

_{Made with ❤️ for the PHP community}
_{MIT Licensed}
Documentation • Security • Code of Conduct • Contributing
🗂️ Bug • Feature • Documentation • Question • CI failure
🔀 General • Bug fix • Feature • Refactor • Performance • Security & reliability • Documentation • Maintenance

infocyph / dblayer

Package info

Security

Statistics

README

Features

Core Features

Performance

Security

Installation

Quick Start

Basic Configuration

Effective Connection Configuration

External Proxies and Database Listeners

Query Controls

Persistent Workers and Optional Runwire

Telemetry

On-Demand Database Monitoring

Query Builder

Repository Layer

Collections and Result Caching

Choosing APIs (DB vs QueryBuilder vs Repository)

TableRepository (Static Queries and Optional Binding)

Transactions

Testing

Security

Requirements

Responsible Disclosure