Schema & Migrations

Turbine supports two schema workflows:

• Code-first, declare tables with defineSchema(...) in TypeScript, then push or auto-diff migrations.
• Introspection, point Turbine at an existing database and generate a typed client from information_schema.

Both workflows emit the same generated types (types.ts, metadata.ts, index.ts) so you can mix them freely.

Code-first with defineSchema#

Declare your tables in a TypeScript file. Turbine uses these definitions to generate DDL, run migrations, and emit the typed client.

// turbine/schema.ts
import { defineSchema } from 'turbine-orm';
 
export default defineSchema({
  organizations: {
    id: { type: 'serial', primaryKey: true },
    name: { type: 'text', notNull: true },
    createdAt: { type: 'timestamp', default: 'now()' },
  },
  users: {
    id: { type: 'serial', primaryKey: true },
    email: { type: 'text', unique: true, notNull: true },
    name: { type: 'text', notNull: true },
    orgId: { type: 'bigint', notNull: true, references: 'organizations.id' },
    role: { type: 'text', notNull: true, default: "'member'" },
    createdAt: { type: 'timestamp', default: 'now()' },
  },
  posts: {
    id: { type: 'serial', primaryKey: true },
    userId: { type: 'bigint', notNull: true, references: 'users.id' },
    title: { type: 'text', notNull: true },
    content: { type: 'text' },
    published: { type: 'boolean', notNull: true, default: 'false' },
    viewCount: { type: 'integer', notNull: true, default: '0' },
    createdAt: { type: 'timestamp', default: 'now()' },
  },
});

Two things the example does not show. type: 'timestamp' emits TIMESTAMPTZ: the alias is kept for back-compat and means the same as 'timestamptz', so there is no code-first spelling for a zone-less TIMESTAMP column (declare one in a SQL migration and generate picks it up; the type mapping below describes how such a column reads). And varchar takes its bound as maxLength ({ type: 'varchar', maxLength: 200 }); defineSchema does not validate column option names, so a misspelt option such as length is ignored and an unbounded VARCHAR is emitted.

Composite primary keys#

Pass a table-level primaryKey array:

memberships: {
  userId: { type: 'bigint', notNull: true, references: 'users.id' },
  orgId: { type: 'bigint', notNull: true, references: 'organizations.id' },
  role: { type: 'text', notNull: true },
  primaryKey: ['userId', 'orgId'],
}

findUnique accepts the composite key as an object: where: { userId: 1, orgId: 2 }.

Foreign keys and referential actions#

The short references: 'table.column' form emits a plain foreign key. To attach ON DELETE / ON UPDATE actions, pass an object:

posts: {
  id: { type: 'serial', primaryKey: true },
  userId: {
    type: 'bigint',
    notNull: true,
    references: { target: 'users.id', onDelete: 'cascade', onUpdate: 'restrict' },
  },
}
// REFERENCES "users"("id") ON DELETE CASCADE ON UPDATE RESTRICT

Supported actions: 'cascade', 'restrict', 'set null', 'set default', 'no action'. Omit a clause to leave it at the SQL default (NO ACTION). The plain string form emits no action clauses. Introspection reads existing actions back from pg_constraint, so a pull round-trips them and migrate create --auto detects action changes.

Enums#

Declare enum types once in the schema options, then reference them from columns by name:

export default defineSchema(
  {
    posts: {
      id: { type: 'serial', primaryKey: true },
      status: { type: 'enum', enumName: 'post_status', notNull: true, default: "'draft'" },
    },
  },
  { enums: { post_status: ['draft', 'published', 'archived'] } },
);

DDL emits CREATE TYPE "post_status" AS ENUM ('draft', 'published', 'archived') before the tables that use it, and the column is typed "post_status". Enum columns generate a string-literal union in types.ts ('draft' | 'published' | 'archived').

Writes to an enum column get an explicit ::"post_status" cast on every write path (create, createMany, update, upsert), schema-qualified so it is safe across schemas. This avoids the column is of type post_status but expression is of type text error createMany can otherwise hit.

Array columns#

Add array: true to any scalar column:

posts: {
  id: { type: 'serial', primaryKey: true },
  tags: { type: 'text', array: true, notNull: true },  // TEXT[]
}

Array columns map to T[] in generated types and support the array WHERE operators, has, hasEvery, hasSome.

Vector columns#

For pgvector embeddings, use { type: 'vector', dimensions: n }:

documents: {
  id: { type: 'serial', primaryKey: true },
  embedding: { type: 'vector', dimensions: 1536, notNull: true },  // vector(1536)
}

A schema containing a vector column prepends CREATE EXTENSION IF NOT EXISTS vector; by default. Pass extensions: 'manual' to schemaToSQL to emit a comment instead and manage the extension yourself. Vector columns map to number[]. See Vector Search for querying them.

Check constraints#

Add a column-level check for an inline constraint, or table-level checks for named ones:

products: {
  id: { type: 'serial', primaryKey: true },
  price: { type: 'integer', notNull: true, check: 'price >= 0' },
  cost: { type: 'integer', notNull: true },
  checks: [{ name: 'price_gt_cost', expression: 'price > cost' }],
}
// "price" INTEGER NOT NULL CHECK (price >= 0)
// CONSTRAINT "price_gt_cost" CHECK (price > cost)

A table-level check without a name emits a bare CHECK (expr). Introspection reads check constraints back, so they survive a pull. A violated check throws CheckConstraintError (TURBINE_E011) at write time.

Declared indexes#

Declare plain column-list indexes at the table level with indexes. Each entry names its camelCase columns, optionally unique: true, and optionally an explicit name (auto-derived as idx_<table>_<col1>_<col2> when omitted):

users: {
  id: { type: 'serial', primaryKey: true },
  email: { type: 'text', notNull: true },
  orgId: { type: 'integer', references: 'orgs.id' },
  indexes: [
    { columns: ['email'], unique: true },          // CREATE UNIQUE INDEX "idx_users_email"
    { columns: ['orgId', 'email'] },               // CREATE INDEX "idx_users_org_id_email"
    { columns: ['email'], name: 'users_email_ci' } // explicit name
  ],
}

schemaToSQL (and therefore push) emits these as CREATE [UNIQUE] INDEX after the table DDL. Foreign-key columns still get an automatic index; a declared index that resolves to the same name takes precedence, so declaring { columns: ['orgId'], unique: true } replaces the plain auto FK index rather than colliding with it. schemaDiff adds declared indexes missing from the live database, warns when a name matches an existing index whose definition differs (uniqueness, column list, or a partial-index WHERE), and never drops an index automatically. PowDB doc-field expression indexes ({ docField, path }) are documented on the engines page and have no SQL emission.

PII fields#

Tag a column with pii: true to mark it as personally identifiable information:

users: {
  id: { type: 'serial', primaryKey: true },
  name: { type: 'text', notNull: true },
  email: { type: 'text', notNull: true, pii: true },
  ssn: { type: 'text', pii: true },
}

A PII column is excluded from every default projection. It comes back in two ways: name it explicitly in select, or pass includePii: UNSAFE, a privilege option unlocked only by the imported symbol (a literal true throws TURBINE_E003). See includePii on reads.

The rest of the policy:

• Writes are unaffected. You can write PII fields freely. The returned row applies the same read policy: the value is persisted, just absent from the returned object unless you re-read with an opt-in.
• where, orderBy, and having are always allowed. They narrow, sort, or filter rows and return no PII value.
• Two aggregate shapes are gated behind the same includePii: UNSAFE, because they return stored values: a PII column as a groupBy by key (including a JSON-path key), and _min / _max over a PII column in groupBy or aggregate (including JSON-path targets). Without the opt-in they throw ValidationError (TURBINE_E003).
• _count, _sum, and _avg stay allowed. None of them hands back a stored cell.

Tag sensitive data, not keys. A PII-tagged column that is part of the primary key is returned anyway, on every read and write path, because a row that comes back without part of its own key is unaddressable: feeding it into an update builds a partial predicate that matches more rows than intended. If a key column is genuinely sensitive, use a surrogate key, not a tag.

The tag flows through the stack: turbine studio redacts PII cells by default (reveal with --show-pii), and turbine generate marks each PII field optional in the emitted entity type, since it is absent by default. Studio and the MCP server introspect the live database, which carries no tags, so they read them from the generated metadata in your out directory: run turbine generate after tagging, or they have nothing to redact. Introspection never auto-tags a column; pii is a code-first declaration you make in defineSchema.

Auto-updated timestamps, updatedAt#

Tag a column with updatedAt: true (or .updatedAt() on the fluent builder) and every update that does not name it explicitly sets it to the current time. This is the equivalent of Prisma's @updatedAt.

const posts = defineSchema({
  posts: {
    id: { type: 'serial', primaryKey: true },
    title: { type: 'text', notNull: true },
    updatedAt: { type: 'timestamptz', notNull: true, default: 'now()', updatedAt: true },
  },
});
 
// No `updatedAt` in the payload: it is filled in for you.
await db.posts.update({ where: { id: 1 }, data: { title: 'new title' } });
 
// An explicit value always wins, including an explicit null.
await db.posts.update({ where: { id: 1 }, data: { title: 'x', updatedAt: pinned } });

The timestamp is generated client-side (as Prisma does), so it flows through the same UTC coercion as any other bound Date on every engine. It applies to update and updateMany, never to create, where a column default is the right tool.

Like pii, this is a code-first declaration and never inferred from a column's name, so an application that already manages its own updated_at is not silently changed by an upgrade. A schema with no tagged column emits byte-identical SQL.

Runtime metadata without a database, schemaDefToMetadata#

The runtime SchemaMetadata a client needs normally comes from turbine generate (which reads a live database). schemaDefToMetadata(def) derives the same object directly from a defineSchema result, no connection, no codegen step:

import { defineSchema, schemaDefToMetadata } from 'turbine-orm';
 
const schema = defineSchema({ /* ... */ });
const metadata = schemaDefToMetadata(schema);

This is the code-first path for engines with no wire introspection, most notably PowDB, where you can hand a defineSchema result straight to the factory:

import { turbinePowDB } from 'turbine-orm/powdb';
 
const db = await turbinePowDB({ embedded: './data' }, schemaDefToMetadata(schema));

DDL generation#

Turbine generates quoted, deterministic DDL from any SchemaDef. Every identifier is quoted via quoteIdent() so reserved words and mixed case are safe.

# Preview the SQL without running it
npx turbine push --dry-run
 
# Apply schema changes to the database
npx turbine push

push is the fast path for development: it diffs your defineSchema output against the live database and applies the difference directly. For production, use migrations.

Programmatic: schemaToSQL and schemaPush#

Both are exported from the package root, for test harnesses, custom bootstrap scripts, and setups where the CLI is not the right entry point.

import { schemaToSQL, schemaToSQLString, schemaPush } from 'turbine-orm';
import schema from './turbine/schema.js';
 
// Build the DDL. No connection involved, so this works for any engine
// whose driver can execute the statements.
const statements = schemaToSQL(schema);          // string[]
const oneScript = schemaToSQLString(schema);     // the same, joined
 
// Diff against a live Postgres database and apply the difference in ONE
// transaction. Data-destroying statements (drops, lossy type changes) are
// refused unless you pass allowDestructive: true.
const result = await schemaPush(schema, process.env.DATABASE_URL!);
console.log(result.statementsExecuted, result.tablesCreated, result.tablesAltered);
 
// Preview instead of applying
await schemaPush(schema, url, { dryRun: true });

schemaPush(schema, connectionString, options?) accepts { dryRun, allowDestructive, precomputedDiff }. If the computed diff contains a data-destroying statement (a lossy ALTER COLUMN ... TYPE and similar), it throws a DestructivePushRefusal (a ValidationError subclass carrying the offending statements on .destructive) and applies nothing, unless you pass allowDestructive: true. This is the same gate turbine push puts behind an interactive confirmation. precomputedDiff lets a caller diff once, show the plan, confirm, and then apply exactly the statements it displayed, rather than re-diffing.

schemaPush and schemaDiff connect through pg and are Postgres-only. schemaToSQL has no connection and is not.

SQL-first migrations#

Turbine migrations are plain .sql files with -- UP and -- DOWN sections. The runner tracks them in a _turbine_migrations table keyed on timestamp + SHA-256 checksum. For the full production workflow (deploy semantics, the destructive-op gate, what --auto can and cannot do, and the two-phase recipe for changing a populated column's type), see Migrations in Practice.

Create a migration#

# Blank migration, write SQL manually
npx turbine migrate create add_users_table
 
# Auto-generate from the diff between defineSchema() and the live database
npx turbine migrate create add_email_index --auto

The resulting file looks like this:

-- 20260409143022_add_users_table.sql
-- UP
CREATE TABLE "users" (
  "id" SERIAL PRIMARY KEY,
  "email" TEXT UNIQUE NOT NULL,
  "name" TEXT NOT NULL,
  "created_at" TIMESTAMPTZ DEFAULT now()
);
 
-- DOWN
DROP TABLE "users";

Apply, rollback, inspect#

npx turbine migrate up       # Apply all pending migrations
npx turbine migrate down     # Roll back the last applied migration
npx turbine migrate status   # Show applied vs pending

Each migration runs in its own transaction. If a migration fails halfway, the transaction rolls back and _turbine_migrations stays clean.

Concurrency safety#

Turbine takes pg_try_advisory_lock() before running any migration. If a second process runs migrate up simultaneously, it exits cleanly instead of racing. Safe to run from CI/CD pipelines and deployment hooks.

Checksums#

Every migration file is SHA-256 hashed and stored alongside the timestamp. If you edit a migration that has already been applied, migrate status flags a checksum mismatch and refuses to proceed until you reconcile.

Schema diffing#

schemaDiff() connects to a live Postgres database, compares it against a SchemaDef, and returns the DDL needed to close the gap. This powers migrate create --auto. Its signature is schemaDiff(schema, connectionString) and it returns a Promise<DiffResult>. Both schemaDiff and introspect are exported from the package root; there is no turbine-orm/introspect subpath:

import { schemaDiff } from 'turbine-orm';
import schema from './turbine/schema.js';
 
const diff = await schemaDiff(schema, process.env.DATABASE_URL!);
 
// diff.statements: SQL to apply (UP direction), ready to run in order
for (const sql of diff.statements) {
  console.log(sql);
}
 
// diff.warnings: changes the diff detected but refuses to apply automatically
for (const warning of diff.warnings ?? []) {
  console.warn(warning);
}

DiffResult has the shape { create, alter, drop, statements, reverseStatements, warnings }. create / alter / drop are the structured plan (tables to create, tables to alter, table names present in the DB but absent from the schema), statements is the flat SQL to apply and reverseStatements the DOWN direction. Anything destructive is deliberately left out of statements: table and column drops are reported (in drop and in the alter plan) but never auto-emitted into the executable SQL, so you apply those by hand. Enum value removals and reorders surface in warnings the same way.

Auto-generated migrations are a starting point: review them before committing. For the limits of --auto (blind type casts, undetected renames, SET NOT NULL without backfill) and the sanctioned recipe for changing a populated column's type, see Migrations in Practice.

Introspection#

If you already have a database, point Turbine at it:

npx turbine pull
# or: npx turbine generate

Turbine reads information_schema and pg_catalog to discover:

• Tables and columns (with types, nullability, defaults)
• Primary keys, unique constraints, foreign keys
• Indexes (including composite and partial)
• Enum types
• Inferred relations (hasMany / belongsTo / hasOne) from foreign keys
• STORED generated columns (read-only, omitted from write inputs)

Pass --include-views to also introspect views and materialized views as read-only entities. See Views & Generated Columns.

Note: When a derived relation name would collide with a scalar column, e.g. a currentVersionId foreign key producing a relation named currentVersion, the relation name is disambiguated instead of overwriting the column. Collision-free names are preserved, so regenerating an existing schema does not rename its relations. The turbine mcp server and the SQLite / MySQL / SQL Server introspectors share the same naming logic.

Three files land in ./generated/turbine/:

• types.ts, entity interfaces (singularized PascalCase), *Create types, *Update types, and relation-included *With* types.
• metadata.ts, runtime SchemaMetadata with column maps, relations, and indexes. Needed for turbineHttp() in edge runtimes.
• index.ts, a TurbineClient subclass with typed declare readonly table accessors, plus a turbine() factory function.

Type mapping#

Turbine maps Postgres types to TypeScript:

Zone-less columns: timestamp and date read as UTC#

A timestamptz carries its offset on the wire. A timestamp (without time zone) and a date do not: the database hands over 2026-07-21 09:30:00 or 2026-07-21, and something has to choose which instant that is. The pg driver's default is the process's local zone, which makes the same stored row a different Date in every deployment region. Turbine reads UTC instead, the same convention Prisma, Rails and Django use.

The read half is a set of pg type parsers on OIDs 1114 (timestamp), 1082 (date) and their array forms 1115 / 1182, also applied to the dates coerced out of nested-relation JSON, so every read path agrees, and scalar and array columns cannot settle differently. The write half matches: binding a Date to a zone-less column renders its UTC components. Reading in the local zone against a UTC write is what made a read-modify-write cycle on a date walk the stored day backward east of UTC, and let where: { runOn: row.runOn } match nothing.

// Stored: date '2026-07-21'. Process running in Europe/Berlin (UTC+2).
const job = await db.jobs.findUniqueOrThrow({ where: { id: 1 } });
 
job.runOn.toISOString();          // '2026-07-21T00:00:00.000Z'
job.runOn.toISOString().slice(0, 10); // '2026-07-21'  <- format a date like this

infinity and -infinity#

Postgres accepts infinity and -infinity in date, timestamp and timestamptz columns, usually to model "no end date" without a nullable column. Turbine reads both as the JS numbers Infinity / -Infinity by default. No JavaScript Date means either value, so every candidate reading gives something up: an Invalid Date is silent and NaN everywhere, null is indistinguishable from a stored NULL, and the number breaks the field's declared Date type at runtime. The number is the only one of the three that cannot lose the value.

The reading is applied in one place, the ORM row parser, so every read path agrees: top-level reads, findUnique / findFirst, streaming, the join / batched / flatten strategies, the positional wire encoding, write RETURNING / reselect / OUTPUT projections, groupBy keys and _min / _max.

What the default costs, on exactly the rows that hold an infinity:

• Date methods throw. The generated type says Date and the value is a number, so row.validUntil.toISOString() and .getTime() raise a TypeError. Guard with typeof row.validUntil === 'number' (or Number.isFinite) before calling a Date method on a column that can hold one.
• JSON.stringify renders null, because JSON has no infinity literal.
• where: { validUntil: null } still compiles to IS NULL and does not match these rows. Filter them with where: { validUntil: 'infinity' }. Making null match both would silently change every null predicate on every temporal column.

temporalInfinity: 'null'

const db = turbine({ connectionString: process.env.DATABASE_URL, temporalInfinity: 'null' });

Reads a stored infinity as null instead. JSON.stringify then matches what the value became, the declared Date | null type of a nullable column holds so no method call throws, and every read strategy agrees just as it does under the default. The cost is the data loss above, plus three consequences:

• groupBy keys stop being unique. GROUP BY returns one row per distinct stored value, and the ORM then labels infinity, -infinity and SQL NULL all as null. Three rows holding those three values come back as three groups keyed null, so a Map or Object.fromEntries built off the group value keeps one of the three counts. distinct: ['col'] has the same shape.
• +infinity and -infinity collapse into each other, not only into NULL. On a validUntil column that puts "never expires" and "expired forever" in the same bucket.
• _max can be null on a table that plainly has rows. With 2026-01-01, 2026-06-01 and infinity stored, aggregate reports _min: 2026-01-01, _max: null, _count: 3, and _max: null is the same value an empty table returns.

Pick 'null' when the rows are read-only in that code path and the declared type contract matters more than the stored value; rows read under it must not be written back. Pick the default when anything reads a row and writes it again, which is most code.

The write path is untouched by either reading: 'infinity' / '-infinity' / Infinity / -Infinity all remain bindable, and a value read as null is still recoverable if you know what it held.

Time-of-day columns: time and timetz#

A time / timetz column has no date part, so Turbine never coerces it to a JS Date on read: it comes back as the driver's string, '09:00:00'. The generated row type is string.

On write the column also accepts a Date. Turbine narrows it to a time-of-day literal built from the Date's UTC components, so the generated *Create and *Update input types are widened to string | Date:

// Both write 09:00:00
await db.shifts.create({ data: { startsAt: '09:00:00' } });
await db.shifts.create({ data: { startsAt: new Date('1970-01-01T09:00:00Z') } });

UTC rather than the local zone matches what Prisma does with a DateTime @db.Time(6) field, so a ported call site stores the same value, and it round-trips regardless of where the process runs. timetz gets an explicit +00:00 so the session's TimeZone cannot be attached instead, and fractional seconds are emitted only when non-zero.

The coercion runs on every write path (create, createMany, upsert, and the update set clause). Widening the input type is the only type change, so existing code still compiles.

See also#

• CLI, every migration command with examples.
• API Reference, how to query the tables you just defined.
• Typed Errors, including MigrationError (TURBINE_E006).