Schemas#

Schemas define the structure of your database tables and how they map to OCaml records.

Table Definitions#

Define a table with Schema.table:

open Repodb

let users_table = Schema.table "users"

(* With explicit schema prefix *)
let posts_table = Schema.table "posts" ~schema:(Some "public")

Field Definitions#

Fields connect your OCaml record to database columns. Each field specifies:

• The column name
• The OCaml type
• How to get/set the value on your record

type user = {
  id : int;
  name : string;
  email : string;
  age : int;
}

let id_field =
  Field.make ~table_name:"users" ~name:"id" ~ty:Types.int
    ~get:(fun u -> u.id)
    ~set:(fun v u -> { u with id = v })
    ~primary_key:true ()

let name_field =
  Field.make ~table_name:"users" ~name:"name" ~ty:Types.string
    ~get:(fun u -> u.name)
    ~set:(fun v u -> { u with name = v })
    ()

let email_field =
  Field.make ~table_name:"users" ~name:"email" ~ty:Types.string
    ~get:(fun u -> u.email)
    ~set:(fun v u -> { u with email = v })
    ()

let age_field =
  Field.make ~table_name:"users" ~name:"age" ~ty:Types.int
    ~get:(fun u -> u.age)
    ~set:(fun v u -> { u with age = v })
    ()

Field Options#

Field.make
  ~table_name:"users"
  ~name:"email"
  ~ty:Types.string
  ~get:(fun u -> u.email)
  ~set:(fun v u -> { u with email = v })
  ~primary_key:false    (* default: false *)
  ~unique:false         (* default: false *)
  ~default:None         (* optional default SQL expression *)
  ()

Types#

repodb provides built-in types that map OCaml types to SQL types:

Optional Types#

Wrap any type in Types.option for nullable columns:

let bio_field =
  Field.make ~table_name:"users" ~name:"bio" ~ty:(Types.option Types.string)
    ~get:(fun u -> u.bio)
    ~set:(fun v u -> { u with bio = v })
    ()

Array Types#

PostgreSQL array types:

let tags_field =
  Field.make ~table_name:"posts" ~name:"tags" ~ty:(Types.array Types.string)
    ~get:(fun p -> p.tags)
    ~set:(fun v p -> { p with tags = v })
    ()

Custom Types#

Define custom types for domain-specific values:

type status = Active | Inactive | Pending

let status_type = Types.custom
  ~encode:(function
    | Active -> Ok "active"
    | Inactive -> Ok "inactive"
    | Pending -> Ok "pending")
  ~decode:(function
    | "active" -> Ok Active
    | "inactive" -> Ok Inactive
    | "pending" -> Ok Pending
    | s -> Error ("Unknown status: " ^ s))
  ~sql_type:"VARCHAR(20)"

let status_field =
  Field.make ~table_name:"users" ~name:"status" ~ty:status_type
    ~get:(fun u -> u.status)
    ~set:(fun v u -> { u with status = v })
    ()

Schema Definitions (DDL)#

For defining complete table schemas with constraints:

let users_schema = Schema.define "users" [
  Schema.column "id" Types.int64 ~primary_key:true ~not_null:true;
  Schema.column "name" Types.string ~not_null:true;
  Schema.column "email" Types.string ~not_null:true ~unique:true;
  Schema.column "age" Types.int;
  Schema.column "created_at" Types.ptime ~not_null:true ~default:"NOW()";
]

Column Constraints#

Schema.column "id" Types.int64
  ~primary_key:true   (* PRIMARY KEY constraint *)
  ~not_null:true      (* NOT NULL constraint *)
  ~unique:true        (* UNIQUE constraint *)
  ~default:"NOW()"    (* DEFAULT value (SQL expression) *)
  ~check:"age >= 0"   (* CHECK constraint *)
  ~references:(Schema.references ~table:"other_table" ~column:"id" ())

Foreign Key References#

let posts_schema = Schema.define "posts" [
  Schema.column "id" Types.int64 ~primary_key:true ~not_null:true;
  Schema.column "author_id" Types.int64 ~not_null:true
    ~references:(Schema.references
      ~table:"users"
      ~column:"id"
      ~on_delete:(Some Cascade)
      ~on_update:(Some Cascade)
      ());
  Schema.column "title" Types.string ~not_null:true;
  Schema.column "body" Types.string;
]

Foreign key actions:

• Cascade - Delete/update related records
• Restrict - Prevent delete/update if references exist
• SetNull - Set foreign key to NULL
• SetDefault - Set to default value
• NoAction - Database default behavior

Table-Level Constraints#

let schema = Schema.define "posts" columns
  |> Schema.with_primary_key ["id"]
  |> Schema.with_unique ["author_id"; "slug"]
  |> Schema.with_check ~name:"positive_views" "views >= 0"

Decoding Rows#

Create a decoder function to convert database rows to your record type:

let decode_user row =
  {
    id = Driver.row_int row 0;
    name = Driver.row_text row 1;
    email = Driver.row_text row 2;
    age = Driver.row_int row 3;
  }

Available row accessors:

• Driver.row_int row index - Get integer at column index
• Driver.row_int64 row index - Get int64 at column index
• Driver.row_float row index - Get float at column index
• Driver.row_text row index - Get string at column index
• Driver.row_bool row index - Get boolean at column index
• Driver.row_blob row index - Get blob/bytes at column index
• Driver.row_text_opt row index - Get optional string (for nullable columns)
• Driver.row_int_opt row index - Get optional int (for nullable columns)

Common Patterns#

ID and Timestamps#

A common pattern is to include ID and timestamp columns:

let schema = Schema.define "posts" (
  [Schema.id_column ()] @
  [
    Schema.column "title" Types.string ~not_null:true;
    Schema.column "body" Types.string;
  ] @
  Schema.timestamps ()
)

This adds:

• id - BIGINT PRIMARY KEY NOT NULL
• inserted_at - TIMESTAMPTZ NOT NULL DEFAULT NOW()
• updated_at - TIMESTAMPTZ NOT NULL DEFAULT NOW()

Next Steps#

• Changesets - Validate data before saving
• Queries - Query your data
• Associations - Define relationships