# 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.

Source: https://voxgig.com/howto/generate-typescript-types-and-sql-from-one-model

- Audience: api-producer
- Level: intermediate
- Languages: typescript, sql
- Verified: 2026-11-22
- Published: 2026-11-22

## 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`](https://nodejs.org/docs/latest-v22.x/api/sqlite.html), which prints an
experimental warning on stderr, and imports `types.ts` through Node's
[type stripping](https://nodejs.org/docs/latest-v22.x/api/typescript.html), on by default from
22.18.

## Approaches compared

| Approach | When it fits | What it costs you | When to pick something else |
| --- | --- | --- | --- |
| [@voxgig/model actions](https://github.com/voxgig/model/blob/main/docs/reference.md) | You want the mapping in your own code, and fields that carry whatever keys a target needs | You write every mapping, every decoder and every check yourself | Your team wants a database toolkit with migrations included |
| [Kiota models](https://learn.microsoft.com/en-us/openapi/kiota/models) | Your model is an OpenAPI document and you want typed client models that keep defaults | Every property comes out optional and nullable, and the SQL is yours to write | The types must say which fields are required |
| [Prisma schema](https://www.prisma.io/docs/cli/v7/migrate/diff) | The database is the center, and you want migrations and a typed client from one file | A schema language, a CLI with telemetry on by default, and types shaped by its client | The types must also serve code that never touches the database |
| [Swagger Codegen models](https://swagger.io/docs/open-source-tools/swagger-codegen/codegen-v3/about/) | Your model is an OpenAPI document and a client SDK needs its types | Nullable fields lose their null, defaults vanish, and no target writes SQL | Nulls and defaults have to survive into the types |
| [TypeSpec emitters](https://typespec.io/docs/extending-typespec/emitters-basics/) | You describe APIs in TypeSpec and want several outputs from one definition | No SQL emitter is listed, so you write one against the compiler API | You 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.

```aontu title="model/field.aontu"
# 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
}
```

```aontu title="model/entity/order.aontu"
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.

```js title="build/fields.cjs"
// 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.

```aontu title="model/.model-config/model-config.aontu"
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](https://github.com/voxgig/model/blob/main/docs/reference.md) 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.

```bash
node build.mjs
```

```text output
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](https://www.sqlite.org/lang_keywords.html).[^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.

```ts title="generated/types.ts"
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.

```bash
node roundtrip.mjs
```

```text output
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.

```bash
node pitfall.mjs
```

```text output
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](https://www.sqlite.org/datatype3.html). 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](https://www.prisma.io/docs/cli/telemetry), 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.

```bash
node compare.mjs
```

```text output
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:

```ts title="generated/kiota-order.ts"
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.

```ts title="one-model.test.mjs"
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 })
})
```

```bash
node --test one-model.test.mjs
```

```text output
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.

## Related how-tos

- [Rebuild artifacts on model change with a watch loop](/howto/rebuild-artifacts-on-model-change)

- [Vet an aontu model for contradictions before you generate](/howto/vet-a-model-file-before-you-generate)

- [Format generated code as the last step of generation](/howto/format-generated-code-as-the-last-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.

[^1]: SQLite's [keyword page](https://www.sqlite.org/lang_keywords.html) 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.

[^2]: SQLite [does not have a separate Boolean storage class](https://www.sqlite.org/datatype3.html).
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.