zql

The problem

At a previous job I worked with Prisma, and it asked for more ceremony than I wanted to give it. The schema had to be defined in Prisma’s own DSL, a format I had no interest in learning. Queries were written in JavaScript with limited support for anything beyond a left join. The types it did generate stopped short of where I needed them: frontend interfaces and DTOs still had to be written and maintained by hand, and the queries themselves were never type validated.

So in the first version of Agates I went the other way and wrote my own wrappers around raw SQL. That gave me the SQL I wanted, but raw SQL is not type safe. Even a small change to the schema meant rewriting tons of boilerplate by hand, with nothing to catch what I missed.

Neither approach gave me all three things at once: real SQL, end-to-end type safety, and validation I could trust at runtime.

The approach

zql is a thin layer that composes a few focused tools instead of replacing them:

• pgtyped reads the real database and types each raw SQL query from the schema itself, so the types cannot drift from what Postgres will actually return.
• Zod validates inputs at the boundary, so malformed data is caught before it reaches the query.
• A small query factory wires these together and handles automatic snake_case to camelCase conversion, so the database stays idiomatic SQL while the application code stays idiomatic TypeScript.

The SQL stays visible and hand-written, the types come from the database rather than a hand-maintained model, and the inputs are validated at runtime. All three at once, not two.

Since then zql has grown from a pattern into a toolchain. It now ships a CLI: zql init scaffolds a project (including the patched pgtyped wire fork it maintains), zql generate runs the whole pipeline, pgtyped, schema emission, and codegen, under a stall watchdog, and zql doctor diagnoses the setup problems that used to cost an afternoon, like a duplicate wire copy in the dependency tree. Codegen emits sibling Zod schema files with enum support and optional date coercion, and the query factory gained $transaction support, named validation errors, and JSONB-safe case conversion.

The outcome

zql is the data layer powering Agates From Mexico, ESPAU, Increscendo, Danny Cuevas, and the Café Combate storefront in production. Because the types come straight from the schema, a database change now surfaces as a type error at compile time instead of boilerplate I have to remember to rewrite, or a bug that only shows up in production. Each new project starts from a foundation the others have already hardened.