gdiazlo.tngl.sh / repodb
a database layer insipred by caqti and ecto
1
fork

Configure Feed

Select the types of activity you want to include in your feed.

repodb / docs / queries.md
at 8a5c8dbcfee0e25c34a2a6ee87f51acc42fc8321 8.1 kB view raw view code
gdiazlo.tngl.sh docs: add comprehensive Ecto-style documentation
6b3b5ddf

Queries#

The Query DSL lets you build type-safe, composable SQL queries.

Basic Queries#

SELECT#

open Repodb

let users_table = Schema.table "users"

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

(* Select specific columns *)
let query = Query.(
  from users_table
  |> select Expr.[raw "name"; raw "email"]
)

INSERT#

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.(raw "id" = int 1)
)

DELETE#

let query = Query.(
  delete_from users_table
  |> where Expr.(raw "id" = int 1)
)

WHERE Clauses#

Basic Conditions#

let query = Query.(
  from users_table
  |> where Expr.(raw "age" >= int 18)
)

Multiple Conditions (AND)#

let query = Query.(
  from users_table
  |> where Expr.(raw "age" >= int 18)
  |> and_where Expr.(raw "status" = string "active")
)

OR Conditions#

let query = Query.(
  from users_table
  |> where Expr.(raw "role" = string "admin")
  |> or_where Expr.(raw "role" = 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#

(* Using raw column name *)
Expr.raw "users.name"

(* Using field definition *)
Expr.column name_field

Comparison Operators#

open Expr

(* Equality *)
raw "age" = int 25
raw "name" <> string "admin"

(* Numeric comparisons *)
raw "age" > int 18
raw "age" >= int 18
raw "age" < int 65
raw "age" <= int 65

(* These are also available as functions *)
eq (raw "age") (int 25)
neq (raw "name") (string "admin")
gt (raw "age") (int 18)
gte (raw "age") (int 18)
lt (raw "age") (int 65)
lte (raw "age") (int 65)

Logical Operators#

open Expr

(* AND *)
(raw "age" >= int 18) && (raw "status" = string "active")
and_ (raw "age" >= int 18) (raw "status" = string "active")

(* OR *)
(raw "role" = string "admin") || (raw "role" = string "mod")
or_ (raw "role" = string "admin") (raw "role" = string "mod")

(* NOT *)
not_ (raw "deleted" = bool true)

NULL Checks#

Expr.is_null (Expr.raw "deleted_at")
Expr.is_not_null (Expr.raw "email")

BETWEEN#

Expr.between (Expr.raw "age") (Expr.int 18) (Expr.int 65)

IN Lists#

Expr.in_list (Expr.raw "status") [
  Expr.string "active";
  Expr.string "pending"
]

Expr.not_in_list (Expr.raw "role") [
  Expr.string "banned";
  Expr.string "suspended"
]

LIKE / Pattern Matching#

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

Arithmetic#

open Expr

(* Integer arithmetic *)
raw "price" + int 10
raw "price" - int 5
raw "quantity" * int 2
raw "total" / int 4
raw "count" mod int 3

(* Float arithmetic *)
raw "price" +. float 10.5
raw "discount" *. float 0.9

String Functions#

Expr.lower (Expr.raw "name")
Expr.upper (Expr.raw "name")
Expr.length (Expr.raw "description")
Expr.trim (Expr.raw "input")
Expr.ltrim (Expr.raw "input")
Expr.rtrim (Expr.raw "input")
Expr.concat [Expr.raw "first_name"; Expr.string " "; Expr.raw "last_name"]
Expr.concat_ws ", " [Expr.raw "city"; Expr.raw "state"; Expr.raw "country"]
Expr.substring (Expr.raw "name") ~from:1 ~for_:3
Expr.replace (Expr.raw "text") ~from:"old" ~to_:"new"
Expr.left (Expr.raw "name") 5
Expr.right (Expr.raw "name") 3

Aggregate Functions#

Expr.count (Expr.raw "id")
Expr.count_all
Expr.count_distinct (Expr.raw "category")
Expr.sum (Expr.raw "amount")
Expr.avg (Expr.raw "price")
Expr.max_ (Expr.raw "created_at") Types.ptime
Expr.min_ (Expr.raw "price") Types.float

Date/Time Functions#

Expr.now ()
Expr.current_date
Expr.current_timestamp
Expr.date_part "year" (Expr.raw "created_at")
Expr.extract "month" (Expr.raw "created_at")
Expr.age (Expr.raw "created_at") (Expr.now ())

Math Functions#

Expr.abs_ (Expr.raw "balance")
Expr.ceil (Expr.raw "price")
Expr.floor (Expr.raw "price")
Expr.round (Expr.raw "average")
Expr.sqrt (Expr.raw "variance")
Expr.power (Expr.raw "base") (Expr.int 2)
Expr.random

Conditional Expressions#

(* COALESCE - return first non-null *)
Expr.coalesce [Expr.raw "nickname"; Expr.raw "name"] Types.string

(* NULLIF - return null if equal *)
Expr.nullif (Expr.raw "value") (Expr.int 0) Types.int

(* GREATEST / LEAST *)
Expr.greatest [Expr.raw "a"; Expr.raw "b"; Expr.raw "c"] Types.int
Expr.least [Expr.raw "x"; Expr.raw "y"] Types.float

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

Type Casting#

Expr.cast (Expr.raw "price") Types.int

Subqueries#

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

ORDER BY#

(* Default ascending *)
let query = Query.(
  from users_table
  |> order_by (Expr.raw "name")
)

(* Explicit direction *)
let query = Query.(
  from users_table
  |> order_by ~direction:Desc (Expr.raw "created_at")
)

(* Helper functions *)
let query = Query.(
  from users_table
  |> asc (Expr.raw "name")
  |> desc (Expr.raw "created_at")
)

(* Multiple columns *)
let query = Query.(
  from users_table
  |> order_by ~direction:Asc (Expr.raw "last_name")
  |> order_by ~direction:Asc (Expr.raw "first_name")
)

LIMIT and OFFSET#

let query = Query.(
  from users_table
  |> order_by (Expr.raw "id")
  |> limit 10
  |> offset 20
)

DISTINCT#

let query = Query.(
  from users_table
  |> distinct
  |> select Expr.[raw "category"]
)

JOINs#

INNER JOIN#

let posts_table = Schema.table "posts"

let query = Query.(
  from users_table
  |> inner_join posts_table
       ~on:Expr.(raw "users.id" = raw "posts.author_id")
)

LEFT JOIN#

let query = Query.(
  from users_table
  |> left_join posts_table
       ~on:Expr.(raw "users.id" = raw "posts.author_id")
)

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.[raw "author_id"; count (raw "id")]
  |> group_by Expr.[raw "author_id"]
  |> having Expr.(count (raw "id") > int 5)
)

RETURNING (PostgreSQL)#

let query = Query.(
  insert_into users_table
  |> values [name_field; email_field] [[Expr.string "Alice"; Expr.string "a@b.com"]]
  |> returning Expr.[raw "id"; raw "created_at"]
)

ON CONFLICT (Upsert)#

Do Nothing#

let query = Query.(
  insert_into users_table
  |> values [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
  |> values [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 age >= 18 ORDER BY name ASC LIMIT 10" *)

Executing Queries#

See Repo for executing queries:

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

Next Steps#