Column TypesSupported

Sql<T> names the SQL column type. It drives the DDL emitted by migrations, and it is the half of a column declaration that TypeScript cannot infer — integer, bigint and numeric are all number in TS, and text and varchar are both string.

Type mapping#

Each dialect renders the type it owns. The declaration stays abstract — it says timestamp, never TIMESTAMPTZ — and the DDL emitter is where that becomes a real type, because the four root grammars do not agree and a schema should not have to pick.

serial is the tenth, and it is the one you spell as a tag rather than an Sql<…> argument — Sql<'serial'> does not typecheck, because Serial already means it. It emits SERIAL / INT AUTO_INCREMENT / INTEGER / INT IDENTITY(1,1), is number in TS, and is omitted from CreateDTO entirely.

Cockroach inherits the Postgres column map except that integer is INT4 and Serial is INT8 DEFAULT unique_rowid(). SingleStore inherits MySQL except that Serial is BIGINT AUTO_INCREMENT; its table-level shard, sort and rowstore declarations are documented on the SingleStore page.

interface Event extends Table<'events'> {
  id: number & Sql<'integer'> & Serial & PrimaryKey;
  kind: 'created' | 'updated' | 'deleted'; // → jsonEnum
  sequence: bigint & Sql<'bigint'>;
  amount: number & Sql<'numeric'> & Numeric<12, 2>;
  label: string & Sql<'varchar'> & Length<80>;
  body: string & Sql<'text'>;
  payload: { source: string; retries: number } & Sql<'json'>;
  live: boolean;
  at: Date & Sql<'timestamp'>;
}

Note what is _not_ written there. live: boolean needs no Sql<'boolean'> and at: Date needs no Sql<'timestamp'> — the mapping is forced, so stating it twice would only create something to disagree about. And there is no jsonEnum tag at all: 'created' | 'updated' | 'deleted' is a literal union, which is how TypeScript says that, and the reflection reads the members off the type.

Four rows are worth a sentence:

• timestamp is TIMESTAMPTZ in Postgres, not TIMESTAMP. TIMESTAMP there means _without_ time zone: it keeps the wall clock and discards the offset, so a Date written from one zone reads

back as a different instant in another. MySQL has no zone-aware type with a usable range — TIMESTAMP converts to the session zone and stops in 2038 — so DATETIME(3) holds UTC with the milliseconds a Date has. SQL Server uses DATETIMEOFFSET(3) for the same instant-preserving contract.

• varchar needs its length, as Length<N>. Length<255> becomes VARCHAR(255) or SQL Server's NVARCHAR(255) where the dialect has a bounded varchar. A varchar with no Length degrades to

the dialect's widest text spelling rather than emitting invalid or one-character DDL; SQL Server uses NVARCHAR(MAX). Length<N> also emits maxLength: N into the JSON Schema, which is one fact serving two outputs rather than two facts to keep aligned.

• bigint is bigint, not number. A BIGINT past 2^53 is not representable as a double, so the app type is the one that can hold it. See bigint keys for what that costs

at the boundary.

• SQLite has affinities, not types. INTEGER PRIMARY KEY _is_ the rowid, which is what makes Serial auto-increment without an AUTOINCREMENT keyword.

That is the whole set#

Ten abstract types, closed. A type supplied by a database extension uses Ext<Extension, Name, Args> instead, including vector, geometry, and citext. Other storage types such as uuid, date, time, interval, inet, cidr, and arrays still need a custom type or a json column.

The union is small on purpose. Every back-end has to answer for every member: the DDL emitter needs a spelling in six dialects, the validator needs a check, the JSON Schema generator needs a keyword, and the seeder needs a generator. Ten members mean ninety answers, all of them written down and tested. A SqlType with forty members would mean most of those answers were guesses in whichever back-end nobody exercised.

Constraining a column#

The value's shape is the SQL type; everything else is a tag on the same property.

interface User extends Table<'users'> {
  id: number & Sql<'integer'> & Serial & PrimaryKey;
  email: string & Sql<'varchar'> & Length<320> & Unique & Pattern<'^[^@]+@[^@]+\\.[^@]+$'>;
  role: ('admin' | 'user') & HasDefault;
  bio: (string & Sql<'text'>) | null;
  authorId: number & Sql<'integer'> & References<'users.id'>;
  createdAt: Date & Sql<'timestamp'> & HasDefault;
}

References<'users.id'> is a string literal read as table.column, so there is no wrapping function and no schema value to import — the whole reason the old references(integer().notNull(), UserSchema, 'id') had to be a function was that it needed the target's value at hand. The tag reference has the rest.

How columns become DDL#

A schema diffs into CREATE TABLE DDL through migrations:

-- postgres
CREATE TABLE "users" ("createdAt" TIMESTAMPTZ NOT NULL, "email" TEXT NOT NULL, "id" SERIAL PRIMARY KEY, "role" TEXT NOT NULL)
-- mysql
CREATE TABLE `users` (`createdAt` DATETIME(3) NOT NULL, `email` TEXT NOT NULL, `id` INT AUTO_INCREMENT PRIMARY KEY, `role` TEXT NOT NULL)
-- mssql
CREATE TABLE [users] ([createdAt] DATETIMEOFFSET(3) NOT NULL, [email] NVARCHAR(MAX) NOT NULL, [id] INT IDENTITY(1,1) PRIMARY KEY, [role] NVARCHAR(MAX) NOT NULL)

Columns come out sorted by name, because a snapshot has to be byte-stable to be diffable.

The snapshot still cannot carry DEFAULT values or general CHECK constraints. It now carries the Unique flag so SingleStore can validate and emit a unique declaration against its shard key, but the ordinary generated migration path still does not create standalone unique constraints for the other dialects. For defaults this is not only a snapshot gap — HasDefault says a column _has_ a default, not _which one_, because a default is a runtime value and no type holds one.

Write the value in the migration, where the DDL is written anyway. Validation tags feed the JSON Schema, the OpenAPI document and the seed generator; enforce them at the HTTP boundary with assert, where a failure becomes a 400 rather than a partially-applied write.

For richer schema objects (indexes, generated columns, sequences), see Indexes & constraints and Generated columns.