PHP code example of phpdot / database

    
        
<?php
require_once('vendor/autoload.php');

/* Start to develop here. Best regards https://php-download.com/ */

    

phpdot / database example snippets

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

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

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

$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('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'))

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

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

$db->table('users')->insert(['name' => 'Omar', 'email' => '[email protected]']);
$id = $db->table('users')->insertGetId(['name' => 'Omar']);
$db->table('users')->insertBatch([
    ['name' => 'A', 'email' => '[email protected]'],
    ['name' => 'B', 'email' => '[email protected]'],
]);
$db->table('users')->insertOrIgnore(['email' => '[email protected]', 'name' => 'Skip']);

$db->table('users')->upsert(
    ['email' => '[email protected]', 'name' => 'Omar'],
    ['email'],
    ['name'],
);

$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();

// 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();

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

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

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

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

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

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

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

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

$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,
));

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

$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();