Building a Query Builder from Scratch in PHP

1. Why build a custom query builder at all

A query builder is an object that assembles SQL statements step by step through method calls, instead of concatenating them as raw strings. The naive approach of appending WHERE conditions to a SQL string with the . operator works for simple cases, but quickly becomes unreadable and error prone once dynamic filters, optional conditions and changing sort orders come into play. A query builder solves exactly this problem by keeping the structure of the query internally as an object graph and translating it into SQL only at the very end.

The second reason to build a custom query builder is educational: once you understand how a query builder internally manages WHERE clauses, parameter bindings and joins, Doctrine, Laravel Eloquent Builder or the Symfony QueryBuilder become much easier to understand as well. These libraries are themselves just mature, well tested variants of the same underlying pattern. In small projects without an ORM, a lean, custom query builder is also often the more pragmatic choice than pulling in a heavyweight dependency for a couple dozen queries.

An important distinction: a query builder is not an ORM. It does not model object relationships and knows nothing about entities, it only produces SQL strings with bound parameters. This deliberate restriction makes a query builder considerably easier to implement and debug than a full ORM, because it always remains obvious which SQL is actually executed in the end.

2. Architecture basics: fluent interface and method chaining

The central design pattern of a query builder is the fluent interface: every method returns $this, so calls can be chained, for example $builder->select(...)->from(...)->where(...). Internally the query builder only collects state in private arrays and properties, without generating SQL immediately. Only a final method such as toSql() or get() translates the accumulated state into an executable statement.

This separation between state accumulation and SQL generation is critical for testability and extensibility. A query builder that concatenates SQL fragments on every method call becomes hard to extend as soon as additional options like GROUP BY or HAVING are added. With a clean separation, adding a new property and a single extra line in the toSql() method is enough.

<?php

declare(strict_types=1);

/**
 * Minimal fluent Query Builder skeleton.
 * Collects state, only builds SQL when compile() is called.
 */
final class QueryBuilder
{
    private string $table = '';
    private array $columns = ['*'];
    private array $wheres = [];
    private array $bindings = [];
    private ?int $limit = null;
    private array $orderBy = [];

    public function __construct(private readonly PDO $pdo)
    {
    }

    public function table(string $table): self
    {
        $this->table = $table;
        return $this;
    }

    public function select(string ...$columns): self
    {
        $this->columns = $columns ?: ['*'];
        return $this;
    }

    public function where(string $column, string $operator, mixed $value): self
    {
        $this->wheres[] = "{$column} {$operator} ?";
        $this->bindings[] = $value;
        return $this;
    }

    public function orderBy(string $column, string $direction = 'ASC'): self
    {
        $this->orderBy[] = "{$column} {$direction}";
        return $this;
    }

    public function limit(int $limit): self
    {
        $this->limit = $limit;
        return $this;
    }

    public function toSql(): string
    {
        $sql = 'SELECT ' . implode(', ', $this->columns) . ' FROM ' . $this->table;
        if ($this->wheres !== []) {
            $sql .= ' WHERE ' . implode(' AND ', $this->wheres);
        }
        if ($this->orderBy !== []) {
            $sql .= ' ORDER BY ' . implode(', ', $this->orderBy);
        }
        if ($this->limit !== null) {
            $sql .= ' LIMIT ' . $this->limit;
        }
        return $sql;
    }
}

3. Assembling SELECT queries programmatically

Once the basic skeleton is in place, the actual benefit of a query builder becomes visible: queries can be assembled conditionally without manually gluing string fragments together. A typical scenario is a product search with optional filters for category, price range and availability. Without a query builder, every combination of set and unset filters would have to be modeled as its own SQL string or through error prone string concatenation.

With a query builder, a single chain of method calls suffices, where each where() call is only executed when the corresponding filter value is actually present. The result is more readable, because the intent of the query stays visible in the code instead of disappearing into string interpolation.

<?php

declare(strict_types=1);

$builder = (new QueryBuilder($pdo))
    ->table('products')
    ->select('id', 'name', 'price', 'stock');

// Conditional filters — only applied when the value is present
if ($categoryId !== null) {
    $builder->where('category_id', '=', $categoryId);
}
if ($minPrice !== null) {
    $builder->where('price', '>=', $minPrice);
}
if ($onlyInStock) {
    $builder->where('stock', '>', 0);
}

$builder->orderBy('price', 'ASC')->limit(20);

$sql = $builder->toSql();
echo $sql;
// SELECT id, name, price, stock FROM products
// WHERE category_id = ? AND price >= ? AND stock > ?
// ORDER BY price ASC LIMIT 20

4. Parameter binding: ruling out SQL injection in the query builder

The most important security aspect of a custom query builder is the strict separation of SQL structure and values. Column names, table names and operators must never be taken directly from user input, because they cannot be bound through prepared statements. Values that end up in WHERE conditions or INSERT statements, on the other hand, must without exception appear as placeholders in the SQL string and be bound through PDOStatement::execute().

A query builder that consistently enforces this separation makes classic SQL injection structurally impossible, because there is no longer any place in the generated code where user data could be interpolated directly into the SQL string. It is still important to whitelist dynamic column names used for ORDER BY or column selection, since placeholders are not syntactically allowed there.

<?php

declare(strict_types=1);

final class QueryExecutor
{
    public function __construct(private readonly PDO $pdo)
    {
    }

    /**
     * Executes a built query with bound parameters, never interpolated values.
     *
     * @param array<int, mixed> $bindings
     */
    public function run(string $sql, array $bindings): PDOStatement
    {
        $statement = $this->pdo->prepare($sql);
        $statement->execute($bindings);
        return $statement;
    }

    /** Whitelist check for dynamic identifiers that cannot be bound as parameters. */
    public function assertValidColumn(string $column, array $allowed): void
    {
        if (!in_array($column, $allowed, true)) {
            throw new InvalidArgumentException("Column '{$column}' is not allowed for ordering");
        }
    }
}

5. Modeling joins: inner, left and right programmatically

As soon as more than one table is involved, the query builder must also be able to model join clauses. The basic pattern stays the same: a method like join() takes the target table, local column, operator and foreign column, and internally appends a SQL fragment to a joins array. For left and right joins, an additional type parameter is enough, taken into account when the final statement is assembled.

A common design mistake in custom built query builders is treating joins as plain string concatenation without any structure. A small join class or an associative array per join is a better approach, so that later extensions like multiple conditions per join or nested joins do not lead to unreadable code.

<?php

declare(strict_types=1);

// Extension of the QueryBuilder skeleton from section 2
final class Join
{
    public function __construct(
        public readonly string $type,
        public readonly string $table,
        public readonly string $first,
        public readonly string $operator,
        public readonly string $second,
    ) {
    }

    public function toSql(): string
    {
        return "{$this->type} JOIN {$this->table} ON {$this->first} {$this->operator} {$this->second}";
    }
}

// Usage inside the builder
public function join(string $table, string $first, string $operator, string $second, string $type = 'INNER'): self
{
    $this->joins[] = new Join($type, $table, $first, $operator, $second);
    return $this;
}

// Example query
$sql = (new QueryBuilder($pdo))
    ->table('orders')
    ->select('orders.id', 'customers.name')
    ->join('customers', 'orders.customer_id', '=', 'customers.id')
    ->join('shipments', 'orders.id', '=', 'shipments.order_id', 'LEFT')
    ->toSql();

6. INSERT, UPDATE and DELETE through the query builder

A complete query builder does not stop at SELECT, it also models write operations. For INSERT, a method takes an associative array of column value pairs, derives the column list and the matching number of placeholders from it, and binds the values in the same order. For UPDATE the same principle applies, extended by the already familiar WHERE conditions so the entire table is not accidentally updated.

DELETE requires particular care: a query builder should either refuse a DELETE without at least one WHERE condition, or at minimum log it explicitly, because a forgotten where() call would otherwise empty the entire table. This guard clause is a small addition that prevents considerable damage in practice.

<?php

declare(strict_types=1);

public function insert(array $data): int
{
    $columns = implode(', ', array_keys($data));
    $placeholders = implode(', ', array_fill(0, count($data), '?'));
    $sql = "INSERT INTO {$this->table} ({$columns}) VALUES ({$placeholders})";

    $statement = $this->pdo->prepare($sql);
    $statement->execute(array_values($data));

    return (int) $this->pdo->lastInsertId();
}

public function update(array $data): int
{
    $assignments = implode(', ', array_map(fn (string $col) => "{$col} = ?", array_keys($data)));
    $sql = "UPDATE {$this->table} SET {$assignments}";

    if ($this->wheres === []) {
        throw new LogicException('Refusing UPDATE without WHERE clause');
    }
    $sql .= ' WHERE ' . implode(' AND ', $this->wheres);

    $statement = $this->pdo->prepare($sql);
    $statement->execute([...array_values($data), ...$this->bindings]);

    return $statement->rowCount();
}

public function delete(): int
{
    if ($this->wheres === []) {
        throw new LogicException('Refusing DELETE without WHERE clause');
    }
    $sql = "DELETE FROM {$this->table} WHERE " . implode(' AND ', $this->wheres);

    $statement = $this->pdo->prepare($sql);
    $statement->execute($this->bindings);

    return $statement->rowCount();
}

7. Nested conditions and subqueries

Real world queries often need more than a flat chain of AND connected conditions. A mature query builder therefore supports groups of conditions that are combined with OR and parenthesized as a whole, for example WHERE status = 'active' AND (region = 'DE' OR region = 'AT'). Technically this is solved by passing a closure to a where() variant that produces a new, nested query builder state, whose SQL is then inserted in parentheses at the end.

Subqueries are the second advanced building block: a query builder that accepts a full instance of itself as a value in a WHERE condition can model constructs like WHERE customer_id IN (SELECT id FROM customers WHERE active = 1). The bindings of the subquery must be placed in the correct order before the bindings of the outer query, since PDO resolves placeholders strictly by their position in the SQL string.

<?php

declare(strict_types=1);

public function whereGroup(Closure $callback): self
{
    $nested = new self($this->pdo);
    $callback($nested);

    $this->wheres[] = '(' . implode(' OR ', $nested->wheres) . ')';
    array_push($this->bindings, ...$nested->bindings);

    return $this;
}

public function whereIn(string $column, self $subquery): self
{
    $this->wheres[] = "{$column} IN ({$subquery->toSql()})";
    array_push($this->bindings, ...$subquery->bindings);

    return $this;
}

// Usage: nested OR group combined with a subquery
$activeCustomers = (new QueryBuilder($pdo))
    ->table('customers')
    ->select('id')
    ->where('active', '=', 1);

$orders = (new QueryBuilder($pdo))
    ->table('orders')
    ->select('id', 'total')
    ->whereIn('customer_id', $activeCustomers)
    ->whereGroup(function (QueryBuilder $q): void {
        $q->where('region', '=', 'DE');
        $q->where('region', '=', 'AT');
    });

8. Testability: decoupling the query builder from the database

A decisive advantage of a custom built query builder is the ability to test it without a real database connection. As long as the toSql() and getBindings() methods are pure functions without side effects, unit tests can verify exactly which SQL and which parameters result from a given method call, without ever instantiating PDO. This speeds up test runs considerably and surfaces errors in SQL generation immediately, instead of only noticing them through failed database calls.

For integration tests that actually run the query builder against a database, in memory SQLite is recommended as a fast, isolated test environment. Since a good query builder only produces standard SQL that is largely compatible between MySQL and SQLite, many tests can run without a real database server, which noticeably speeds up the CI pipeline.

9. Query builder compared: custom build, Doctrine, raw SQL

The decision between a custom built query builder, an established library like Doctrine and raw SQL depends heavily on project scope. A custom build pays off when requirements are limited, full control over generated SQL is desired, or a heavyweight dependency should be avoided. Ready made solutions, on the other hand, offer considerably more functionality, for example database portability across several dialects.

A custom built query builder is particularly worthwhile when a project should deliberately stay lean and its queries stay manageable. For complex domain models with many relationships, switching to an established solution pays off instead, because years of edge case handling have already gone into it that a custom build would first have to rebuild.

10. Summary

A custom query builder solves the problem of fragile SQL string concatenation through a fluent interface that collects state and translates it into SQL only at the end. The most important building blocks are method chaining for readable call chains, consistent parameter binding against SQL injection, a structured representation of joins, and support for nested conditions for real world queries.

The biggest payoff of a custom built query builder lies in testability: since SQL generation and actual execution are separated, queries can be verified without a database connection. Once you know its limits, you can combine a lean custom query builder for small projects with a switch to Doctrine or Laravel Eloquent Builder once requirements for portability and feature scope grow.

11. FAQ: Query Builders in PHP