SQL-First PHP: Query Objects, Typed Rows and a Database That Checks Your SQL
There are good reasons to skip the ORM and write SQL yourself, and most of them come down to wanting the SQL that runs to be exactly the SQL you wrote. The trouble is that raw SQL done casually tends to rot in a particular way. Column names get typed out by hand in fifty places, casts are sprinkled through every result and money quietly passes through floats, whilst the unit tests mock the database so thoroughly they would pass against a table that doesn't exist. This article describes a pattern for keeping the control without the rot, and the checks that make it hold up over time rather than just on the day it was written.
The Short Version, and the Demo Repo
Every statement lives as a public const string SQL on its own small class and goes through one thin gateway interface, which a hand-written fake implements for unit tests. Rows are read through a typed, fail-fast reader rather than (int)$row['stock']. Column names are written down once, in a hand-written table class, and everything else refers to them through it. Then the build enforces those conventions rather than hoping for them: three custom PHPStan rules ban the shortcuts, and two checks against a real database have MySQL itself confirm that the table class matches the table and that every SQL constant in the application prepares.
All of it is in a public repository, LongTermSupport/sql-first-php-demo. It is a concept demo, not a library: it is deliberately small, every file is heavily commented, and it is meant to be read and copied from rather than installed. The snippets in this article are trimmed from it, and each section links to the full file so you can see the surrounding comments and tests.
Why You Might Want Raw SQL at All
I should say up front that this is not an anti-ORM piece. Doctrine and Eloquent are mature, well-maintained tools, and for a greenfield application where the code owns the schema and most of the work is creating, reading and updating individual records, an ORM is probably the faster and more sensible route. The general wisdom that ORMs pay off for CRUD-heavy domains is general wisdom for a reason. But there is a long list of situations where writing SQL directly is the better fit, and if you recognise your project in a few of these, the rest of the article is for you.
You want absolute control over what runs. With hand-written SQL, the statement in your code is the statement MySQL receives. You can paste it into a client, run EXPLAIN on it, and know the plan you see is the plan production gets. There's no lazy loading to trigger a query you didn't write, no N+1 hiding behind a property access, and no hydration of forty columns when you needed three.
You are working with a legacy database you do not own. This is the big one. Plenty of real schemas were designed long before anyone thought about mapping them to objects: tables without a single-column primary key, MyISAM tables that cannot take part in a transaction, columns whose type says one thing and whose data says another, naming conventions that change halfway through the alphabet. An ORM can usually be persuaded to work with all of that, but you end up fighting its assumptions on every table, and the mapping configuration becomes a second, harder-to-read description of a schema that already exists.
Several applications share the same database. An ORM tends to assume it owns the schema, including generating it and migrating it. When three applications, a reporting job and a vendor's integration all read the same tables, no single codebase gets to be the owner, and plain SQL is the common language they all already speak.
The interesting work is reporting and aggregation. Grouping, window functions, conditional sums, multi-table joins with careful index use: these are what SQL is for, and they are often awkward to express through a query builder and impossible through an entity API. Writing them out longhand is clearer, and the database does the arithmetic far faster than a PHP loop over hydrated objects.
You want fewer layers between you and the problem. Every layer is something to learn, configure, upgrade and debug. A thin gateway over PDO has very little in it that can surprise you, and the people who know SQL best on your team, who may not be PHP developers at all, can read and review every statement.
None of those reasons are an argument for doing raw SQL carelessly, though, and careless is the default unless you put a structure in place.
What Goes Wrong When It Is Done Casually
I suspect most PHP developers have met some version of this class. Each method is reasonable in isolation, and the problems only show when you look at the codebase as a whole.
<?php
// The familiar shape of raw SQL that has grown without a plan. Every line works today.
final class ProductRepository
{
public function __construct(private PDO $pdo) {}
public function find(int $id): ?array
{
$stmt = $this->pdo->prepare('SELECT * FROM product WHERE id = ?');
$stmt->execute([$id]);
$row = $stmt->fetch(PDO::FETCH_ASSOC);
if (!$row) {
return null;
}
$row['stock'] = (int)$row['stock']; // NULL quietly becomes 0
$row['unit_price'] = (float)$row['unit_price']; // money as a float
return $row; // an array, keys typed by hope
}
// ...and forty more methods like it, each spelling 'unit_price' by hand.
}
The column name unit_price is a string literal that appears wherever anything touches the price, so renaming the column means a search across the codebase and a hope that nobody built the name dynamically. The (int) cast turns a NULL into 0 without complaint, which is a wonderful way to ship a stock level of zero that was really "unknown". The (float) cast puts money through binary floating point, which cannot represent most decimal fractions exactly. The return type is an array whose keys and types are a matter of trust. And when someone writes a unit test for this, the natural move is to mock PDO or the repository, at which point the test is checking that the mock returns what the mock was told to return.
None of this fails on the day it is written. It fails months later, when a column is renamed, or a nullable column starts actually containing nulls, or a report total is a penny out and nobody can work out why.
One Gateway, Deliberately Small
Everything that talks to the database goes through one interface. Here is all of it, with the docblocks trimmed.
Full source: src/Db/DbServiceInterface.php
<?php
// Source: https://github.com/LongTermSupport/sql-first-php-demo/blob/main/src/Db/DbServiceInterface.php
// Trimmed from src/Db/DbServiceInterface.php (docblocks removed).
// This is the whole surface. Every query in the application goes through it.
interface DbServiceInterface
{
/** @param array<string, scalar|null> $params */
public function queryRows(string $sql, array $params = []): RowSet;
/** @return list<array<string, mixed>> */
public function query(string $sql, array $params = []): array;
/** @return Generator<int, array<string, mixed>> constant memory, for big result sets */
public function stream(string $sql, array $params = []): Generator;
/** @return int affected rows */
public function execute(string $sql, array $params = []): int;
/** @return int the new auto-increment id; refuses SQL that is not an INSERT */
public function insert(string $sql, array $params = []): int;
/** COMMIT if $action returns, ROLLBACK if it throws. */
public function transactional(callable $action): mixed;
}
The size is the point: six methods, every one of which takes the SQL and a separate array of named parameters, so there is simply no method that interpolates a value into a statement. There is no query builder behind it and no entity manager, and what you pass in is what runs. It also gives you a single place to put the behaviour you want on every statement, and a single seam to replace in tests, which we will come to.
You'll notice there's no "which database" parameter anywhere, because one instance of the service is one connection. An application that talks to a primary and a read replica wires two instances and hands each query class the one it needs, which keeps the decision visible at the point it matters rather than buried in a connection-name string.
The real implementation, PdoDbService, adds a small number of things around each call:
a bounded prepared-statement cache, slow-query logging that includes the SQL,
wrapping every failure in a single QueryException that carries the failing statement (but not the parameter values, which can hold personal data and end up in log aggregators),
and retry on a lost connection. That last one is subtler than it looks, so here it is.
Full source: src/Db/PdoDbService.php, withRetry()
<?php
// Source: https://github.com/LongTermSupport/sql-first-php-demo/blob/main/src/Db/PdoDbService.php#L184-L221
// Simplified from src/Db/PdoDbService.php (logging calls removed)
final class PdoDbService implements DbServiceInterface
{
// ...
// $retryable is true for query()/queryRows()/stream() and false for execute()/insert().
private function withRetry(bool $retryable, callable $attempt): mixed
{
for ($try = 1; ; ++$try) {
try {
return $attempt($this->connections->getConnection());
} catch (Throwable $e) {
if (!$this->isConnectionLost($e)) {
throw $e; // the SQL is wrong, or a constraint failed: never retried
}
if ($this->transactionDepth > 0) {
throw $e; // transactional() discards the connection
}
// Outside a transaction the dead handle is useless either way, so drop it now and
// the next statement, retried or not, starts on a fresh connection.
$this->discardConnection();
if (!$retryable || $try > $this->retryAttempts) {
throw $e; // a write may already have been applied
}
usleep($this->retryDelayMs * 1000);
}
}
}
}
Long-running workers, idle connection pools and database failovers all produce "MySQL server has gone away" often enough that you want some retry, and running a SELECT a second time on a fresh connection is harmless. Writes are a different matter. A connection can drop after the server has already run and committed the statement but before the reply arrives, so blindly re-sending SET stock = stock + 3 or an INSERT could apply it twice. The demo therefore retries reads only: a write that loses its connection is reported rather than repeated, the dead connection is thrown away so the next statement gets a fresh one, and the caller, who knows whether that particular write is safe to run again, decides what to do.
Inside a transaction, nothing is retried at all, reads included. The new connection would have no open transaction, so the remaining statements would each auto-commit on their own. The final COMMIT would then fail against the dead handle, and the caller would be told the work failed when half of it had been permanently applied. So the service counts how deeply it is nested inside transactional() and refuses to reconnect there.
The demo has real-database tests for all three cases,
which read the service's own CONNECTION_ID() and KILL it from a second connection to prove the behaviour.
The connection itself is set up by PdoConnectionFactory,
with exceptions on error, server-side prepares, utf8mb4 and a strict sql_mode applied on every connect.
The strict mode matters on legacy servers in particular, which are often configured permissively: it turns a value that does not fit into an error rather than a silent truncation or a zero date.
One Class Per Query
A query is a small object, not a method on a repository that grows forever. Here is a complete one.
Full source: example/Product/FindProductByIdQuery.php
<?php
// Source: https://github.com/LongTermSupport/sql-first-php-demo/blob/main/example/Product/FindProductByIdQuery.php
// example/Product/FindProductByIdQuery.php (docblock removed)
final readonly class FindProductByIdQuery
{
public const string SQL = <<<'SQL'
SELECT id, sku, name, description, status, unit_price, sale_price, stock, supplier_id, is_featured
FROM product
WHERE id = :id
SQL;
public function __construct(private DbServiceInterface $db)
{
}
public function execute(int $id): ?Product
{
$row = $this->db->queryRows(self::SQL, [ProductTable::COL_ID => $id])->firstOrNull();
return null === $row ? null : Product::fromRow($row);
}
}
// At the call site:
$product = new FindProductByIdQuery($db)->execute($id);
Every query class has the same shape. It is final readonly, has no base class and no state beyond the injected gateway, and holds its SQL as a public constant written out in full as SQL. It exposes one method, named for what it does, taking typed arguments and returning a typed result, where null here means "no such product". The column list is spelled out rather than SELECT *, so adding a column to the table never silently changes what the query returns.
Why a constant, specifically? Three reasons, and the third turns out to be the most useful. A reviewer reads the SQL as SQL, in one place, without reconstructing it from string concatenation. A unit test can configure the fake with exactly FindProductByIdQuery::SQL, so the test and the code cannot disagree about the statement. And because every statement in the application lives in a constant with the same name, a test can find all of them by reflection and have MySQL check each one, which is the subject of a later section.
Callers simply construct the query where they need it. In a framework application you would register the gateway in the container and exclude query classes from autowiring; they have one dependency each and are cheap to build, so there is nothing for a container to manage.
Statement classes: construct once, run many times
Some SQL runs many times in a row with different parameters, typically in a loop over an import file. For those there is a second shape, a statement class, built once and called repeatedly.
Full source: example/Product/AdjustStockStmt.php
<?php
// Source: https://github.com/LongTermSupport/sql-first-php-demo/blob/main/example/Product/AdjustStockStmt.php
// Trimmed from example/Product/AdjustStockStmt.php
final class AdjustStockStmt extends AbstractPreparedStmt
{
public const string SQL = <<<'SQL'
UPDATE product
SET stock = stock + :delta
WHERE sku = :sku
AND stock >= :required
SQL;
public function adjust(string $sku, int $delta): bool
{
return 1 === $this->execute(self::SQL, [
self::PARAM_DELTA => $delta,
ProductTable::COL_SKU => $sku,
self::PARAM_REQUIRED => max(0, -$delta), // removing 3 needs at least 3 in stock
]);
}
}
// Constructed once, called many times, inside one transaction:
$adjust = new AdjustStockStmt($db);
$db->transactional(function () use ($adjust, $lines): void {
foreach ($lines as $line) {
$adjust->adjust($line->sku, $line->delta);
}
});
The gateway prepares the statement on the first call and reuses it for every one after.
The base class, AbstractPreparedStmt, is almost empty on purpose:
it keeps the gateway's methods protected, so the public surface of a statement class is only its own domain-named methods and never a raw execute(string $sql) that would let a caller pass arbitrary SQL through it.
It's also worth looking at where the business rule lives. "Stock never goes below zero" is in the WHERE clause, so the database enforces it atomically, and two workers applying adjustments at the same moment cannot race each other the way a read-check-write in PHP could. The method reports whether a row changed, and false means either an unknown SKU or not enough stock. (The separate :required parameter exists because, with native prepares, a named placeholder cannot appear twice in one statement, which is an easy thing to trip over.)
Typed Rows That Fail Loudly
PDO does not hand you PHP types the way you might hope. mysqlnd on PHP 8.1 and later sends an INT as 42, but with PDO::ATTR_STRINGIFY_FETCHES, a libmysqlclient build or an older PHP it arrives as "42", and plenty of existing code and test data assumes the string form. DECIMAL always arrives as a string. A BIGINT UNSIGNED can hold values larger than PHP's integer maximum. And a NULL arrives as null, which a careless cast turns into something that looks like data.
Rather than let those rules leak into every class that reads a result, one class owns them. Row wraps a single result row, and every getter either returns exactly the type its name promises or throws, naming the column.
Full source: src/Result/Row.php
<?php
// Source: https://github.com/LongTermSupport/sql-first-php-demo/blob/main/src/Result/Row.php
// Simplified from src/Result/Row.php (int() inlines the private toInt() helper, and the
// patterns are written out rather than referenced as class constants)
final readonly class Row
{
// ...
public function decimal(string $key): string
{
$value = $this->get($key); // throws, naming the key, if the column is missing
if (\is_int($value)) {
return (string)$value;
}
if (\is_string($value) && 1 === preg_match('/^-?\d+(\.\d+)?$/D', $value)) {
return $value; // "5000.50" stays "5000.50"
}
// A float here means the column is FLOAT or DOUBLE, not DECIMAL, and the digits may
// already be gone. Refuse it rather than format it into something plausible.
throw RowException::forWrongType($key, 'an exact decimal string', $value);
}
public function int(string $key): int
{
$value = $this->get($key);
if (\is_int($value)) {
return $value; // what mysqlnd sends on PHP 8.1+
}
if (\is_string($value) && 1 === preg_match('/^-?\d+$/D', $value) && (string)(int)$value === $value) {
return (int)$value; // STRINGIFY_FETCHES, libmysqlclient or older PHP
}
throw RowException::forWrongType($key, 'int', $value); // including NULL: no silent 0
}
}
The decimal getter is the one I would draw your attention to. Money comes back as an exact string, "5000.50", and a float arriving there is refused rather than formatted. Once a value has been through a float its original form is gone: 5000.50 comes back as 5000.5, which breaks string comparison and display formatting even though it is numerically the same. Values near the top of a DECIMAL(15,2) column fare worse, losing real digits as soon as PHP turns the float back into a string (9999999999999.99 becomes 10000000000000). If a float shows up, it means the column is really FLOAT or DOUBLE, and the right response is to find that out now rather than format the damage into something plausible. Arithmetic on the string goes through bcmath, or you convert deliberately to integer pence.
The same "say what you mean" idea applies to how many rows you expect.
Reads return a RowSet,
which offers single() (exactly one, or throw), firstOrNull() and iteration.
A lookup by unique key uses single(), so if the WHERE clause that "can only ever match one row" matches two one day, you find out immediately rather than quietly using whichever came first.
Two smaller traps are covered too. MySQL has no real boolean: BOOLEAN is an alias for TINYINT(1), and the (1) is a display width rather than a constraint, so the column will happily store 7. Reading it as a bool is a statement about how your application uses the column, and the getter enforces that statement by accepting only 0 and 1. And unsigned big integers stay as digit strings, because casting 18446744073709551615 to a PHP int gives you the wrong number with no warning at all.
Column Names, Written Down Once
The typed row reader solves the type problem but not the naming problem, since something still has to say 'unit_price'. The answer is a hand-written table class that is the one place a table's names and types appear in PHP.
Full source: example/Product/ProductTable.php
<?php
// Source: https://github.com/LongTermSupport/sql-first-php-demo/blob/main/example/Product/ProductTable.php
// Trimmed from example/Product/ProductTable.php (four of the ten columns shown)
final class ProductTable
{
public const string TABLE_NAME = 'product';
public const string COL_ID = 'id';
public const string COL_UNIT_PRICE = 'unit_price';
public const string COL_SALE_PRICE = 'sale_price';
public const string COL_STOCK = 'stock';
public readonly IntColumn $id;
public readonly DecimalColumn $unitPrice;
public readonly NullableDecimalColumn $salePrice;
public readonly IntColumn $stock;
private static ?self $instance = null;
private function __construct()
{
$this->id = new IntColumn(self::COL_ID);
$this->unitPrice = new DecimalColumn(self::COL_UNIT_PRICE);
$this->salePrice = new NullableDecimalColumn(self::COL_SALE_PRICE);
$this->stock = new IntColumn(self::COL_STOCK);
}
public static function columns(): self
{
return self::$instance ??= new self();
}
}
It has two layers. String constants, TABLE_NAME and COL_*, are usable anywhere a constant expression is needed, including as keys in a bound-parameter array. And each column also gets a typed column object built from its constant, which pairs the name with its PHP type. Each column type is a tiny final class:
Full source: src/Schema/NullableDecimalColumn.php
<?php
// Source: https://github.com/LongTermSupport/sql-first-php-demo/blob/main/src/Schema/NullableDecimalColumn.php
// src/Schema/NullableDecimalColumn.php, near enough. One small final class per PHP type, so
// PHPStan knows exactly what fromRow() returns and what is() accepts.
final readonly class NullableDecimalColumn extends AbstractColumn
{
public function fromRow(Row $row): ?string
{
return $row->nullableDecimal($this->name);
}
public function is(?string $value): ColumnValue
{
return null === $value
? new ColumnValue($this, null)
: new DecimalColumn($this->name)->is($value); // validates the decimal format
}
}
PHP has no generics, so one small class per carrier type is what lets PHPStan know that $t->salePrice->fromRow($row) returns ?string and that $t->stock->is('five') is a type error. Calling the wrong getter for a column stops being a mistake you can make, because the column object only has the right one. The base class is also deliberately not Stringable: if it were, "SELECT $column FROM ..." would work and pass static analysis, and a name would go into SQL without anyone deciding whether it needed quoting. Callers pick ->name, ->quoted() or ->param() explicitly.
With that in place, a DTO's hydration has no string-literal column names in it at all.
Full source: example/Product/Product.php, fromRow()
<?php
// Source: https://github.com/LongTermSupport/sql-first-php-demo/blob/main/example/Product/Product.php#L43-L59
// Trimmed from example/Product/Product.php. Not one literal column name.
final readonly class Product
{
// ... constructor with ten typed, promoted, public properties ...
public static function fromRow(Row $row): self
{
$t = ProductTable::columns();
return new self(
id: $t->id->fromRow($row),
sku: $t->sku->fromRow($row),
name: $t->name->fromRow($row),
description: $t->description->fromRow($row), // ?string
status: ProductStatus::from($t->status->fromRow($row)), // throws on an unknown ENUM value
unitPrice: $t->unitPrice->fromRow($row), // string, exact
salePrice: $t->salePrice->fromRow($row), // ?string, exact
stock: $t->stock->fromRow($row), // int, and NULL would throw
supplierId: $t->supplierId->fromRow($row), // ?int
isFeatured: $t->isFeatured->fromRow($row), // bool, only from 0 or 1
);
}
}
Compare that with the casual version earlier. A missing column, a NULL in a field the code believes is NOT NULL, a float where a decimal was promised, an ENUM value the PHP has never heard of: each of these throws at this line, naming the column, rather than travelling on into a total or an invoice.
Why hand-written rather than generated? A table class is a few dozen lines and changes rarely, writing it by hand keeps it readable, and it lets you model only the columns you actually use. That last point matters a great deal for legacy schemas, where a sixty-column table can be adopted a few columns at a time. A generator starts to earn its keep when you have a lot of tables changing often, and at that point I would happily reach for one, but it is not where I would start.
Writes, and the one piece of SQL that is composed
There is exactly one place where SQL is built for you rather than written out, and it is a single-row INSERT.
Full source: example/Product/CreateProductCommand.php
<?php
// Source: https://github.com/LongTermSupport/sql-first-php-demo/blob/main/example/Product/CreateProductCommand.php
// example/Product/CreateProductCommand.php (docblock removed)
final readonly class CreateProductCommand
{
public function __construct(private DbServiceInterface $db)
{
}
public function execute(NewProduct $new): int
{
$t = ProductTable::columns();
return Insert::row(
$this->db,
ProductTable::TABLE_NAME,
$t->sku->is($new->sku),
$t->name->is($new->name),
$t->description->is($new->description), // NullableStringColumn::is(?string)
$t->status->is($new->status->value),
$t->unitPrice->is($new->unitPrice), // DecimalColumn::is(string): no float accepted
$t->salePrice->is($new->salePrice),
$t->stock->is($new->stock), // IntColumn::is(int)
$t->supplierId->is($new->supplierId),
$t->isFeatured->is($new->isFeatured), // bound as 1 or 0
);
}
}
// Insert::row() builds both lists from the same values, so they cannot drift apart:
// INSERT INTO `product` (`sku`, `name`, `description`, `status`, `unit_price`, `sale_price`,
// `stock`, `supplier_id`, `is_featured`)
// VALUES (:sku, :name, :description, :status, :unit_price, :sale_price,
// :stock, :supplier_id, :is_featured)
A hand-written INSERT has a failure mode that SELECTs do not: the column list and the VALUES list are two lists that must line up, and nothing checks that they do.
Insert::row() builds both from the same values, so they cannot drift apart,
and each value comes from its column's typed is() method, so the types are checked statically too.
It stops there, deliberately. There is no Update builder, no Where builder and no Select builder. Once a project has a WHERE builder it tends to grow, slowly and with the best of intentions, into a query builder, and then the SQL that runs is no longer the SQL anybody reads. Every other statement stays written out as a constant, where both a reviewer and the prepare check can see it.
Columns that only exist in a query's output, such as COUNT(*) AS total, get the same treatment through a small
result class that declares the alias names as constants.
You can see it in use in CountProductsByStatusQuery,
which is also a nice example of the database doing exact DECIMAL arithmetic that a PHP loop over floats would get slightly wrong.
Testing: a Fake, Not a Mock, and Always a Real Database Too
Because everything goes through the gateway interface, a unit test can swap in FakeDbService, a hand-written implementation keyed by SQL text.
Full source: tests/Product/FindProductByIdQueryTest.php
<?php
// Source: https://github.com/LongTermSupport/sql-first-php-demo/blob/main/tests/Product/FindProductByIdQueryTest.php
// Adapted from tests/Product/FindProductByIdQueryTest.php (the real test reuses a row fixture
// from ProductTest; it is written out inline here so the shape is visible)
final class FindProductByIdQueryTest extends TestCase
{
#[Test]
public function itHydratesTheMatchingRow(): void
{
// Configured by SQL text. The row is written in its most string-typed form, as some
// drivers and settings deliver it; Row copes with that and with native ints alike.
$db = new FakeDbService()->onQuery(FindProductByIdQuery::SQL, [[
'id' => '3', 'sku' => 'ANV-001', 'name' => 'Anvil', 'description' => null,
'status' => 'active', 'unit_price' => '149.90', 'sale_price' => null,
'stock' => '4', 'supplier_id' => null, 'is_featured' => '1',
]]);
$product = new FindProductByIdQuery($db)->execute(3);
self::assertNotNull($product);
self::assertSame('ANV-001', $product->sku);
self::assertSame([['id' => 3]], array_column($db->executed(), 'params'));
}
}
// Unconfigured SQL is not an empty result. It fails, listing what was configured:
//
// FakeDbService::query() got SQL nobody configured:
// SELECT * FROM product
// Configured:
// SELECT id, sku, name, ... FROM product WHERE id = :id
The difference from a mocking library matters here. A mock is configured in terms of method calls ("expect query() once, with any arguments, and return this"), which couples the test to how the class happens to call the gateway and says nothing about the SQL. The fake is configured in terms of statements: when this SQL runs, return these raw rows, exactly as a driver would hand them over. Afterwards, the test can read back precisely which statements ran with which parameters. Unconfigured SQL fails with a message listing what was configured, rather than passing on an accidental empty result, which catches a whole class of mistakes on its own.
What the fake can't do is tell you the SQL is right. It returns the rows you hand it, even for a SELECT that names a column that doesn't exist, so a suite of fake-only tests can be entirely green on code that fails on its first real request. That is why every query class also gets an integration test, running the same constant against a real MySQL.
Full source: tests/Product/FindProductByIdQueryIntegrationTest.php
<?php
// Source: https://github.com/LongTermSupport/sql-first-php-demo/blob/main/tests/Product/FindProductByIdQueryIntegrationTest.php
// Trimmed from tests/Product/FindProductByIdQueryIntegrationTest.php
#[Group('real-db')]
final class FindProductByIdQueryIntegrationTest extends TestCase
{
use RealDatabase; // a fresh test_demo_* schema per test, built from schema.sql, dropped after
#[Test]
public function itFindsARealRow(): void
{
$ids = ProductFixtures::seed($this->db());
$product = new FindProductByIdQuery($this->db())->execute($ids['anvil']);
self::assertSame('149.99', $product?->unitPrice);
self::assertSame('129.50', $product?->salePrice); // the trailing zero survives
}
}
The RealDatabase trait gives each test its own throwaway schema,
built from the same schema.sql a developer would run by hand, and drops it afterwards.
A whole schema per test is slower than wrapping each test in a rolled-back transaction, but it has no exceptions: MySQL commits DDL implicitly, so the transaction trick breaks the first time a test alters a table, whereas nothing a test does to its own schema can leak into the next one. Without database credentials in the environment the tests skip, so the unit suite runs on a laptop with nothing installed. CI runs them with --fail-on-skipped, so a misconfigured database turns the build red instead of silently skipping the only tests that prove the SQL.
Making the Conventions True: Three PHPStan Rules
Everything so far is a convention, and conventions written in a README erode one pull request at a time. Somebody is in a hurry, writes $row->int('stock') just this once, and the "one place for column names" claim is now false in a way nothing in PHP will ever notice, because a string is a string. The fix is to make the build fail. Here are three shortcuts in one innocent-looking test:
<?php
declare(strict_types=1);
namespace SqlFirstDemo\Tests\Scratch;
use PHPUnit\Framework\TestCase;
use SqlFirstDemo\Testing\FakeDbService;
final class ShortcutQueryTest extends TestCase
{
public function testShortcuts(): void
{
$db = new FakeDbService()->onQuery('SELECT stock FROM product WHERE id = 1', [['stock' => '3']]);
$row = $db->queryRows('SELECT stock FROM product WHERE id = 1')->single();
self::assertSame(3, $row->int('stock'));
}
}
Dropped into the demo as tests/Scratch/ShortcutQueryTest.php, this is what its PHPStan configuration says about it:
$ vendor/bin/phpstan analyse tests/Scratch
------ ----------------------------------------------------------------------
Line ShortcutQueryTest.php
------ ----------------------------------------------------------------------
14 ShortcutQueryTest uses FakeDbService, which cannot tell whether the
SQL is valid. Add ShortcutQueryIntegrationTest.php next to it,
running the same query against a real database.
🪪 sqlFirst.fakeWithoutIntegrationTest
15 queryRows() is given SQL as a string literal. Move it to a public
const string SQL on a query class and pass Foo::SQL, so the prepare
check can find and validate it.
🪪 sqlFirst.sqlLiteral
17 Row::int('stock') uses a string-literal column name. Read it through
the table class instead, e.g. $table->column->fromRow($row), so a
rename cannot leave this line behind.
🪪 sqlFirst.rowKeyLiteral
------ ----------------------------------------------------------------------
[ERROR] Found 3 errors
Each rule targets one habit.
RowKeyMustNotBeLiteralRule keeps column names in the table class.
SqlMustBeAClassConstantRule keeps every statement in a SQL constant,
which is what lets the prepare check in the next section cover the application's SQL rather than whatever happened to be written in a query class.
And FakeDbTestNeedsIntegrationTestRule
requires any unit test that uses the fake to have a matching *IntegrationTest.php beside it, which removes "I'll write the real-database test later" as an option.
Custom PHPStan rules are less work than people tend to expect. Here is the core of the first one:
Full source: phpstan/Rules/RowKeyMustNotBeLiteralRule.php, processNode()
<?php
// Source: https://github.com/LongTermSupport/sql-first-php-demo/blob/main/phpstan/Rules/RowKeyMustNotBeLiteralRule.php#L74-L98
// Trimmed from phpstan/Rules/RowKeyMustNotBeLiteralRule.php
/** @implements Rule<MethodCall> */
final readonly class RowKeyMustNotBeLiteralRule implements Rule
{
// ...
public function processNode(Node $node, Scope $scope): array
{
// A method call named like a Row getter...
if (!$node->name instanceof Identifier || !\in_array($node->name->toString(), self::GETTERS, true)) {
return [];
}
// ...whose first argument is a string literal written right here...
$key = $node->getArgs()[0]->value ?? null;
if (!$key instanceof String_) {
return [];
}
// ...on something PHPStan can prove is a Row.
if (!new ObjectType(Row::class)->isSuperTypeOf($scope->getType($node->var))->yes()) {
return [];
}
if (\in_array($scope->getClassReflection()?->getName(), $this->exemptClasses, true)) {
return [];
}
return [
RuleErrorBuilder::message(self::message($node->name->toString(), $key->value))
->identifier(self::IDENTIFIER)
->build(),
];
}
}
All three are deliberately narrow. They fire on a string literal written at the call site, which is how the mistake is actually made, and let through anything indirect, such as a variable or a parameter, because a generic helper legitimately takes a string $sql that its own caller supplied as Foo::SQL. Chasing every value back to its origin would make for a much bigger rule that is wrong more often, and a rule that cries wolf tends to get suppressed rather than obeyed. The price of that narrowness is that the rules are a strong nudge rather than a proof: a key held in a variable, a raw array from query() or an exempted class can still get past them, which is one more reason the database checks below exist.
The few places that genuinely need to break a rule, such as the tests of the row reader itself, are exempted by class name in
phpstan.neon, so every exemption is listed in one file and shows up in review.
Letting MySQL Check the SQL
Static analysis can catch most of the ways PHP code bypasses the table class or builds SQL outside a constant. What it cannot check at all is that the table class matches the table, or that the SQL in those constants parses and matches the schema. For that you need the database, and the demo has two checks that ask it directly.
Check one: the table class against information_schema
Full source: tests/Support/TableClassSchemaChecker.php, check()
<?php
// Source: https://github.com/LongTermSupport/sql-first-php-demo/blob/main/tests/Support/TableClassSchemaChecker.php#L74-L120
// Trimmed from tests/Support/TableClassSchemaChecker.php
final readonly class TableClassSchemaChecker
{
public const string SQL = <<<'SQL'
SELECT COLUMN_NAME, IS_NULLABLE, DATA_TYPE, COLUMN_TYPE
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = DATABASE()
AND TABLE_NAME = :table_name
ORDER BY ORDINAL_POSITION
SQL;
// Which column class may sit over which MySQL types, and whether it expects NULL.
private const array COMPATIBLE = [
IntColumn::class => ['nullable' => false, 'types' => self::INTEGER_TYPES],
NullableIntColumn::class => ['nullable' => true, 'types' => self::INTEGER_TYPES],
DecimalColumn::class => ['nullable' => false, 'types' => ['decimal']],
NullableDecimalColumn::class => ['nullable' => true, 'types' => ['decimal']],
BoolColumn::class => ['nullable' => false, 'types' => ['tinyint']],
// ...
];
// For each typed column the table class declares (found by reflection), the live table
// must have it, with matching nullability and a compatible type. Live columns the class
// does not declare are reported, not failed, so a wide legacy table can be modelled a bit
// at a time.
}
The checker reads the live table's columns from information_schema,
finds the typed columns a table class declares by reflection, and fails if any declared column is missing, has the wrong nullability, or has a type the PHP class cannot read.
A NullableIntColumn over a NOT NULL column fails, as does a BoolColumn over anything wider than a TINYINT, or an IntColumn over a BIGINT UNSIGNED whose values will not fit a PHP int. Live columns the class does not declare are reported but not failed, which is what lets you adopt a wide legacy table gradually. Because the query is scoped with DATABASE(), the same check runs against a throwaway test schema, a staging copy or, read-only, against production itself.
Check two: prepare every SQL constant
The second check is the one I find most satisfying, because it involves almost no code. A server-side prepare makes MySQL parse a statement and resolve every table, column and alias in it against the real schema, without executing anything. So the check finds every SQL constant in the application and asks MySQL to prepare each one.
Full source: tests/Support/ServerSidePrepareValidator.php, validate()
<?php
// Source: https://github.com/LongTermSupport/sql-first-php-demo/blob/main/tests/Support/ServerSidePrepareValidator.php#L37-L57
// Trimmed from tests/Support/ServerSidePrepareValidator.php
final readonly class ServerSidePrepareValidator
{
public function __construct(private PDO $pdo)
{
// With emulated prepares, prepare() never reaches the server and this check would pass
// for any SQL at all. Refuse such a connection rather than prove nothing.
if (false !== (bool)$pdo->getAttribute(PDO::ATTR_EMULATE_PREPARES)) {
throw new LogicException('Needs a PDO with ATTR_EMULATE_PREPARES off.');
}
}
/** @param array<string, string> $statements class => SQL */
public function validate(array $statements): array
{
$failures = [];
foreach ($statements as $class => $sql) {
try {
$this->pdo->prepare($sql); // parsed and resolved by MySQL, not executed
} catch (PDOException $e) {
$failures[] = $class . '::SQL does not prepare: ' . $e->getMessage();
}
}
return $failures; // every failure, not just the first
}
}
That makes MySQL the SQL parser, and MySQL is the only parser guaranteed to agree with MySQL. There is no SQL parsing library involved and no regular expressions over SQL text, and because nothing is executed it is as safe for an UPDATE or a DELETE as it is for a SELECT. An unknown column, a typo in a join, or a GROUP BY that ONLY_FULL_GROUP_BY rejects all fail here with MySQL's own error message, and the check reports every failing statement in one run rather than stopping at the first. What it cannot catch is anything that only happens when the statement runs, such as a subquery returning two rows or a query that is valid but wrong; that is still the integration tests' job.
There is one trap, and it is a nasty one: with PDO::ATTR_EMULATE_PREPARES switched on, prepare() never contacts the server at all. PDO stores the string and substitutes values itself on execute, so the check would pass for any SQL whatsoever. The validator refuses such a connection in its constructor, because a check that silently proves nothing is worse than no check.
Full source: tests/Checks/SqlConstantsPrepareTest.php
<?php
// Source: https://github.com/LongTermSupport/sql-first-php-demo/blob/main/tests/Checks/SqlConstantsPrepareTest.php
// Trimmed from tests/Checks/SqlConstantsPrepareTest.php
#[Group('real-db')]
final class SqlConstantsPrepareTest extends TestCase
{
use RealDatabase;
#[Test]
public function everySqlConstantPreparesAgainstTheMigratedSchema(): void
{
$statements = self::applicationSql(); // SqlConstantFinder::find() over example/
// Guard against the check silently checking nothing (a wrong path, a renamed constant).
self::assertGreaterThanOrEqual(4, \count($statements), 'expected to discover the example query classes');
$failures = new ServerSidePrepareValidator($this->rawPdo())->validate($statements);
self::assertSame([], $failures, implode("\n", $failures));
}
#[Test]
public function aRenamedColumnIsCaughtInEveryQueryThatStillUsesIt(): void
{
$this->db()->execute('ALTER TABLE product RENAME COLUMN sale_price TO promo_price');
$failures = new ServerSidePrepareValidator($this->rawPdo())->validate(self::applicationSql());
$failed = array_map(static fn (string $f): string => strstr($f, '::SQL', true) ?: $f, $failures);
self::assertContains(FindProductByIdQuery::class, $failed);
self::assertContains(ListProductsByStatusQuery::class, $failed);
self::assertStringContainsString('sale_price', $failures[0]); // "Unknown column 'sale_price'"
}
}
The first test covers every query class there is or ever will be, because it discovers them rather than listing them, and the assertion on the count guards against the uncomfortable case where a wrong path means it quietly checks nothing. The second test is the demonstration: rename a column underneath the application and every query still spelling the old name is listed, each with its own error.
What a Rename Looks Like
Let's walk through the case the whole structure is designed for. Say sale_price is being renamed to promo_price.
- The migration renames the column in the database.
- In PHP,
ProductTable::COL_SALE_PRICEchanges value. Every hydration and every insert goes through the table class, and the rules stop the usual ways of bypassing it, so all of those follow automatically. - The query constants still spell
sale_pricein their SQL text, because SQL is written as SQL rather than assembled. The prepare check lists every one of them, each with MySQL's own error, so the search is done for you. - If you had renamed the column in the database and forgotten the table class, the schema check would fail on it instead; if you had changed the table class and forgotten the migration, the same check catches it from the other side.
None of that relies on someone remembering to search the codebase.
What This Costs
This is not terse, and is arguably a bit on the verbose side. It might feel like over-engineering, and maybe it is erring on that side. Where it is going to feel attractive is to people shipping important systems, where mistakes cost real money and reputation. Basically - it's really attractive to people working with big, complicated legacy systems that are vitally important and might be handling many zeros of revenue.
There are more files. A query class, a unit test and an integration test for every statement is a real overhead compared with adding a method to a repository, and on a small project with a handful of queries it might well be more structure than the problem deserves. The payoff grows with the number of queries and the number of people touching them.
The SQL text itself still spells out column names, which is a deliberate trade, since keeping SQL readable as SQL is the point of the whole exercise, and the prepare check is what makes it safe, but it does mean a rename touches the heredocs as well as the table class.
Genuinely dynamic queries are awkward, because a search screen with a dozen optional filters doesn't fit neatly into "one constant per statement". The options are a few explicit variants, a single statement that handles optional filters with (:status IS NULL OR status = :status_value)-style conditions (two parameter names bound to the same value, since native prepares will not reuse one, and with an eye on what the pattern does to index use), or, for that one case, a small and carefully contained builder. I don't think there is a perfect answer here, and it is the part of the pattern I would expect to need the most judgement.
It is MySQL-specific in its details. The prepare check relies on server-side prepares behaving as they do in MySQL and MariaDB, and the schema check reads MySQL's information_schema types. The ideas carry over to PostgreSQL, but the code would need adapting.
And the integration tests need a real database in CI. The demo's workflow uses a MySQL service container, which is not much configuration, but it is more than a unit suite that runs anywhere.
What the Demo Leaves Out
The demo leaves out several things a real project would need, to keep the core idea visible: migrations (a plain schema.sql stands in for them), framework wiring, more column types such as dates and JSON, bulk writes, streaming exports and multiple connections. All of them fit the same gateway and none of them change the shape.
The README has a suggested reading order through the files if you want to see how the pieces connect.
The combination matters more than any single piece, since query classes, typed rows and a table class each only help a little on their own. What turns them into something that still holds a year later, after a dozen people have worked on it, is the rules keeping them honest and the two database checks covering the gap static analysis cannot reach. Hopefully the demo gives you enough to try it on a corner of your own codebase and see whether it earns its place.
If you want to discuss your enterprise or legacy PHP development, get in touch directly. You can contract with me personally or, if the work needs more hands, use the team of developers at Edmonds Commerce, the agency I run.
Got a PHP system that needs this kind of attention?
Get in touch