Migrations

The schema interface does not have to be written by hand: TypeSQL can derive it from the DDL that creates your database, entirely at the type level.

ApplyDdl

ApplyDdl<DB, "…"> returns the schema after a DDL statement or a ;-separated script:

interface Before {
  users: { id: Generated<"int4">; email: Col<"text"> };
}
type After = ApplyDdl<Before, "ALTER TABLE users ADD COLUMN age int NOT NULL DEFAULT 0; ALTER TABLE users RENAME COLUMN email TO login">;
// After["users"]: { id: Generated<"int4">; login: Col<"text">; age: Generated<"int4"> }

CREATE TABLE (including PRIMARY KEY, serial, identity and generated columns, LIKE, INHERITS), ALTER TABLE, DROP TABLE, views, enums (CREATE TYPE … AS ENUM, ALTER TYPE … ADD VALUE) and schemas are supported. Indexes, functions, triggers, grants and similar statements are accepted and leave the schema unchanged. Errors match Postgres: relation "users" already exists, column "age" of relation "users" does not exist, and so on. The complete list is in the DDL reference.

The migrations builder

migrations() collects DDL statements, checks each one when it is added, and carries the resulting schema as a type:

const m = migrations()
  .add(`CREATE TYPE mood AS ENUM ('happy', 'neutral', 'sad')`)
  .add(`CREATE TABLE users (
          id serial PRIMARY KEY,
          email text NOT NULL UNIQUE,
          display_name text,
          mood mood NOT NULL DEFAULT 'neutral'
        )`)
  .add(`CREATE VIEW named_users AS SELECT id, display_name FROM users WHERE display_name IS NOT NULL`);

type DB = SchemaOf<typeof m>;
const sql = createSql<DB>();
const q = sql("SELECT id, display_name, mood FROM users WHERE email = $1", "a@b.c");
// { id: number; display_name: string | null; mood: "happy" | "neutral" | "sad" }

SchemaOf<typeof m> is the schema; m.statements is the list of statements at runtime.

Running migrations

Both adapters export migrate(db, m), which runs m.statements in order inside one transaction:

import { migrate } from "@typesql/pg";

await migrate(pool, m);

Statements are sent without parameters, so $$-quoted function bodies work. migrate does not record which migrations already ran; keep a migrations table, or use a dedicated migration tool to run them and migrations() for the types.