Database Layer

lib/DbQuery.php provides a lightweight query builder over PDO (works with BbPdo for SQLite/MySQL portability). No ORM, no magic — just sugar over prepared statements.

Basic Usage

$db = new DbQuery($pdo);

// SELECT
$user = $db->table('users')->where('id', 42)->first();
$threads = $db->table('threads')
    ->where('status', 'visible')
    ->orderBy('created_at', 'DESC')
    ->limit(10)
    ->get();

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

// UPDATE
$db->table('users')->where('id', 42)->update([
    'email' => 'new@example.com'
]);

// DELETE
$db->table('users')->where('status', 'banned')->delete();

Method Reference

Query Building

Method Description
table($table) Set target table (returns clone)
select(...$cols) Specify columns (default: *)
where($column, $value, $op = '=') Add WHERE clause
whereIn($column, $values) Add WHERE IN clause
whereNull($column) Add column IS NULL
whereNotNull($column) Add column IS NOT NULL
whereBetween($column, $min, $max) Add column BETWEEN min AND max (both bound)
whereRaw($sql, $params) Add raw WHERE clause
join($table, $first, $operator, $second) Add INNER JOIN
leftJoin($table, $first, $operator, $second) Add LEFT JOIN
orderBy($column, $direction = 'ASC') Add ORDER BY
limit($limit) Set LIMIT
offset($offset) Set OFFSET

Identifier and operator allow-lists

Table and column identifiers must match [A-Za-z_][A-Za-z0-9_]* with an
optional .table qualifier. select() additionally accepts *, table.* and
a simple column alias; orderBy() accepts a plain or qualified column.
where(), join() and leftJoin() accept
only these operators: =, !=, <>, <, <=, >, >=, LIKE, NOT LIKE,
IS, IS NOT. Anything else throws InvalidArgumentException
(lib/DbQuery.php:38,55-100,196-252). Expressions and functions inside
select() are rejected; use raw() / rawFirst() for those.

where() also normalises NULL: where('deleted_at', null) becomes
deleted_at IS NULL, and != null / <> null / IS NOT null become
column IS NOT NULL (lib/DbQuery.php:124-145).

Execution

Method Returns
get() array (all rows)
first() ?array (single row or null)
count() int (number of rows the query returns)
exists() bool
pluck($column) array (single column values)
paginate($perPage, $page = 1) array (items, total, per_page, current_page, last_page)

count() runs SELECT COUNT(*) with the query’s joins and WHERE clauses, so
with a join it counts joined rows, not distinct rows of the main table: a
threads LEFT JOIN posts count includes one row per post (and one for a thread
with no posts). Use rawFirst('SELECT COUNT(DISTINCT threads.id) FROM ...')
when a distinct count is needed (lib/DbQuery.php:330-344).

Write Operations

Method Returns
insert(array $data) int (last insert ID)
insertIgnore(array $data) int (SQLite: INSERT OR IGNORE, MySQL: INSERT IGNORE)
update(array $data) int (affected rows)
delete() int (affected rows)

Raw Queries

Method Returns
raw($sql, $params) array
rawFirst($sql, $params) ?array
rawExec($sql, $params) int (affected rows)

Pagination

$page = $db->table('threads')
    ->where('category_id', 5)
    ->orderBy('created_at', 'DESC')
    ->paginate(15, max(1, (int)($_GET['page'] ?? 1)));

// Returns:
// [
//     'items' => [...],
//     'total' => 42,
//     'per_page' => 15,
//     'current_page' => 1,
//     'last_page' => 3
// ]

Immutability

Builder methods (table(), select(), where(), whereIn(), whereNull(),
whereNotNull(), whereBetween(), whereRaw(), join(), leftJoin(),
orderBy(), limit(), offset()) return a clone of the instance, so you
can branch queries:

$baseQuery = $db->table('threads')->where('status', 'visible');

$count = $baseQuery->count();           // doesn't affect $baseQuery
$latest = $baseQuery->orderBy('created_at', 'DESC')->limit(5)->get();

The execution methods do not return a builder: first() returns ?array,
pluck() returns an array of values, and paginate() returns a result array
(lib/DbQuery.php:322-328,463-490). They clone internally and do not mutate the
query you call them on.

Integration with BbPdo

DbQuery accepts any PDO instance. When used with BbPdo, SQL is automatically normalized for the active driver (SQLite ↔ MySQL):

// In setup.php — BbPdo handles driver differences
$pdo = new BbPdo($dsn, $user, $pass);
$db = new DbQuery($pdo);

// This works on both SQLite and MySQL:
$db->table('roles')->insertIgnore(['name' => 'admin', 'permissions' => '{}']);