Many Thanks, Sebastian!
But I face another beginner problem: how do you create the connection in the first place when using rapper, using the cacti way:
let conn = Caqti_lwt.connect (Uri.of_string "sqlite3://gallery_strauss.db")
ends up with a typing error:
Error: This expression has type
(Caqti_lwt.connection, [> Caqti_error.load_or_connect ]) result
Lwt.t
but an expression was expected of type
(module Rapper_helper.CONNECTION)
Is their a Rapper_helper.connect or some way of creating the db handler with rapper?
A query created by ppx_rapper can be used in the same way as one constructed with Caqti manually. I would get connections with a pool like this:
let pool =
match Caqti_lwt.connect_pool (Uri.of_string uri) with
| Ok pool -> pool
| Error _ -> failwith "Error creating DB connection pool"
let execute query = Caqti_lwt.Pool.use query pool
let show =
[%rapper
get_opt
{sql|
SELECT @int{id}, @string{title}, @bool{completed}
FROM todos
WHERE id = %int{id}
|sql}
record_out]
let _ = execute @@ show ~id:123
I’ve got this building fine, but my output is blank on the console.
I’ve uploaded the sqlite database file on this link, and verified the SQL part is working fine with the sqlite console.
let connection_url = "sqlite3:///Users/philippe/mysrc/Web/florapix.dream/gallery_strauss.db"
let pool =
match Caqti_lwt.connect_pool (Uri.of_string connection_url) with
| Ok pool -> pool
| Error _ -> failwith "Error creating DB connection pool"
let execute query = Caqti_lwt.Pool.use query pool
(*
SELECT album_name, basename, size, width, height
FROM gallery, album, photo, picture
WHERE gallery.gallery_name = "Murielle Strauss"
AND gallery.rowid = album.id_gallery AND album.rowid = photo.id_album AND photo.rowid = picture.id_photo;
*)
let show_tabular =
[%rapper
get_many {sql|
SELECT @string{album_name}, @string{basename}, @int{size}, @int{width}, @int{height}
FROM gallery, album, photo, picture
WHERE gallery.gallery_name = "Murielle Strauss"
AND gallery.rowid = album.id_gallery AND album.rowid = photo.id_album AND photo.rowid = picture.id_photo |sql}
syntax_off function_out
] (fun ~height ~width ~size ~basename ~album_name ->
Printf.printf "%s ; %s ; %d ; %d ; %d\n%!" album_name basename size width height)
let _ = execute @@ show_tabular ()
The last line of my code was just an example of how to execute the query, but in real code you would want to use the result in the _ variable. In your case you could do something like this:
let () = Lwt_main.run (execute @@ show_tabular ())
but it would probably be better to change your query to return some data by removing the function_out and then print it later. See the lwt manual for more info about this.
I did what you describe and my script is beginning to work fine.
Thanks you both for the help, I was trying to get this first step running without reading the very nice intro to Lwt, definitively not the right approach. For the SQL request showed in my source code above, I’ll need Rapper.load_many I guess.
I am encountering a typing error with Dream/PostgreSQL/ppx_rapper. I have recently started programming in OCaml, and I think I understand the origin of the error, but I’m unable to resolve it.
Here is the source code:
module type DB = Caqti_lwt.CONNECTION
module T = Caqti_type
(1) ----- Does not work (ppx_rapper version)
let list_buyers =
[%rapper
get_many
{sql|
SELECT @string{first_name}, @string{last_name}
FROM buyer
|sql}]
(* (2) ----- Works (Caqti version) *)
(*
let list_buyers =
let query =
let open Caqti_request.Infix in
(T.unit ->* T.(tup2 string string))
"SELECT first_name, last_name FROM buyer" in
fun (module Db : DB) ->
let%lwt buyers_or_error = Db.collect_list query () in
Caqti_lwt.or_fail buyers_or_error *)
let render buyers request =
<html>
<body>
% buyers |> List.iter (fun (_id, buyer) ->
<p><%s buyer %></p><% ); %>
<form method="POST" action="/">
<%s! Dream.csrf_tag request %>
<input name="text" autofocus>
</form>
</body>
</html>
let () =
Dream.run
@@ Dream.logger
@@ Dream.sql_pool "postgresql://mentor:superMentor@localhost/dream_market"
@@ Dream.sql_sessions
@@ Dream.router [
Dream.get "/" (fun request ->
let%lwt buyers = Dream.sql request list_buyers in
Dream.html (render buyers request));
]
And the associated error message:
File "bin/main.eml.ml", line 44, characters 41-52:
Error: This expression has type
unit ->
(module Rapper_helper.CONNECTION) ->
((string * string) list, [> Caqti_error.call_or_retrieve ]) result
Lwt.t
but an expression was expected of type
Caqti_lwt.connection -> 'a Lwt.t
Type unit is not compatible with type
Caqti_lwt.connection = (module Caqti_lwt.CONNECTION)
Hint: Did you forget to provide `()' as argument?