Tables

table User "users" {
    user_id @primary_key;
    name: sql"TEXT";
    created_at;
};

Inside a table block:

Columns (with optional type, nullability, attributes, foreign keys)
Table-level constraints (@primary_key, @unique, @check, @index)
Standalone foreign keys
Mixin inclusions

Columns

table Example {
    column_name: type;
};

Type can be omitted. Immigrant resolves it from a scalar/enum/struct with the same name:

scalar user_id = sql"TEXT";
enum challenge_kind { password; gpg; totp; };

table UserChallenge {
    /// resolved to scalar user_id
    user_id;
    /// resolved to enum challenge_kind
    challenge_kind;
    /// explicit inline type
    name: sql"TEXT";
    /// explicit inline type
    check_data: sql"BYTEA";
};

Nullability

Append ? for nullable. Without it, columns are NOT NULL:

table Profile {
    user_id @primary_key;
    /// nullable, type resolved by name
    name?;
    /// nullable, explicit type
    picture: sql"TEXT"?;
};

Inline SQL types

Write SQL type directly instead of referencing a scalar:

table Example {
    data: sql"BYTEA";
    tags: sql"TEXT[]";
    /// reference another type inside inline SQL
    scores: sql"{score_type}[]";
};

Column Attributes

@primary_key

table User {
    user_id @primary_key;
};

Multiple columns with @primary_key merge into one composite primary key:

table SessionIdentity {
    session_id @primary_key;
    user_id @primary_key;
    /// produces PRIMARY KEY(session_id, user_id)
};

A column can also list additional columns to include in the primary key:

table AudienceScope {
    scope_id @primary_key(audience_id);
    audience_id @index;
    /// produces PRIMARY KEY(scope_id, audience_id)
};

Named primary key:

table SessionIdentity {
    session_id @primary_key "session_identity_pkey";
};

@unique

table RefreshToken {
    refresh_token_base @primary_key;
    access_token @unique;
};

@check

CHECK constraint using the expression language. _ is placeholder for column value:

table Example {
    age: sql"INTEGER" @check(_ >= 0);
    name: sql"TEXT" @check(char_length(_) > 0);
    code: sql"TEXT" @check "valid_code" (LEFT(_, 1) == 'U' && char_length(_) == 33);
};

@default

Sets column default using the expression language:

table Client {
    created_at: sql"TIMESTAMPTZ" @default(NOW());
    active: sql"BOOLEAN" @default(true);
    expires_in: sql"INTEGER" @default(7200);
};

@initialize_as

Like @default, but for populating columns at creation time. Can reference other columns and use expression language casts:

table UserBuiltinConsent {
    scope_id: builtin_scope @initialize_as(scope_id::builtin_scope);
    public: bool @initialize_as(false);
};

@index

table UsernameHistory {
    user_id @index;
    user_id @index "custom_index_name";
};

Index options for specialized indexes:

table Example {
    embedding @index.opclass(vector_cosine_ops);
    embedding @index.using(ivfflat);
    embedding @index.unique;
    embedding @index.with("lists = 100");

    /// all options combined
    embedding @index.opclass(vector_cosine_ops).using(ivfflat).with("options = \"..settings..\"");
};

Named indexes merge columns into one index:

table Example {
    a: sql"INTEGER" @index "composite_idx";
    b: sql"INTEGER" @index "composite_idx";
    /// produces CREATE INDEX composite_idx ON example(a, b)
};

Table-Level Constraints

For composite constraints or referencing multiple columns:

table UserChallenge {
    user_id;
    challenge_kind;
    check_data: sql"BYTEA";
    search_key: sql"BYTEA"?;
    keybase_material_sort_key: sql"INTEGER"?;

    @primary_key(user_id, challenge_kind, check_data);
    @unique(challenge_kind, search_key);
    @unique(user_id, keybase_material_sort_key);
};

Named constraints:

table AuthorizationCodeAudience {
    authorization_code;
    audience_id;
    @primary_key "oauth_authorization_code_audiences_pk" (audience_id, authorization_code);
};

Table-level @check references columns by name (no _ placeholder):

table Example {
    lo: sql"INTEGER";
    hi: sql"INTEGER";
    @check "valid_range" (lo <= hi);
};

Table-level @index:

table Example {
    user_id: sql"TEXT";
    email: sql"TEXT";
    @index(user_id, email);
    @index.unique(user_id, email);
};

Constraint Merging

All primary keys merge into one. Checks with the same name merge with AND:

scalar test = sql"TEXT" @inline @check "testcheck" (_ != '1');

table Foo {
    test @check "testcheck" (_ != '2');
    /// produces CHECK (test <> '1' AND test <> '2')
};

Named @unique and @index also merge their columns.

@external

Prevents immigrant from generating CREATE/DROP TABLE. For tables managed outside immigrant:

table External {
    id: sql"INTEGER";
    @external;
};

Mixins

Reusable groups of columns, attributes, and foreign keys:

@mixin Timestamps {
    created_at;
    updated_at;
};

table User {
    @mixin Timestamps;
    user_id @primary_key;
};

table Post {
    @mixin Timestamps;
    id: sql"INTEGER" @primary_key;
};

Mixins can include other mixins.