Schema Management from PHP

Every PHP codebase that talks to a database long enough develops the same quiet bug: the migration that created the table says one thing, the ORM’s annotated entity says another, and the actual catalog on the server says a third, and nobody notices until a nullable column meets a not-null assumption in production at 2 AM. Raw DDL over a sql() call makes this worse rather than better, because a CREATE TABLE string is fire-and-forget; you can send it, but you cannot parse it back, diff it, or assert against it without writing a SQL parser of your own, which is a project nobody finishes. The MongrelDB PHP client takes the other route: schema is typed data in both directions, so the array you pass to createTable() has the same shape as the descriptor you read back from schemaFor(), and the gap between “what I deployed” and “what the server has” becomes a comparison you can run in a test instead of a hope.

Creating a table as data, not as a string

createTable() takes a name, a list of column definitions, and optional constraint and index lists, and it returns the table ID the engine assigned; each column is a plain PHP array with an id, a name, a type under ty, and the flags you would expect, so the definition reads like a config file rather than a dialect you have to quote and escape:

use Visorcraft\MongrelDB\Database;

$db = new Database('http://127.0.0.1:8453');

$tableId = $db->createTable('orders', [
    ['id' => 1, 'name' => 'id',     'ty' => 'int64',   'primary_key' => true,  'nullable' => false],
    ['id' => 2, 'name' => 'status', 'ty' => 'enum',    'primary_key' => false, 'nullable' => false,
        'enum_variants' => ['new', 'paid', 'cancelled']],
    ['id' => 3, 'name' => 'amount', 'ty' => 'float64', 'nullable' => false],
    ['id' => 4, 'name' => 'tag',    'ty' => 'varchar', 'default_value' => 'standard'],
    ['id' => 5, 'name' => 'ref',    'ty' => 'varchar', 'default_expr' => 'uuid'],
    ['id' => 6, 'name' => 'placed', 'ty' => 'timestamp', 'default_expr' => 'now'],
]);

The distinction between default_value and default_expr is worth a beat, because it is the kind of thing a DDL string blurs: default_value is a literal the engine stores as-is, so the tag column above really does get the four characters standard on every insert that omits it, while default_expr names an expression the engine evaluates at insert time, which is how uuid and now end up generating a fresh identifier and a server-side timestamp instead of a string that merely says “uuid”. The client is also careful about the wire shape it emits, and this is the unglamorous detail that saves you later: optional keys you did not set are simply absent from the JSON body, so a column without an enum does not send an empty enum_variants array for the server to misread, and the conformance tests in the repo assert exactly that by capturing the literal POST to /kit/create_table.

Indexes and constraints ride in the same call

Because the schema is a payload rather than a statement, the indexes and check constraints are just more arrays on the same request, which means a table and its access paths are created atomically instead of in three migrations you hope ran in order:

$db->createTable('docs', [
    ['id' => 1, 'name' => 'id',     'ty' => 'int64',   'primary_key' => true],
    ['id' => 2, 'name' => 'status', 'ty' => 'varchar', 'nullable' => false],
    ['id' => 3, 'name' => 'body',   'ty' => 'varchar', 'nullable' => false],
    ['id' => 4, 'name' => 'hits',   'ty' => 'int64',   'default_value' => 0],
], [
    'checks' => [[
        'id'   => 1,
        'name' => 'ck_hits_range',
        'expr' => ['And' => [
            ['Ge' => [['Col' => 4], ['Lit' => ['Int64' => 0]]]],
            ['Le' => [['Col' => 4], ['Lit' => ['Int64' => 1000000]]]],
        ]],
    ]],
], [
    ['name' => 'bm',    'column_id' => 2, 'kind' => 'bitmap'],
    ['name' => 'fm',    'column_id' => 3, 'kind' => 'fm_index'],
    ['name' => 'range', 'column_id' => 4, 'kind' => 'learned_range'],
]);

The check expression is a small typed tree rather than a SQL fragment, with Col naming a column by ID, Lit carrying a typed value, and the comparison and boolean nodes composing them, so ck_hits_range reads as “0 <= hits <= 1000000” and the engine evaluates it atomically at commit time against the whole transaction instead of row-by-row as a trigger would. The kind strings in the index list name the same index families the query builder targets, so the bitmap on status, the FM-index on body, and the learned-range index on hits you declare here are the ones your where('bitmap_eq', ...) and where('fm_contains', ...) conditions will hit later, and keeping both sides in one typed vocabulary is what makes the schema the contract rather than a suggestion.

Reading the catalog back

The half of schema management most clients skip is the read path, and it is the half that pays for the typed approach; tables() lists names, schema() returns the full catalog as a map of table name to descriptor, and schemaFor() fetches one table, all over plain GETs against /kit/schema:

$names = $db->tables();                 // ['orders', 'docs', ...]
$catalog = $db->schema();               // every table descriptor, keyed by name
$orders = $db->schemaFor('orders');     // one table's descriptor

// The drift check that used to require a parser:
$amount = array_values(array_filter(
    $orders['columns'],
    fn (array $c) => $c['name'] === 'amount',
))[0] ?? null;

if ($amount === null || $amount['nullable'] !== false) {
    throw new RuntimeException('orders.amount drifted; aborting deploy');
}

That last block is the whole argument in miniature, because the descriptor the server returns is the same shape as the definition you sent, so verifying a deploy is an array_filter and an equality check rather than a regex over SHOW CREATE TABLE output, and the check can live in a deploy script or a PHPUnit bootstrap where it fails loudly instead of silently corrupting a quarter of reports. When the table is genuinely done, dropTable() removes it by name, and the catalog read will confirm that too.

When raw sql() is still the right tool

None of this makes the escape hatch wrong, and the client keeps sql() around for the things a typed surface should not pretend to own, like recursive CTEs over the org chart or a one-off analytical query that will never run twice; the honest split is that DDL and schema verification belong in typed calls where both directions are data, while exploratory SQL belongs in a string where flexibility beats structure. The rule I would hand a team is the one we would have wanted in the mysql_* era, when we edited schemas in phpMyAdmin and prayed: create and verify through the typed API, explore through SQL, and never let a string you cannot read back be the only record of what your database looks like.