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([...]);

Table prefixes — write `#PREFIX#` yourself¶

The builder does not add the installation's table prefix for you. It substitutes the #PREFIX# token, and only that:

$qb->table('#PREFIX#settings')      // → prefix_settings
$qb->table('settings')              // → settings  (no prefix, ever)

Both forms appear in the framework, because a table that no installation prefixes reads better without the token. But if the table is prefixed — and every table a migration creates through the schema builder is — omitting #PREFIX# produces a query against a name that exists only where the prefix is empty. It works on the developer's machine and finds nothing on the installation that has one.

Rule of thumb: if the raw SQL you are replacing had #PREFIX#, keep it.

The suite cannot catch a missing prefix — a static check does

Both test fixtures declare 'prefix' => '', which makes #PREFIX#users and users the same string. Every test passes either way, so nothing about running the suite tells you a query is missing its prefix.

That is not hypothetical: seventy-nine queries in the framework had lost it, ten of them in User\User — three inside its constructor, so on a prefixed installation simply constructing a user failed. It was reported by an application whose suite produced 97 failures, all Table '….users' doesn't exist, on its first migration attempt. This guide already said what would happen; saying it was not enough.

tests/Unit/Database/TablePrefixInQueriesTest.php now fails on any bare occurrence, in a table() / from() / join position, of a name the framework writes with #PREFIX# anywhere. It derives that list from the source, so a new table following the convention is covered without editing the test.

A configurable table name belongs behind one accessor

User\User computes DB_USERSTABLE into a property and had ten queries naming the table themselves — six lines referenced the resolved name while ten bypassed it. The users, user-details and user-friends tables now go through one private static accessor each, which is also what makes them usable from the class's static methods.

The four friend methods were the last of them, with the harsher version of the same defect: a bare userfriends, no #PREFIX# at all, so they addressed a table that does not exist on a prefixed installation rather than the wrong one. A constant that only some queries honour is worse than no constant: it works until somebody sets it.

Schema-qualified tables¶

authserver.roles is resolved per driver: a PostgreSQL schema, and a prefix-flattened prefix_authserver_roles on MySQL, which has no schemas. This is one of the reasons hand-written SQL against those tables silently matches nothing — see rule 12 in the project rules.

A Model over one resolves the same way. Model::getFullTableName() sends a _dbtable containing a dot through the same resolver, so

class Role extends \Pramnos\Application\Model
{
    protected $_dbtable    = 'authserver.roles';
    protected $_primaryKey = 'roleid';
}

reads and writes the schema on PostgreSQL and prefix_authserver_roles on the MySQL family (MariaDB included). It did not always, and it took three fixes because the Model has three places that name a table:

getFullTableName() did not resolve a dotted name at all, so MySQL got yourdb.authserver.roles — a cross-database reference — and threw.
It then resolved it after prepending the connection's schema on PostgreSQL, so every read and write went to public.authserver.roles. A qualified name is checked first now: it has already said where it lives, and prepending another schema to it can only be wrong.
The column-introspection query asked PostgreSQL for table_schema = 'public' AND table_name = 'authserver.roles'. That matches nothing, an empty column list is not an error anywhere, and _save() went on to build INSERT INTO authserver.roles () VALUES (). There were three copies of that query and only one of them split the name; there is one now, and a save with no writable columns throws instead of composing invalid SQL.

A model over a pramnos.* or authserver.* table needs nothing but the name.

TimescaleDB behaves as PostgreSQL throughout — it is an extension, and a settings type of timescaledb is normalised to postgresql with a timescale flag when the settings are read. Everything downstream compares type == 'postgresql' literally and depends on that.

#PREFIX# still wins over the dot: #PREFIX#some.thing has already said where the prefix goes, and resolving the dot on top of that would rename the table twice.

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

Placeholders: ? or %s, one per binding. This builder's own placeholders are typed — %s string, %i integer, %d float, %b boolean — and a raw fragment may use them directly. A ? is also accepted and is replaced with the placeholder its binding's type calls for, at the position it was written:

$qb->whereRaw('channel_id IN (SELECT id FROM channels WHERE station_id = ?)', [$id]);
// compiles to … station_id = %i, bound in this clause's own position

A ? inside a quoted string (label = 'why?') is left alone, and a fragment with no bindings is never rewritten — whereRaw('enabled = TRUE') and PostgreSQL's jsonb ? key operator both mean what they say.

A count mismatch throws immediately, from the whereRaw() call itself:

$qb->whereRaw('a = ?', [1, 2]);
// InvalidArgumentException: whereRaw() was given 2 binding(s) for 1 placeholder(s) in: a = ?

That is deliberate, and it replaces a silent failure worth knowing about: a mismatch used to leave a literal ? in the statement with one binding too many, the server rejected it, get()/first() returned false, and the only symptom was Attempt to read property "fields" on false in the calling code, several lines from the cause. A statement that cannot be prepared is now also written to the application error log with its SQL, so the false always has a trail.

orWhereRaw() and orHavingRaw() exist for the OR forms; havingRaw() behaves identically for HAVING.

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

Joining on more than one column¶

Pass a closure instead of the column arguments. It receives a JoinClause, and every condition you add to it lands in the same ON:

use Pramnos\Database\JoinClause;

$qb->leftJoin('authserver.user_organizations uo', function (JoinClause $join) {
    $join->on('uo.userid', '=', 'ur.userid')
         ->on('uo.organization_id', '=', 'rd.organization_id');
});
// → LEFT JOIN authserver_user_organizations uo
//      ON uo.userid = ur.userid AND uo.organization_id = rd.organization_id

on() ANDs, orOn() ORs, and on('a.x', 'b.x') means equality. Both sides of a condition are column references: there is no where() on a JoinClause, because a comparison against a value belongs in the query's WHERE, where it is bound as a parameter rather than pasted into the ON.

Aliases on qualified names¶

authserver.roles rd resolves the same way authserver.roles does — a schema on PostgreSQL, a prefixed table on MySQL — and keeps the alias:

$qb->table('authserver.user_roles ur')
   ->join('authserver.roles rd', 'rd.roleid', '=', 'ur.roleid');
// MySQL → FROM authserver_user_roles ur INNER JOIN authserver_roles rd ON …

`joinRaw(string $sql): static`¶

For a join the builder cannot express. Note that a multi-condition ON no longer needs one:

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

joinRaw() takes the SQL as given — the table name is not resolved and nothing is bound, so never build one out of user input.

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', ...]

`getAll()` and `pluck()` answer `[]` for a failed query too — and only on PostgreSQL¶

get() keeps the distinction: false when the query failed, a Result when it succeeded including when it matched nothing. getAll() and pluck() collapse both into [], which is the convenience they exist for.

Which engine you are on decides whether that can hide anything. With throwOnError off — the default — a failed prepare returns false on PostgreSQL and throws on MySQL:

	A missing table, via `getAll()`
PostgreSQL	`[]` — indistinguishable from an empty table
MySQL	throws `mysqli_sql_exception`

So an application developed against one and deployed against the other gets a different failure mode for free.

Where this bites is not the method, it is where the method gets reached for. getAll() is the obvious way to read a list, and the lists whose empty answer is most plausible are the ones where it is most consequential — settings, permissions, bans, allowlists. A ban list that failed to read is an empty ban list, and one cache call later it is a cached empty ban list, outliving the failure that caused it.

That is not hypothetical. A consumer renamed their settings table away and their getGlobalSettings() returned array() without throwing; the answer was cached as the installation's configuration, so every feature toggle sat at its compiled-in default for the whole TTL, with nothing in the logs.

Three ways to keep the distinction, cheapest first:

// 1. get() and check — no new API, works everywhere
$result = $db->queryBuilder()->from('url_blacklist')->get();
if ($result === false) {
    throw new \RuntimeException('blacklist unreadable — refusing to treat it as empty');
}
$patterns = $result->fetchAll();

// 2. getAllOrFail() — the same as getAll(), except a failed query throws QueryException.
//    One exception type on both drivers, so the per-driver split above stops mattering.
$patterns = $db->queryBuilder()->from('url_blacklist')->getAllOrFail();

// 3. connection-wide, when everything in a process should be loud
$db->throwOnError = true;

Reach for (2) on a read whose empty answer would be a decision, and especially when the answer is about to be cached. See How database failures surface for the driver detail.

A `try/catch` around a call that does not throw is a comment¶

Worth naming, because it is what this looks like in code somebody already worried about:

try {
    $patterns = $db->queryBuilder()->from('url_blacklist')->getAll();
} catch (\Throwable $e) {
    // a membership failing open grants somebody another station's tools, so this denies
    return self::DENY;
}

return $this->cache($patterns);   // ← an unreadable table arrives here, as []

getAll() does not throw on PostgreSQL, so the catch is unreachable and the failure walks straight into the branch that caches a miss. The author had decided the right thing and written it down; the decision simply could not run.

A consumer found eight reads of this class in their application and reported that six of them already had a catch written for exactly this failure — one of them arguing the fail-closed direction explicitly, in a comment. Their summary is the useful part: the week's work was less about deciding correct behaviour than about making decisions somebody had already taken actually run.

So when you find a try/catch around a convenience read, treat it as a signal rather than a guard: somebody knew this could fail and which way it should go. Check what the call actually does on failure, and if the answer is "returns []", the fix is getAllOrFail() or get() — the catch was already the right intention.

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

A raw value is emitted, never bound. raw() returns an Expression, and wherever a value is expected the grammar writes the fragment itself in place of a placeholder:

$qb->from('sessions')->where('expires_at', '<', $qb->raw('NOW()'));
// WHERE expires_at < NOW()      ← no placeholder, and nothing bound for it

Scalars beside it are still bound, in the position they were written:

$qb->from('loginlockouts')
   ->where('locktype', 'ip')                       // %s  ← bound
   ->where('lockoutuntil', '>', $qb->raw('NOW()')) // inlined
   ->where('failedattempts', '>=', 3);             // %i  ← bound

That holds for where(), orWhere(), having(), whereIn() and both endpoints of whereBetween(), as well as the insert() / update() / upsert() value maps.

Corrected 2026-08-20. Until this date a raw value in where(), having() or whereBetween() was inlined into the SQL and appended to the bindings, so the statement carried one value more than it had placeholders. Both drivers refused it — PostgreSQL with "bind message supplies 1 parameters, but prepared statement requires 0", MySQL with "bind_param(): Argument #1 (\$types) must not be empty" — which means the DELETE example further down this page did not run as written. The insert() and upsert() paths filtered Expressions out at their own call sites, so the same fragment worked in a value map and threw in a WHERE. The filter now lives in addBinding(), which every clause goes through.

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

Column names with upper-case letters¶

A bare column name containing an upper-case letter is quoted automatically:

$db->queryBuilder()->table('usertokens')
    ->select(['tokenid', 'parentToken'])
    ->where('parentToken', 42)
    ->get();

// PostgreSQL: SELECT tokenid, "parentToken" FROM usertokens WHERE "parentToken" = $1
// MySQL:      SELECT tokenid, `parentToken` FROM usertokens WHERE `parentToken` = ?

You need this because PostgreSQL folds an unquoted identifier to lower case. SELECT parentToken asks for parenttoken, which does not exist, and the query fails. INSERT and UPDATE have always quoted, so before this a camelCase column could be written and not read — and the failure looks exactly like an empty result, because the builder returns nothing either way.

Only a bare identifier is quoted, and only when it has an upper-case letter:

Written	Emitted	Why
`parentToken`	`"parentToken"`	Would otherwise fold
`tokenid`	`tokenid`	Folding cannot affect it
`ut.parentToken`	unchanged	Already qualified — quote it yourself if you need to
`MAX(ut.lastused) AS x`	unchanged	An expression, not an identifier
``, `ut.`	unchanged	Not a name
`"parentToken"`	unchanged	Already quoted

A qualified camelCase column — ut.parentToken in a join — is not quoted for you, because the builder cannot tell a table alias from a schema without parsing the reference. Quote it yourself, per dialect, or select the table with * and read the field from the result:

// Portable: the column name survives in the result either way.
$row = $db->queryBuilder()->table('usertokens')->select('*')
    ->where('tokenid', $id)->first();

$parent = $row->fields['parentToken'];

Applies to select(), where() and its variants, whereIn, whereNull, whereNotNull, whereBetween, groupBy, orderBy and having.

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.

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¶

Table prefixes — write #PREFIX# yourself¶

Schema-qualified tables¶

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(...)¶

Joining on more than one column¶

Aliases on qualified names¶

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¶

getAll() and pluck() answer [] for a failed query too — and only on PostgreSQL¶

A try/catch around a call that does not throw is a comment¶

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¶

Column names with upper-case letters¶

Debugging¶

Conditional Execution with `ifCapable()`¶

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

`distinct(): static`¶

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

Table prefixes — write `#PREFIX#` yourself¶

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

`getAll()` and `pluck()` answer `[]` for a failed query too — and only on PostgreSQL¶

A `try/catch` around a call that does not throw is a comment¶

`insertOrIgnore(array $values): Result`¶

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

`toSql(): string`¶

`getBindings(): array`¶