Every typed database layer eventually grows a hole in the wall back to SQL, because the day always comes when you need a recursive CTE or a window function and the fluent builder simply does not have a verb for it, and the design question that actually matters is not whether the hole exists but whether the typed layer and the escape hatch are one system or two products glued together at the network boundary. I watched this go wrong for years in the PHP world, where the query builder in your framework and the mysql_query string you reached for under pressure were different planets with different quoting rules, different transaction scopes, and different ideas about what the schema even was, so a migration written half in the builder and half in raw strings was a coin flip. When we shaped MongrelDB Kit we wanted the typed DSL and raw SQL to be two front doors into the same engine, same catalog, same transaction machinery, and the honest way to describe the result is to admit what the DSL is not, because it is not a SQL generator.

The DSL does not emit SQL

The natural assumption, the one I made before reading the source, is that a declaration like this gets compiled down into CREATE TABLE text and that the query builder assembles SELECT strings under the hood:

export const orders = table('orders', {
  columns: [
    int('id', { primaryKey: true, default: sequenceDefault('orders_id_seq') }),
    int('customer_id', { nullable: false }),
    text('status', {
      enumValues: ['pending', 'paid', 'shipped', 'cancelled'],
      default: staticDefault('pending'),
    }),
    timestamp('placed_at', { default: nowDefault() }),
  ],
  primaryKey: 'id',
  indexes: [index(['customer_id'])],
  foreignKeys: [
    foreignKey(['customer_id'], { table: 'customers', columns: ['id'] }, { onDelete: 'cascade' }),
  ],
});

That assumption is wrong, and it is wrong in an interesting way. The table() call builds a TableSpec, a typed descriptor that Kit validates at construction time (duplicate column names, a primary key referencing a column that does not exist, an index over nothing), persists into the engine’s schema catalog with stable table and column ids, and then uses directly: reads and writes through selectFrom / insertInto push the predicates they can down into the storage engine’s native indexes and compute the rest, joins and grouping and ordering overflow, in memory, with no SQL text produced at any point. Even at the wire boundary the two surfaces stay separate; the remote client speaks typed endpoints, POST /kit/query for a native typed query and POST /kit/txn for an atomic write batch, while SQL statements go to POST /sql, so “the DSL compiles to the same wire format as raw SQL” is not the architecture, it is the thing we deliberately did not build. The payoff for skipping the SQL round trip is that one set of declarations drives type inference, validation, constraint enforcement, and migrations all at once, so renaming a column is a compile error in your TypeScript rather than a runtime surprise three deploys later, and the int('id') up there coming back as a bigint, with the compiler refusing row.id === 1, is the kind of pedantry that has saved me real money.

Where raw SQL is the right door

The DSL is honest about its ceiling, and the ceiling is exactly where the SQL frontend starts. Recursive CTEs, window functions, CREATE TABLE AS SELECT, materialized views, and multi-statement execution live on the raw surface, and the embedded TypeScript API is two calls wide:

const result = await db.sql('SELECT count(*) AS n FROM users');
const rows = await db.sqlRows('SELECT id, email FROM users ORDER BY id');

db.sql(...) hands back an Apache Arrow table and db.sqlRows(...) decodes it to plain objects, while the remote client exposes the same names synchronously because the native binding performs the HTTP call internally, so the shape of your code does not change when you move from embedded to daemon. On top of that surface Kit ships small expression helpers for the extended function catalog, things like percentileCont(events.latency_ms, 0.95) and jsonExtract(events.payload, '$.city'), each returning a { sql: string } fragment you splice into a statement yourself, plus mongreldbFtsRank(text, query) for BM25-style relevance ordering, and virtual tables follow the same pattern, with virtualTable(...) describing a module-backed table that generates its own CREATE VIRTUAL TABLE ... USING ... statement. The rule of thumb I use is that the builder owns the hot paths, the typed CRUD and the filtered scans that run a thousand times a minute, and SQL owns the analytical long tail, the reports and one-off probes where nobody wants type inference anyway; the builder is not an ORM trying to swallow SQL, it is a typed fast lane with a clearly marked exit.

Mixing both inside one migration

The place the two surfaces genuinely meet is the migration runner, and this is where the “one system, two doors” claim either holds or falls apart. A TypeScript migration’s up(ctx) is imperative, and the context object carries both doors: helpers like ctx.ensureTable(table) and ctx.addColumn(...) work from the typed TableSpec, and ctx.sql(sql) runs raw SQL inside the same migration transaction, so a single versioned change can create a table from its spec and then install a view over it without leaving the runner:

await migrate(db, schema, [
  {
    version: 7,
    name: 'orders_reporting_view',
    async up(ctx) {
      await ctx.ensureTable(orders);
      await ctx.sql(`
        CREATE VIEW paid_orders AS
        SELECT id, customer_id, placed_at FROM orders WHERE status = 'paid'
      `);
    },
  },
]);

There are two catches worth knowing before you reach for this, and both are load-bearing rather than incidental. First, ctx.sql() is available in async migrations only and throws in migrateSync, because the sync runner has no suspension point for the SQL frontend, so the moment a migration needs raw SQL you commit to the async migrate(db, schema, migrations) helper for the whole run. Second, the optional ops array on a migration is metadata in TypeScript, not an execution plan; the runner folds it into the content-addressed checksum so that an after-the-fact edit to an applied migration is caught as drift, but your up() is what actually does the work, which is exactly the split you want, since the thing that runs and the thing that is audited are allowed to be expressed differently. Either way both doors land in the same __kit_schema_migrations history under the same advisory lock, which is the entire point: there is no shadow schema state that only the SQL side knows about.

The tradeoff you actually sign

The seam I watch in production is the remote-mode authority boundary, because it is the one place the two doors are not symmetric. The daemon enforces engine-level constraints, unique, foreign-key actions, checks, atomically and server-side for both surfaces, but the Kit-specific field validations, defaults, enums, min/max bounds, regex patterns, live in the client, so a remote caller who bypasses the typed layer and hand-writes INSERT statements against /sql is opting out of that richer validation and accepting the engine’s floor as the whole contract, and that is a deliberate boundary, not an accident we plan to paper over. I will take that trade every time, and the comparison I keep reaching for is the old LAMP habit of treating the query builder as a toy and raw SQL as the real thing, which gave you one honest surface and one decorative one; the modern equivalent of doing it right is two surfaces that share a catalog, a transaction, and a migration history, where choosing between them is a per-query judgment about types and ergonomics rather than a per-project bet about which subsystem will still be maintained in three years.