repodb / docs / queries.md

# 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:

```ocaml
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:

```ocaml
(* 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`?

| Approach | Type Safety | Refactoring | Example |
|----------|-------------|-------------|---------|
| `Expr.column field` | ✅ Compile-time checked | ✅ Rename field, all usages update | `Expr.column age_field` |
| `Expr.raw "name"` | ❌ No checking | ❌ Manual find/replace | `Expr.raw "age"` |

```ocaml
(* 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

```ocaml
(* 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

```ocaml
(* 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 "[e-mail]", 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 "[e-mail]", Expr.int 25);
        (Expr.string "Bob", Expr.string "[e-mail]", Expr.int 30);
        (Expr.string "Carol", Expr.string "[e-mail]", 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:

```ocaml
(* 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 "[e-mail]"]]
```

With `Query_values`, all these are **compile-time errors**:

```ocaml
(* 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:

```ocaml
(* 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 "[e-mail]"; Expr.int 25]
     ]
)
```

### UPDATE

```ocaml
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

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

## WHERE Clauses

### Basic Conditions

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

### Multiple Conditions (AND)

```ocaml
(* 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

```ocaml
(* 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

```ocaml
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

```ocaml
(* 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

```ocaml
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

```ocaml
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

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

### BETWEEN

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

### IN Lists

```ocaml
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

```ocaml
Expr.like (Expr.column name_field) "%alice%"
Expr.ilike (Expr.column name_field) "%alice%"  (* case-insensitive, PostgreSQL *)
Expr.similar_to (Expr.column email_field) "[e-mail]"
Expr.regexp (Expr.column name_field) "^[A-Z]"  (* PostgreSQL regex *)
```

### Arithmetic

```ocaml
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

```ocaml
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

```ocaml
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

```ocaml
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

```ocaml
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

```ocaml
(* 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

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

### Subqueries

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

## ORDER BY

```ocaml
(* 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

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

## DISTINCT

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

## JOINs

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

```ocaml
(* 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

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

### LEFT JOIN

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

### Other Joins

```ocaml
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

```ocaml
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)

```ocaml
let query = Query.(
  insert_into users_table
  |> Query_values.values2
      (name_field, email_field)
      (Expr.string "Alice", Expr.string "[e-mail]")
  |> returning Expr.[column id_field; column created_at_field]
)
```

## ON CONFLICT (Upsert)

### Do Nothing

```ocaml
let query = Query.(
  insert_into users_table
  |> Query_values.values2
      (email_field, name_field)
      (Expr.string "[e-mail]", Expr.string "Alice")
  |> on_conflict_do_nothing ~target:[email_field]
)
```

### Do Update

```ocaml
let query = Query.(
  insert_into users_table
  |> Query_values.values2
      (email_field, name_field)
      (Expr.string "[e-mail]", Expr.string "Alice")
  |> on_conflict_do_update
       ~target:[email_field]
       ~set:[(name_field, Expr.string "Alice Updated")]
)
```

## Generating SQL

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

## Executing Queries

See [Repo](repo.md) for executing queries:

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

## Summary: Type-Safe Query Patterns

| Operation | Type-Safe Approach |
|-----------|-------------------|
| Column reference | `Expr.column field` |
| WHERE condition | `Query.where Expr.(column field = value)` |
| INSERT | `Query_values.valuesN (fields) (values)` |
| UPDATE SET | `Query.set field value` |
| ORDER BY | `Query.order_by (Expr.column field)` |
| SELECT | `Query.select Expr.[column f1; column f2]` |
| JOIN ON | `Query.inner_join t ~on:Expr.(column f1 = column f2)` |
| ON CONFLICT | `Query.on_conflict_do_nothing ~target:[field]` |

## Next Steps

- [Repo](repo.md) - Execute queries and perform CRUD operations
- [Associations](associations.md) - Work with relationships
- [Transactions](transactions.md) - Transaction handling