a database layer insipred by caqti and ecto
gdiazlo.tngl.sh
1# Transactions
2
3repodb provides two ways to handle transactions: simple transactions via `Repo.transaction` and composable multi-step transactions via the `Multi` module.
4
5## Simple Transactions
6
7For straightforward transactions with a few operations:
8
9```ocaml
10let transfer_funds conn ~from_id ~to_id ~amount =
11 Repo.transaction conn (fun conn ->
12 (* Debit from account *)
13 match Repo.update conn
14 ~table:accounts_table
15 ~columns:["balance"]
16 ~values:[Driver.Value.float (-.amount)]
17 ~where_column:"id"
18 ~where_value:(Driver.Value.int from_id)
19 with
20 | Error e -> Error e
21 | Ok () ->
22 (* Credit to account *)
23 Repo.update conn
24 ~table:accounts_table
25 ~columns:["balance"]
26 ~values:[Driver.Value.float amount]
27 ~where_column:"id"
28 ~where_value:(Driver.Value.int to_id)
29 )
30```
31
32If any operation returns `Error`, the entire transaction is rolled back.
33
34## Multi: Composable Transactions
35
36The `Multi` module enables building complex, multi-step transactions with:
37- Named operations for easy debugging
38- Dependencies between operations
39- Access to previous operation results
40- Automatic rollback on failure
41
42### Setup
43
44```ocaml
45module Multi = Repodb.Multi.Make(Repodb_sqlite)
46(* or *)
47module Multi = Repodb.Multi.Make(Repodb_postgresql)
48```
49
50### Basic Multi
51
52```ocaml
53let create_user_with_profile conn ~name ~email ~bio =
54 let m = Multi.(
55 empty
56 |> insert "user"
57 ~table:users_table
58 ~columns:["name"; "email"]
59 ~values:[Driver.Value.text name; Driver.Value.text email]
60 ) in
61 Multi.execute conn m
62```
63
64### Operations with RETURNING
65
66Get the inserted row back:
67
68```ocaml
69let m = Multi.(
70 empty
71 |> insert_returning "user"
72 ~table:users_table
73 ~columns:["name"; "email"]
74 ~values:[Driver.Value.text "Alice"; Driver.Value.text "alice@example.com"]
75 ~decode:decode_user
76)
77
78match Multi.execute conn m with
79| Ok results ->
80 let user : user = Multi.get_exn results "user" in
81 Printf.printf "Created user with ID: %d\n" user.id
82| Error { failed_operation; error; _ } ->
83 Printf.printf "Failed at %s: %s\n" failed_operation (Error.show_db_error error)
84```
85
86### Dependent Operations
87
88Use `_fn` variants to access results from previous operations:
89
90```ocaml
91let create_user_with_profile conn ~name ~email ~bio =
92 let m = Multi.(
93 empty
94 |> insert_returning "user"
95 ~table:users_table
96 ~columns:["name"; "email"]
97 ~values:[Driver.Value.text name; Driver.Value.text email]
98 ~decode:decode_user
99 |> insert_fn "profile" ~f:(fun results ->
100 let user : user = Multi.get_exn results "user" in
101 (profiles_table,
102 ["user_id"; "bio"],
103 [Driver.Value.int user.id; Driver.Value.text bio])
104 )
105 )
106 in
107 Multi.execute conn m
108```
109
110### All Operation Types
111
112```ocaml
113let m = Multi.(
114 empty
115 (* Insert *)
116 |> insert "op1"
117 ~table:users_table
118 ~columns:["name"]
119 ~values:[Driver.Value.text "Alice"]
120
121 (* Insert with RETURNING *)
122 |> insert_returning "op2"
123 ~table:users_table
124 ~columns:["name"]
125 ~values:[Driver.Value.text "Bob"]
126 ~decode:decode_user
127
128 (* Insert depending on previous result *)
129 |> insert_fn "op3" ~f:(fun results ->
130 let user : user = Multi.get_exn results "op2" in
131 (posts_table, ["author_id"; "title"],
132 [Driver.Value.int user.id; Driver.Value.text "Hello"])
133 )
134
135 (* Insert with RETURNING depending on previous result *)
136 |> insert_returning_fn "op4" ~f:(fun results ->
137 let user : user = Multi.get_exn results "op2" in
138 (posts_table, ["author_id"; "title"],
139 [Driver.Value.int user.id; Driver.Value.text "World"],
140 decode_post)
141 )
142
143 (* Update *)
144 |> update "op5"
145 ~table:users_table
146 ~columns:["name"]
147 ~values:[Driver.Value.text "Alice Updated"]
148 ~where_column:"id"
149 ~where_value:(Driver.Value.int 1)
150
151 (* Update depending on previous result *)
152 |> update_fn "op6" ~f:(fun results ->
153 let user : user = Multi.get_exn results "op2" in
154 (users_table, ["name"], [Driver.Value.text "Updated"],
155 "id", Driver.Value.int user.id)
156 )
157
158 (* Delete *)
159 |> delete "op7"
160 ~table:posts_table
161 ~where_column:"id"
162 ~where_value:(Driver.Value.int 999)
163
164 (* Delete depending on previous result *)
165 |> delete_fn "op8" ~f:(fun results ->
166 let post : post = Multi.get_exn results "op4" in
167 (posts_table, "id", Driver.Value.int post.id)
168 )
169)
170```
171
172### Custom Operations
173
174For complex logic that doesn't fit the predefined operations:
175
176```ocaml
177let m = Multi.(
178 empty
179 |> run "custom_op" ~f:(fun conn results ->
180 (* Full access to connection and all previous results *)
181 let user : user = Multi.get_exn results "user" in
182
183 (* Run any database operation *)
184 match Repo.all_query conn
185 Query.(from posts_table |> where Expr.(raw "author_id" = int user.id))
186 ~decode:decode_post
187 with
188 | Error e -> Error e
189 | Ok posts -> Ok (Multi.Result posts) (* Return packed result *)
190 )
191
192 (* Operation that doesn't return a value *)
193 |> run_no_result "side_effect" ~f:(fun conn results ->
194 (* Do something without returning a value *)
195 match send_welcome_email (Multi.get_exn results "user") with
196 | true -> Ok ()
197 | false -> Error (Error.Query_failed "Email failed")
198 )
199)
200```
201
202### Merging Multis
203
204Combine multiple Multi transactions:
205
206```ocaml
207let create_user = Multi.(
208 empty
209 |> insert_returning "user" ~table:users_table ~columns:["name"]
210 ~values:[Driver.Value.text "Alice"] ~decode:decode_user
211)
212
213let create_profile = Multi.(
214 empty
215 |> insert_fn "profile" ~f:(fun results ->
216 let user : user = Multi.get_exn results "user" in
217 (profiles_table, ["user_id"], [Driver.Value.int user.id])
218 )
219)
220
221let combined = Multi.merge create_user create_profile
222```
223
224### Inspecting Multis
225
226```ocaml
227let names = Multi.names m (* ["user"; "profile"; ...] *)
228let has_user = Multi.has_name "user" m (* true *)
229
230(* Validate before executing *)
231match Multi.validate_multi m with
232| Ok () -> Multi.execute conn m
233| Error msg -> Printf.printf "Invalid multi: %s\n" msg
234```
235
236### Error Handling
237
238```ocaml
239match Multi.execute conn m with
240| Ok results ->
241 (* Access results by name *)
242 let user : user option = Multi.get results "user" in
243 let post : post = Multi.get_exn results "post" in (* raises if missing *)
244 ...
245
246| Error { failed_operation; error; completed } ->
247 Printf.printf "Failed at operation: %s\n" failed_operation;
248 Printf.printf "Error: %s\n" (Error.show_db_error error);
249 (* completed contains results from operations that succeeded before failure *)
250 let partial_user : user option = Multi.get completed "user" in
251 ...
252```
253
254### Working with Results
255
256```ocaml
257(* Safe get - returns option *)
258let user : user option = Multi.get results "user"
259
260(* Unsafe get - raises if not found *)
261let user : user = Multi.get_exn results "user"
262
263(* Add to results manually (in run functions) *)
264let results = Multi.put "key" value results
265```
266
267## Complete Example
268
269```ocaml
270let create_blog_post conn ~author_id ~title ~body ~tags =
271 let m = Multi.(
272 empty
273 (* Create the post *)
274 |> insert_returning "post"
275 ~table:posts_table
276 ~columns:["author_id"; "title"; "body"]
277 ~values:[
278 Driver.Value.int author_id;
279 Driver.Value.text title;
280 Driver.Value.text body;
281 ]
282 ~decode:decode_post
283
284 (* Create tags and associations *)
285 |> run "tags" ~f:(fun conn results ->
286 let post : post = get_exn results "post" in
287
288 (* Insert each tag and create association *)
289 let insert_tag tag_name =
290 match Repo.insert_returning conn
291 ~table:tags_table
292 ~columns:["name"]
293 ~values:[Driver.Value.text tag_name]
294 ~decode:decode_tag
295 with
296 | Error e -> Error e
297 | Ok tag ->
298 Repo.insert conn
299 ~table:post_tags_table
300 ~columns:["post_id"; "tag_id"]
301 ~values:[Driver.Value.int post.id; Driver.Value.int tag.id]
302 in
303
304 let rec process = function
305 | [] -> Ok (Result tags)
306 | tag :: rest ->
307 match insert_tag tag with
308 | Error e -> Error e
309 | Ok () -> process rest
310 in
311 process tags
312 )
313
314 (* Update author's post count *)
315 |> run_no_result "update_count" ~f:(fun conn results ->
316 Repo.exec conn
317 "UPDATE users SET post_count = post_count + 1 WHERE id = $1"
318 ~params:[Driver.Value.int author_id]
319 )
320 )
321 in
322 match Multi.execute conn m with
323 | Ok results -> Ok (Multi.get_exn results "post")
324 | Error { error; _ } -> Error error
325```
326
327## Next Steps
328
329- [Repo](repo.md) - Basic database operations
330- [Migrations](migrations.md) - Database schema versioning
331- [Queries](queries.md) - Building complex queries