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 becoming an 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 - Connection pooling + 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 3 query results with 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

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.

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')
);

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 5 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 3 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 (Repository-Oriented, Non-ORM)

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

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:tests
composer ic:test:code
composer ic:test:static
composer ic:test:security
composer ic:release:guard

Test execution is driver-aware:

SQLite-only environments run the base test set.
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.

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
Composer installs infocyph/DBLayer ^5.1, infocyph/cachelayer ^3.1, and psr/log ^3.0.2
ext-pdo_mysql (for MySQL and MariaDB)
ext-pdo_pgsql (for PostgreSQL)
ext-pdo_sqlsrv (for Microsoft SQL Server)
ext-pdo_sqlite (for SQLite)

Security

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

Maintainers

Package info

Statistics

Security

README

Features

Core Features

Performance

Security

Installation

Quick Start

Basic Configuration

Effective Connection Configuration

Query Controls

Telemetry

On-Demand Database Monitoring

Query Builder

Repository Layer

Collections and Result Caching

Choosing APIs (DB vs QueryBuilder vs Repository)

TableRepository (Repository-Oriented, Non-ORM)

Transactions

Testing

Security

Requirements

Security