A prepared statement knows where values belong in SQL. It does not know that one value is a user identifier, that the identifier determines a shard, that a hexadecimal string must become binary, or that a read must go to a primary for read-after-write consistency. Those are application semantics, and they usually accumulate around every query as repetitive code.
A thin programmable layer can carry that information in the query template itself:
SELECT id, display_name
FROM user
WHERE id = #u
AND state = #s Each marker invokes a registered callback. A simple marker such as #i validates and normalizes an integer. A binary marker such as #x decodes a hexadecimal representation. A semantic marker such as #u binds a user ID and contributes the shard that owns it. The important modern refinement is that none of these callbacks quote data into SQL. They compile to ordinary placeholders and typed bindings.
The Historical Model
I originally built this layer in 2010, when horizontal partitioning was already practiced by large systems but sharding was not yet everyday vocabulary in the PHP application stacks I was working with. The first version used PHP 5.3, mysqli, mysqlnd, APC, a custom formatting function, and a read/write connection manager. Its most useful idea was not the database wrapper itself. It was that a query could carry enough semantic information for the layer to decide where and how to execute it.
The user database used a hybrid placement scheme. Most identifiers mapped directly to a cluster with a modulo calculation. One reserved identifier class represented movable users and triggered a lookup in a global directory:
slot = user_id mod cluster_size
if slot is not zero
return slot
if user_id belongs to the current session
return the session's cached cluster
return directory.lookup(user_id) That gave the common path a constant-time calculation with no database request, while selected identities retained a level of indirection and could move. At the beginning, every logical shard could be a separate schema on one MySQL server. The same application API remained in place when schemas moved to separate machines and each shard acquired a primary and replicas.
The model worked well at scale because routing was cheap, connections were reused per target, exceptional lookups were sparse and cacheable, and application code did not need deployment-specific connection logic. It also exposed the hard limits that any sharded system eventually meets: a directory can become a critical dependency, changing a modulo divisor remaps keys, replica reads can be stale, and a write involving two shards is not an ordinary local transaction.
What the Markers Really Represent
The original implementation mixed several responsibilities into one marker table. Separating them reveals where the idea remains useful:
#iis a value normalizer: accept only a valid integer representation and bind an integer.#sis a value contract: require a string and bind it without manual quoting.#xis a codec: validate hexadecimal input, decode it, and bind binary data.#uis a domain marker: normalize a user ID, bind it, and resolve its shard.- A read or write declaration is an execution intent: select a consistency policy and an eligible connection after compilation.
Other historical markers do not belong in this layer. HTML escaping is an output-context concern and must happen in the view, not before storage. An IN list needs a dedicated operation that emits one placeholder per value; binding a CSV string is not equivalent. Dynamic identifiers must come from an application allowlist because prepared statement placeholders can represent complete data values, not table names, column names, keywords, or arbitrary SQL fragments.
Bindings and Semantic Markers Solve Different Problems
Classic parameter binding remains the correct SQL-injection boundary. The database driver keeps SQL structure separate from values, handles binary data without quoting tricks, and can reuse statement metadata or plans. A custom layer should not compete with that mechanism.
Semantic markers add information that a driver placeholder cannot express:
- Strict application-level validation before PHP or MySQL performs a permissive cast.
- Normalization of domain values such as IDs, timestamps, UUIDs, IP addresses, and binary keys.
- Shard, tenant, region, consistency, or primary/replica requirements attached to a query plan.
- Early rejection of accidental cross-shard operations.
- One place for metrics, route traces, redacted binding types, and stable query fingerprints.
The costs are real. A private query language is less visible to IDEs and database tooling, callbacks can hide too much behavior, and a simplistic regular expression can mistake marker-like text inside strings or comments for code. Keep the grammar small, make compiled plans inspectable, and test both successful and rejected templates.
A Minimal Compiler in PHP
The following implementation demonstrates the boundary. Marker callbacks return a value, a PDO binding type, and an optional route. They do not open connections and cannot inject SQL fragments. The compiler produces a plan; a pool or executor consumes that plan afterward.
<?php
final class BoundValue
{
public function __construct(
public mixed $value,
public int $pdoType,
) {}
}
final class MarkerEffect
{
public function __construct(
public mixed $value,
public int $pdoType,
public ?string $route = null,
) {}
}
final class QueryContext
{
public function __construct(
public int $shardCount,
public array $relocations = [],
) {
if ($shardCount < 1) {
throw new InvalidArgumentException('shardCount must be positive.');
}
}
public function shardForUser(int $userId): string
{
if (array_key_exists($userId, $this->relocations)) {
return $this->relocations[$userId];
}
return sprintf('user-%02d', $userId % $this->shardCount);
}
}
final class QueryPlan
{
public function __construct(
public string $intent,
public string $sql,
public array $bindings,
public ?string $route,
) {}
public function execute(PDO $pdo): PDOStatement
{
$statement = $pdo->prepare($this->sql);
foreach ($this->bindings as $placeholder => $binding) {
$statement->bindValue(
$placeholder,
$binding->value,
$binding->pdoType,
);
}
$statement->execute();
return $statement;
}
}
final class SemanticSqlCompiler
{
private array $handlers = [];
public function register(string $marker, Closure $handler): self
{
if (preg_match('/^[A-Za-z]$/D', $marker) !== 1) {
throw new InvalidArgumentException('A marker must be one ASCII letter.');
}
$this->handlers[$marker] = $handler;
return $this;
}
public function compile(
string $intent,
string $template,
array $arguments,
QueryContext $context,
): QueryPlan {
if ($intent !== 'read' && $intent !== 'write') {
throw new InvalidArgumentException('Intent must be read or write.');
}
$argumentIndex = 0;
$bindings = [];
$route = null;
$sql = preg_replace_callback(
'/#([A-Za-z])\b/',
function (array $match) use (
$arguments,
&$argumentIndex,
&$bindings,
&$route,
$context,
): string {
$marker = $match[1];
$handler = $this->handlers[$marker] ?? null;
if ($handler === null) {
throw new InvalidArgumentException("Unknown marker #$marker.");
}
if (!array_key_exists($argumentIndex, $arguments)) {
throw new InvalidArgumentException("Missing value for #$marker.");
}
$effect = $handler($arguments[$argumentIndex], $context);
++$argumentIndex;
if (!$effect instanceof MarkerEffect) {
throw new LogicException("Handler #$marker returned an invalid effect.");
}
if ($effect->route !== null) {
if ($route !== null && $route !== $effect->route) {
throw new DomainException('The query crosses shard boundaries.');
}
$route = $effect->route;
}
$placeholder = ':p' . (count($bindings) + 1);
$bindings[$placeholder] = new BoundValue(
$effect->value,
$effect->pdoType,
);
return $placeholder;
},
$template,
);
if ($sql === null) {
throw new RuntimeException('The query template could not be compiled.');
}
if ($argumentIndex !== count($arguments)) {
throw new InvalidArgumentException('Too many values for the query template.');
}
return new QueryPlan($intent, $sql, $bindings, $route);
}
}
$toInteger = static function (mixed $value): int {
if (is_int($value)) {
return $value;
}
if (!is_string($value)
|| preg_match('/^-?(?:0|[1-9][0-9]*)$/D', $value) !== 1
) {
throw new InvalidArgumentException('Expected an integer.');
}
$integer = filter_var($value, FILTER_VALIDATE_INT);
if ($integer === false) {
throw new RangeException('The integer is outside the PHP integer range.');
}
return $integer;
};
$compiler = new SemanticSqlCompiler();
$compiler->register(
'i',
static fn (mixed $value, QueryContext $context): MarkerEffect =>
new MarkerEffect($toInteger($value), PDO::PARAM_INT),
);
$compiler->register(
's',
static function (mixed $value, QueryContext $context): MarkerEffect {
if (!is_string($value)) {
throw new InvalidArgumentException('Expected a string.');
}
return new MarkerEffect($value, PDO::PARAM_STR);
},
);
$compiler->register(
'x',
static function (mixed $value, QueryContext $context): MarkerEffect {
if (!is_string($value)
|| strlen($value) % 2 !== 0
|| ctype_xdigit($value) === false
) {
throw new InvalidArgumentException('Expected an even-length hexadecimal string.');
}
$binary = hex2bin($value);
if ($binary === false) {
throw new InvalidArgumentException('The hexadecimal value is invalid.');
}
return new MarkerEffect($binary, PDO::PARAM_LOB);
},
);
$compiler->register(
'u',
static function (mixed $value, QueryContext $context) use ($toInteger): MarkerEffect {
$userId = $toInteger($value);
if ($userId < 1) {
throw new InvalidArgumentException('A user ID must be positive.');
}
return new MarkerEffect(
$userId,
PDO::PARAM_INT,
$context->shardForUser($userId),
);
},
); The compiler can now turn one semantic template into an inspectable plan:
$context = new QueryContext(
shardCount: 16,
relocations: [3200 => 'user-23'],
);
$plan = $compiler->compile(
intent: 'read',
template: 'SELECT id, payload FROM document
WHERE owner_id = #u AND revision >= #i AND digest = #x',
arguments: [3200, '18', 'c0ffee'],
context: $context,
); The resulting SQL contains :p1, :p2, and :p3. The bindings contain an integer, another integer, and three binary bytes. The route is user-23. No value has been interpolated into SQL, and no connection has been opened yet.
An executor can now ask a pool for the correct connection and apply an explicit consistency policy:
$pdo = $pool->connection(
shard: $plan->route,
intent: $plan->intent,
consistency: 'read-your-writes',
);
$statement = $plan->execute($pdo); Configure PDO to throw exceptions and choose native or emulated prepares deliberately. Every generated placeholder is unique because PDO does not portably support reusing one named placeholder several times. Stable marker types also avoid needless MySQL repreparation when the same statement is executed repeatedly.
Keep the Compiler Honest
The regular expression above is the smallest useful demonstration, not a general SQL parser. It reserves every # followed by one letter as marker syntax. Templates using that form must not place marker-like sequences inside quoted literals or comments; use conventional placeholders there or replace the regular expression with a lexer that understands the target SQL dialect. MySQL's own # line-comment syntax is another reason to use -- or block comments in these templates.
Keep these invariants in production:
- Templates are developer-authored code. User input is accepted only as marker arguments.
- Value callbacks return values and metadata, never raw SQL.
- Structural variation uses a finite allowlist or a typed query builder.
- Compilation is deterministic and performs no network I/O.
- The selected route, intent, consistency mode, template fingerprint, and binding types are observable.
- Cross-shard reads and writes require an explicit scatter, saga, or distributed-transaction policy.
Evolving the Sharding Model
The historical modulo scheme assumed a fixed cluster count. Changing that count moves most keys, so a modern router would use stable key ranges, virtual shards, consistent hashing, an ID-encoded home shard, or a directory with cached placement records. The sparse-override idea remains valuable: calculate the normal placement locally and consult an indirection record only for entities that have moved.
A move still needs an explicit protocol. Copy the entity, replicate or replay changes, verify the destination, publish a versioned route, invalidate caches, cut traffic over, and retain a forwarding record long enough for stale clients. A callback can attach the resulting route to a plan, but it cannot make those distributed-state transitions atomic.
Read/write splitting needs the same honesty. A marker can indicate the owning shard, while the plan's intent and consistency mode decide between primary and replica. Reads after a write, transactions, locking reads, and lag-sensitive operations should stay on the primary. Replica routing is a policy, not something to infer from whether a string begins with SELECT.
Why the Idea Still Holds Up
The original syntax looked like a friendlier alternative to parameter binding, but its lasting value was richer: compact markers connected domain meaning to infrastructure decisions. Used carelessly, that becomes invisible magic. Used as a small compiler that emits ordinary prepared statements and an inspectable execution plan, it becomes a practical boundary between application code, data normalization, and database topology.
That division also gives the design a clean migration path. A single-node application can emit the same plans while every route points to one connection. Shards, replicas, regional placement, or tenant isolation can be introduced in the executor without replacing every query. The query text stays close to SQL, while the layer adds only the semantics SQL and its parameter protocol cannot carry by themselves.
References
- [PDOPrepare]PHP Manual: PDO::prepare.
- [PDOBindValue]PHP Manual: PDOStatement::bindValue.
- [MySQLPrepare]MySQL 8.4 Reference Manual: Prepared Statements.
- [Vitess]Vitess Documentation: Scalability Philosophy.