PostgreSQL schemas

PostgreSQL is Ship's first-class production database. SQLite has the same declaration and ship db workflow where its actual capabilities overlap; Ship does not flatten PostgreSQL into a portable least-common denominator.

Dialect-owned schema

A database declaration owns one schema registry and one named database effect. Fields use Ship types. Metadata carries database facts such as defaults, keys, references, indexes, and checks.

database ProductionDb(Postgres) {
  migrations = "../migrations/production";

  table Organizations {
    id: UUID #{
      primary = True;
      default = sql("gen_random_uuid()");
    };

    name: String;
  }

  table Users {
    id: UUID #{
      primary = True;
      default = sql("gen_random_uuid()");
    };

    organization_id: UUID #{
      references = Organizations.id;
      on_delete = :cascade;
    };

    email: String #{
      unique = True;
      check = sql("position('@' in email) > 1");
    };

    display_name: String;
    active: Bool #{ default = True; };
    created_at: Instant #{ default = sql("now()"); };

    index UsersByOrganization {
      fields = [organization_id];
    };

    check DisplayNameNotEmpty {
      expr = sql("length(display_name) > 0");
    };
  }

  relation OrganizationUsers {
    from = Organizations.id;
    to = Users.organization_id;
    cardinality = :one_to_many;
  }
}

Opening a transaction carries eff ProductionDb; two databases never collapse into one global SQL capability. Every generated operation takes a transaction of this database as its first argument.

  ✓ProductionDb.transactiontransaction: fn!() -> Database.Transaction(ProductionDb), eff ProductionDb: fn!() -> Database.Transaction(ProductionDb), eff ProductionDb;
  ✓ProductionDb.Users.allall: fn(tx: Database.Transaction(ProductionDb)) -> Vector(ProductionDb.Users.Row), exn! tx [> :db.rejected(String) ]: fn(
    tx: Database.Transaction(ProductionDb),
  ) -> Vector(ProductionDb.Users.Row), exn! tx [> :db.rejected(String) ];
  ✓ProductionDb.Users.insertinsert: fn(tx: Database.Transaction(ProductionDb), input: ProductionDb.Users.Insert) -> ProductionDb.Users.Row, exn! tx [> :db([ :rejected(String) | :unique(String) ]) ]: fn(
    tx: Database.Transaction(ProductionDb),
    input: ProductionDb.Users.Insert,
  ) -> ProductionDb.Users.Row, exn! tx [> :db([ :rejected(String) | :unique(String) ]) ];

Typed queries

A query is a callable value that takes a transaction before its own parameters. Its parameters become prepared-statement binds; unresolved runtime capture is rejected rather than interpolated into SQL.

let active_usersactive_users: fn(tx: Database.Transaction(ProductionDb), organization_id: UUID) -> Vector(ProductionDb.Users.Row), exn! tx [> :db.rejected(String) ] = query(organization_idorganization_id: UUID: UUID) {
  from user in ProductionDb.Users,
  where user.organization_id == organization_id && user.active,
  select user;
};

  ✓active_usersactive_users: fn(tx: Database.Transaction(ProductionDb), organization_id: UUID) -> Vector(ProductionDb.Users.Row), exn! tx [> :db.rejected(String) ]: fn(
    tx: Database.Transaction(ProductionDb),
    organization_id: UUID,
  ) -> Vector(ProductionDb.Users.Row), exn! tx [> :db.rejected(String) ];

Raw SQL stays a visible, typed island. Braced holes are still bind parameters, never source-string interpolation.

let search_userssearch_users: fn(tx: Database.Transaction(ProductionDb), term: String) -> Vector(ProductionDb.Users.Row), exn! tx [> :db.rejected(String) ] = query(termterm: String: String) {
  from user in ProductionDb.Users,
  where sql(Bool) {
    user.display_name ILIKE ('%' || {term} || '%')
  },
  select user;
};

  ✓search_userssearch_users: fn(tx: Database.Transaction(ProductionDb), term: String) -> Vector(ProductionDb.Users.Row), exn! tx [> :db.rejected(String) ]: fn(
    tx: Database.Transaction(ProductionDb),
    term: String,
  ) -> Vector(ProductionDb.Users.Row), exn! tx [> :db.rejected(String) ];

Secret columns

Column visibility works like product-field visibility: a Secret column is stored and encoded as usual, but its rows redact it in Display and Inspect.

database CredentialsDb(Postgres) {
  table Credentials {
    id: Int #{ primary = True; };
    username: String;
    password_hash: String #{ visibility = Secret; };
  }
}

  let credentialcredential: CredentialsDb.Credentials.Row = CredentialsDb.Credentials.Row {
    id = 11: Int;
    username = "ada""ada": String;
    password_hash = "must-never-appear""must-never-appear": String;
  };
  expect ✓Inspect.inspectinspect: pure fn!('a: Inspect)(value: CredentialsDb.Credentials.Row) -> String(credentialcredential: CredentialsDb.Credentials.Row)
    == "CredentialsDb.Credentials.Row { id = 1; username = \"ada\"; password_hash = <redacted> }""CredentialsDb.Credentials.Row { id = 1; username = \"ada\"; password_hash = <redacted> }": String;