Query Builder

Note

Not part of core. Install it separately:

composer require kinetis/query-builder

A thin, parameterized SQL query builder over amphp/mysql/amphp/postgres — not an ORM. No relationships, no migrations, no change-tracking, no save()-on-a-model. It builds parameterized SQL and maps result rows into typed DTOs via Routing & Validation’s Hydrator — the same mechanism that hydrates a #[Body] request DTO.

use Kinetis\QueryBuilder\Query;

$orders = new Query($db)
    ->table('orders')
    ->where('customer_id', '=', $customerId)
    ->where('status', '!=', 'cancelled')
    ->orderBy('created_at', 'desc')
    ->limit(20)
    ->get(OrderRow::class);

MySQL and Postgres

Query works with either backend through the same shared Amp\Sql\SqlLink family both drivers implement, auto-detected from the concrete connection you pass in:

new Query($mysqlDb);    // MySqlDialect
new Query($postgresDb); // PostgresDialect
new Query($db, new PostgresDialect()); // explicit override

A different database: named connections

Query takes whatever connection you hand it — including one built for a named connection via Kinetis\Persistence\SqlConnectionFactory (see Persistence, Configuration):

use Kinetis\Persistence\SqlConnectionFactory;

$reporting = SqlConnectionFactory::fromConfig($config, 'db2');
$orders = new Query($reporting)->table('orders')->get(OrderRow::class);

Identifier quoting (backtick vs double-quote) and retrieving a generated primary key after an INSERT (MySQL exposes it on the result; Postgres needs RETURNING) are isolated in a small Dialect interface. Everything else — parameterized ? placeholders, LIMIT n OFFSET m, affected-row counts — is identical between the two.

A qualified column name is quoted per segment: orders.total becomes `orders`.`total` (or "orders"."total" on Postgres), not one literal identifier containing a dot.

Works inside TransactionGuard

Query accepts a plain connection pool or an in-flight Amp\Sql\SqlTransaction — both satisfy the same interface:

$transactions->transaction($db, function ($db) use ($data) {
    new Query($db)->table('orders')->insert([...]);
    new Query($db)->table('inventory')
        ->where('sku', '=', $data->sku)
        ->update(['stock' => $newStock]);
});

See Persistence for TransactionGuard’s commit/rollback behavior.

Reading: get(), first(), count()

$rows = new Query($db)->table('users')->where('active', '=', true)->get();       // list<array<string, mixed>>
$rows = new Query($db)->table('users')->where('active', '=', true)->get(UserRow::class); // list<UserRow>
$user = new Query($db)->table('users')->where('id', '=', $id)->first(UserRow::class);    // UserRow|null
$total = new Query($db)->table('orders')->where('status', '=', 'paid')->count();          // int

Pass a DTO class and each row is hydrated through Hydrator::hydrate(), constraints included (#[Email], #[MinLength], …); omit it and you get plain arrays.

Pagination: paginate(), cursorPaginate()

Two ways to page through a result set, returning a plain value object a controller can hand straight back — it encodes to JSON exactly like any other readonly DTO, with no extra step:

#[Get('/orders')]
public function index(#[Query] int $page = 1, #[Query] int $perPage = 20): Paginator
{
    return new Query($this->db)->table('orders')->orderBy('id')->paginate($perPage, $page);
}

GET /orders?page=2&perPage=20

{
    "data": [{"id": 21, "...": "..."}, {"...": "..."}],
    "currentPage": 2,
    "perPage": 20,
    "total": 145,
    "lastPage": 8
}

paginate(int $perPage, int $page = 1, ?string $dtoClass = null) runs a count() for total and a limit()/offset()-based get() for the page itself — both against the same where()/join() filters already on the query. A page past the last one returns an empty data array with the real total/lastPage still reported, not an error.

Cursor-based pagination advances by the last row’s own column value instead of a page number, so rows inserted or deleted between requests can’t shift results the way an offset-based page number can — a better fit for a large or fast-changing table:

#[Get('/orders')]
public function index(#[Query] ?string $cursor = null): CursorPaginator
{
    return new Query($this->db)->table('orders')->cursorPaginate(perPage: 20, cursor: $cursor);
}

GET /orders, then GET /orders?cursor=145

{"data": ["...", "..."], "nextCursor": "165", "hasMore": true}

cursorPaginate(int $perPage, ?string $cursor, string $cursorColumn = 'id', ?string $dtoClass = null) orders the query by $cursorColumn itself and filters WHERE $cursorColumn > $cursor once a cursor is given — null (the first call) fetches from the start. The cursor is the column’s own raw value, not an encoded token; nothing here is sensitive, so there’s no reason to obscure it. There’s no total count and no page number — that’s the actual tradeoff for avoiding COUNT(*) on a table where that query would be expensive, and it means a client can’t jump to an arbitrary page, only “give me the next one.”

Warning

cursorPaginate() always orders by $cursorColumn. Adding your own orderBy() call on a different column can make it skip or repeat rows — the WHERE $cursorColumn > ? comparison only makes sense against the column the results are actually ordered by.

Neither method caps $perPage — a request for ?perPage=1000000 is passed straight through. Capping it, if your application needs one, is a normal application-level concern (clamp it in the controller before calling either method), the same way Query doesn’t validate a where() value either.

Describing the item shape in OpenAPI

Paginator/CursorPaginator are the same two classes for every paginated route, regardless of what each one actually holds, so the generated OpenAPI document describes data as a bare object by default — reflecting the return type alone can’t recover what’s inside it. #[PaginatedItem] names it explicitly:

use Kinetis\Http\Attributes\PaginatedItem;

#[Get('/orders')]
#[PaginatedItem(OrderResponse::class)]
public function index(#[Query] int $page = 1, #[Query] int $perPage = 20): Paginator
{
    return new Query($this->db)->table('orders')->orderBy('id')->paginate($perPage, $page);
}

data now describes as an array of OrderResponse’s own schema, deduplicated into components/schemas the same way a nested DTO already is. Purely descriptive — nothing checks that the route actually returns that item type at runtime, the same trust already placed in #[Response(status, description)]’s own status code.

Writing: insert(), insertGetId(), update(), delete()

new Query($db)->table('users')->insert(['email' => $email, 'name' => $name]);

$id = new Query($db)->table('users')->insertGetId(['email' => $email], primaryKey: 'id');

$affected = new Query($db)->table('users')->where('id', '=', $id)->update(['name' => $newName]);

$deleted = new Query($db)->table('users')->where('id', '=', $id)->delete();

update()/delete() return the affected-row count.

Raw SQL

A plain SqlLink/SqlTransaction and $db->execute(...) — see Persistence — bypasses the builder entirely with no special support needed.

For raw fragments inside an otherwise-fluent query:

new Query($db)->table('orders')
    ->selectRaw('COUNT(*) as total, DATE(created_at) as day')
    ->whereRaw('YEAR(created_at) = ?', [2026])
    ->orderByRaw('RAND()')
    ->get();

Danger

whereRaw()’s $params are bound as real parameters, in the exact position their ? appears in $sql — never string-interpolated. Building $sql by concatenating a user-controlled value instead of passing it through $params reintroduces exactly the injection risk parameterized queries exist to prevent.

Parameter order

Structured where() calls, whereIn(), and whereRaw() fragments can all be mixed in one query; their bound values always appear in the same order as the ? placeholders in the generated SQL:

new Query($db)->table('orders')
    ->where('customer_id', '=', 7)
    ->whereRaw('YEAR(created_at) = ?', [2026])
    ->whereIn('status', ['pending', 'paid'])
    ->where('total', '>', 100)
    ->get();
// WHERE `customer_id` = ? AND YEAR(created_at) = ? AND `status` IN (?, ?) AND `total` > ?
// params: [7, 2026, 'pending', 'paid', 100]

Warning

One Query instance is one query. table()/select()/where()/… mutate and accumulate on the same instance — nothing resets between calls. Construct a fresh new Query($link) per query; reusing one instance across separate queries merges their where()s together.

See also

• Persistence — connecting to MySQL/Postgres, TransactionGuard, and caching query results.

• Routing & Validation — more on Hydrator, including nested-DTO support.