Type-Safe SQL Queries Across Multiple Dialects

Schema Yields Typed Queries

Typos in raw SQL hide until production, and this builder catches them at compile time by inferring types from schema definitions.

A schema feeds the builder, which emits parameterized queries with types inferred from definitions, so the compiler flags a misspelled column before runtime, and it generates correct SQL for SQLite, PostgreSQL, and MySQL without dialect rewrites.

Hidden SQL, Silent Strings

Most database tools fall into two camps. ORMs hide SQL behind abstractions that break at scale, while raw strings offer no type safety and break when columns rename. The gap was a builder that stays close to SQL and infers types directly from schema.

Developers writing queries late at night make mistakes that type systems should catch, but raw SQL bypasses the compiler entirely, and ORMs that catch them often generate queries so far from intent that debugging becomes archaeology, so the goal was a builder that enforces names, types, and nullability from one schema, while producing SQL that reads like hand-written code.

Typos at 2 AM

• ORMs hide SQL behind object graphs that break at scale and debug poorly.
• Raw string queries bypass the compiler and break silently on column renames.
• Multi-database support usually means branching SQL per dialect in app code.
• Late-night typos in raw SQL reach production before anyone catches them.

Same Query, Three Dialects

• Define a table once with typed columns and let the compiler infer TypeScript types.
• Chain select, where, join, group, order, and limit into a single query state.
• Compile to parameterized SQL per dialect with placeholder and clause adjustments.
• Run the same query on SQLite, PostgreSQL, and MySQL without branching.
• Validate identifiers and inputs before compilation with informative error context.

Builder Meets Compiler

The strategy is one builder, many dialects. A schema engine defines tables with typed columns, feeding a compiler that translates query state into parameterized SQL per dialect, adjusting placeholders, conflict resolution, and returning clauses based on what each database supports, so the same query runs on SQLite, PostgreSQL, and MySQL without any branching in code.

One schema, three dialects, zero branching. The compiler reads capabilities and adjusts SQL per database, handling placeholders, conflict resolution, and returning clauses without code changes, so the same typed query runs unchanged across all three.

Every method call returns a new builder instead of mutating state, so partial queries compose and reuse without side effects, while a capabilities system per driver tells the compiler what each database supports from returning clauses to conflict resolution to placeholders, letting the same chain produce correct SQL whether the target uses positional or numbered params.

Each Query, Compiled

Every query starts as a schema and ends as parameterized SQL. A table builder maps column types to TypeScript types, feeding the query builder, which chains select, where, join, and order into a state object, and a compiler walks that state to produce SQL with bound parameters, adjusting placeholders and syntax per dialect, so the same chain runs on any database.

// Define a table, build a query, compile to SQL
const users = Schema.table('users', {
  id: Schema.column.integer().primaryKey().autoIncrement(),
  name: Schema.column.text(),
  email: Schema.column.varchar(255).unique()
})

const rows = await db.from(users)
  .select('name', 'email')
  .where('id', '>', 10)
  .orderBy('name', 'ASC')
  .limit(20)
  .all()

A validator catches invalid identifiers and unsafe inputs before compilation, throwing errors that name the exact column or table that failed, so mistakes surface at build time with context instead of at runtime with a cryptic database error message.

Transactions support nested savepoints with automatic rollback on failure, keeping writes atomic without manual recovery steps.

Beyond basic queries, the builder blocks mutations without a WHERE clause, auto-batches bulk inserts into transactional chunks, and exposes window functions, table expressions, and conditional logic through the same typed chain without raw strings.

Shipped per Dialect

Feature	Delivered
Schema	Typed columns with TypeScript inference
Query	Select, where, join, group, order, limit, union
Advanced	Window functions, CTE, CASE, row locking
Dialects	SQLite, PostgreSQL, MySQL from one schema
Safety	Mutation guards, identifier validation, parameterized
Transaction	Nested savepoints with automatic rollback
Performance	Connection pooling, auto-batching, pagination

Staying Close to SQL

• Staying close to SQL beat abstracting it away, as a builder that produces readable SQL lets developers debug with EXPLAIN and profile real queries while ORMs that hide SQL make issues invisible until production.
• Immutable builders prevented side effects when reusing partial queries across different call sites.
• Capabilities per dialect prevented feature assumptions, as what SQLite supports MySQL may not and the compiler handles that.
• Type inference from schema caught typos at build time, not at 2 AM in production.
• Parameterized queries by default eliminated an entire class of injection risks.
• Mutation guards blocking updates and deletes without a WHERE clause prevented accidental full-table wipes.

The Compiler Catches Mistakes

What began as a way to catch typos before production became a builder running across three databases from one schema. Define, query, compile, and execute share one entry point, keeping the loop from schema to SQL short and error messages useful.

One schema, three dialects, zero raw strings. The compiler catches mistakes so developers do not have to, even at very late 2 AM.