# Transactions

repodb provides two ways to handle transactions: simple transactions via `Repo.transaction` and composable multi-step transactions via the `Multi` module.

## Simple Transactions

For straightforward transactions with a few operations:

```ocaml
let transfer_funds conn ~from_id ~to_id ~amount =
  Repo.transaction conn (fun conn ->
    (* Debit from account *)
    match Repo.update conn
      ~table:accounts_table
      ~columns:["balance"]
      ~values:[Driver.Value.float (-.amount)]
      ~where_column:"id"
      ~where_value:(Driver.Value.int from_id)
    with
    | Error e -> Error e
    | Ok () ->
        (* Credit to account *)
        Repo.update conn
          ~table:accounts_table
          ~columns:["balance"]
          ~values:[Driver.Value.float amount]
          ~where_column:"id"
          ~where_value:(Driver.Value.int to_id)
  )
```

If any operation returns `Error`, the entire transaction is rolled back.

### Exception Handling

Transactions properly handle OCaml exceptions. If your callback raises an exception, the transaction is automatically rolled back before the exception propagates:

```ocaml
let result =
  Repo.transaction conn (fun conn ->
    Repo.insert conn ~table:users_table ~columns:["name"] 
      ~values:[Driver.Value.text "Alice"];
    failwith "Something went wrong"  (* Exception raised *)
  )
(* Transaction is rolled back, exception re-raised *)
```

### Nested Transactions

repodb does not support nested transactions. If you call `transaction` while already inside a transaction, the behavior depends on your database:

- **PostgreSQL**: The inner `BEGIN` will fail with an error
- **SQLite**: The inner `BEGIN` will fail with "cannot start a transaction within a transaction"

If you need nested transaction semantics, use savepoints directly:

```ocaml
Repo.transaction conn (fun conn ->
  (* Outer transaction *)
  match D.exec conn "SAVEPOINT sp1" ~params:[||] with
  | Error e -> Error e
  | Ok () ->
      match risky_operation conn with
      | Ok v -> 
          let _ = D.exec conn "RELEASE SAVEPOINT sp1" ~params:[||] in
          Ok v
      | Error e ->
          let _ = D.exec conn "ROLLBACK TO SAVEPOINT sp1" ~params:[||] in
          (* Continue with fallback... *)
)
```

## Multi: Composable Transactions

The `Multi` module enables building complex, multi-step transactions with:
- Named operations for easy debugging
- Dependencies between operations
- Type-safe access to previous operation results
- Automatic rollback on failure

### Setup

```ocaml
module Multi = Repodb.Multi.Make(Repodb_sqlite)
(* or *)
module Multi = Repodb.Multi.Make(Repodb_postgresql)
```

### Basic Multi

```ocaml
let create_user_with_profile conn ~name ~email ~bio =
  let m = Multi.(
    empty
    |> insert "user"
         ~table:users_table
         ~columns:["name"; "email"]
         ~values:[Driver.Value.text name; Driver.Value.text email]
  ) in
  Multi.execute conn m
```

### Operations with RETURNING

Insert and retrieve the row. The row is stored raw and decoded when accessed:

```ocaml
let m = Multi.(
  empty
  |> insert_returning "user"
       ~table:users_table
       ~columns:["name"; "email"]
       ~values:[Driver.Value.text "Alice"; Driver.Value.text "alice@example.com"]
)

match Multi.execute conn m with
| Ok results ->
    (* Decode the row when retrieving *)
    let user = Multi.get_row_exn results "user" ~decode:decode_user in
    Printf.printf "Created user with ID: %d\n" user.id
| Error { failed_operation; error; _ } ->
    Printf.printf "Failed at %s: %s\n" failed_operation (Error.show_db_error error)
```

### Dependent Operations

Use `_fn` variants to access results from previous operations:

```ocaml
let create_user_with_profile conn ~name ~email ~bio =
  let m = Multi.(
    empty
    |> insert_returning "user"
         ~table:users_table
         ~columns:["name"; "email"]
         ~values:[Driver.Value.text name; Driver.Value.text email]
    |> insert_fn "profile" ~f:(fun results ->
         (* Decode the previous result to get the user ID *)
         let user = Multi.get_row_exn results "user" ~decode:decode_user in
         (profiles_table,
          ["user_id"; "bio"],
          [Driver.Value.int user.id; Driver.Value.text bio])
       )
  )
  in
  Multi.execute conn m
```

### All Operation Types

```ocaml
let m = Multi.(
  empty
  (* Insert - stores Unit *)
  |> insert "op1"
       ~table:users_table
       ~columns:["name"]
       ~values:[Driver.Value.text "Alice"]

  (* Insert with RETURNING - stores the raw Row *)
  |> insert_returning "op2"
       ~table:users_table
       ~columns:["name"]
       ~values:[Driver.Value.text "Bob"]

  (* Insert depending on previous result *)
  |> insert_fn "op3" ~f:(fun results ->
       let user = Multi.get_row_exn results "op2" ~decode:decode_user in
       (posts_table, ["author_id"; "title"],
        [Driver.Value.int user.id; Driver.Value.text "Hello"])
     )

  (* Insert with RETURNING depending on previous result *)
  |> insert_returning_fn "op4" ~f:(fun results ->
       let user = Multi.get_row_exn results "op2" ~decode:decode_user in
       (posts_table, ["author_id"; "title"],
        [Driver.Value.int user.id; Driver.Value.text "World"])
     )

  (* Update - stores Unit *)
  |> update "op5"
       ~table:users_table
       ~columns:["name"]
       ~values:[Driver.Value.text "Alice Updated"]
       ~where_column:"id"
       ~where_value:(Driver.Value.int 1)

  (* Update depending on previous result *)
  |> update_fn "op6" ~f:(fun results ->
       let user = Multi.get_row_exn results "op2" ~decode:decode_user in
       (users_table, ["name"], [Driver.Value.text "Updated"],
        "id", Driver.Value.int user.id)
     )

  (* Delete - stores Unit *)
  |> delete "op7"
       ~table:posts_table
       ~where_column:"id"
       ~where_value:(Driver.Value.int 999)

  (* Delete depending on previous result *)
  |> delete_fn "op8" ~f:(fun results ->
       let post = Multi.get_row_exn results "op4" ~decode:decode_post in
       (posts_table, "id", Driver.Value.int post.id)
     )
)
```

### Custom Operations

For complex logic that doesn't fit the predefined operations, use `run` with explicit result types:

```ocaml
let m = Multi.(
  empty
  (* Run returning a single row *)
  |> run_row "fetch_user" ~f:(fun conn _results ->
       match Repodb_postgresql.query_one conn
         "SELECT * FROM users WHERE id = $1"
         ~params:[| Driver.Value.int 1 |]
       with
       | Error e -> Error (Error.Query_failed (Repodb_postgresql.error_message e))
       | Ok None -> Error (Error.Query_failed "User not found")
       | Ok (Some row) -> Ok row
     )

  (* Run returning multiple rows *)
  |> run_rows "fetch_posts" ~f:(fun conn results ->
       let user = Multi.get_row_exn results "fetch_user" ~decode:decode_user in
       match Repodb_postgresql.query conn
         "SELECT * FROM posts WHERE author_id = $1"
         ~params:[| Driver.Value.int user.id |]
       with
       | Error e -> Error (Error.Query_failed (Repodb_postgresql.error_message e))
       | Ok rows -> Ok rows
     )

  (* Run without returning a value *)
  |> run_no_result "side_effect" ~f:(fun _conn results ->
       let user = Multi.get_row_exn results "fetch_user" ~decode:decode_user in
       match send_welcome_email user with
       | true -> Ok ()
       | false -> Error (Error.Query_failed "Email failed")
     )
)
```

### Result Types

Multi stores three types of results:

| Result Type | Stored By | Retrieved With |
|-------------|-----------|----------------|
| `Unit` | `insert`, `update`, `delete`, `run_no_result` | `get_unit` |
| `Row` | `insert_returning`, `run_row` | `get_row ~decode` |
| `Rows` | `run_rows` | `get_rows ~decode` |

### Merging Multis

Combine multiple Multi transactions:

```ocaml
let create_user = Multi.(
  empty
  |> insert_returning "user" ~table:users_table ~columns:["name"]
       ~values:[Driver.Value.text "Alice"]
)

let create_profile = Multi.(
  empty
  |> insert_fn "profile" ~f:(fun results ->
       let user = Multi.get_row_exn results "user" ~decode:decode_user in
       (profiles_table, ["user_id"], [Driver.Value.int user.id])
     )
)

let combined = Multi.merge create_user create_profile
```

### Inspecting Multis

```ocaml
let names = Multi.names m  (* ["user"; "profile"; ...] *)
let has_user = Multi.has_name "user" m  (* true *)

(* Validate before executing *)
match Multi.validate_multi m with
| Ok () -> Multi.execute conn m
| Error msg -> Printf.printf "Invalid multi: %s\n" msg
```

### Error Handling

```ocaml
match Multi.execute conn m with
| Ok results ->
    (* Access results by name with decode function *)
    let user = Multi.get_row results "user" ~decode:decode_user in
    let post = Multi.get_row_exn results "post" ~decode:decode_post in
    ...

| Error { failed_operation; error; completed } ->
    Printf.printf "Failed at operation: %s\n" failed_operation;
    Printf.printf "Error: %s\n" (Error.show_db_error error);
    (* completed contains results from operations that succeeded before failure *)
    let partial_user = Multi.get_row completed "user" ~decode:decode_user in
    ...
```

### Working with Results

```ocaml
(* For Unit results (insert, update, delete) *)
let () = Multi.get_unit_exn results "insert_op"

(* For Row results - safe get returns option *)
let user = Multi.get_row results "user" ~decode:decode_user

(* For Row results - unsafe get raises if not found *)
let user = Multi.get_row_exn results "user" ~decode:decode_user

(* Get raw row without decoding *)
let row = Multi.get_row_raw_exn results "user"

(* For multiple rows *)
let posts = Multi.get_rows results "posts" ~decode:decode_post
let posts = Multi.get_rows_exn results "posts" ~decode:decode_post
```

## Complete Example

```ocaml
let create_blog_post conn ~author_id ~title ~body ~tags =
  let m = Multi.(
    empty
    (* Create the post - stores raw row *)
    |> insert_returning "post"
         ~table:posts_table
         ~columns:["author_id"; "title"; "body"]
         ~values:[
           Driver.Value.int author_id;
           Driver.Value.text title;
           Driver.Value.text body;
         ]

    (* Create tags and associations *)
    |> run_rows "tags" ~f:(fun conn results ->
         let post = get_row_exn results "post" ~decode:decode_post in

         (* Insert each tag and create association *)
         let rec process_tags acc = function
           | [] -> Ok (List.rev acc)
           | tag_name :: rest ->
               match Repo.insert_returning conn
                 ~table:tags_table
                 ~columns:["name"]
                 ~values:[Driver.Value.text tag_name]
                 ~decode:decode_tag
               with
               | Error e -> Error e
               | Ok tag ->
                   match Repo.insert conn
                     ~table:post_tags_table
                     ~columns:["post_id"; "tag_id"]
                     ~values:[Driver.Value.int post.id; Driver.Value.int tag.id]
                   with
                   | Error e -> Error e
                   | Ok () -> process_tags (tag :: acc) rest
         in
         match process_tags [] tags with
         | Error e -> Error e
         | Ok tag_list ->
             (* Return empty rows - we don't need the result *)
             Ok []
       )

    (* Update author's post count *)
    |> run_no_result "update_count" ~f:(fun conn _results ->
         Repo.exec conn
           "UPDATE users SET post_count = post_count + 1 WHERE id = $1"
           ~params:[| Driver.Value.int author_id |]
       )
  )
  in
  match Multi.execute conn m with
  | Ok results -> Ok (Multi.get_row_exn results "post" ~decode:decode_post)
  | Error { error; _ } -> Error error
```

## API Reference

### Creating Operations

| Function | Stores | Description |
|----------|--------|-------------|
| `insert` | `Unit` | Insert a row |
| `insert_fn` | `Unit` | Insert with dynamic values |
| `insert_returning` | `Row` | Insert and return the row |
| `insert_returning_fn` | `Row` | Insert with dynamic values, return row |
| `update` | `Unit` | Update rows |
| `update_fn` | `Unit` | Update with dynamic values |
| `delete` | `Unit` | Delete rows |
| `delete_fn` | `Unit` | Delete with dynamic values |
| `run` | any | Custom operation returning `stored_result` |
| `run_unit` | `Unit` | Custom operation returning unit |
| `run_row` | `Row` | Custom operation returning a row |
| `run_rows` | `Rows` | Custom operation returning rows |
| `run_no_result` | `Unit` | Custom operation with no return value |

### Retrieving Results

| Function | Returns | Raises |
|----------|---------|--------|
| `get_unit results name` | `unit option` | No |
| `get_unit_exn results name` | `unit` | Yes, if not found |
| `get_row results name ~decode` | `'a option` | No |
| `get_row_exn results name ~decode` | `'a` | Yes, if not found |
| `get_row_raw results name` | `Driver.row option` | No |
| `get_row_raw_exn results name` | `Driver.row` | Yes, if not found |
| `get_rows results name ~decode` | `'a list option` | No |
| `get_rows_exn results name ~decode` | `'a list` | Yes, if not found |
| `get_rows_raw results name` | `Driver.row list option` | No |
| `get_rows_raw_exn results name` | `Driver.row list` | Yes, if not found |

## Next Steps

- [Repo](repo.md) - Basic database operations
- [Migrations](migrations.md) - Database schema versioning
- [Queries](queries.md) - Building complex queries