Schema DeclarationSupported

A table is a TypeScript type. You declare it once, as an interface, and everything else — the row type, the create and update DTOs, the DDL, the validator, the JSON Schema document — is derived from that one declaration.

import type { HasDefault, Length, PrimaryKey, Serial, Sql, Table, Unique } from '@zmdb/core/schema';

export interface User extends Table<'users'> {
  id: number & Sql<'integer'> & Serial & PrimaryKey;
  email: string & Sql<'varchar'> & Length<255> & Unique;
  name: (string & Sql<'text'>) | null;
  role: ('admin' | 'user') & HasDefault;
  createdAt: Date & Sql<'timestamp'> & HasDefault;
}

That is the whole column declaration. There is no second property map to keep in sync with it. Unique is carried into the IR, but the root dialects still need an explicit standalone unique index in a schema-object migration; see Indexes & Constraints.

How to read a column#

Each property is its app type intersected with tags. The app type is what your handler code sees; the tags say the things TypeScript has no syntax for.

id: number & Sql<'integer'> & Serial & PrimaryKey;
// ^^          ^^^^^^^^^^^^^^^^ the SQL column type
// the type your code sees      ^^^^^^^^^^^^^^^^^^ facts about the column

A tag is a phantom unique symbol property. It exists only in the type system: it erases completely, so a tagged type is the same value at runtime as the untagged one, and number & Sql<'integer'> is assignable to number in both directions. You can pass a row's id to anything that wants a number.

Two facts have no tag, on purpose:

The tags you will use most#

The full list is the tag reference. These five cover most tables:

Sql<…> is optional when the app type only maps one way — Date is a timestamp, boolean is a boolean. Write it when you mean something specific: string could be text or varchar, and number could be integer or numeric.

What you get from it#

import { defineRepository, schemaOf } from '@zmdb/core';
import type { CreateDTO, Entity, UpdateDTO } from '@zmdb/core';

type Row = Entity<User>;
// { id: number; email: string; name: string | null; role: 'admin' | 'user'; createdAt: Date }

type NewUser = CreateDTO<User>;
// { email: string; name?: string | null; role?: 'admin' | 'user'; createdAt?: Date }
// no `id`: it is Serial, so the database makes it

type Patch = UpdateDTO<User>;
// every field optional

const users = defineRepository(schemaOf<User>(), driver);
await users.create({ email: 'a@b.com' }); // validated before any SQL is sent

CreateDTO drops Serial columns entirely rather than making them optional, because there is no value you could usefully pass. Columns with HasDefault and nullable columns become optional — omitting a nullable column inserts NULL, which is what passing null does.

See Type Derivation for the full family, including the read and query DTOs.

Managed soft-delete timestamp#

Soft delete is an entity-level declaration because it changes repository behavior for the whole table:

import type { PrimaryKey, Serial, SoftDelete, Sql, Table } from '@zmdb/core/schema';

export interface User extends Table<'users'>, SoftDelete<'deletedAt'> {
  id: number & Sql<'integer'> & Serial & PrimaryKey;
  email: string & Sql<'text'>;
  deletedAt: (Date & Sql<'timestamp'>) | null;
}

The named column must exist, be nullable, and use Sql<'timestamp'>. It remains part of the entity returned from reads, with an ISO date-time wire form, but is absent from CreateDTO<User> and UpdateDTO<User> because delete, restore, and hardDelete own the state transition. See Entity Filters for read/write filtering, hooks, and unique/upsert behavior.

schemaOf<T>() needs a build step#

schemaOf<User>() is a compile-time call. It has no runtime implementation and cannot have one: the answer is a function of a type argument, and type arguments do not exist at runtime. The zmdb transform replaces the call with a frozen object literal.

// what you write
const users = defineRepository(schemaOf<User>(), driver);

// what runs
const users = defineRepository(
  Object.freeze({ table: 'users', columns: { … }, primaryKey: ['id'], ir: { … } }),
  driver,
);

If the transform did not run, the call throws a message saying exactly that. It does not return an empty schema and let you find out in production. Set it up with the build plugin or the codegen CLI, which commits the generated files so a fresh clone needs no tool at all.

Foreign keys#

import type { PrimaryKey, References, Serial, Sql, Table } from '@zmdb/core/schema';

export interface Post extends Table<'posts'> {
  id: number & Sql<'integer'> & Serial & PrimaryKey;
  title: string & Sql<'text'>;
  authorId: number & Sql<'integer'> & References<'users.id'>;
}

References<'users.id'> is table.column, checked as a string literal. It reaches the DDL as a real FOREIGN KEY constraint, and Relations is how you traverse it in queries.

JSON columns keep their shape#

interface Preferences {
  theme: 'light' | 'dark';
  digest: boolean;
}

export interface Account extends Table<'accounts'> {
  id: number & Sql<'integer'> & Serial & PrimaryKey;
  prefs: Preferences & Sql<'json'>;
}

type Row = Entity<Account>;
// Row['prefs']['theme'] is 'light' | 'dark'

The payload's shape survives every derivation, and the emitted validator checks it on the way in. This is the clearest thing the old builder DSL could not do: json<Preferences>() erased its type parameter at runtime, so the shape reached nothing downstream.

Using the schema value directly#

schemaOf<T>() returns a plain frozen object when you need one:

import { createQueryCompiler } from '@zmdb/core/sql';
import { postgres } from '@zmdb/core/postgres';

const schema = schemaOf<User>();
const compiler = createQueryCompiler(postgres);
const query = compiler.selectFrom(schema).select(['id', 'email']).where('role', '=', 'admin').compile();

SELECT "id", "email" FROM "users" WHERE "role" = $1
-- parameters: ['admin']

The value carries the full IR on schema.ir, which is what every back-end reads — the DDL emitter, the validator, the JSON Schema generator and the seeder all work from the same bytes, so they cannot disagree about a column.

schema.table, schema.columns and schema.primaryKey are SQL-facing physical identifiers. The direct compiler is also a SQL-level API, so with non-identity naming pass physical column keys (for example from schema.columns). Repository DTOs continue to use declared property names and perform that translation for you.

Related#

• Tag reference — every tag, what it means, what it emits
• Column Types — the SQL type set and why it is small
• Type Derivation — Entity, the DTOs, and the read models
• Relations — declaring and traversing relationships
• Repository — using a schema for CRUD

Continue with the repository to persist this schema, then validate requests in the blog API tutorial.