Queries#

The Query DSL lets you build type-safe, composable SQL queries using your schema definitions.

Schema Integration#

repodb queries leverage your field definitions for type safety. Instead of using raw column names, use Expr.column with your field definitions:

open Repodb

(* Define your schema *)
type user = { id : int; name : string; email : string; age : int }

let users_table = Schema.table "users"

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 }) ()

Now use these fields in your queries:

(* Type-safe query using field definitions *)
let adults = Query.(
  from users_table
  |> where Expr.(column age_field >= int 18)
  |> order_by (Expr.column name_field)
  |> limit 10
)

Why Use Expr.column Instead of Expr.raw?#

(* RECOMMENDED: Type-safe, refactor-friendly *)
Query.where Expr.(column age_field >= int 18)

(* NOT RECOMMENDED: Stringly-typed, error-prone *)
Query.where Expr.(raw "age" >= int 18)

Basic Queries#

SELECT#

(* Select all columns *)
let query = Query.from users_table

(* Select specific columns using fields *)
let query = Query.(
  from users_table
  |> select Expr.[column name_field; column email_field]
)

INSERT (Type-Safe)#

The Query_values module provides fully type-safe INSERT statements. The compiler verifies:

• Column count matches value count
• Each value type matches its field type
• All fields belong to the same table

(* Type-safe insert with heterogeneous column types *)
let query =
  Query.insert_into users_table
  |> Query_values.values4
      (id_field, name_field, email_field, age_field)
      (Expr.int 1, Expr.string "Alice", Expr.string "alice@example.com", Expr.int 25)

(* Multi-row insert - also fully type-safe *)
let query =
  Query.insert_into users_table
  |> Query_values.values3_multi
      (name_field, email_field, age_field)
      [
        (Expr.string "Alice", Expr.string "alice@example.com", Expr.int 25);
        (Expr.string "Bob", Expr.string "bob@example.com", Expr.int 30);
        (Expr.string "Carol", Expr.string "carol@example.com", Expr.int 28);
      ]

Functions are available from values1 to values30 for single rows, and values1_multi to values30_multi for bulk inserts.

Why Type-Safe INSERTs Matter#

With the legacy Query.values approach, the compiler cannot catch these bugs:

(* DANGEROUS: These compile but are wrong! *)

(* Bug 1: Column/value count mismatch *)
Query.values [name_field; email_field] [[Expr.string "Alice"]]  (* Missing value! *)

(* Bug 2: Type mismatch - swapped string and int *)
Query.values [name_field; age_field] [[Expr.int 25; Expr.string "Alice"]]

(* Bug 3: Swapped columns of same type *)
Query.values [email_field; name_field] [[Expr.string "Alice"; Expr.string "alice@example.com"]]

With Query_values, all these are compile-time errors:

(* All of these fail to compile *)

(* Error: expects 2-tuple, got 1 value *)
Query_values.values2 (name_field, email_field) (Expr.string "Alice")

(* Error: int Expr.t is not string Expr.t *)
Query_values.values2 (name_field, age_field) (Expr.int 25, Expr.string "Alice")

INSERT (Legacy)#

The original Query.values function is still available but provides weaker type safety:

(* Works but no compile-time type checking between columns and values *)
let query = Query.(
  insert_into users_table
  |> values [name_field; email_field; age_field] [
       [Expr.string "Alice"; Expr.string "alice@example.com"; Expr.int 25]
     ]
)

UPDATE#

let query = Query.(
  update users_table
  |> set name_field (Expr.string "Bob")
  |> set age_field (Expr.int 30)
  |> where Expr.(column id_field = int 1)
)

DELETE#

let query = Query.(
  delete_from users_table
  |> where Expr.(column id_field = int 1)
)

WHERE Clauses#

Basic Conditions#

let query = Query.(
  from users_table
  |> where Expr.(column age_field >= int 18)
)

Multiple Conditions (AND)#

(* Assuming you have a status_field defined *)
let query = Query.(
  from users_table
  |> where Expr.(column age_field >= int 18)
  |> and_where Expr.(column status_field = string "active")
)

OR Conditions#

(* Assuming you have a role_field defined *)
let query = Query.(
  from users_table
  |> where Expr.(column role_field = string "admin")
  |> or_where Expr.(column role_field = string "moderator")
)

Expressions#

The Expr module provides type-safe expression building.

Literals#

Expr.int 42
Expr.int64 42L
Expr.float 3.14
Expr.string "hello"
Expr.bool true
Expr.null Types.string  (* NULL of type string *)

Column References#

(* RECOMMENDED: Using field definition - type-safe *)
Expr.column name_field
Expr.column age_field

(* NOT RECOMMENDED: Using raw column name - no type checking *)
Expr.raw "name"
Expr.raw "users.name"  (* qualified name for joins *)

Comparison Operators#

open Expr

(* Using fields (recommended) *)
column age_field = int 25
column name_field <> string "admin"
column age_field > int 18
column age_field >= int 18
column age_field < int 65
column age_field <= int 65

(* Function syntax *)
eq (column age_field) (int 25)
neq (column name_field) (string "admin")
gt (column age_field) (int 18)
gte (column age_field) (int 18)
lt (column age_field) (int 65)
lte (column age_field) (int 65)

Logical Operators#

open Expr

(* AND *)
(column age_field >= int 18) && (column status_field = string "active")
and_ (column age_field >= int 18) (column status_field = string "active")

(* OR *)
(column role_field = string "admin") || (column role_field = string "mod")
or_ (column role_field = string "admin") (column role_field = string "mod")

(* NOT *)
not_ (column deleted_field = bool true)

NULL Checks#

Expr.is_null (Expr.column deleted_at_field)
Expr.is_not_null (Expr.column email_field)

BETWEEN#

Expr.between (Expr.column age_field) (Expr.int 18) (Expr.int 65)

IN Lists#

Expr.in_list (Expr.column status_field) [
  Expr.string "active";
  Expr.string "pending"
]

Expr.not_in_list (Expr.column role_field) [
  Expr.string "banned";
  Expr.string "suspended"
]

LIKE / Pattern Matching#

Expr.like (Expr.column name_field) "%alice%"
Expr.ilike (Expr.column name_field) "%alice%"  (* case-insensitive, PostgreSQL *)
Expr.similar_to (Expr.column email_field) "%@example.com"
Expr.regexp (Expr.column name_field) "^[A-Z]"  (* PostgreSQL regex *)

Arithmetic#

open Expr

(* Integer arithmetic *)
column price_field + int 10
column price_field - int 5
column quantity_field * int 2
column total_field / int 4
column count_field mod int 3

(* Float arithmetic *)
column price_field +. float 10.5
column discount_field *. float 0.9

String Functions#

Expr.lower (Expr.column name_field)
Expr.upper (Expr.column name_field)
Expr.length (Expr.column description_field)
Expr.trim (Expr.column input_field)
Expr.ltrim (Expr.column input_field)
Expr.rtrim (Expr.column input_field)
Expr.concat [Expr.column first_name_field; Expr.string " "; Expr.column last_name_field]
Expr.concat_ws ", " [Expr.column city_field; Expr.column state_field; Expr.column country_field]
Expr.substring (Expr.column name_field) ~from:1 ~for_:3
Expr.replace (Expr.column text_field) ~from:"old" ~to_:"new"
Expr.left (Expr.column name_field) 5
Expr.right (Expr.column name_field) 3

Aggregate Functions#

Expr.count (Expr.column id_field)
Expr.count_all
Expr.count_distinct (Expr.column category_field)
Expr.sum (Expr.column amount_field)
Expr.avg (Expr.column price_field)
Expr.max_ (Expr.column created_at_field) Types.ptime
Expr.min_ (Expr.column price_field) Types.float

Date/Time Functions#

Expr.now ()
Expr.current_date
Expr.current_timestamp
Expr.date_part "year" (Expr.column created_at_field)
Expr.extract "month" (Expr.column created_at_field)
Expr.age (Expr.column created_at_field) (Expr.now ())

Math Functions#

Expr.abs_ (Expr.column balance_field)
Expr.ceil (Expr.column price_field)
Expr.floor (Expr.column price_field)
Expr.round (Expr.column average_field)
Expr.sqrt (Expr.column variance_field)
Expr.power (Expr.column base_field) (Expr.int 2)
Expr.random

Conditional Expressions#

(* COALESCE - return first non-null *)
Expr.coalesce [Expr.column nickname_field; Expr.column name_field] Types.string

(* NULLIF - return null if equal *)
Expr.nullif (Expr.column value_field) (Expr.int 0) Types.int

(* GREATEST / LEAST *)
Expr.greatest [Expr.column a_field; Expr.column b_field; Expr.column c_field] Types.int
Expr.least [Expr.column x_field; Expr.column y_field] Types.float

(* CASE WHEN *)
Expr.case [
  (Expr.(column status_field = string "active"), Expr.string "Active");
  (Expr.(column status_field = string "pending"), Expr.string "Pending");
] ~else_:(Expr.string "Unknown") Types.string

Type Casting#

Expr.cast (Expr.column price_field) Types.int

Subqueries#

Expr.subquery "SELECT MAX(price) FROM products"

ORDER BY#

(* Default ascending *)
let query = Query.(
  from users_table
  |> order_by (Expr.column name_field)
)

(* Explicit direction *)
let query = Query.(
  from users_table
  |> order_by ~direction:Desc (Expr.column created_at_field)
)

(* Helper functions *)
let query = Query.(
  from users_table
  |> asc (Expr.column name_field)
  |> desc (Expr.column created_at_field)
)

(* Multiple columns *)
let query = Query.(
  from users_table
  |> order_by ~direction:Asc (Expr.column last_name_field)
  |> order_by ~direction:Asc (Expr.column first_name_field)
)

LIMIT and OFFSET#

let query = Query.(
  from users_table
  |> order_by (Expr.column id_field)
  |> limit 10
  |> offset 20
)

DISTINCT#

let query = Query.(
  from users_table
  |> distinct
  |> select Expr.[column category_field]
)

JOINs#

For joins, you'll typically have fields from multiple tables:

(* Posts schema *)
let posts_table = Schema.table "posts"

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

let author_id_field =
  Field.make ~table_name:"posts" ~name:"author_id" ~ty:Types.int
    ~get:(fun p -> p.author_id) ~set:(fun v p -> { p with author_id = v }) ()

INNER JOIN#

let query = Query.(
  from users_table
  |> inner_join posts_table
       ~on:Expr.(column id_field = column author_id_field)
)

LEFT JOIN#

let query = Query.(
  from users_table
  |> left_join posts_table
       ~on:Expr.(column id_field = column author_id_field)
)

Other Joins#

Query.right_join table ~on:condition query
Query.full_join table ~on:condition query
Query.join ~kind:Inner table ~on:condition query

GROUP BY and HAVING#

let query = Query.(
  from posts_table
  |> select Expr.[column author_id_field; count (column post_id_field)]
  |> group_by Expr.[column author_id_field]
  |> having Expr.(count (column post_id_field) > int 5)
)

RETURNING (PostgreSQL)#

let query = Query.(
  insert_into users_table
  |> Query_values.values2
      (name_field, email_field)
      (Expr.string "Alice", Expr.string "a@b.com")
  |> returning Expr.[column id_field; column created_at_field]
)

ON CONFLICT (Upsert)#

Do Nothing#

let query = Query.(
  insert_into users_table
  |> Query_values.values2
      (email_field, name_field)
      (Expr.string "a@b.com", Expr.string "Alice")
  |> on_conflict_do_nothing ~target:[email_field]
)

Do Update#

let query = Query.(
  insert_into users_table
  |> Query_values.values2
      (email_field, name_field)
      (Expr.string "a@b.com", Expr.string "Alice")
  |> on_conflict_do_update
       ~target:[email_field]
       ~set:[(name_field, Expr.string "Alice Updated")]
)

Generating SQL#

let sql = Query.to_sql query
(* "SELECT * FROM users WHERE users.age >= 18 ORDER BY users.name ASC LIMIT 10" *)

Executing Queries#

See Repo for executing queries:

let users = Repo.all_query conn query ~decode:decode_user

Summary: Type-Safe Query Patterns#

Next Steps#

• Repo - Execute queries and perform CRUD operations
• Associations - Work with relationships
• Transactions - Transaction handling