Pramnos QueryBuilder Guide

The QueryBuilder provides a fluent, dialect-aware interface for constructing SQL queries programmatically. It automatically handles dialect differences between MySQL, PostgreSQL, and TimescaleDB, and supports advanced features like window functions, subqueries, and set operations.

Class: Pramnos\Database\QueryBuilder
Entry point: $db->queryBuilder() — returns a fresh builder bound to the current database connection.

Foundational Concepts

Read/Write Replicas

Applications that scale horizontally typically run one primary database for writes and one or more read replicas for SELECT queries. The Database class maintains separate read and write connections, automatically routing queries based on their type.

Configuration

Add read and write blocks to your settings.php:

'database' => [
    'type'      => 'mysql',
    'write' => [
        'hostname' => 'db-primary.example.com',
        'user'     => 'app_rw',
        'password' => 'secret',
        'database' => 'myapp',
    ],
    'read' => [
        'hostname' => 'db-replica.example.com',
        'user'     => 'app_ro',
        'password' => 'secret',
        'database' => 'myapp',
    ],
    'port'      => 3306,
    'prefix'    => 'pramnos_',
    'collation' => 'utf8mb4_unicode_ci',
]

PostgreSQL / TimescaleDB works identically:

'database' => [
    'type'   => 'postgresql',
    'write'  => ['hostname' => 'pg-primary', 'user' => 'app', 'password' => '...', 'database' => 'myapp'],
    'read'   => ['hostname' => 'pg-replica', 'user' => 'app', 'password' => '...', 'database' => 'myapp'],
    'schema' => 'public',
]

How Routing Works

Database::isWriteQuery(string $sql): bool checks the first SQL keyword. Queries beginning with SELECT, SHOW, EXPLAIN, DESC, or DESCRIBE are treated as reads; everything else as a write.

$db = \Pramnos\Database\Database::getInstance();

// Automatically uses the READ connection
$result = $db->query("SELECT * FROM #PREFIX#users WHERE active = 1");

// Automatically uses the WRITE connection
$db->query("UPDATE #PREFIX#users SET last_login = NOW() WHERE userid = %i", 42);

API Reference

Method	Description
`getConnection(bool $isWrite = false)`	Returns the appropriate live connection, reconnecting if needed
`isConnectionAlive(mixed $connection): bool`	Checks if connection handle is open
`isWriteQuery(string $sql): bool`	Returns `true` if the query's first keyword implies a write operation

BC Note: If read/write config keys are absent, the database behaves as before — a single connection for all queries.

Connection Health & Auto-reconnect

Long-running workers and daemon processes lose database connections when the server closes idle sockets (e.g., MySQL's wait_timeout). Previously this caused silent failures; now Database::query() detects a lost connection and transparently reconnects once before executing.

How It Works

On each query, if the connection is dead, the framework calls tryReconnect() before executing SQL. If reconnect succeeds, the query runs normally. If it fails, the original exception propagates.

API Reference

Method	Description
`tryReconnect(): bool`	Non-fatal reconnect. Returns `true` on success, `false` on failure
`refresh(bool $throwOnFailure = true): bool`	Full reconnect. Throws `RuntimeException` on failure if `$throwOnFailure` is `true`
`isConnectionAlive(mixed $connection): bool`	Low-level check used internally

Usage

For most applications, reconnect is fully automatic — no code changes needed:

// Normal query — transparently reconnects if connection dropped
$result = $db->query('SELECT * FROM users WHERE active = 1');

For long-running daemons that want to pro-actively verify before a critical operation:

// Non-fatal check (returns bool)
if (!$db->tryReconnect()) {
    $logger->warning('Database unavailable, skipping this cycle');
    sleep(5);
    continue;
}

For workers that should abort on connection failure:

// Throws RuntimeException on failure
$db->refresh(throwOnFailure: true);

DatabaseCapabilities — Runtime Detection

Features like JSONB, TimescaleDB hypertables, and spatial indexes are not available on every backend. DatabaseCapabilities detects the connected server's actual capabilities at runtime and provides a clean API to branch on them.

Class: Pramnos\Database\DatabaseCapabilities

Getting Started

$db   = \Pramnos\Database\Database::getInstance();
$caps = new \Pramnos\Database\DatabaseCapabilities($db);

if ($caps->hasTimescaleDB()) {
    // use time_bucket(), hypertable APIs
} elseif ($caps->isPostgreSQL()) {
    // plain PostgreSQL fallback
} else {
    // MySQL fallback
}

Conditional Execution with `ifCapable()`

$caps->ifCapable(
    \Pramnos\Database\DatabaseCapabilities::FEATURE_TIMESCALEDB,
    function () use ($db, $table) {
        // runs only on TimescaleDB
        $db->query("SELECT create_hypertable('%s', 'time')", $table);
    },
    function () {
        // runs on all other backends
    }
);

Feature Constants

Constant	Value	Detected via
`FEATURE_TIMESCALEDB`	`'timescaledb'`	`pg_extension` catalog query
`FEATURE_JSON`	`'json'`	Always true (MySQL 5.7+, all PG versions)
`FEATURE_JSONB`	`'jsonb'`	PostgreSQL only
`FEATURE_FULLTEXT`	`'fulltext'`	MySQL only
`FEATURE_SPATIAL`	`'spatial'`	MySQL with spatial extensions

API Reference

has(string $capability): bool — Returns true if capability is supported
isMySQL(): bool — Returns true for MySQL
isPostgreSQL(): bool — Returns true for PostgreSQL and TimescaleDB
hasTimescaleDB(): bool — Returns true only if TimescaleDB extension is loaded
ifCapable(string $capability, callable $ifTrue, ?callable $ifFalse = null): mixed — Executes callback based on capability
supports(string $capability): bool — Fluent alias for has()

Getting Started

Basic Patterns

$db = \Pramnos\Database\Database::getInstance();

// SELECT with conditions
$activeUsers = $db->queryBuilder()
    ->from('users')
    ->where('active', 1)
    ->orderBy('created_at', 'desc')
    ->limit(10)
    ->get();

while ($activeUsers->fetch()) {
    echo $activeUsers->fields['username'] . "\n";
}

// INSERT
$db->queryBuilder()
    ->table('users')
    ->insert(['username' => 'jane', 'email' => 'jane@example.com']);

// UPDATE
$db->queryBuilder()
    ->table('users')
    ->where('userid', 5)
    ->update(['active' => 0]);

// DELETE
$db->queryBuilder()
    ->from('users')
    ->where('active', 0)
    ->delete();

SELECT Queries

Column Selection

`select(array|string $columns = ['*']): static`

Sets the SELECT column list. Accepts individual strings, comma-separated strings, or an array.

// Select specific columns
$qb->select('userid', 'username', 'email');

// Array format with aliases
$qb->select(['u.userid', 'u.username', 'g.groupname']);

// SQL expressions
$qb->select('COUNT(*) as total');

// Raw expressions
$qb->select($qb->raw("TO_CHAR(created_at, 'YYYY-MM') as month"));

`distinct(): static`

Adds DISTINCT to the SELECT.

$qb->select('country')->distinct()->from('users');
// → SELECT DISTINCT country FROM users

Table & Aliasing

`from(string $table): static` / `table(string $table): static`

Sets the FROM table with optional alias.

$qb->from('users');
$qb->from('users u');           // with alias
$qb->from('users AS u');        // explicit AS

// INSERT/UPDATE/DELETE prefer table()
$qb->table('users')->insert([...]);

WHERE Conditions

`where(string $column, mixed $operator = null, mixed $value = null): static`

Adds a WHERE condition. Supports multiple calling patterns:

// Two-argument: column = value (shorthand)
$qb->where('active', 1);
$qb->where('status', 'pending');

// Three-argument: column operator value
$qb->where('age', '>=', 18);
$qb->where('name', 'ILIKE', '%john%');

// Nested closure (parenthesized group)
$qb->where(function ($q) {
    $q->where('status', 'active')->orWhere('role', 'admin');
});
// → WHERE (status = 'active' OR role = 'admin')

`orWhere(...)`: static`

OR variant. Same calling conventions as where().

$qb->where('role', 'admin')->orWhere('role', 'superuser');
// → WHERE role = 'admin' OR role = 'superuser'

`whereIn(string $column, array $values): static`

$qb->whereIn('userid', [1, 2, 3]);
// → WHERE userid IN (1, 2, 3)

// Negation
$qb->whereIn('status', ['active', 'pending'], 'and', true);
// → WHERE status NOT IN ('active', 'pending')

`whereNull(string $column): static` / `whereNotNull(string $column): static`

$qb->whereNotNull('email');
// → WHERE email IS NOT NULL

`whereBetween(string $column, array $values): static`

$qb->whereBetween('age', [18, 65]);
// → WHERE age BETWEEN 18 AND 65

`whereRaw(string $sql, array $bindings = []): static`

Raw WHERE clause for dialect-specific expressions.

$qb->whereRaw("LOWER(username) = %s", ['johndoe']);
$qb->whereRaw("ST_DWithin(geom, ST_MakePoint(%s, %s)::geography, 1000)", [23.72, 37.98]);
$qb->whereRaw("created_at > NOW() - INTERVAL '7 days'");

`whereExists(Closure $callback): static`

EXISTS subquery condition.

$result = $db->queryBuilder()
    ->from('products')
    ->whereExists(function (\Pramnos\Database\QueryBuilder $sub) {
        $sub->select(['1'])
            ->from('order_items')
            ->whereRaw('order_items.product_id = products.id')
            ->whereRaw("order_items.status = 'pending'");
    })
    ->get();

Joins

`join(string $table, string $first, string $operator, string $second, string $type = 'inner'): static`

$qb->join('orders o', 'o.userid', '=', 'u.userid');
// → INNER JOIN orders o ON o.userid = u.userid

$qb->join('roles r', 'r.roleid', '=', 'u.roleid', 'left');
// → LEFT JOIN roles r ON r.roleid = u.roleid

`leftJoin(...)`, `rightJoin(...)`, `crossJoin(...)`

Convenience methods:

$qb->leftJoin('profiles p', 'p.userid', '=', 'u.userid');
$qb->rightJoin('categories c', 'c.id', '=', 'p.category_id');
$qb->crossJoin('colors');  // CROSS JOIN (no ON clause)

`joinRaw(string $sql): static`

$qb->joinRaw("LEFT JOIN permissions p ON p.userid = u.userid AND p.active = 1");

Ordering & Grouping

`orderBy(string $column, string $direction = 'asc'): static`

$qb->orderBy('created_at', 'desc');
$qb->orderBy('username');           // defaults to 'asc'
$qb->orderBy('id', 'asc')->orderBy('name', 'asc');  // multiple columns

`latest(string $column = 'created_at'): static` / `oldest(...)`

Shortcuts for orderBy(..., 'desc') and orderBy(..., 'asc').

$qb->from('posts')->latest()->limit(10)->get();
// → ORDER BY created_at DESC

`groupBy(string|array $columns): static`

$qb->groupBy('country');
$qb->groupBy(['country', 'city']);

`having(string $column, mixed $operator = null, mixed $value = null): static`

Same calling convention as where().

$qb->groupBy('country')->having('count', '>', 100);
// → GROUP BY country HAVING count > 100

Pagination

`limit(int $value)` / `offset(int $value): static`

$qb->limit(25);
$qb->offset(50);  // page 3 with limit(25)

`forPage(int $page, int $perPage = 15): static`

Shorthand for offset(($page - 1) * $perPage)->limit($perPage).

$result = $db->queryBuilder()
    ->from('products')
    ->orderBy('name')
    ->forPage(3, 20)   // page 3, 20 per page
    ->get();

Execution & Results

`get(): Result`

Compiles and executes the query.

$result = $qb->from('users')->where('active', 1)->get();

`first(): Result`

Adds LIMIT 1 and executes.

$result = $qb->from('users')->where('username', 'jane')->first();
if ($result->numRows > 0) {
    echo $result->fields['email'];
}

`count(): int`

Executes a COUNT(*) aggregate.

$total = $qb->from('users')->where('active', 1)->count();

// Pagination example
$qb = $db->queryBuilder()->from('orders')
    ->where('status', 1)
    ->orderBy('created_at', 'desc')
    ->limit(20)
    ->offset(40);

$total = $qb->count();  // Clones internally, strips ORDER BY/LIMIT/OFFSET
$rows  = $qb->get();

Aggregates: `sum()`, `avg()`, `min()`, `max()`

$total   = $qb->from('orders')->sum('amount');
$average = $qb->from('products')->avg('price');
$cheapest = $qb->from('products')->min('price');
$priciest = $qb->from('products')->max('price');

`exists(): bool` / `doesntExist(): bool`

if ($db->queryBuilder()->from('users')->where('email', $email)->exists()) {
    throw new \RuntimeException('Email already registered');
}

if ($db->queryBuilder()->from('roles')->where('name', 'admin')->doesntExist()) {
    // seed admin role
}

`value(string $column): mixed` / `pluck(string $column): array`

$email = $db->queryBuilder()->from('users')->where('userid', 42)->value('email');

$emails = $db->queryBuilder()->from('users')->where('active', 1)->pluck('email');
// → ['alice@example.com', 'bob@example.com', ...]

Advanced Features

Window Functions

$qb = $db->queryBuilder();
$result = $qb
    ->select([
        'id', 'name', 'category', 'price',
        $qb->over('RANK()', alias: 'price_rank',
            partition: ['category'],
            order: ['price' => 'asc']
        ),
    ])
    ->from('products')
    ->orderBy('category')
    ->get();

Supported functions: RANK(), DENSE_RANK(), ROW_NUMBER(), NTILE(), SUM(), AVG(), MIN(), MAX(), COUNT(), LAG(), LEAD(), FIRST_VALUE(), LAST_VALUE()

Subqueries

// As SELECT column (correlated)
$result = $db->queryBuilder()
    ->select(['userid', 'username'])
    ->selectSub(function ($sub) {
        $sub->select('COUNT(*)')->from('orders')
            ->whereRaw('orders.userid = users.userid');
    }, 'order_count')
    ->from('users')
    ->get();

// As FROM source (derived table)
$result = $db->queryBuilder()
    ->select(['category', 'avg_price'])
    ->fromSub(function ($sub) {
        $sub->select(['category', $sub->raw('AVG(price) AS avg_price')])
            ->from('products')
            ->groupBy('category');
    }, 'cat_avgs')
    ->where('avg_price', '>', 5.00)
    ->get();

Set Operations

// UNION (removes duplicates)
$active = $db->queryBuilder()->select('userid', 'email')->from('users')->where('active', 1);
$admins = $db->queryBuilder()->select('userid', 'email')->from('admin_users');
$result = $active->union($admins)->get();

// UNION ALL (keeps duplicates)
$q1 = $db->queryBuilder()->select('name')->from('buyers');
$q2 = $db->queryBuilder()->select('name')->from('sellers');
$result = $q1->unionAll($q2)->get();

Raw Expressions

$qb->select('userid', $qb->raw("TO_CHAR(created_at, 'YYYY-MM') as month"));
$qb->orderBy($qb->raw('COALESCE(last_login, created_at)'), 'desc');
$qb->update(['last_login' => $qb->raw('NOW()')]);

Conditional Building

$qb = $db->queryBuilder()->from('products');

// Adds WHERE only when $categoryId is set
$result = $qb->when($categoryId, fn($q) => $q->where('category_id', $categoryId))->get();

// With fallback
$result = $qb->when($sortField, 
    fn($q) => $q->orderBy($sortField),
    fn($q) => $q->orderBy('created_at', 'desc')
)->get();

INSERT/UPDATE/DELETE

INSERT

$result = $db->queryBuilder()
    ->table('logs')
    ->insert([
        'message'    => 'User logged in',
        'userid'     => 42,
        'created_at' => $qb->raw('NOW()'),
    ]);

UPDATE

$db->queryBuilder()
    ->table('users')
    ->where('userid', 42)
    ->update(['last_login' => $qb->raw('NOW()')]);

DELETE

$db->queryBuilder()
    ->from('sessions')
    ->where('expires_at', '<', $qb->raw('NOW()'))
    ->delete();

TRUNCATE

$db->queryBuilder()->from('tmp_imports')->truncate();

Atomic Operations

// Increment
$db->queryBuilder()->from('posts')->where('postid', 123)->increment('views');

// Decrement
$db->queryBuilder()->from('wallets')->where('userid', 42)->decrement('balance', 9.99);

PostgreSQL RETURNING

// INSERT and get the new ID
$result = $db->queryBuilder()
    ->table('users')
    ->returning('userid')
    ->insert(['username' => 'jane', 'email' => 'jane@example.com']);

$newId = $result->fields['userid'];

// UPDATE and retrieve modified row
$result = $db->queryBuilder()
    ->table('users')
    ->where('userid', 5)
    ->returning(['userid', 'updated_at'])
    ->update(['active' => 0]);

Insert Variants

`insertOrIgnore(array $values): Result`

$db->queryBuilder()
    ->table('user_subscriptions')
    ->insertOrIgnore(['userid' => 42, 'topic' => 'alerts']);
// Second call with same keys does nothing — no exception

`upsert(array $values, array $conflictColumns, array $updateValues = []): Result`

$db->queryBuilder()
    ->table('user_settings')
    ->upsert(
        ['userid' => 5, 'setting_key' => 'theme', 'setting_value' => 'dark'],
        ['userid', 'setting_key'],   // conflict target
        ['setting_value']            // columns to update on conflict
    );

Batch Processing

Chunked Iteration

$db->queryBuilder()
    ->from('users')
    ->where('active', 1)
    ->orderBy('userid')
    ->chunk(500, function (array $rows, int $page) {
        foreach ($rows as $user) {
            sendWelcomeEmail($user['email']);
        }
        // return false here to stop early
    });

Important: Always include ORDER BY with chunk(). Without deterministic ordering, rows may be skipped or duplicated.

Result Objects

get(), first(), and write operations return a Pramnos\Database\Result instance.

Cursor-based Iteration

$result = $qb->from('logs')->orderBy('logid', 'desc')->get();

while ($result->fetch()) {
    echo $result->fields['message'] . "\n";
}

Fetch All At Once

$rows = $qb->from('users')->get()->fetchAll();
// $rows is a plain PHP array of associative arrays

Properties & Methods

Property / Method	Description
`$result->fields`	Associative array of current row
`$result->numRows`	Total rows in result set
`$result->eof`	`true` once all rows read
`$result->getNumRows()`	Rows count (method form)
`$result->getAffectedRows()`	Rows affected by UPDATE/DELETE
`$result->getInsertId()`	Auto-increment ID from INSERT (MySQL)
`$result->fetchAll()`	All rows as array
`$result->fetch()`	Advance cursor
`$result->free()`	Free resource

Debugging

`toSql(): string`

Returns compiled SQL without executing:

echo $qb->from('users')->where('active', 1)->toSql();
// → SELECT * FROM "users" WHERE "active" = '...'

`getBindings(): array`

Returns bound parameter values:

$bindings = $qb->from('users')->where('active', 1)->getBindings();
// → ['where' => [1], 'join' => [], ...]

Complete Example — Paginated List

$db = \Pramnos\Database\Database::getInstance();

$page    = max(1, (int)($_GET['page'] ?? 1));
$perPage = 20;

$qb = $db->queryBuilder()
    ->select('u.userid', 'u.username', 'u.email', 'g.groupname')
    ->from('users u')
    ->leftJoin('usergroups g', 'g.groupid', '=', 'u.groupid')
    ->where('u.active', 1)
    ->orderBy('u.username')
    ->forPage($page, $perPage);

// count() clones internally — ORDER BY/LIMIT/OFFSET stripped automatically
$total = $qb->count();
$users = $qb->get()->fetchAll();

// Use $users and $total for rendering

Backward Compatibility

QueryBuilder is new and purely additive. The existing Database::query(), Database::prepareQuery(), and Database::execute() methods are unchanged and continue to work exactly as before. No migration required for existing code.

FilesExpand file tree

Pramnos_QueryBuilder_Guide.md

Latest commit

History

Pramnos_QueryBuilder_Guide.md

File metadata and controls

Pramnos QueryBuilder Guide

Foundational Concepts

Read/Write Replicas

Configuration

How Routing Works

API Reference

Connection Health & Auto-reconnect

How It Works

API Reference

Usage

DatabaseCapabilities — Runtime Detection

Getting Started

Conditional Execution with ifCapable()

Feature Constants

API Reference

Getting Started

Basic Patterns

SELECT Queries

Column Selection

select(array|string $columns = ['*']): static

distinct(): static

Table & Aliasing

from(string $table): static / table(string $table): static

WHERE Conditions

where(string $column, mixed $operator = null, mixed $value = null): static

orWhere(...): static`

whereIn(string $column, array $values): static

whereNull(string $column): static / whereNotNull(string $column): static

whereBetween(string $column, array $values): static

whereRaw(string $sql, array $bindings = []): static

whereExists(Closure $callback): static

Joins

join(string $table, string $first, string $operator, string $second, string $type = 'inner'): static

leftJoin(...), rightJoin(...), crossJoin(...)

joinRaw(string $sql): static

Ordering & Grouping

orderBy(string $column, string $direction = 'asc'): static

latest(string $column = 'created_at'): static / oldest(...)

groupBy(string|array $columns): static

having(string $column, mixed $operator = null, mixed $value = null): static

Pagination

limit(int $value) / offset(int $value): static

forPage(int $page, int $perPage = 15): static

Execution & Results

get(): Result

first(): Result

count(): int

Aggregates: sum(), avg(), min(), max()

exists(): bool / doesntExist(): bool

value(string $column): mixed / pluck(string $column): array

Advanced Features

Window Functions

Subqueries

Set Operations

Raw Expressions

Conditional Building

INSERT/UPDATE/DELETE

INSERT

UPDATE

DELETE

TRUNCATE

Atomic Operations

PostgreSQL RETURNING

Insert Variants

insertOrIgnore(array $values): Result

upsert(array $values, array $conflictColumns, array $updateValues = []): Result

Batch Processing

Chunked Iteration

Result Objects

Cursor-based Iteration

Fetch All At Once

Properties & Methods

Debugging

toSql(): string

Conditional Execution with `ifCapable()`

`select(array|string $columns = ['*']): static`

`distinct(): static`

`from(string $table): static` / `table(string $table): static`

`where(string $column, mixed $operator = null, mixed $value = null): static`

`orWhere(...)`: static`

`whereIn(string $column, array $values): static`

`whereNull(string $column): static` / `whereNotNull(string $column): static`

`whereBetween(string $column, array $values): static`

`whereRaw(string $sql, array $bindings = []): static`

`whereExists(Closure $callback): static`

`join(string $table, string $first, string $operator, string $second, string $type = 'inner'): static`

`leftJoin(...)`, `rightJoin(...)`, `crossJoin(...)`

`joinRaw(string $sql): static`

`orderBy(string $column, string $direction = 'asc'): static`

`latest(string $column = 'created_at'): static` / `oldest(...)`

`groupBy(string|array $columns): static`

`having(string $column, mixed $operator = null, mixed $value = null): static`

`limit(int $value)` / `offset(int $value): static`

`forPage(int $page, int $perPage = 15): static`

`get(): Result`

`first(): Result`

`count(): int`

Aggregates: `sum()`, `avg()`, `min()`, `max()`

`exists(): bool` / `doesntExist(): bool`

`value(string $column): mixed` / `pluck(string $column): array`

`insertOrIgnore(array $values): Result`

`upsert(array $values, array $conflictColumns, array $updateValues = []): Result`

`toSql(): string`

`getBindings(): array`