How-to › Model, configure and generate

How to emit TypeScript types and SQL from one application model#

Write TypeScript entity types and a SQL schema from one @voxgig/model model, and fail the build when a stored row does not match its type.

Audience
API producer
Level
intermediate
Topic
Model an application and generate from it
Languages
TypeScript and sql
Verified

Your Order type says paid: boolean, and the row the database returns says paid: 0. Both files were written by hand at different times, without a build step that compares them. So the code compiles, and a caller that tests order.paid === false takes the wrong branch.

What you get

You will end up with one model that writes TypeScript types and a SQL schema, and a round-trip check that fails the build when a stored row does not match its type. This is for you if your entity types and your tables are maintained by hand, side by side.

Short answer

Describe each field in an @voxgig/model model by an abstract kind, and declare two actions that map every kind to TypeScript and to SQL through one shared table. Set the action order explicitly. Then insert a row against the generated schema, read it back through the generated decoder, and check every value against the generated interface, so a mismatch fails the build.

You will need

Node 22.18 or later and a model in aontu files. Run npm ci in the sample directory, which installs @voxgig/model with pino, its logger, and typescript for the check. The round trip uses node:sqlite, which prints an experimental warning on stderr, and imports types.ts through Node’s type stripping, on by default from 22.18.

Approaches compared

ApproachWhen it fitsWhat it costs youWhen to pick something else
@voxgig/model actionsYou want the mapping in your own code, and fields that carry whatever keys a target needsYou write every mapping, every decoder and every check yourselfYour team wants a database toolkit with migrations included
Kiota modelsYour model is an OpenAPI document and you want typed client models that keep defaultsEvery property comes out optional and nullable, and the SQL is yours to writeThe types must say which fields are required
Prisma schemaThe database is the center, and you want migrations and a typed client from one fileA schema language, a CLI with telemetry on by default, and types shaped by its clientThe types must also serve code that never touches the database
Swagger Codegen modelsYour model is an OpenAPI document and a client SDK needs its typesNullable fields lose their null, defaults vanish, and no target writes SQLNulls and defaults have to survive into the types
TypeSpec emittersYou describe APIs in TypeSpec and want several outputs from one definitionNo SQL emitter is listed, so you write one against the compiler APIYou need SQL from the model without writing an emitter

Voxgig maintains @voxgig/model, the subject of this page. Prisma, Kiota, Swagger Codegen, and TypeSpec are maintained elsewhere, and Prisma ships the migrations this page leaves out.

The model route asks the most code of you and keeps every mapping decision in view. Prisma writes both outputs for less code, as long as its client is the code reading the rows. The OpenAPI routes give you client types and leave the SQL to you. TypeSpec was read from its documentation only. Its standard library, as that page describes it, emits OpenAPI 3.0, JSON Schema and Protobuf, so SQL needs an emitter of your own.

Describe each field by its kind

A field in this model names what it means, never how a column stores it. The kind is a closed disjunction, so 'BOOLEAN' or 'TIMESTAMP' written there fails unification.

# A field has an abstract kind, never a column type. The actions map each kind
# to TypeScript and to SQL, so a database name here fails unification.
shape: field: {
  kind: *'text' | 'int' | 'bool'
  required: *false | boolean
}
main: ent: order: field: {
  id:       $.shape.field & { kind: 'int', required: true }
  customer: $.shape.field & { kind: 'int', required: true, ref: 'customer' }
  total:    $.shape.field & { kind: 'int', required: true }
  note:     $.shape.field
  status:   $.shape.field & { required: true, default: 'new' }
  paid:     $.shape.field & { kind: 'bool', required: true, default: false }
}

default and ref are not in the shape. The maps are open, so an entity adds a key that one target needs, and the action that needs it reads it. That is the answer here to what a target needs and the model cannot express: add the key, then make the action refuse a value it does not understand.

Write both artifacts from one mapping

The two actions read the model through one function and one table, so for each of the three kinds the TypeScript type and the column type come from one row.

// The one mapping from a field kind to TypeScript and to SQL. Both actions read
// the model through entities(), so they cannot disagree about a field.
const KINDS = new Map([
  ['text', { ts: 'string', sql: 'TEXT', accepts: (v) => typeof v === 'string' }],
  ['int', { ts: 'number', sql: 'INTEGER', accepts: (v) => Number.isSafeInteger(v) }],
  ['bool', { ts: 'boolean', sql: 'INTEGER', accepts: (v) => typeof v === 'boolean' }],
])
const NAME = /^[a-z][a-z0-9_]*$/

The agreement covers what the table holds, and the suite tests each edge. A field added to the model reaches both files. A name outside [a-z][a-z0-9_]* is refused, so a quote, a backtick, or a semicolon never reaches either file, and a keyword such as select is quoted in SQL. An enum or a nested object is not a kind and fails unification. An int column holds 64 bits, but number holds 53, and node:sqlite throws reading a larger value.

The config declares both actions and their order.

sys: model: action: {
  types: load: 'build/types.cjs'
  sql:   load: 'build/sql.cjs'
}
# Without this line the actions run in sorted key order, sql before types.
sys: model: order: action: 'types,sql'

The reference says that without the order line the actions run in declaration order. build.mjs runs voxgig-model on a scratch copy of model/ and build/ as committed, then again without that line.

node build.mjs
voxgig-model model/model.aontu, on a scratch copy
  post actions ran: types, sql
  generated/types.ts    66 lines, same as the committed file
  generated/schema.sql  18 lines, same as the committed file
the same build without sys.model.order.action
  post actions ran: sql, types

Without the line, sql ran first: the unified config’s keys come out sorted. Nothing here depends on the order, but an action that reads the output of another would. The SQL action quotes every identifier, because order is a reserved word.1

Round trip a row through both

The types file holds two interfaces per entity. Order is a row as SQLite returns it, so a nullable column is present and null. OrderInput is what an insert must supply, so a field with a default is optional.

export interface Order {
  id: number
  customer: number
  note: string | null
  paid: boolean
  status: string
  total: number
}

export interface OrderInput {
  id?: number
  customer: number
  note?: string | null
  paid?: boolean
  status?: string
  total: number
}

roundtrip.mjs applies generated/schema.sql to an in-memory database and inserts the rows in inserts.json. It first checks each value against the Input interface, read from types.ts with the TypeScript compiler API, and refuses a wrong type by field name. It binds the row through the generated encoder, which writes a boolean as 1 or 0, because node:sqlite cannot bind a boolean. It reads each row back through the generated decoder, and checks every value against the row interface.

node roundtrip.mjs
customer: sent email
  read back {"id":1,"email":"ada@example.com","name":null,"vip":false}
  matches Customer
order: sent customer, total
  read back {"id":1,"customer":1,"note":null,"paid":false,"status":"new","total":1250}
  matches Order

The insert sent two fields. status and paid came back with their defaults, note came back null, and paid is false because the decoder turned SQLite’s 0 into a boolean.2 The script exits 1 on a mismatch, so a build step that runs it fails. The fixture leaves both booleans out, so a test in the suite sends vip and paid as true and then as false, and reads each back as the same boolean.

Keep column types out of the model

pitfall/ holds the same order written with column types, and actions that map each column type to the TypeScript type it suggests. pitfall.mjs round trips its output, then makes the same edit in the main model.

node pitfall.mjs
column types in the model (pitfall/)
  types.ts declares  paid: boolean, placed: Date
  MISMATCH paid: declared boolean, read number
  MISMATCH placed: declared Date, read string
abstract kinds in the model, with paid: kind 'BOOLEAN'
  build failed: [aontu/empty]: Cannot unify values at path $.main.ent.order.field.paid.kind
  Cannot unify value: "BOOLEAN" with value: *"text"|"int"|"bool"

BOOLEAN reads as boolean and TIMESTAMP reads as Date, and SQLite returns a number and a string. The TypeScript output is wrong before any code uses it, and only the round trip shows it. With abstract kinds, the same edit stops at unification with both file positions in the message.

Carry the same fields through Swagger Codegen and Kiota

A team that generates its SDK with Swagger Codegen or Kiota keeps its model as an OpenAPI document, here openapi/openapi.json. Both generators write TypeScript models from it, but not SQL. generate.sh searched every template in the Swagger Codegen 3.0.85 jar for CREATE TABLE and found it only in a Gatling load-test template’s own tables, and its langs list holds no SQL target. Kiota 1.39.0’s generate takes nine languages, none of them SQL. The working substitute is openapi/openapi-sql.mjs, which reads components.schemas and writes the DDL, quoting every name and refusing a default of the wrong type.

generate.sh ran both generators, and Prisma 7.10.1 on prisma/schema.prisma, and committed excerpts. Prisma 7.10.1 sends a usage checkpoint unless CHECKPOINT_DISABLE is set, and the script sets it. Prisma’s telemetry page, written for Prisma 8, names PRISMA_DISABLE_TELEMETRY and DO_NOT_TRACK, which 7.10.1’s CLI does not read. compare.mjs reads the committed files.

node compare.mjs
order.note
  @voxgig/model           note: string | null     input note?: string | null  TEXT
  Prisma 7.10.1           note: string | null     input note?: string | null  TEXT
  Swagger Codegen 3.0.85  note?: string           one type                    TEXT
  Kiota 1.39.0            note?: string | null    no default on read          TEXT
order.paid
  @voxgig/model           paid: boolean           input paid?: boolean        INTEGER NOT NULL DEFAULT 0
  Prisma 7.10.1           paid: boolean           input paid?: boolean        BOOLEAN NOT NULL DEFAULT false
  Swagger Codegen 3.0.85  paid: boolean           one type                    INTEGER NOT NULL DEFAULT 0
  Kiota 1.39.0            paid?: boolean | null   read ?? false               INTEGER NOT NULL DEFAULT 0
order.status
  @voxgig/model           status: string          input status?: string       TEXT NOT NULL DEFAULT 'new'
  Prisma 7.10.1           status: string          input status?: string       TEXT NOT NULL DEFAULT 'new'
  Swagger Codegen 3.0.85  status: string          one type                    TEXT NOT NULL DEFAULT 'new'
  Kiota 1.39.0            status?: string | null  read ?? "new"               TEXT NOT NULL DEFAULT 'new'
order.total
  @voxgig/model           total: number           input total: number         INTEGER NOT NULL
  Prisma 7.10.1           total: number           input total: number         INTEGER NOT NULL
  Swagger Codegen 3.0.85  total: number           one type                    INTEGER NOT NULL
  Kiota 1.39.0            total?: number | null   no default on read          INTEGER NOT NULL
Swagger Codegen and Kiota write no SQL: their column is openapi/openapi-sql.mjs.

Swagger Codegen keeps required and drops nullable, so note?: string cannot hold the null the database returns. Kiota marks every property optional and nullable, and keeps the defaults in its deserializer instead:

export function deserializeIntoOrder(order: Partial<Order> | undefined = {}) : Record<string, (node: ParseNode) => void> {
    return {
        "customer": n => { order.customer = n.getNumberValue(); },
        "id": n => { order.id = n.getNumberValue(); },
        "note": n => { order.note = n.getStringValue(); },
        "paid": n => { order.paid = n.getBooleanValue() ?? false; },
        "status": n => { order.status = n.getStringValue() ?? "new"; },
        "total": n => { order.total = n.getNumberValue(); },
    }
}

Kiota is the better fit when the client must fill in defaults the server left out. Swagger Codegen is the better fit when the type must say which fields a payload has to carry. The OpenAPI document has no way to say that order.customer points at a customer, so the substitute’s SQL has no foreign key, which the test pins.

Check it worked

The suite builds the model, round trips both models, refuses a column type and a hostile name, and compares the OpenAPI route’s columns with the model’s. One test reads a raw row without the decoder, and it must fail on paid. Another sends a number for vip and must be refused with the field’s name.

test('a row round trips: null, both defaults and the bool come back as the types say', async () => {
  const results = await roundTrip(join(here, 'generated'), inserts)
  assert.deepEqual(results.map((r) => r.problems), [[], []])
  const order = results.find((r) => r.entity === 'order').value
  assert.deepEqual(order, { id: 1, customer: 1, note: null, paid: false, status: 'new', total: 1250 })
})
node --test one-model.test.mjs
1..23
# tests 23
# suites 0
# pass 23
# fail 0
# cancelled 0
# skipped 0
# todo 0
# duration_ms 5961.608781

When it goes wrong

An insert throws ERR_INVALID_ARG_TYPE, and the message names a parameter number, not a field. A boolean reached node:sqlite without the generated encoder. Bind every insert through encodeOrderInput, which the same action writes beside the decoder.

The round trip reports paid: declared boolean, read number. The decoder was skipped, or the action that writes it does not know the kind. Read rows through decodeOrder, never with a cast.

A dry run writes files. --dryrun redirects only writes through build.fs, and a test in the suite shows an action that calls node:fs writing types.ts to disk under it. Write through build.fs in every action.

A check that reads the build log passes on your machine and fails in CI. voxgig-model colors its log when CI is set beside a provider’s variable such as GITHUB_ACTIONS, even into a pipe. The rule is line 43 of node_modules/colorette/index.cjs, the color library under its logger. An action’s name then arrives with an escape sequence after it. Run the command with NO_COLOR=1, as build.mjs does, and a test in the suite sets both variables to hold it.

The build fails with Cannot unify values at path $.main.ent.order.field.paid.kind. Someone wrote a column type in the model. Put the column type in the action’s table, keyed by kind.

When not to do this

Do not hand-write a model and two actions if Prisma is already your database layer. Its schema writes the migration and the client types, and keeps defaults and nulls in both. You give up types that code outside Prisma’s client can use as they stand.

Do not reach past the OpenAPI document if your SDK generator is Kiota or Swagger Codegen and the database belongs to another team. Generate the client models from the document, and leave the tables to the people who migrate them.

Skip @voxgig/model when the schema has one consumer. Two actions, a shared table and a round-trip check are more code than a CREATE TABLE and an interface that one person keeps in step.

Last verified

Verified 2026-11-22 against Node 22.23.5, @voxgig/model 12.0.2 (aontu 0.76.3, pino 10.4.0) and TypeScript 5.9.3. Swagger Codegen 3.0.85, Kiota 1.39.0 and Prisma 7.10.1 ran once, in generate.sh, and the page reads the excerpts it committed. TypeSpec was not run: its row comes from its emitter documentation.

Footnotes

  1. SQLite’s keyword page lists four ways to quote a keyword. Double quotes are standard SQL. Square brackets come from MS Access and SQL Server, and grave accents from MySQL, both included for compatibility. Single quotes make a string literal, and the page adds that SQLite will sometimes bend these rules for historical statements. ↩︎ Back to text

  2. SQLite does not have a separate Boolean storage class. It has recognized the keywords TRUE and FALSE since version 3.23.0, in April 2018, and the same page calls them alternative spellings for the integer literals 1 and 0. ↩︎ Back to text

Read this page as markdown · All how-to guides

Generate the client instead of writing it#

Retries, timeouts, pagination and auth are the same problems in every client. Voxgig generates them from your OpenAPI description, in 23 languages, from one model.

Listen to Fireside, the Voxgig podcast

Since 2018, Richard Rodger has talked with developer advocates, event organizers, and the people who build developer-facing companies about the specifics of the work.