# What could hinder sql query statement from being invoked?

**URL:** <https://discuss.ocaml.org/t/what-could-hinder-sql-query-statement-from-being-invoked/12905>\
**Category:** Learning\
**Tags:** caqti\
**Created:** [August 25, 2023, 9:00am UTC](https://discuss.ocaml.org/t/what-could-hinder-sql-query-statement-from-being-invoked/12905 "2023-08-25T09:00:59Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![lubegasimon](https://sea2.discourse-cdn.com/flex020/user_avatar/discuss.ocaml.org/lubegasimon/32/2185_2.png) [@lubegasimon](https://discuss.ocaml.org/u/lubegasimon)\
**Post date:** [August 25, 2023, 9:00am UTC](https://discuss.ocaml.org/t/what-could-hinder-sql-query-statement-from-being-invoked/12905/1 "2023-08-25T09:00:59Z")

</div>

I am trying to get execute sql query statement but seems not to be a success. What could I be doing wrong?  
I have the following modules and the main is executed via `dune exec ...main.exe`

```auto
(* ./drop_tbl.ml *)

open Caqti_request.Infix
module T = Caqti_type

let drop_table (module Db : Caqti_lwt.CONNECTION) =
  let query = (T.unit -->. T.unit) @:- Printf.sprintf "DROP DATABASE mydb" in
  let%lwt unit_or_error = Db.exec query () in
  Caqti_lwt.or_fail unit_or_error

```

```auto
(*./main.ml *)
(* FIXME: use environment variable. *)
let database_url = "postgresql://localhost:<default port>/postgres"

let db =
  (module (val let connect = Caqti_lwt.connect (Uri.of_string database_url) in
               Lwt_main.run (Lwt.bind connect Caqti_lwt.or_fail))
  : Caqti_lwt.CONNECTION)

let () =
  let drop_tbl = Lwt.return_ok (drop_tbl.drop_table db) in
  match Lwt_main.run drop_tbl with
  | Ok _ -> Printf.printf "Successful set up"
  | Error e -> Printf.printf "Error: %s" (Caqti_error.show e)

```

```auto
; dune file

(executable
 (name main)
 (public_name main)
 (libraries
  caqti-driver-postgresql
  caqti
  caqti-lwt)
 (preprocess
  (pps lwt_ppx)))

```

Edited: Changed module name and function names (create → drop) to reflect the query statement (which I changed after their creation at the time)

---

<div class="post-metadata">

**Author:** ![benjamin-thomas](https://sea2.discourse-cdn.com/flex020/user_avatar/discuss.ocaml.org/benjamin-thomas/32/3966_2.png) [@benjamin-thomas](https://discuss.ocaml.org/u/benjamin-thomas)\
**Post date:** [August 26, 2023, 8:04am UTC](https://discuss.ocaml.org/t/what-could-hinder-sql-query-statement-from-being-invoked/12905/2 "2023-08-26T08:04:13Z")

</div>

You kinda double wrapped a promise, which looks wrong to me.

I think you can simplify your library code that way:

```auto
(* ./drop_tbl.ml *)

open Caqti_request.Infix
module T = Caqti_type

let drop_table (module Db : Caqti_lwt.CONNECTION) =
  let query = (T.unit -->. T.unit) @:- Printf.sprintf "DROP DATABASE mydb" in
  Db.exec query

```

And let the binary execute the definitions of your queries (my terminology may be wrong here):

```auto
(*./main.ml *)

let get_uri () =
  let env_vars =
    let ( let* ) = Option.bind in
    let* pg_host = Sys.getenv_opt "PGHOST" in
    let* pg_port = Sys.getenv_opt "PGPORT" in
    let* pg_database = Sys.getenv_opt "PGDATABASE" in
    Some (pg_host, pg_port, pg_database)
  in
  match env_vars with
  | Some (pg_host, pg_port, pg_database) ->
      Printf.sprintf "postgresql://%s:%s/%s" pg_host pg_port pg_database
  | None -> "postgresql://" (* use system defaults *)

let db =
  (module (val let connect = Caqti_lwt.connect (Uri.of_string (get_uri ())) in
               Lwt_main.run (Lwt.bind connect Caqti_lwt.or_fail))
  : Caqti_lwt.CONNECTION)

let () =
  let promise = Lib.drop_table db () in
  match Lwt_main.run promise with
  | Ok () -> Printf.printf "I've dropped the table!"
  | Error e -> Printf.printf "Got an error: %s" (Caqti_error.show e)

```

What helped me studying Caqti is to pay particular attention to the types.

As a way of contributing, I’ve been working on a documentation project here: [GitHub - benjamin-thomas/caqti-study at v1](https://github.com/benjamin-thomas/caqti-study/tree/v1)

It’ still a WIP, but in the meantime, maybe browsing through it will give you a few ideas on how to get going.

---

<div class="post-metadata">

**Author:** ![paurkedal](https://sea2.discourse-cdn.com/flex020/user_avatar/discuss.ocaml.org/paurkedal/32/586_2.png) [@paurkedal](https://discuss.ocaml.org/u/paurkedal)\
**Post date:** [August 26, 2023, 10:58am UTC](https://discuss.ocaml.org/t/what-could-hinder-sql-query-statement-from-being-invoked/12905/3 "2023-08-26T10:58:03Z")

</div>

When you match on `Ok _` you discard the premise holding the execution of the SQL statement (so, a double promise, as noted). The promise is scheduled anyway, but `Lwt_main.run drup_table` exits immediately since `drop_table` is a resolved-by-construction promise, which terminates the scheduler before reaching the real task. (At least that’s may understanding of Lwt.)

---

<div class="post-metadata">

**Author:** ![lubegasimon](https://sea2.discourse-cdn.com/flex020/user_avatar/discuss.ocaml.org/lubegasimon/32/2185_2.png) [@lubegasimon](https://discuss.ocaml.org/u/lubegasimon)\
**Post date:** [August 27, 2023, 3:12pm UTC](https://discuss.ocaml.org/t/what-could-hinder-sql-query-statement-from-being-invoked/12905/4 "2023-08-27T15:12:37Z")

</div>

Thank you @benjamin-thomas and @paurkedal.  
I was able to follow up closely on project @benjamin-thomas linked, and I got my tiny program running well. I also agree that Caqti and Lwt require some great attention to the types for a fair grasp.