Testing the query builder — assert generated SQL strings

Intermediate5 min read·fw-07-007

Concept

Testing the Query Builder verifies that it produces correct SQL strings from a fluent chain of method calls. The tests don't need a real database — just assert the generated SQL string matches expectations. Tests that DO require execution use SQLite in-memory.

Two test strategies:

SQL assertion (unit tests): Call the builder methods, get the compiled SQL string + bindings. Assert the SQL string matches the expected string. No DB needed. Fast.
Integration tests: Actually execute against SQLite in-memory. Assert results. Catches bugs in binding handling and type coercion.

toSql() method: The builder should expose toSql(): string to return the compiled SQL without executing. getBindings(): array returns the binding values. These are the primary test targets.

Testing whats: Not just that SELECT works, but that:

WHERE clause order is correct.
Bindings match placeholders count.
JOIN positions are correct.
ORDER BY, LIMIT, OFFSET are in the right order.
GROUP BY + HAVING compile correctly.

Edge cases to test:

Empty WHERE — no WHERE clause in output.
Multiple WHERE conditions — correct AND joining.
NULL values in bindings — IS NULL not = NULL.
Raw expressions — not quoted or escaped.
Column aliases — column AS alias.

Snapshot testing: Some test suites assert SQL strings using snapshots (store expected SQL and compare). Easier to update than string literals but less explicit.

Code Example

php

<?php
namespace Tests\Unit\Database;

use PHPUnit\Framework\TestCase;
use Framework\Database\QueryBuilder;
use Framework\Database\Connection;

class QueryBuilderTest extends TestCase
{
    private QueryBuilder $builder;

    protected function setUp(): void
    {
        $connection    = $this->createMock(Connection::class);
        $this->builder = new QueryBuilder($connection);
    }

    // --- SQL generation tests (no DB needed) ---
    public function test_basic_select(): void
    {
        $sql = $this->builder->table('users')->toSql();
        $this->assertSql('SELECT * FROM users', $sql);
    }

    public function test_select_with_columns(): void
    {
        $sql = $this->builder->table('users')->select(['id', 'name', 'email'])->toSql();
        $this->assertSql('SELECT id, name, email FROM users', $sql);
    }

    public function test_where_clause(): void
    {
        $builder = $this->builder->table('users')->where('email', '=', 'alice@example.com');
        $this->assertSql('SELECT * FROM users WHERE email = ?', $builder->toSql());
        $this->assertEquals(['alice@example.com'], $builder->getBindings());
    }

    public function test_multiple_wheres(): void
    {
        $sql = $this->builder->table('users')
            ->where('active', '=', 1)
            ->where('role', '=', 'admin')
            ->toSql();
        $this->assertSql('SELECT * FROM users WHERE active = ? AND role = ?', $sql);
    }

    public function test_join_compiles_correctly(): void
    {
        $sql = $this->builder->table('posts')
            ->select(['posts.id', 'users.name'])
            ->join('users', 'posts.user_id', '=', 'users.id')
            ->toSql();
        $this->assertSql('SELECT posts.id, users.name FROM posts INNER JOIN users ON posts.user_id = users.id', $sql);
    }

    public function test_order_limit_offset(): void
    {
        $sql = $this->builder->table('posts')
            ->orderBy('created_at', 'DESC')
            ->limit(10)->offset(20)->toSql();
        $this->assertSql('SELECT * FROM posts ORDER BY created_at DESC LIMIT 10 OFFSET 20', $sql);
    }

    // --- Integration test with real SQLite ---
    public function test_insert_and_select_with_real_db(): void
    {
        $pdo  = new \PDO('sqlite::memory:');
        $pdo->exec('CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, email TEXT)');
        $conn = new Connection($pdo);
        $qb   = new QueryBuilder($conn);

        $qb->table('users')->insert(['name' => 'Alice', 'email' => 'alice@example.com']);
        $users = $qb->table('users')->where('email', '=', 'alice@example.com')->get();

        $this->assertCount(1, $users);
        $this->assertEquals('Alice', $users[0]['name']);
    }

    private function assertSql(string $expected, string $actual): void
    {
        $this->assertEquals(
            preg_replace('/\s+/', ' ', trim($expected)),
            preg_replace('/\s+/', ' ', trim($actual))
        );
    }
}

PreviousPagination — LIMIT/OFFSET vs cursor-basedQuery Builder from Scratch NextModel base class design — table name inference, primary keyActiveRecord ORM