andrenth / sequoia

OCaml type-safe query builder with syntax tree extension
123 stars 16 forks source link

Fix placeholder for postgres. #11

Closed NightBlues closed 5 years ago

NightBlues commented 5 years ago

Hi there! I've tried to use sequoia-postgresql with pgocaml and found that placeholder seems to be wrong. I'm not fluent with sql, but I see that postgres prepare statements do not accept params as ?:

sudo -u postgres psql
[sudo] password for username: 
psql (10.6 (Ubuntu 10.6-0ubuntu0.18.04.1))
Type "help" for help.

postgres=# CREATE TEMPORARY TABLE users (id serial PRIMARY KEY, name VARCHAR(255) NOT NULL);
CREATE TABLE
postgres=# PREPARE my AS SELECT users.id, users.name FROM users WHERE ((users.id) = (?));
ERROR:  syntax error at or near ")"
LINE 1: ...CT users.id, users.name FROM users WHERE ((users.id) = (?));
                                                                    ^
postgres=# PREPARE my AS SELECT users.id, users.name FROM users WHERE ((users.id) = ($1));
PREPARE
postgres=#
NightBlues commented 5 years ago

Also, I wrote some boilerplate code to integrate sequoia with pgocaml. Maybe such code should be placed somewhere in sequoia-postgresql or separate library? For example:

let sequoia_to_pgocaml =
  let open Postgresql.Param in
  function
  | String v -> Some (PGOCaml.string_of_string v)
  | Int v -> Some (PGOCaml.string_of_int v)
  | Bool v -> Some (PGOCaml.string_of_bool v)
  | Float v -> Some (PGOCaml.string_of_float v)
  | _ -> failwith "Not implemented conversion"

let run_sequoia sq dbh =
  let query, params_seq = Postgresql.Select.seal sq in
  let params = List.map sequoia_to_pgocaml params_seq in
  let prepare = PGOCaml.prepare dbh ~query () in
  let rows = PGOCaml.bind prepare (fun () ->
      PGOCaml.execute dbh ~params ()) in
  rows
andrenth commented 5 years ago

Hi

Thanks for the fix. I like the integration idea, but I wonder if it would be better to integrate with Caqti which also has support for Mariadb and Sqlite.