Repo#

The Repo module is your interface to the database. It handles all CRUD operations and query execution.

Setup#

Create a Repo module by parameterizing with your driver:

open Repodb

(* For SQLite *)
module Repo = Repodb.Repo.Make(Repodb_sqlite)

(* For PostgreSQL *)
module Repo = Repodb.Repo.Make(Repodb_postgresql)

Basic CRUD Operations#

Get by ID#

(* Returns Error if not found *)
let user = Repo.get conn ~table:users_table ~id:1 ~decode:decode_user
(* (user, Error.db_error) result *)

(* Returns None if not found *)
let user_opt = Repo.get_opt conn ~table:users_table ~id:1 ~decode:decode_user
(* (user option, Error.db_error) result *)

Get All#

let all_users = Repo.all conn ~table:users_table ~decode:decode_user
(* (user list, Error.db_error) result *)

Insert#

let result = Repo.insert conn
  ~table:users_table
  ~columns:["name"; "email"; "age"]
  ~values:[
    Driver.Value.text "Alice";
    Driver.Value.text "alice@example.com";
    Driver.Value.int 25;
  ]
(* (unit, Error.db_error) result *)

Insert with Returning#

Get the inserted row back (with auto-generated ID, timestamps, etc.):

let result = Repo.insert_returning conn
  ~table:users_table
  ~columns:["name"; "email"; "age"]
  ~values:[
    Driver.Value.text "Alice";
    Driver.Value.text "alice@example.com";
    Driver.Value.int 25;
  ]
  ~decode:decode_user
(* (user, Error.db_error) result *)

Update#

let result = Repo.update conn
  ~table:users_table
  ~columns:["name"; "age"]
  ~values:[
    Driver.Value.text "Bob";
    Driver.Value.int 30;
  ]
  ~where_column:"id"
  ~where_value:(Driver.Value.int 1)
(* (unit, Error.db_error) result *)

Delete#

let result = Repo.delete conn
  ~table:users_table
  ~where_column:"id"
  ~where_value:(Driver.Value.int 1)
(* (unit, Error.db_error) result *)

Query Execution#

Execute queries built with the Query DSL:

Select All Matching#

let query = Query.(
  from users_table
  |> where Expr.(raw "age" >= int 18)
  |> order_by ~direction:Asc (Expr.raw "name")
  |> limit 100
)

let adults = Repo.all_query conn query ~decode:decode_user
(* (user list, Error.db_error) result *)

Select One#

(* Returns Error Not_found if no match *)
let user = Repo.one_query conn query ~decode:decode_user
(* (user, Error.db_error) result *)

(* Returns None if no match *)
let user_opt = Repo.one_query_opt conn query ~decode:decode_user
(* (user option, Error.db_error) result *)

Insert Query#

let query = Query.(
  insert_into users_table
  |> values [name_field; email_field] [
       [Expr.string "Alice"; Expr.string "alice@example.com"]
     ]
)

let result = Repo.insert_query conn query
(* (unit, Error.db_error) result *)

Insert with RETURNING#

let query = Query.(
  insert_into users_table
  |> values [name_field; email_field] [
       [Expr.string "Alice"; Expr.string "alice@example.com"]
     ]
  |> returning Expr.[raw "*"]
)

let user = Repo.insert_query_returning conn query ~decode:decode_user
(* (user, Error.db_error) result *)

Update Query#

let query = Query.(
  update users_table
  |> set name_field (Expr.string "Bob")
  |> where Expr.(raw "id" = int 1)
)

let result = Repo.update_query conn query
(* (unit, Error.db_error) result *)

Update with RETURNING#

let query = Query.(
  update users_table
  |> set name_field (Expr.string "Bob")
  |> where Expr.(raw "status" = string "pending")
  |> returning Expr.[raw "*"]
)

let updated_users = Repo.update_query_returning conn query ~decode:decode_user
(* (user list, Error.db_error) result *)

Delete Query#

let query = Query.(
  delete_from users_table
  |> where Expr.(raw "status" = string "inactive")
)

let result = Repo.delete_query conn query
(* (unit, Error.db_error) result *)

Delete with RETURNING#

let query = Query.(
  delete_from users_table
  |> where Expr.(raw "status" = string "inactive")
  |> returning Expr.[raw "*"]
)

let deleted_users = Repo.delete_query_returning conn query ~decode:decode_user
(* (user list, Error.db_error) result *)

Transactions#

let result = Repo.transaction conn (fun conn ->
  match Repo.insert conn ~table:users_table ~columns:["name"] ~values:[Driver.Value.text "Alice"] with
  | Error e -> Error e
  | Ok () ->
      Repo.insert conn ~table:posts_table ~columns:["title"; "author_id"]
        ~values:[Driver.Value.text "Hello"; Driver.Value.int 1]
)

If any operation returns Error, the transaction is rolled back. See Transactions for more advanced patterns with Multi.

Preloading Associations#

Efficiently load associations to avoid N+1 queries:

Preload Has Many#

let posts_assoc = Assoc.has_many "posts"
  ~related_table:"posts"
  ~foreign_key:"author_id"
  ()

let users_with_posts = Repo.preload_has_many conn users
  ~assoc:posts_assoc
  ~get_owner_id:(fun u -> u.id)
  ~decode_related:decode_post
  ~get_fk:(fun p -> p.author_id)
  ~set_assoc:(fun posts u -> { u with posts = Assoc.Loaded posts })

Preload Has One#

let profile_assoc = Assoc.has_one "profile"
  ~related_table:"profiles"
  ~foreign_key:"user_id"
  ()

let users_with_profiles = Repo.preload_has_one conn users
  ~assoc:profile_assoc
  ~get_owner_id:(fun u -> u.id)
  ~decode_related:decode_profile
  ~get_fk:(fun p -> p.user_id)
  ~set_assoc:(fun profile u -> { u with profile = Assoc.Loaded profile })

Preload Belongs To#

let author_assoc = Assoc.belongs_to "author"
  ~related_table:"users"
  ~foreign_key:"author_id"
  ()

let posts_with_authors = Repo.preload_belongs_to conn posts
  ~assoc:author_assoc
  ~get_fk:(fun p -> Some p.author_id)
  ~decode_related:decode_user
  ~get_related_id:(fun u -> u.id)
  ~set_assoc:(fun author p -> { p with author = Assoc.Loaded author })

Preload Many to Many#

let tags_assoc = Assoc.many_to_many "tags"
  ~related_table:"tags"
  ~join_table:"post_tags"
  ~join_keys:("post_id", "tag_id")
  ()

let posts_with_tags = Repo.preload_many_to_many conn posts
  ~assoc:tags_assoc
  ~get_owner_id:(fun p -> p.id)
  ~decode_related:decode_tag
  ~set_assoc:(fun tags p -> { p with tags = Assoc.Loaded tags })

Chunked Preloading#

For large datasets, preload in chunks to control memory usage:

let offset = ref 0 in
Repo.preload_chunked conn
  ~chunk_size:100
  ~fetch_chunk:(fun () ->
    let query = Query.(
      from users_table
      |> order_by (Expr.raw "id")
      |> limit 100
      |> offset !offset
    ) in
    offset := !offset + 100;
    Repo.all_query conn query ~decode:decode_user
  )
  ~preload:(fun conn users ->
    Repo.preload_has_many conn users
      ~assoc:posts_assoc
      ~get_owner_id:(fun u -> u.id)
      ~decode_related:decode_post
      ~get_fk:(fun p -> p.author_id)
      ~set_assoc:(fun posts u -> { u with posts = Assoc.Loaded posts })
  )
  ~process:(fun users_with_posts ->
    List.iter process_user users_with_posts
  )

Using Changesets with Repo#

let insert_user conn params =
  let cs = create_user_changeset params in
  Repo.insert_changeset
    ~changeset:cs
    ~on_valid:(fun user ->
      Repo.insert conn
        ~table:users_table
        ~columns:["name"; "email"; "age"]
        ~values:[
          Driver.Value.text user.name;
          Driver.Value.text user.email;
          Driver.Value.int user.age;
        ]
    )
    ~on_invalid:(fun errors ->
      Error (Error.Validation_failed errors)
    )

Or use validate_changeset for simpler Result handling:

let insert_user conn params =
  let cs = create_user_changeset params in
  match Repo.validate_changeset cs with
  | Error e -> Error e
  | Ok user ->
      Repo.insert conn
        ~table:users_table
        ~columns:["name"; "email"; "age"]
        ~values:[
          Driver.Value.text user.name;
          Driver.Value.text user.email;
          Driver.Value.int user.age;
        ]

Driver Values#

When building queries with parameters, use Driver.Value:

Driver.Value.int 42
Driver.Value.int64 42L
Driver.Value.float 3.14
Driver.Value.text "hello"
Driver.Value.bool true
Driver.Value.blob "\x00\x01\x02"
Driver.Value.null

Error Handling#

All Repo functions return ('a, Error.db_error) result:

type db_error =
  | Not_found
  | Query_failed of string
  | Connection_error of string
  | Validation_failed of string list
  | Constraint_violation of { constraint_name : string; message : string }
  | Transaction_error of string

Example error handling:

match Repo.get conn ~table:users_table ~id:1 ~decode:decode_user with
| Ok user -> Printf.printf "Found: %s\n" user.name
| Error Error.Not_found -> print_endline "User not found"
| Error (Error.Query_failed msg) -> Printf.printf "Query error: %s\n" msg
| Error (Error.Constraint_violation { constraint_name; message }) ->
    Printf.printf "Constraint %s violated: %s\n" constraint_name message
| Error e -> Printf.printf "Error: %s\n" (Error.show_db_error e)

Next Steps#

• Associations - Define and work with relationships
• Transactions - Advanced transaction handling with Multi
• Migrations - Schema versioning