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\Config\DatabaseConfig;

$db = new DatabaseConnection(new DatabaseConfig(
    driver: 'mysql',
    host: 'localhost',
    database: 'myapp',
    username: 'root',
));

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

Architecture

graph TD
    subgraph DatabaseConnection Layer
        DM[DatabaseManager<br/>multi-connection management]
        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

    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 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

Loading

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

Loading

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

Loading

DatabaseConnection Resilience

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

    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 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

Loading

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\Config\DatabaseConfig;
use PHPdot\Pool\Pool;
use PHPdot\Pool\PoolConfig;

$pool = new Pool(
    new DatabaseConnector(new DatabaseConfig(
        driver: 'mysql',
        host: 'localhost',
        database: 'myapp',
    )),
    new PoolConfig(
        minConnections: 2,
        maxConnections: 10,
        validateOnBorrowAfterIdle: 5.0,
    ),
);

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

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::ping() (issues SELECT 1); returns false on any error.
close()	Call DatabaseConnection::close() — idempotent and never throws.

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

$migrator = new Migrator($db, __DIR__ . '/migrations', $logger);
$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
$db->transaction(fn($conn) => ..., maxRetries: 3);

Read/Write Splitting

$db = new DatabaseConnection(new DatabaseConfig(
    driver: 'mysql',
    host: 'primary.db.internal',
    database: 'myapp',
    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.

DatabaseConnection Resilience

Auto-reconnect with exponential backoff. Handles disconnections transparently.

$db = new DatabaseConnection(new DatabaseConfig(
    maxRetries: 3,
    retryDelayMs: 200,
));

Multiple Connections

$manager = new DatabaseManager([
    'default' => new DatabaseConfig(driver: 'mysql', database: 'myapp'),
    'analytics' => new DatabaseConfig(driver: 'pgsql', database: 'analytics'),
]);

$manager->table('users')->get();
$manager->connection('analytics')->table('events')->get();

Package Structure

src/
├── DatabaseConnection.php
├── DatabaseManager.php
├── Config/
│   └── DatabaseConfig.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)
composer test-all    # PHPUnit (all databases)
composer analyse     # PHPStan level 10
composer cs-fix      # PHP-CS-Fixer
composer check       # All three

License

MIT