README

Query builder, schema management, and migrations for PHP. Built on Doctrine DBAL.

Install

composer require phpdot/database

Supports MySQL 5.7+, MariaDB 10.4+, PostgreSQL 12+, SQLite 3.25+.

Quick Start

use PHPdot\Database\DatabaseConnection;
use PHPdot\Database\Connection\MySql\MySqlConfig;
use PHPdot\Database\Connection\Sqlite\SqliteConfig;

$db = new DatabaseConnection(new MySqlConfig(
    database: 'myapp',
    host: '127.0.0.1',
    username: 'root',
));

$users = $db->table('users')->where('active', true)->get();

$cache = new DatabaseConnection(new SqliteConfig(database: ':memory:'));

Each engine has its own typed config carrying only the keys that engine uses — SqliteConfig needs nothing but a path, MySqlConfig and PostgresConfig carry host, port, credentials, and charset with driver-correct defaults (utf8mb4 for MySQL, utf8 for PostgreSQL).

Configuration

The configuration set maps arbitrary connection names to driver-tagged parameter blocks. Heterogeneous engines and several instances of the same engine coexist in one map; the default key names the connection used when none is specified.

// config/database.php
return [
    'default' => 'main',

    'connections' => [
        'main' => [
            'driver' => 'mysql',
            'host' => fn() => getenv('DB_HOST') ?: '127.0.0.1',
            'port' => 3306,
            'database' => 'myapp',
            'username' => 'app',
            'password' => fn() => getenv('DB_PASS') ?: '',
            'charset' => 'utf8mb4',
        ],
        'analytics' => [
            'driver' => 'pgsql',
            'host' => 'analytics.db.internal',
            'database' => 'analytics',
            'username' => 'reporting',
        ],
        'cache' => [
            'driver' => 'sqlite',
            'database' => '/var/data/cache.sqlite',
        ],
    ],
];

PHPdot\Database\Config\DatabaseConfig carries #[Config('database')], so inside a PHPdot application the file above is generated by phpdot/package from the class defaults and hydrated by phpdot/config — closures such as fn() => getenv('DB_HOST') are resolved at load time.

Each block is resolved into its typed per-driver config by PHPdot\Database\Connection\ConnectionFactory, which dispatches on the driver key and validates the block against what that driver actually requires. Misconfiguration fails fast with a message naming the connection and the offending key:

Invalid configuration for database connection 'cache':
Database connection 'cache' (sqlite) requires a non-empty 'database'.

Driver-agnostic behaviour — table prefix, read replicas, sticky routing, reconnection retries, slow-query threshold — is shared by every driver via ConnectionOptions (keys prefix, read, sticky, maxRetries, retryDelayMs, slowQueryThreshold in a block, or the options: argument when constructing a config directly).

Architecture

graph TD
    subgraph Configuration
        DC[DatabaseConfig<br/>default + named connection blocks]
        CF[ConnectionFactory<br/>driver dispatch · fail-fast validation]
        CC[MySqlConfig · PostgresConfig · SqliteConfig<br/>per-driver typed configs]
        CO[ConnectionOptions<br/>prefix · replicas · sticky · retries]
    end

    subgraph DatabaseConnection Layer
        DM[DatabaseManager<br/>lazy name-keyed registry]
        CONN[DatabaseConnection<br/>lazy connect · auto-reconnect · read/write split]
    end

    subgraph Query Engine
        QB[Query Builder<br/>~100 methods · fluent API]
        GR[Grammar<br/>MySQL · PostgreSQL · SQLite]
        JC[JoinClause]
        EX[Expression]
    end

    subgraph Schema Engine
        SB[Schema Builder<br/>create · alter · drop · introspection]
        BP[Blueprint<br/>column types · indexes · foreign keys]
        SG[Schema Grammar<br/>DDL compilation per dialect]
    end

    subgraph Results
        RS[ResultSet<br/>map · filter · pluck · keyBy]
        PG[Paginator<br/>offset-based]
        CP[CursorPaginator<br/>cursor-based]
        TC[TypeCaster<br/>int · bool · float · json · datetime]
    end

    subgraph Migrations
        MG[Migrator<br/>run · rollback · reset · pretend · status]
        MR[MigrationRepository<br/>batch tracking]
    end

    DC --> DM
    DM --> CF
    CF --> CC
    CC --> CO
    CC --> CONN
    DM --> CONN
    CONN --> QB
    CONN --> SB
    CONN --> MG
    QB --> GR
    QB --> JC
    QB --> EX
    SB --> BP
    SB --> SG
    QB --> RS
    QB --> PG
    QB --> CP
    RS --> TC
    MG --> MR
    MG --> SB

    style DC fill:#0891b2,stroke:#0e7490,color:#fff
    style CF fill:#0891b2,stroke:#0e7490,color:#fff
    style CC fill:#0891b2,stroke:#0e7490,color:#fff
    style CO fill:#0891b2,stroke:#0e7490,color:#fff
    style CONN fill:#2563eb,stroke:#1d4ed8,color:#fff
    style DM fill:#2563eb,stroke:#1d4ed8,color:#fff
    style QB fill:#7c3aed,stroke:#6d28d9,color:#fff
    style GR fill:#7c3aed,stroke:#6d28d9,color:#fff
    style JC fill:#7c3aed,stroke:#6d28d9,color:#fff
    style EX fill:#7c3aed,stroke:#6d28d9,color:#fff
    style SB fill:#059669,stroke:#047857,color:#fff
    style BP fill:#059669,stroke:#047857,color:#fff
    style SG fill:#059669,stroke:#047857,color:#fff
    style RS fill:#d97706,stroke:#b45309,color:#fff
    style PG fill:#d97706,stroke:#b45309,color:#fff
    style CP fill:#d97706,stroke:#b45309,color:#fff
    style TC fill:#d97706,stroke:#b45309,color:#fff
    style MG fill:#dc2626,stroke:#b91c1c,color:#fff
    style MR fill:#dc2626,stroke:#b91c1c,color:#fff

Read/Write Splitting

graph LR
    APP[Application] --> CONN[DatabaseConnection]

    CONN -->|SELECT| READ[Read Replica]
    CONN -->|INSERT / UPDATE / DELETE| WRITE[Primary]
    CONN -->|SELECT FOR UPDATE| WRITE
    CONN -->|Inside transaction| WRITE
    CONN -->|After write · sticky| WRITE
    READ -.->|Replica fails · fallback| WRITE

    style APP fill:#334155,stroke:#1e293b,color:#fff
    style CONN fill:#2563eb,stroke:#1d4ed8,color:#fff
    style READ fill:#059669,stroke:#047857,color:#fff
    style WRITE fill:#dc2626,stroke:#b91c1c,color:#fff

Query Lifecycle

graph LR
    A[Builder] -->|compile| B[Grammar]
    B -->|SQL + bindings| C[DatabaseConnection]
    C -->|execute| D[(Database)]
    D -->|rows| E[ResultSet]
    E -->|optional| F[TypeCaster]

    style A fill:#7c3aed,stroke:#6d28d9,color:#fff
    style B fill:#7c3aed,stroke:#6d28d9,color:#fff
    style C fill:#2563eb,stroke:#1d4ed8,color:#fff
    style D fill:#334155,stroke:#1e293b,color:#fff
    style E fill:#d97706,stroke:#b45309,color:#fff
    style F fill:#d97706,stroke:#b45309,color:#fff

DatabaseConnection Resilience

graph TD
    Q[Execute Query] --> TRY{Try}
    TRY -->|Success| RES[Return Result]
    TRY -->|DatabaseConnection Lost| TXN{Inside<br/>transaction?}
    TXN -->|Yes| THROW[Throw — transaction<br/>fails atomically]
    TXN -->|No| DETECT{gone away?<br/>broken pipe?<br/>lost connection?}
    DETECT -->|Yes| RECONN[Reconnect<br/>exponential backoff]
    DETECT -->|No| THROW2[Throw QueryException]
    RECONN --> RETRY{Retry Query}
    RETRY -->|Success| RES
    RETRY -->|Fail| THROW2

    style Q fill:#334155,stroke:#1e293b,color:#fff
    style RES fill:#059669,stroke:#047857,color:#fff
    style THROW fill:#dc2626,stroke:#b91c1c,color:#fff
    style THROW2 fill:#dc2626,stroke:#b91c1c,color:#fff
    style TXN fill:#d97706,stroke:#b45309,color:#fff
    style DETECT fill:#d97706,stroke:#b45309,color:#fff
    style RECONN fill:#2563eb,stroke:#1d4ed8,color:#fff
    style RETRY fill:#7c3aed,stroke:#6d28d9,color:#fff
    style TRY fill:#334155,stroke:#1e293b,color:#fff

A statement is never retried while a transaction is open: reconnecting would silently re-execute it in autocommit mode outside the transaction. The exception propagates instead, so the transaction fails atomically.

Pool Integration

DatabaseConnection instances can be pooled by any phpdot/pool-compatible pool through the bundled DatabaseConnector. The connector implements PHPdot\Contracts\Pool\ConnectorInterface from phpdot/contracts, so phpdot/database does not require phpdot/pool itself — it just declares the interface it satisfies.

use PHPdot\Database\DatabaseConnector;
use PHPdot\Database\Connection\MySql\MySqlConfig;
use PHPdot\Pool\Pool;
use PHPdot\Pool\PoolConfig;

$pool = new Pool(
    new DatabaseConnector(new MySqlConfig(
        database: 'myapp',
        host: '127.0.0.1',
    )),
    new PoolConfig(
        minConnections: 2,
        maxConnections: 10,
        validateOnBorrowAfterIdle: 5.0,
    ),
);

$pool->init();   // pre-create min connections

Under the application config, one pool per named connection comes straight from the factory:

use PHPdot\Database\Connection\ConnectionFactory;

$factory = new ConnectionFactory();
$connector = $factory->connector('main', $config->connections['main']);

Borrow / use / release a connection:

$conn = $pool->borrow();
try {
    $rows = $conn->table('users')->get();
} finally {
    $pool->release($conn);
}

The connector's behavior:

Method	What it does
`connect()`	Build a fresh `DatabaseConnection`, call `ensureConnected()`, return it.
`isAlive()`	Call `DatabaseConnection::reset()` — roll back any leftover-open transaction, clear sticky routing and the query log — then `ping()` (issues `SELECT 1`); returns `false` on any error.
`close()`	Call `DatabaseConnection::close()` — idempotent and never throws.

The reset-before-ping matters for coroutine reuse: pools validate on release and on borrow-after-idle, which is exactly when a connection must be scrubbed so one request's transaction or query log never leaks into the next borrower.

The phpdot/pool package provides validate-on-borrow with a TTL gate, optional validate-on-return, heartbeat, idle cleanup, and metrics — see its README for the full configuration surface.

Query Builder

Select

$db->table('users')->get();                              // all rows
$db->table('users')->select(['name', 'email'])->get();   // specific columns
$db->table('users')->distinct()->get();                  // distinct
$db->table('users')->where('id', 42)->first();           // single row or null
$db->table('users')->where('id', 42)->firstOrFail();     // single row or exception
$db->table('users')->where('id', 42)->value('name');     // single value
$db->table('users')->pluck('email');                     // array of values
$db->table('users')->pluck('email', 'id');               // keyed array
$db->table('users')->count();                            // aggregate
$db->table('users')->sum('balance');
$db->table('users')->avg('age');

Where

->where('status', 'active')                     // column = value
->where('age', '>', 18)                         // with operator
->orWhere('role', 'admin')                      // OR
->whereIn('id', [1, 2, 3])                      // IN
->whereBetween('age', 18, 65)                   // BETWEEN
->whereNull('deleted_at')                        // IS NULL
->whereNotNull('email')                          // IS NOT NULL
->whereColumn('updated_at', '>', 'created_at')   // column vs column
->whereDate('created_at', '=', '2026-01-01')     // date extraction
->whereYear('created_at', '>', '2025')           // year extraction
->whereJsonContains('tags', 'php')               // JSON containment
->whereJsonLength('tags', '>', 3)                // JSON length
->whereLike('name', '%omar%')                    // LIKE
->whereFullText(['title', 'body'], 'search')     // full-text search
->whereRaw('YEAR(created_at) = ?', [2026])       // raw SQL
->whereExists(fn($q) => $q->from('posts')->whereColumn('posts.user_id', '=', 'users.id'))

Nested Where

$db->table('users')
    ->where('active', true)
    ->where(function ($query) {
        $query->where('role', 'admin')
              ->orWhere('role', 'editor');
    })
    ->get();

Joins

$db->table('users')
    ->join('posts', 'users.id', '=', 'posts.user_id')
    ->leftJoin('profiles', 'users.id', '=', 'profiles.user_id')
    ->select(['users.name', 'posts.title'])
    ->get();

Insert

$db->table('users')->insert(['name' => 'Omar', 'email' => 'omar@x.com']);
$id = $db->table('users')->insertGetId(['name' => 'Omar']);
$db->table('users')->insertBatch([
    ['name' => 'A', 'email' => 'a@x.com'],
    ['name' => 'B', 'email' => 'b@x.com'],
]);
$db->table('users')->insertOrIgnore(['email' => 'exists@x.com', 'name' => 'Skip']);

Upsert

$db->table('users')->upsert(
    ['email' => 'omar@x.com', 'name' => 'Omar'],
    ['email'],
    ['name'],
);

Update & Delete

$db->table('users')->where('id', 42)->update(['name' => 'Updated']);
$db->table('users')->where('id', 42)->increment('login_count');
$db->table('users')->where('id', 42)->decrement('balance', 100);
$db->table('users')->where('id', 42)->delete();
$db->table('users')->truncate();

Pagination

// Offset pagination (with total count)
$result = $db->table('users')->orderBy('name')->paginate(page: 2, perPage: 25);
$result->items();
$result->total();
$result->lastPage();
$result->hasMorePages();

// Simple pagination (no COUNT query)
$result = $db->table('users')->orderBy('id')->simplePaginate(page: 3, perPage: 25);

// Cursor pagination (for large tables)
$result = $db->table('users')->orderBy('id')->cursorPaginate(perPage: 25, cursor: $cursor);
$result->nextCursor();

Chunking

$db->table('users')->chunk(100, function ($rows) {
    foreach ($rows as $user) { processUser($user); }
});

foreach ($db->table('users')->lazy(1000) as $user) {
    processUser($user);
}

Type Casting

$db->table('users')
    ->castTypes(['id' => 'int', 'active' => 'bool', 'settings' => 'json', 'created_at' => 'datetime'])
    ->get();

Debug

echo $db->table('users')->where('active', true)->toSql();
// SELECT * FROM `users` WHERE `active` = ?

echo $db->table('users')->where('active', true)->toRawSql();
// SELECT * FROM `users` WHERE `active` = 1

Schema Builder

$db->schema()->create('users', function (Blueprint $table) {
    $table->id();
    $table->string('name');
    $table->string('email')->unique();
    $table->string('password');
    $table->boolean('active')->default(true);
    $table->json('settings')->nullable();
    $table->timestamps();
});

$db->schema()->create('posts', function (Blueprint $table) {
    $table->id();
    $table->unsignedBigInteger('user_id');
    $table->string('title');
    $table->text('body');
    $table->boolean('published')->default(false);
    $table->timestamps();
    $table->softDeletes();

    $table->foreign('user_id')->references('id')->on('users')->cascadeOnDelete();
    $table->index(['published', 'created_at']);
});

Introspection

$db->schema()->hasTable('users');
$db->schema()->hasColumn('users', 'email');
$db->schema()->getColumnListing('users');
$db->schema()->getTables();

Migrations

// 2026_04_03_000001_create_users_table.php
use PHPdot\Database\Migration\Migration;
use PHPdot\Database\Schema\Blueprint;
use PHPdot\Database\Schema\SchemaBuilder;

return new class extends Migration {
    public function up(SchemaBuilder $schema): void {
        $schema->create('users', function (Blueprint $table) {
            $table->id();
            $table->string('name');
            $table->string('email')->unique();
            $table->timestamps();
        });
    }

    public function down(SchemaBuilder $schema): void {
        $schema->dropIfExists('users');
    }
};

use PHPdot\Database\Migration\MigrationRepository;
use PHPdot\Database\Migration\Migrator;

$migrator = new Migrator($db, new MigrationRepository($db));
$migrator->run(__DIR__ . '/migrations');
$migrator->rollback(__DIR__ . '/migrations');
$migrator->status(__DIR__ . '/migrations');
$migrator->pretend(__DIR__ . '/migrations');  // dry-run

Transactions

$db->transaction(function ($conn) {
    $conn->table('accounts')->where('id', 1)->decrement('balance', 100);
    $conn->table('accounts')->where('id', 2)->increment('balance', 100);
});

// With deadlock retry (top-level transactions only)
$db->transaction(fn($conn) => ..., maxRetries: 3);

Read/Write Splitting

use PHPdot\Database\Connection\ConnectionOptions;
use PHPdot\Database\Connection\MySql\MySqlConfig;

$db = new DatabaseConnection(new MySqlConfig(
    database: 'myapp',
    host: 'primary.db.internal',
    options: new ConnectionOptions(
        read: [
            ['host' => 'replica-1.db.internal'],
            ['host' => 'replica-2.db.internal'],
        ],
        sticky: true,
    ),
));

SELECTs go to a random replica. Writes go to primary. After any write with sticky mode, reads also go to primary. Replica entries override only the keys they specify — everything else is inherited from the primary block.

Or config-driven, inside a connection block:

'main' => [
    'driver' => 'mysql',
    'host' => 'primary.db.internal',
    'database' => 'myapp',
    'read' => [
        ['host' => 'replica-1.db.internal'],
        ['host' => 'replica-2.db.internal'],
    ],
    'sticky' => true,
],

DatabaseConnection Resilience

Auto-reconnect with exponential backoff. Handles disconnections transparently — except inside a transaction, where the statement is never retried and the transaction fails atomically.

$db = new DatabaseConnection(new SqliteConfig(
    database: '/var/data/app.sqlite',
    options: new ConnectionOptions(
        maxRetries: 3,
        retryDelayMs: 200,
    ),
));

Multiple Connections

DatabaseManager is a lazy, name-keyed registry over the configuration set. Connections are built on first access and cached; unnamed calls resolve to the default.

use PHPdot\Database\Config\DatabaseConfig;
use PHPdot\Database\DatabaseManager;

$manager = new DatabaseManager(new DatabaseConfig(
    default: 'main',
    connections: [
        'main' => ['driver' => 'mysql', 'host' => '127.0.0.1', 'database' => 'myapp', 'username' => 'app'],
        'analytics' => ['driver' => 'pgsql', 'host' => 'analytics.db.internal', 'database' => 'analytics'],
        'cache' => ['driver' => 'sqlite', 'database' => '/var/data/cache.sqlite'],
    ],
));

$manager->table('users')->get();                            // default connection
$manager->connection('analytics')->table('events')->get();  // named lookup
$manager->connection('cache')->table('entries')->get();     // different engine, same app

An unknown name throws ConnectionException::unknownConnection; a block that fails its driver's validation throws ConnectionException::invalidConfiguration naming the connection and the offending key. Drivers you never use are never loaded.

Package Structure

src/
├── DatabaseConnection.php
├── DatabaseConnector.php
├── DatabaseManager.php
├── Config/
│   └── DatabaseConfig.php
├── Connection/
│   ├── ConnectionConfig.php
│   ├── ConnectionFactory.php
│   ├── ConnectionOptions.php
│   ├── ConfigValue.php
│   ├── MySql/
│   │   └── MySqlConfig.php
│   ├── Postgres/
│   │   └── PostgresConfig.php
│   └── Sqlite/
│       └── SqliteConfig.php
├── Query/
│   ├── Builder.php
│   ├── Expression.php
│   ├── JoinClause.php
│   └── Grammar/
│       ├── Grammar.php
│       ├── MySqlGrammar.php
│       ├── PostgresGrammar.php
│       └── SqliteGrammar.php
├── Schema/
│   ├── SchemaBuilder.php
│   ├── Blueprint.php
│   ├── ColumnDefinition.php
│   ├── ForeignKeyDefinition.php
│   ├── IndexDefinition.php
│   └── Grammar/
│       ├── SchemaGrammar.php
│       ├── MySqlSchemaGrammar.php
│       ├── PostgresSchemaGrammar.php
│       └── SqliteSchemaGrammar.php
├── Migration/
│   ├── Migration.php
│   ├── Migrator.php
│   ├── MigrationRepository.php
│   └── MigrationCreator.php
├── Result/
│   ├── ResultSet.php
│   ├── Paginator.php
│   ├── CursorPaginator.php
│   └── TypeCaster.php
└── Exception/
    ├── DatabaseException.php
    ├── ConnectionException.php
    ├── QueryException.php
    ├── RecordNotFoundException.php
    ├── SchemaException.php
    └── MigrationException.php

Development

composer test        # PHPUnit (SQLite only — excludes mysql/pgsql groups)
composer test-all    # PHPUnit (all databases)
composer analyse     # PHPStan level 10
composer cs-fix      # PHP-CS-Fixer (fix)
composer cs-check    # PHP-CS-Fixer (dry-run)
composer check       # test + analyse + cs-check

License

MIT

phpdot / database

Maintainers

Package info

Statistics

Security

README

Install

Quick Start

Configuration

Architecture

Read/Write Splitting

Query Lifecycle

DatabaseConnection Resilience

Pool Integration

Query Builder

Select

Where

Nested Where

Joins

Insert

Upsert

Update & Delete

Pagination

Chunking

Type Casting

Debug

Schema Builder

Introspection

Migrations

Transactions

Read/Write Splitting

DatabaseConnection Resilience

Multiple Connections

Package Structure

Development

License