paurkedal / ocaml-caqti Goto Github PK

I had an issue when I ran into assert_single_use by using Lwt.all ... to run a list of queries on a connection where it just fails without error message. Would you accept a PR where I change that assert to throw an exception containing an error message that gives a hint, that it is not allowed to run multiple queries at the some time on one connection?

Would it even make sense maybe to get rid of all asserts and return Caqti_error.t?

I've recently ran into some surprising behavior while using CockroachDB and populate.

The symptom I observed was string columns ending up in the database with quotes around them, e.g. "\"foo\"" instead of "foo".

The cause of this behavior is the escaping implementation in the Postgres driver (CockroachDB uses the PG wire protocol).

While this is certainly a limitation in CockroachDB (it doesn't support the full range of options for COPY, and the same populate worked as expected against a Postgres database), I wanted to open this issue to ask if the escaping behavior is necessary when using populate calls directly. In a local fork I tried removing them and the populate operation worked as expected.

Wondering what edge cases I missed.

Say there's a query that's taking too much time, and I want to kill it so that I can reuse that connection for other queries. Is it possible?

I thought naively that Lwt.cancel could do the job but it doesn't work and I end up with Invalid concurrent usage of PostgreSQL connection detected.

Looks like --dev-pkg might be the culprit:

$ opam install caqti
The following actions will be performed:
  ∗  install   calendar 2.03.2                [required by caqti]
  ∗  install   caqti    0.5.3
  ↻  recompile episql   0.8.8*                [uses caqti]
===== ∗  2   ↻  1 =====
Do you want to continue ? [Y/n] y

=-=- Gathering sources =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=  🐫
[calendar] Archive in cache
[episql] [email protected]:paurkedal/episql.git#fa7df7e2562e31487c2a13384e3db11492315ccc already up-to-date
[caqti.0.5.3] https://github.com/paurkedal/ocaml-caqti/releases/download/v0.5.3/caqti-0.5.3.tbz downloaded

=-=- Processing actions -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=  🐫
⊘  removed   episql.0.8.8
∗  installed calendar.2.03.2
[ERROR] The compilation of caqti failed at "ocaml pkg/pkg.ml build --dev-pkg false --with-async
        false --with-lwt true --with-mariadb false --with-postgresql true --with-sqlite3 false".

#=== ERROR while installing caqti.0.5.3 =======================================#
# opam-version 1.2.2
# os           darwin
# command      ocaml pkg/pkg.ml build --dev-pkg false --with-async false --with-lwt true --with-mariadb false --with-postgresql true --with-sqlite3 false
# path         /Users/s/.opam/graphql2/build/caqti.0.5.3
# compiler     4.03.0
# exit-code    1
# env-file     /Users/s/.opam/graphql2/build/caqti.0.5.3/caqti-2548-0a5907.env
# stdout-file  /Users/s/.opam/graphql2/build/caqti.0.5.3/caqti-2548-0a5907.out
# stderr-file  /Users/s/.opam/graphql2/build/caqti.0.5.3/caqti-2548-0a5907.err
### stderr ###
# pkg.ml: [ERROR] key --dev-pkg: Unknown key.



=-=- Error report -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=  🐫
The following actions were aborted
  ∗  install episql 0.8.8
The following actions failed
  ∗  install caqti 0.5.3
The following changes have been performed
  ⊘  remove  episql   0.8.8
  ∗  install calendar 2.03.2

The former state can be restored with:
    opam switch import "~/.opam/graphql2/backup/state-20170429053353.export"
Seans-MacBook-Pro-7:examples s$ cat /Users/s/.opam/graphql2/build/caqti.0.5.3/caqti-2548-0a5907.err
pkg.ml: [ERROR] key --dev-pkg: Unknown key.

There are currently two MySQL client libraries for OCaml which I know about, mysql and mysql_protocol. However, non of them are non-blocking. MariaDB has a nice non-blocking API, but no OCaml bindings as far as I know.

This is not an issue, more a feature request.
I'm using Cacti in 2/3 production projects and in that context I'm also using Async to handle concurrency and everything runs smoothly.

Now I have a simple system program (it could actually be written in BASH but... why should I shot myself in a foot?) interacting with PostgreSQL. In that context I don't need Lwt/Async because it's a very simple and sequential program making a couple of SELECT, some computations and finally some UPDATEs.

Is it possible to use Caqti in such environment? I don't need pooling or any other advanced feature: the old plain Unix module is more than enough.

Hi, I'd be interested in using an "offline" backend. (I don't know if there's already a name for that.)

It would simply consist in printing all the SQL commands instead of passing them to the DB engine.

Please could you tell me if there's already a way to do it, or where to look if I were to implement it?

The README mentions supports for transactions. I perused the interface but did not see any function related to transactions. Is it as simple as framing multiple exec with exec "BEGIN" and exec "COMMIT" ?

I want to use PostgreSQL's json data types, which is not supported by default by Caqti. Issue #16 suggest sending strings, however this doesn't work for me (probably because of more recent PostgreSQL versions):

ERROR: column "json" is of type json but expression is of type string

PostgreSQL wants us to explicitely cast the string, ie. ... VALUES ('{ "hello": "world"}'::json). I've been trying to define a custom Caqti_type.field to handle this, but it seems any custom field has to be based on an existing field which:

• Makes it impossible to encode something as '...'::json since I can't output raw strings.
• Doesn't make much sense to me, as if all custom fields are defined upon existing supported fields, then they are necessarily supported by all drivers contrary to what is stated in the documentation. On top of that this can be achieved much more cleanly with Caqti_type.custom, making it seems to me field extension completely redundant and useless.

I would imagine the way to implement custom field would be to provide the actual, raw representation for the value for the driver.

Did I miss something here ? Otherwise, WDYT about the approach I suggest ? Happy to submit a PR.

Std defines up to tup4. What is the best approach to define longer tuples? Using custom?

The referenced value Q._q does not exist anywhere.

When running with log level Debug, Caqti writes entire queries and their parameters into the log. This can be a security issue, as some of the parameters can be secrets. See aantron/dream#44, where Caqti is shown logging session ids.

Is there a good way to address this, besides not setting Caqti's log level to Debug? I suspect the answer is no, but I wanted to ping here for discussion/ideas.

ppx_deriving is currently blocking me from compiling a project statically.

Is it used for something other than deriving.eq?

ocaml-ppx/ppx_deriving#212

I hope you don't mind me creating another issue...

─( 19:34:13 )─< command 3 >────────────────────────────────────────────────{ counter: 0 }─
utop # Caqti_lwt.connect (Uri.of_string "postgresql://localhost:5432");;
Exception:
Invalid_argument
 "The dynlink.cma library cannot be used inside the OCaml toplevel".
Raised at file "pervasives.ml", line 33, characters 25-45
Called from file "otherlibs/dynlink/dynlink.ml", line 148, characters 4-97
Called from file "otherlibs/dynlink/dynlink.ml", line 261, characters 2-8
Called from file "list.ml", line 85, characters 12-15
Called from file "fl_dynload.ml", line 37, characters 5-227
Called from file "list.ml", line 85, characters 12-15
Called from file "lib-dynload/caqti_dynload.ml", line 65, characters 10-49
Called from file "lib/caqti_connect.ml", line 31, characters 13-56
Called from file "lib/caqti_connect.ml", line 57, characters 19-50
Called from file "lib/caqti_connect.ml", line 104, characters 11-26
Called from file "//toplevel//", line 1, characters 0-63
Called from file "toplevel/toploop.ml", line 180, characters 17-56

It seems to be related to the dynamic linking. Do you perhaps know how to use it via utop? Here's my current jbuild file:

(jbuild_version 1)

(library
  ((name app)
   (wrapped false)
   (libraries
     (opium lwt lwt.unix
      caqti caqti-dynload caqti-lwt))
   (preprocess (pps (lwt.ppx)))))

Further to #24, I am now trying to work out the best way to insert large amounts of data into a postgres database quickly. Based on the documentation here, the best way to do this is to make use of the COPY command to stream in the data.

I have run some experiments, and the use of COPY speeds up data insertion by roughly 10x in the case of my data, so I definitely want to make use of it (although I guess it is not always this much of a speed up).

Unfortunately, COPY is a postgres-specific extension (which is supported by ocaml-postgres but unused by caqti). It seems there is an equivalent for mariadb, but I haven't been able to find a convincing equivalent for sqlite.

This leads to a couple of questions:

• Do you think this is the sort of thing that caqti should be supporting, or should it live in application code?
• If so what do you think the best way to support it would be?

My opinion is that this would be a nice addition to caqti. I think that a good equivalent here is transaction support, which seems to trigger different connection code based on the can_transact parameter in the driver_info for a given driver - maybe a can_copy parameter would allow the drivers to declare copy methods, and would raise an error if you tried to use them with a driver that did not support it? This would be combined with some new helper functions for CONNECTION objects.

I am happy to make a PR with and attempt at changes if you think this is worth pursuing.

Encountered by @vog in ocsigen/lwt#714 (comment) when building outside opam (i.e. non-release profile):

File "ocaml-caqti/lib/caqti_pool.ml", line 28, characters 56-74:
28 |     let compare {priority = pA; _} {priority = pB; _} = Pervasives.compare pB pA
                                                             ^^^^^^^^^^^^^^^^^^
Error (alert deprecated): module Stdlib.Pervasives
Use Stdlib instead.

If you need to stay compatible with OCaml < 4.07, you can use the 
stdlib-shims library: https://github.com/ocaml/stdlib-shims
File "ocaml-caqti/lib/caqti_type.ml", line 47, characters 13-38:
47 |     let ec = Obj.extension_constructor ft in
                  ^^^^^^^^^^^^^^^^^^^^^^^^^
Error (alert deprecated): Stdlib.Obj.extension_constructor
use Obj.Extension_constructor.of_val
File "ocaml-caqti/lib/caqti_type.ml", line 51, characters 13-38:
51 |     let ec = Obj.extension_constructor ft in
                  ^^^^^^^^^^^^^^^^^^^^^^^^^
Error (alert deprecated): Stdlib.Obj.extension_constructor
use Obj.Extension_constructor.of_val
File "ocaml-caqti/lib/caqti_type.ml", line 66, characters 11-29:
66 |    | ft -> Obj.extension_name (Obj.extension_constructor ft)
                ^^^^^^^^^^^^^^^^^^
Error (alert deprecated): Stdlib.Obj.extension_name
use Obj.Extension_constructor.name
File "ocaml-caqti/lib/caqti_type.ml", line 66, characters 31-56:
66 |    | ft -> Obj.extension_name (Obj.extension_constructor ft)
                                    ^^^^^^^^^^^^^^^^^^^^^^^^^
Error (alert deprecated): Stdlib.Obj.extension_constructor
use Obj.Extension_constructor.of_val
File "ocaml-caqti/lib/caqti_error.ml", line 26, characters 10-35:
26 |   let c = Obj.extension_constructor msg in
               ^^^^^^^^^^^^^^^^^^^^^^^^^
Error (alert deprecated): Stdlib.Obj.extension_constructor
use Obj.Extension_constructor.of_val
File "ocaml-caqti/lib/caqti_error.ml", line 33, characters 7-25:
33 |       (Obj.extension_name c)
            ^^^^^^^^^^^^^^^^^^
Error (alert deprecated): Stdlib.Obj.extension_name
use Obj.Extension_constructor.name

The module should be Caqti_async implementing

Caqti_sigs.CONNECT with type 'a io = 'a Deferred.t

Also split the caqti library into caqti, caqti.async, and caqti.lwt and compile the latter two conditionally.

Hi there! I am a big fan of the project and I was using it happily until recently.

My database currently contains tables with a custom "enum" variable which I was able to manipulate using Caqti_type.string values until the latest update. Below is a toy example to illustrate my situation:

Database:

CREATE TYPE color_t AS ENUM ('RED', 'GREEN', 'BLUE');

CREATE TABLE items (
  id           SERIAL PRIMARY KEY,
  name         TEXT NOT NULL,
  color        color_t NOT NULL
);

Code

module Item = {
  type t = {
    id: Int32.t,
    name: string,
    color: [`RED | `GREEN | `BLUE]
  };

  let color_of_string = (fun | "RED" => Some(`RED) | "GREEN" => Some(`GREEN) | "BLUE" => Some(`BLUE) | _ => None);
  let string_of_color = (fun | `RED => "RED" | `GREEN => "GREEN" | `BLUE => "BLUE");

  let db_t = {
    let rep = Caqti_type.(tup3(int32, string, string));
    let encode = ({id, name, color}) => Ok((id, name, string_of_color(color)));
    let decode = ((id, name, color)) => {
      color_of_string(color)
      |> (fun | Some(color) => Ok({id, name, color}) | None => Error(Format.sprintf("Unknown color %s", color)))
    };
    Caqti_type.custom(~encode, ~decode, rep)
  };

  let add = ((module C: Caqti_lwt.CONNECTION), name, color) => {
    let qs = "INSERT INTO items(name, color) VALUES (?, ?) RETURNING *";
    let query = Caqti_request.find(Caqti_type.(tup2(string, string)), db_t, qs);
    C.find(query, (name, string_of_color(color)));
  };

  let get = ((module C: Caqti_lwt.CONNECTION), id) => {
    let qs = "SELECT * FROM items WHERE id = ?";
    let query = Caqti_request.find(Caqti_type.int32, db_t, qs);
    C.find_opt(query, id);
  };
};

Before the latest update, the above code was able to add and get items from my table without any problem, however, since recently, the add function returns the following error:

id: 1, name: potato, color: GREEN
[ERROR] Request to <postgresql://testuser:_@localhost/caqtitest> failed: ERROR:  column "color" is of type color_t but expression is of type text
LINE 1: INSERT INTO items(name, color) VALUES ($1, $2) RETURNING *
                                                   ^
HINT:  You will need to rewrite or cast the expression.
. Query: "INSERT INTO items(name, color) VALUES ($1, $2) RETURNING *".

(Note: the get function still works fine).

It is unclear to me how to fix this error since everything was working fine with Caqti version 1.3.0. I am using PostgreSQL version 13.2 and Caqti version 1.4.0 on Arch Linux. Downgrading to 1.3.0 solves the issue but I like to keep my packages up to date. Any help appreciated.

Thanks for the help and the awesome library!

I'm getting

Exception: Assert_failure ("lib/caqti_connection.ml", 26, 4).
Raised at file "lib/caqti_connection.ml", line 26, characters 4-24

when I try to execute lots of queries using a pool. Is this expected?

My code is something like this:

let do_many dbh =
    let results = List.map ~f:(fun x -> do_some_query x dbh) xs in
   sequence results

where sequence is

let sequence xs =
  let open Lwt_result.Infix in
  List.fold ~init:(return [])
    ~f:(fun accum x ->
      accum >>= fun accum ->
      x >|= fun x -> x :: accum)
    xs

And then I do Caqti_lwt.Pool.use do_many a_pool.

This is using Postgres.

When the find_opt is used with a SELECT ... WHERE .. that returns more than one value, the Sqlite3 backend seems to return the first value it finds, while the postgresql backend fails with

        Database-error: Unexpected result from
        <postgresql://postgres:_@localhost:5432/postgres>: Received 2 tuples,

(I do prefer the PostgreSQL behavior :) which is the "production" backend in my use-case)

I installed caqti and its dependencies using the additional opam repo https://github.com/paurkedal/opam-repo-paurkedal.git. After compiling examples/bikereg.ml and running it, it fails with:

Fatal error: exception Caqti_plugin.Plugin_invalid("caqti.caqtus-sqlite3", "Failed to link plugin: error loading shared library: /home/armael/.opam/4.04.0/lib/caqti/caqtus-sqlite3.cmxa: invalid ELF header")

Am I doing something wrong?

P.S: episql and caqti look very very nice. Thank you for these!

Hi, thanks for this library! I've been playing with it and I'm loving the types. I managed to get basic queries running. However it would seem to me that Caqti_lwt.CONNECTION does not directly provide a way to return a list of results? Here's what I have so far:

module Q = struct
  let get_all_todos =
    Caqti_request.collect
      Caqti_type.unit
      Caqti_type.(tup2 int string)
      "SELECT id, content FROM todos"
end

let get_all_internal (module Db : Caqti_lwt.CONNECTION) =
  Db.fold Q.get_all_todos (fun (id, content) acc ->
    { id = id; content = content } :: acc
  ) () []

See that in get_all_internal I use Db.fold to construct a list of todos. However, the way I append it means that the final result I get is reversed. Of course I can reverse it back before returning it to the caller, but I'm curious if there's a better way to do this.

Perhaps there should be a Db.collect function or am I missing something?

Repo reproducing the issue.

I'm trying to get caqti to work in an Alpine Docker container. Everything works, except I've run into this error:

Failed: Request to mariadb://root:@db/goomba failed: Error 1264, Query: "INSERT INTO test_sql (i, s) VALUES (?, ?)".
Fatal error: exception Request to mariadb://root:@db/goomba failed: Error 1264, Query: "INSERT INTO test_sql (i, s) VALUES (?, ?)".

The values passed are (1, "one"). See bin/main.ml.

The related Dockerfile:

### Build esy ###
# Adapted from https://github.com/andreypopp/esy-docker/blob/eae0eb686c15576cdf2c9d05309a2585b0a3e95e/esy-docker.mk

# start from node image so we can install esy from npm

FROM node:10.15-alpine as esy-build

ENV TERM=dumb \
  LD_LIBRARY_PATH=/usr/local/lib:/usr/lib:/lib

RUN mkdir /esy
WORKDIR /esy

ENV NPM_CONFIG_PREFIX=/esy
RUN npm install -g --unsafe-perm [email protected]

# now that we have esy installed we need a proper runtime

FROM alpine:3.9 as esy-bin

ENV TERM=dumb \
  LD_LIBRARY_PATH=/usr/local/lib:/usr/lib:/lib

WORKDIR /

COPY --from=esy-build /esy /esy

RUN apk add --no-cache \
  ca-certificates wget \
  bash curl perl-utils \
  git patch gcc g++ musl-dev make m4

RUN wget -q -O /etc/apk/keys/sgerrand.rsa.pub https://alpine-pkgs.sgerrand.com/sgerrand.rsa.pub
RUN wget https://github.com/sgerrand/alpine-pkg-glibc/releases/download/2.28-r0/glibc-2.28-r0.apk
RUN apk add --no-cache glibc-2.28-r0.apk

ENV PATH=/esy/bin:$PATH


### Development environment ###
FROM esy-bin as development

# Niceties
RUN apk add --no-cache fish vim

# Needed for @esy-ocaml/[email protected]
RUN apk add --no-cache texinfo
# Needed for @opam/tls
RUN apk add --no-cache gmp-dev
# Needed for @opam/caqti-driver-mariadb
RUN apk add --no-cache mariadb-dev

RUN mkdir /app
WORKDIR /app

ENTRYPOINT ["tail", "-f", "/dev/null"]

Just creating this to document a work-in progress driver using the FreeTDS module. Unfortunately FreeTDS doesn't seem to have an async interface so we'll have to just run everything in background threads.

Also, FreeTDS requires that everything run in the same background thread, which I'm not sure the current Preemptive module does?

WIP here: https://github.com/brendanlong/ocaml-caqti/commits/mssql

Sqlite supports a notion of "application-defined SQL functions", namely, functions that can be used in SQL expressions but which are implemented by the user of the database driver (in this case, in OCaml). sqlite3-ocaml supports the necessary bindings already.

I understand that this probably wouldn't apply to the other database drivers, and it seems like quite a substantial task to make it work with the rest of the API, but I thought it might be reasonable to open this as a tracking issue. Please close if you disagree.

I'm not sure what to make of this - it compiles, but looks like when loading the plugin because of the uri it fails with this message. I have postgres installed and working just fine from jdbc, so I'm not sure which shared lib it's trying to find.

./server.native
Fatal error: exception Caqti_plugin.Plugin_invalid("caqti.caqtus-postgresql", "Failed to link plugin: error loading shared library: dlopen(<dir>/lib/caqti/caqtus-postgresql.cmxa, 138): no suitable image found.  Did find:\n\t<dir>/lib/caqti/caqtus-postgresql.cmxa: file too short")

This can be observed in the following program, which acquires a connection from the pool (I beleive, of size 1 for SQLite), and then immediately rejects an Lwt promise. Once that is done, it tries to acquire a connection again, but never proceeds to do so.

let run_query pool =
  pool
  |> Caqti_lwt.Pool.use (fun _db ->
    prerr_endline "acquired a connection";
    Lwt.fail Exit)

let () =
  Lwt_main.run begin
    let uri = Uri.of_string "sqlite3:db.sqlite" in
    let pool = Caqti_lwt.connect_pool uri |> Result.get_ok in

    Lwt.on_termination
      (run_query pool)
      (fun () -> run_query pool |> ignore);

    Lwt_unix.sleep 1.
  end

The program prints "acquired a connection" only once, but it should print twice.

Changing Lwt.fail Exit to raise Exit or Lwt.return (Ok ()) causes it to print "acquired a connection" twice.

This is probably because

handles nested result cases and immediate exceptions, but not promise rejections. It probably can't readily handle promise rejections, because it is defined for a generic promise monad that is assumed not to reject.

A correct Lwt version of use would probably use Lwt.finalize to handle all cases in a concise manner. Perhaps a finally should be added to Caqti_sql_io.MONAD?

We've been seeing prepared statement errors after connection drops, e.g.

ERROR: prepared statement "_caq3" does not exist

Reading through the code it looks like what happens is:

• the client enters the call function, the prepared statement ID is found in the pcache
• call calls query_prepared with the prepared statement ID
• query_prepared calls db#send_query_prepared, which throws up a connection failure error, caught in retry.
• retry calls reset to reset the prepared statement cache, and then retries db#send_query_perpared but still uses the previously cached prepared statement ID

Is this by design and something that should be handled by users of caqti-postgres, or should the retry by moved up to a higher level so that the prepared statements are re-sent?

Firstly, thanks for a great package, caqti has proved to be super-useful for handling database interactions in ocaml.

I'm currently trying to work out how to efficiently extract large amounts of data (multiple GB) from a postgresql database, which I then want to fold over using lwt. Unfortunately, naively using a single SELECT query and fold_s does not work, as caqti loads all of the data in memory, and causes my machine to run out of RAM).

I have had a look through the postgresql documentation, and it seems like there are two ways to do this (if you can think of others I would be happy to hear them):

• Break the query into multiple smaller queries using OFFSET /LIMIT batching, potentially with an extra query at the start to count the rows that will be returned
• Making use of postgresql's single row mode in order to stream through the results. This is supported by the ocaml postgres package, but isn't currently used by caqti as far as I can tell

I could potentially implement the OFFSET/LIMIT behaviour in my application, but that would mean that I could no-longer make use of the fold_s helper function, which is very useful IMO. Similarly, it probably does not make sense for caqti to implement this sort of batching, as the API does not provide enough detail about the SQL statements being executed to make sure that adding OFFSET and LIMIT parameters would not conflict.

Do you think it would be worth adding the single row mode behaviour to caqti when using the postgres driver - either by default or (more likely) as an option?

I would be happy to put in a PR to add this function.

First I'd like to thank you for releasing Caqti as it's proven to be a great library so far and saved me quite a lot of time and effort!

Currently it seems the only way to consume a response without allocating it all is to use fold, fold_s or iter_s.

While the current API seems to be enough for simple operations it might be a bit limited as your response handling logic complexifies.

I mostly use Lwt and am not too familiar with Async but they both have streams that come with a whole bunch of helper functions to filter, map and iterate through them. Being able to get a row stream from a response would definitely make things easier.

Would you consider adding such functions to the API?

Exposing functions that directly return such streams might not be ideal as it could turn out to be quite error prone with regards to deadlocks and other connection issues. In that case I guess exposing some sort of wrapper in CONNECTION modules such as:

val with_response_stream :
  ('a, 'b, [< `Zero | `One | `Many ]) Caqti_request.t ->
  ('b stream -> ('c, 'e) result future) ->
  'a ->
  ('c, [> Caqti_error.call_or_retrieve ] as e) result future

So that users could still benefit from the stream helpers while not being able to mess things up too much.

From a quick look at the code it seems like this should be fairly easy to implement although it requires some changes on Caqti_driver_sig.System_unix and a bit of plumbing to make it work with both Lwt and Async.

I'm happy to provide a PR if you feel like this would be a worthy addition to caqti. Please let me know what you think!

I try to use PostgreSQL Driver with PostgreSQL 12.1
There is no problem to install the libraries.
But when i execute dune utop, appear following error.

[koji:hello_opium]$ dune utop mylib
_build/default/mylib/.utop/utop.exe: symbol lookup error: _build/default/mylib/.utop/utop.exe: undefined symbol: PQresultMemorySize

When it use PostgreSQL 11.6, there is no problem.
Does not yet this library support PostgreSQL 12?

My denends entry on opam file is following.

depends: [
    "dune" {build}
    "alcotest"
    "merlin"
    "ocp-indent"
    "utop"
    "ssl"
    "lwt_ssl"
    "cohttp"
    "lwt"
    "lwt_ppx"
    "caqti"
    "caqti-lwt"
    "caqti-driver-postgresql"
    "js_of_ocaml"
    "jwto"
    "mtime"
    "opium" {= "0.17.1"}
]

I'm not sure if this is something you want to implement, but supporting pg_notify would be really nice.

An example of how it's implemented in postgresql-ocaml can be found here: https://github.com/mmottl/postgresql-ocaml/blob/5ed13a60d804953f1f3356271ffc20d347134c9a/examples/prompt.ml#L51

Caqti exhibits the default behavior for handling PostgreSQL notices (see https://www.postgresql.org/docs/current/libpq-notice-processing.html).

That is, all PostgreSQL notices are printed to stderr when using Caqti, which is annoying.

• Is it possible to tell Caqti to provide a difference notice processing function to PostgreSQL?

• Alternatively, is it possible to suppress all notices, warnings etc. of PostgreSQL in Caqti?

(Note: This is a copy of https://stackoverflow.com/q/60279183/19163, as I don't know which is the correct place to ask this question.)

if you want people to use, or even just try, your library, it should be on opam :-)

hey, there's a thread in the OCaml forum, but I will ask here as well:
https://discuss.ocaml.org/t/lgpl-licenses-handling-in-ocaml/6883

It seems that the license is listed as LGPL, but the license copy is GPL and does no express what kinds of linking is permitted for this library. This means that caqti can only be used if the source code of whatever uses it becomes open source. Is this intended?
For some additional context - I've been checking out OCaml, but find the licenses used in some projects very limiting to use in commercial projects (particularly compared to equivalent C#, Java and JavaScript libraries). So I'm checking if the licenses are intentionally chosen as such or have they remained such for historical reasons and have not been updated

other references:
https://opensource.stackexchange.com/questions/4287/what-are-software-dependencies-and-what-are-the-implications-of-floss-dependenc

https://discuss.ocaml.org/t/lwt-is-now-mit-licensed/1901

Looking through Caqti_type, I don't see a way to handle situations like multi-row inserts

INSERT INTO goomba VALUES (?, ?, ?), (?, ?, ?), (?, ?, ?);

where number of the sets of values to be inserted can only be known at run time.

Another example would be something like

SELECT a, b, c FROM goomba WHERE b in (?);

where ? is a list of values whose length is not known until run time.

Is there a way to handle these problems that I'm not seeing? I was expecting something like Caqti_type.list at least for the second example. I don't know where to start with the first. Would Caqti_type.custom be able to take care of these situations? If so, are there any examples that handle situations like these?

I am using the sqlite3 backend, and I would like to set enforcement of foreign key constraints. This needs to be done on every connection. What's the best way to go around this? I think it's not super obvious what's the best way to go around this, especially when using a pool.

Inserting just one column is easy:

let add_query =
  Caqti_request.exec Caqti_type.string "INSERT INTO todos (content) VALUES (?)"

However, if we have more than 4 columns, how to make it works(for example, is there any tup11 like?)?

let add_query =
  Caqti_request.exec Caqti_type.(tup11 string string string string string string string string string string string) 
  "INSERT INTO todos (content, f1, f2, f3, f4, f5, f6, f7, f8, f9, f10) 
  VALUES (?,?,?,?,?,?,?,?,?,?,?)"

For example, upon a UNIQUE constraint error, Caqti exposes a Caqti_error.t which looks like this after show:

Response from <sqlite3:db.sqlite> failed: CONSTRAINT. Query: <snip>

The actual value is

`Response_failed _

The msg field of _ could be either...

• Caqti_error.Msg, which carries a string, and is therefore not useful for pattern matching. The string would have to be parsed instead.
• Caqti_driver_sqlite3.Rc carrying, presumably, Sqlite3.CONSTRAINT. This would be useful, but:
  • Caqti_driver_sqlite3.Rc is not exposed AFAICT.
  • We actually want something portable to all the database drivers, not just SQLite3.
  • It doesn't specify that the constraint violation was of a UNIQUE constraint.

Is there a way to get more detail in the error types? Since at the moment, all we can reasonably match on is `Response_failed _, all we essentially can know is that "some error occurred."

On the subject of detail on CONSTRAINT, by contrast, @thangngoc89 reports in aantron/dream#86 that sqlite3 cli gives errors like

Error: UNIQUE constraint failed: user.email

Ideally, we would have a detailed database-agnostic error type, and translate database-specific errors in a thorough way into it.

See attached screenshot for location of broken link in README.

The current postgres driver depends on libpq and pg-config. Statically linking libpq is very cumbersome. I believe that a pgx driver would be a minor but noticable quality of life improvement for many (including me).

This ticket is for discussing final API changes before releasing a stable version of the library. I am especially interested to hear from developers of high-level DB interface who consider adding support for Caqti as a back-end, and who might need adjustments to the API.

Some issues with the current design:

• The WRAPPER instrumentation adds significant noise to the driver implementations. I don't think it's important enough to keep. Resolution: Removed.
• The direct query-and-iterate approach is convenient but does not give access to some parts of the result objects from the underlying implementations like the number of returned or affected rows. Resolution: V2 provides a call function which gives access to a result object.
• Decoding tuples can be error prone when used directly, since it uses a array-get style interface with explicit indices. There are two good reasons for this though: Efficiently and the ability to interpret integers as floats or booleans in case the underlying database is not type-precise or lacks boolean. Resolution: V2 has a typed decoders with up to 4-tupled granularity for good fanout.
• Dynamic typing was chosen for parameters and tuples to supply an easy interface for higher level APIs, but this makes direct use more error-prone. Resolution: V2 uses static types.
• Maybe replace the current exceptions with result, and redesign their types. Resolution: V2 uses result everywhere.
• I'm not found of the monolithic Caqti_sigs. Resolution: Many signatures moved to separate file, the remaining are V1 only.

I wanted to apply a few .sql files

apply:

• read file
• execute file_contents as SQL query
• read next file ..

if anything fails - rollback -> wanted everything to run as a transaction:

• I start a connection with Connection.start ()
• execute apply for a list of files
  • if nothing failed -> Connection.commit ()
  • if something failed -> Connection.rollback ()

To execute apply I used:

  let query = Caqti_request.exec Caqti_type.unit file_contents in
  Connection.exec query ()

Error1: Request to ... failed: ERROR: cannot insert multiple commands into a prepared statement

Not sure how to insert multiple commands, found that oneshot: true moves past this error, (but I couldn't find any information on what is oneshot, are there any docs?)

Error2: Unexpected result from ... More than one response received.

Does this mean that because there are multiple results Caqti_type.unit does not work?
Tried this as a workaround:

  let typ =
    Caqti_type.custom
      ~encode:(fun _ -> failwith "no parameters expected")
      ~decode:(fun _ -> Ok ())
      Caqti_type.unit
  in
  let query = Caqti_request.exec ~oneshot:true typ contents in
  Connection.exec query ()

But found Error3: Invalid concurrent usage of PostgreSQL connection detected.

I'm not sure what is invalid and what this means?
Are there any resources with documentation and examples that can help with these issues?

Great project! It provides just the right level of abstraction over the low level ocaml-sqlite3 library.

In the README it says that the sqlite3 driver does not cache prepared statements. However, I took a quick glance through the code and it does look like it is caching prepared statements (in the pcache Hashtbl).

Since caching prepared statements is a bonus, I'd be happy to update the README so that it correctly advertises this feature. But I wanted to check first to make sure I am interpreting the code correctly.

I'm trying to integrate caqti into my project and I have some questions about caqti api. Why functions like collect, exec, find etc are duplicated in both CONNECTION and Caqti_request?

Would it be possible to have something like this:

let collect = Caqti_request.collect Caqti_type.int Caqti_type.string "REQUEST"
val collect : (int, string list) request

let find = Caqti_request.find Caqti_type.int Caqti_type.string "REQUEST"
val find : (int, string option) request

and then just

Conn.exec collect 42
val string option Lwt.t

Conn.exec find 42
val string list Lwt.t

?

For example I could call Conn.collect_list (Caqti_request.find ... )... and it would work fine if there are entries, but fail if no entries were found.

First of all, thanks for this great project!

I am running into an issue while running migrations for following query on MariaDB 10.1.41:

let create_uuid_of = connection => {
  module Connection = (val connection: Caqti_lwt.CONNECTION);
  let request =
    Caqti_request.exec(
      ~oneshot=true,
      Caqti_type.unit,
      "
CREATE OR REPLACE
  FUNCTION uuid_of(uuid BINARY(16))
  RETURNS VARCHAR(36)
  RETURN LOWER(CONCAT(
  SUBSTR(HEX(uuid), 1, 8), '-',
  SUBSTR(HEX(uuid), 9, 4), '-',
  SUBSTR(HEX(uuid), 13, 4), '-',
  SUBSTR(HEX(uuid), 17, 4), '-',
  SUBSTR(HEX(uuid), 21)
));
",
    );
  Connection.exec(request, ());
};

Error 1295, This command is not supported in the prepared statement protocol yet

I tried to track down the issue, but I am not sure what the underlying MariaDB library is doing.

In caqti_driver_mariadb.ml:

...
      (match Caqti_request.query_id req with
       | None ->
          let templ = Caqti_request.query req driver_info in
          let query = linear_query_string templ in
          Mdb.prepare db query >>=
...

Even though the query id is None (due to oneshot being true), Mdb.prepare is called. I assume this is using the prepared statement protocol?

Hello,
is there any particular reason why the sqlite3 driver uses the FULLMUTEX mode ?

cf https://github.com/paurkedal/ocaml-caqti/blob/master/lib-driver/caqti_driver_sqlite3.ml#L540
Sqlite3.db_open ~mutex:`FULL ?mode (Uri.path uri |> Uri.pct_decode))

Edit: to be a bit more specific, I'm using caqti-driver-sqlite3 in a Lwt multithreaded program, and I am encountering Invalid concurrent usage of SQLite connection detected. error messages, so I was wondering if that could cause this issue.

After reading sqlite3 documentation a bit more carefully (https://sqlite.org/threadsafe.html) :

Multi-thread. In this mode, SQLite can be safely used by multiple threads provided that no single database connection is used simultaneously in two or more threads. `

a solution might be to open a connection each time i need one.

When doing Caqti_lwt.Pool.use(create', pool), would it make sense to return the result instead of unit?

My use case is that I have a GraphQL mutation that creates one register in Postgres. I need to take it back and return it through my resolver function. I thought I could use the return of my create function to get the created user with id and return it, but it turns out its unit.

For example, ocaml-mysql can connect to a database with specific charset optionally.

Mysql.(connect ~options:[SET_CHARSET_NAME "utf8mb4"])

Does caqti have such a option?

Is it possible so extend the PostgreSQL driver to use custom types? I'm particularly interested in array types and jsonb

https://www.postgresql.org/docs/current/static/arrays.html
https://www.postgresql.org/docs/current/static/datatype-json.html