Ppx_rapper and database connection

Hello,

I’m a beginner at using SQL with OCaml, for now I’ve settled on using both cacti and ppx_rapper, on top of sqlite and sometimes postgresql.

I’dont get how do you tell ppx_rapper about the connection to the database, using plain cacti is fine:

But using ppx_rapper, I don’t get it at all! :-/

Thanks!

The connection is the last argument e.g.

let my_query =
  [%rapper
    get_opt
      {sql|
      SELECT ....
      |sql}]

my_query ~id:123 conn
2 Likes

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?

TIA!

I’m using Caqti and rapper using Dream, Dreams handles the connection failure for me, but from looking at the error I see that you get a result Lwt.t.

I think this might be something like:

let%lwt conn_result =
   Caqti_lwt.connect (Uri.of_string "sqlite3://gallery_strauss.db")
in
conn_result
|> Result.map (fun conn -> 
  my_query ~id:123 conn
)
1 Like

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
1 Like

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 ()

TIA!

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.

1 Like

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.

1 Like