# \[ANN\] First release of PGX (Pure-OCaml PostgreSQL client) **URL:** \ **Category:** Community\ **Tags:** announce\ **Created:** [May 31, 2018, 10:07pm UTC](https://discuss.ocaml.org/t/ann-first-release-of-pgx-pure-ocaml-postgresql-client/2068 "2018-05-31T22:07:09Z")\ **Posts on this page:** 20\ **Page:** 1
**Author:** ![BrendanLong](https://sea2.discourse-cdn.com/flex020/user_avatar/discuss.ocaml.org/brendanlong/32/522_2.png) [@BrendanLong](https://discuss.ocaml.org/u/BrendanLong)\ **Post date:** [May 31, 2018, 10:07pm UTC](https://discuss.ocaml.org/t/ann-first-release-of-pgx-pure-ocaml-postgresql-client/2068/1 "2018-05-31T22:07:09Z")
I’m happy to announce the first release of [PGX](https://opam.ocaml.org/packages/pgx/) on opam. PGX is a pure-OCaml PostgreSQL client based on PG’Ocaml. Since the fork, we’ve made the following improvements: - More tests ([80% coverage](https://coveralls.io/github/arenadotio/pgx?branch=master) on the latest commit) - We designed our tests so we [write them once](https://github.com/arenadotio/pgx/blob/master/pgx_test/src/pgx_test.ml) and then run them against [all](https://github.com/arenadotio/pgx/blob/master/pgx_async/test/test_pgx_async.ml) [three](https://github.com/arenadotio/pgx/blob/master/pgx_lwt/test/test_pgx_lwt.ml) [IO](https://github.com/arenadotio/pgx/blob/master/pgx_unix/test/test_pgx_unix.ml) backends - Tests also [run on every commit](https://circleci.com/gh/arenadotio/pgx) - More consistent use of async API’s (we do our best to never use synchronous API’s) - Addition of [Pgx.Value](https://github.com/arenadotio/pgx/blob/master/pgx/src/pgx_value_intf.ml) for hopefully easier conversion to and from DB types - [Safe handling of concurrent queries](https://github.com/arenadotio/pgx/blob/0.1/pgx_test/src/pgx_test.ml#L295) (not any faster, but they won’t crash) - [Improved interface for prepared statements](https://github.com/arenadotio/pgx/blob/0.1/pgx/src/pgx.mli#L202) to [make it harder to use them wrong](https://github.com/arenadotio/pgx/blob/0.1/pgx_test/src/pgx_test.ml#L230) - We include Pgx\_async, Pgx\_lwt, and Pgx\_unix (synchronous) so you don’t have to write your IO module (and possibly get it wrong) - Probably other things I’ve forgotten by now We’re still looking for feedback on the API and pull requests are welcome! ---
**Author:** ![keleshev](https://sea2.discourse-cdn.com/flex020/user_avatar/discuss.ocaml.org/keleshev/32/3779_2.png) [@keleshev](https://discuss.ocaml.org/u/keleshev)\ **Post date:** [October 11, 2018, 5:06pm UTC](https://discuss.ocaml.org/t/ann-first-release-of-pgx-pure-ocaml-postgresql-client/2068/2 "2018-10-11T17:06:47Z")
I’ve just discovered this, and looks like a really good piece of work. I hope I’ll be able to try pgx soon. Are you using any ppx on top of this or any user-level conveniences? ---
**Author:** ![BrendanLong](https://sea2.discourse-cdn.com/flex020/user_avatar/discuss.ocaml.org/brendanlong/32/522_2.png) [@BrendanLong](https://discuss.ocaml.org/u/BrendanLong)\ **Post date:** [October 11, 2018, 5:46pm UTC](https://discuss.ocaml.org/t/ann-first-release-of-pgx-pure-ocaml-postgresql-client/2068/3 "2018-10-11T17:46:03Z")
There’s currently not any ppx to go with it. We’ve talked about doing something similar to [Diesel](http://diesel.rs/) in Rust, but none of us really have experience with ppx and the convenience factor hasn’t been worth it for us yet. (We’re definitely open to new contributions though) ---
**Author:** ![glennsl](https://sea2.discourse-cdn.com/flex020/user_avatar/discuss.ocaml.org/glennsl/32/142_2.png) [@glennsl](https://discuss.ocaml.org/u/glennsl)\ **Post date:** [December 28, 2021, 7:36pm UTC](https://discuss.ocaml.org/t/ann-first-release-of-pgx-pure-ocaml-postgresql-client/2068/4 "2021-12-28T19:36:04Z")
Could you elaborate on the rationale for forking PGOcaml instead of contributing to its development? Is there a fundamental difference in philosophy? The big draw of PGOcaml over PGX for me is the PPX, which provides compile-time checking of SQL queries and results against the database. It doesn’t seem like this and the mentioned features of PGX are mutually exclusive though. ---
**Author:** ![glennsl](https://sea2.discourse-cdn.com/flex020/user_avatar/discuss.ocaml.org/glennsl/32/142_2.png) [@glennsl](https://discuss.ocaml.org/u/glennsl)\ **Post date:** [December 28, 2021, 7:40pm UTC](https://discuss.ocaml.org/t/ann-first-release-of-pgx-pure-ocaml-postgresql-client/2068/5 "2021-12-28T19:40:32Z")
Continuing the comparison with Rust libraries, PGOcaml seems more similar to [SQLx](https://github.com/launchbadge/sqlx), so perhaps the difference is in the vision as Diesel is more like an ORM. ---
**Author:** ![BrendanLong](https://sea2.discourse-cdn.com/flex020/user_avatar/discuss.ocaml.org/brendanlong/32/522_2.png) [@BrendanLong](https://discuss.ocaml.org/u/BrendanLong)\ **Post date:** [December 28, 2021, 7:52pm UTC](https://discuss.ocaml.org/t/ann-first-release-of-pgx-pure-ocaml-postgresql-client/2068/6 "2021-12-28T19:52:36Z")
> Could you elaborate on the rationale for forking PGOcaml instead of contributing to its development? I’m not really sure since the fork happened before I started. I do know that we were focused on making the library usable without ppx and had no interest in the ppx interface. ---
**Author:** ![glennsl](https://sea2.discourse-cdn.com/flex020/user_avatar/discuss.ocaml.org/glennsl/32/142_2.png) [@glennsl](https://discuss.ocaml.org/u/glennsl)\ **Post date:** [December 28, 2021, 9:09pm UTC](https://discuss.ocaml.org/t/ann-first-release-of-pgx-pure-ocaml-postgresql-client/2068/7 "2021-12-28T21:09:00Z")
Ah, that’s unfortunate. Thanks for the quick answer though! I’m interested in the PPX for the same reason I’m interested in strong statically typed programming languages. It seems like a very natural extension to also check the syntax and types of SQL queries at compile-time, and to have the same fast feedback loop when writing them. I’m hoping you wouldn’t be opposed to adding such a PPX if I find there’s compelling reasons to move to PGX instead of incrementally improving PGOcaml. ---
**Author:** ![BrendanLong](https://sea2.discourse-cdn.com/flex020/user_avatar/discuss.ocaml.org/brendanlong/32/522_2.png) [@BrendanLong](https://discuss.ocaml.org/u/BrendanLong)\ **Post date:** [December 28, 2021, 9:38pm UTC](https://discuss.ocaml.org/t/ann-first-release-of-pgx-pure-ocaml-postgresql-client/2068/8 "2021-12-28T21:38:12Z")
I’m not the maintainer of PGX anymore, so you’d have to ask the current Arena team. Personally, I think it would be better to make a separate ppx for generating SQL, and then use it with any PostgreSQL client library though (although there may be some complexity with handling the different types used by different client libraries). I don’t think this really needs to be part of the client library itself though. ---
**Author:** ![glennsl](https://sea2.discourse-cdn.com/flex020/user_avatar/discuss.ocaml.org/glennsl/32/142_2.png) [@glennsl](https://discuss.ocaml.org/u/glennsl)\ **Post date:** [December 28, 2021, 9:52pm UTC](https://discuss.ocaml.org/t/ann-first-release-of-pgx-pure-ocaml-postgresql-client/2068/9 "2021-12-28T21:52:24Z")
Generating SQL would be rather limited, like most ORMs are, or a massive undertaking considering all the possible postgres extensions. The benefit of the approach taken by PG’OCaml and SQLx is that the postgres backend itself checks the syntax and types, taking into account which extensions it has installed and therefore supports the whole syntax with very little effort. It should be possible to make an independent PPX with adapters for different client libraries, but the PG’OCaml PPX seems to have [somewhat tighter integration](https://github.com/darioteixeira/pgocaml/blob/ef199d2861826ba0248c21700f7838d572075570/src/PGOCaml_generic.mli#L171-L185) that might make generalizing it a bit more awkward. Definitely worth looking into though! ---
**Author:** ![edwin](https://sea2.discourse-cdn.com/flex020/user_avatar/discuss.ocaml.org/edwin/32/627_2.png) [@edwin](https://discuss.ocaml.org/u/edwin)\ **Post date:** [December 28, 2021, 11:39pm UTC](https://discuss.ocaml.org/t/ann-first-release-of-pgx-pure-ocaml-postgresql-client/2068/10 "2021-12-28T23:39:00Z")
I’m currently using an Async thread-pool based implementation that can send queries in parallel on top of postgresql-ocaml (works quite nicely and can use nearly all cores on a 40 core postgresql server): [rage/postgresql\_async.ml at update2 · edwintorok/rage · GitHub](https://github.com/edwintorok/rage/blob/update2/src/postgresql_async.ml) I’ve tried wrapping libpq with Async more directly (i.e. avoid using multiple threads), but it is quite difficult (libpq likes to close and reopen file descriptors internally when it reconnects to the server, which causes issues if that FD was registered with epoll in Async, since the newly opened FD won’t be registered with epoll). A pure OCaml implementation is welcome and would avoid the difficulties in using libpq in an asynchronous way. Although the README for pgx says “Trying to run multiple queries at the same time will work properly (although there’s no performance benefit, since we currently don’t send queries in parallel).”, so it is not quite a replacement for what I use currently. Do you think this limitation could be removed in the future? Or can I use a similar solution as I used before (open N connections to the server, one/core), I assume queries on different connections could be sent in parallel? (which should still be an improvement over what I use currently since it wouldn’t necessarily need to use a separate OS thread to do that, just an Lwt or Async lightweight thread). ---
**Author:** ![BrendanLong](https://sea2.discourse-cdn.com/flex020/user_avatar/discuss.ocaml.org/brendanlong/32/522_2.png) [@BrendanLong](https://discuss.ocaml.org/u/BrendanLong)\ **Post date:** [December 29, 2021, 12:05am UTC](https://discuss.ocaml.org/t/ann-first-release-of-pgx-pure-ocaml-postgresql-client/2068/11 "2021-12-29T00:05:54Z")
I’m not sure if the Postgres protocol can support multiple queries on a single connection (so Pgx can’t either), but Pgx should work fine if you use multiple connections in parallel inside of a connection pool. Maybe the README should be updated to make that more clear. If there is a way to run parallel queries on the same connection, I think Pgx upstream would accept patches to support it. ---
**Author:** ![Leonidas](https://sea2.discourse-cdn.com/flex020/user_avatar/discuss.ocaml.org/leonidas/32/4039_2.png) [@Leonidas](https://discuss.ocaml.org/u/Leonidas)\ **Post date:** [December 29, 2021, 10:24am UTC](https://discuss.ocaml.org/t/ann-first-release-of-pgx-pure-ocaml-postgresql-client/2068/12 "2021-12-29T10:24:30Z")
> [@glennsl](#): > > I’m interested in the PPX for the same reason I’m interested in strong statically typed programming languages. It seems like a very natural extension to also check the syntax and types of SQL queries at compile-time, and to have the same fast feedback loop when writing them. While in theory I appreciate this, in practice the problem is that to do this kind of check you need a running Postgres server with your schema loaded, which makes builds require network and quite heavy and unusual dependencies (think of how many packages there exist that require a running SQL server to produce a binary). I would much rather have a way to cache the results of that check somehow in a way that could be committed to the repo to avoid complex dependencies. ---
**Author:** ![glennsl](https://sea2.discourse-cdn.com/flex020/user_avatar/discuss.ocaml.org/glennsl/32/142_2.png) [@glennsl](https://discuss.ocaml.org/u/glennsl)\ **Post date:** [December 29, 2021, 11:03am UTC](https://discuss.ocaml.org/t/ann-first-release-of-pgx-pure-ocaml-postgresql-client/2068/13 "2021-12-29T11:03:55Z")
> [@Leonidas](#): > > I would much rather have a way to cache the results of that check somehow in a way that could be committed to the repo to avoid complex dependencies. SQLx has an [offline mode](https://github.com/launchbadge/sqlx/blob/master/FAQ.md#how-do-i-compile-with-the-macros-without-needing-a-database-eg-in-ci) which does exactly this. Alternatively you could require type annotations, which you would otherwise need in some form anyway, and just run the checks in development mode to make sure you got it right. But in practice I haven’t found that running postgres on CI and committing a dump of the database schema to be all that problematic. Definitely takes more fiddling to get up and running though, and a caveat that it’s good to be aware of before deciding to go this route. ---
**Author:** ![gasche](https://sea2.discourse-cdn.com/flex020/user_avatar/discuss.ocaml.org/gasche/32/4_2.png) [@gasche](https://discuss.ocaml.org/u/gasche)\ **Post date:** [December 29, 2021, 1:08pm UTC](https://discuss.ocaml.org/t/ann-first-release-of-pgx-pure-ocaml-postgresql-client/2068/14 "2021-12-29T13:08:04Z")
I wondered if [Caqti](https://github.com/paurkedal/ocaml-caqti) supports PGX; there is [an issue open](https://github.com/paurkedal/ocaml-caqti/issues/38), but this has not been done yet. (I think Caqti is the most usable “general library” for database usage in OCaml, so it’s nice when it can benefit from more specialized developments such as PGX.) ---
**Author:** ![cemerick](https://sea2.discourse-cdn.com/flex020/user_avatar/discuss.ocaml.org/cemerick/32/1383_2.png) [@cemerick](https://discuss.ocaml.org/u/cemerick)\ **Post date:** [December 29, 2021, 5:52pm UTC](https://discuss.ocaml.org/t/ann-first-release-of-pgx-pure-ocaml-postgresql-client/2068/15 "2021-12-29T17:52:44Z")
I have greatly enjoyed using [ppx\_rapper](https://github.com/roddyyaga/ppx_rapper), which provides compile-time checks of both query syntax and relevant in/out query parameters. It does not verify coherence with any given schema (either live or e.g. as represented in a DDL file), but given the complexities involved there, I’ve been happy enough. ---
**Author:** ![glennsl](https://sea2.discourse-cdn.com/flex020/user_avatar/discuss.ocaml.org/glennsl/32/142_2.png) [@glennsl](https://discuss.ocaml.org/u/glennsl)\ **Post date:** [December 29, 2021, 9:04pm UTC](https://discuss.ocaml.org/t/ann-first-release-of-pgx-pure-ocaml-postgresql-client/2068/16 "2021-12-29T21:04:10Z")
The problem with this kind of inline type annotations, while nice and readable and easy to parse, is that it makes it hard to run the query manually. SQLx instead (ab)uses the aliasing syntax, making it valid SQL, e.g. `select stuff as "stuff!: Vec" from whatever`. For its syntax checking, `ppx_rapper` seems to be using bindings to [`libpg_query`](https://github.com/pganalyze/libpg_query), a C library extracted from the postgres server. Interesting approach, but with the downside that it requires using and for someone to quite actively maintain such a library for each database it should support, it won’t be aware of any extensions, and of course it won’t check against the schema. ---
**Author:** ![rgrinberg](https://sea2.discourse-cdn.com/flex020/user_avatar/discuss.ocaml.org/rgrinberg/32/40_2.png) [@rgrinberg](https://discuss.ocaml.org/u/rgrinberg)\ **Post date:** [December 29, 2021, 10:10pm UTC](https://discuss.ocaml.org/t/ann-first-release-of-pgx-pure-ocaml-postgresql-client/2068/17 "2021-12-29T22:10:45Z")
It is possible since [postgres 14](https://www.postgresql.org/docs/14/libpq-pipeline-mode.html) ---
**Author:** ![rgrinberg](https://sea2.discourse-cdn.com/flex020/user_avatar/discuss.ocaml.org/rgrinberg/32/40_2.png) [@rgrinberg](https://discuss.ocaml.org/u/rgrinberg)\ **Post date:** [December 29, 2021, 10:12pm UTC](https://discuss.ocaml.org/t/ann-first-release-of-pgx-pure-ocaml-postgresql-client/2068/18 "2021-12-29T22:12:22Z")
You could do this with dune’s promotion mechanism. You’d need to write a script to fetch the schema from the running database and save into some serializable form. ---
**Author:** ![cemerick](https://sea2.discourse-cdn.com/flex020/user_avatar/discuss.ocaml.org/cemerick/32/1383_2.png) [@cemerick](https://discuss.ocaml.org/u/cemerick)\ **Post date:** [December 30, 2021, 2:44am UTC](https://discuss.ocaml.org/t/ann-first-release-of-pgx-pure-ocaml-postgresql-client/2068/19 "2021-12-30T02:44:58Z")
> [@glennsl](#): > > The problem with this kind of inline type annotations, while nice and readable and easy to parse, is that it makes it hard to run the query manually. SQLx instead (ab)uses the aliasing syntax, making it valid SQL, e.g. `select stuff as "stuff!: Vec" from whatever` . Granted that ppx\_rapper’s type annotations need scrubbing if you’re aiming to copy/paste a query into a different tool, but doing so always requires some degree of editing regardless of the library one uses, at the very least to swap out parameter placeholders for concrete values…so I don’t know that there’s any large difference in difficulty there. > [@glennsl](#): > > and of course it won’t check against the schema That’s honestly a feature for me, and a reason why I didn’t choose PGOcaml. I appreciate static analysis, but not being able to run a build without having an accessible database running in good order felt like a bridge too far. ---
**Author:** ![glennsl](https://sea2.discourse-cdn.com/flex020/user_avatar/discuss.ocaml.org/glennsl/32/142_2.png) [@glennsl](https://discuss.ocaml.org/u/glennsl)\ **Post date:** [December 30, 2021, 9:20am UTC](https://discuss.ocaml.org/t/ann-first-release-of-pgx-pure-ocaml-postgresql-client/2068/20 "2021-12-30T09:20:17Z")
> [@cemerick](#): > > …at the very least to swap out parameter placeholders for concrete values…so I don’t know that there’s any large difference in difficulty there. If you use postegres’s own placeholders (`$1`, `$2` etc.), you can copy it verbatim into a´prepare`statement, then`execute` it without doing anything other than supplying the parameter values and types. I also think there’s a pretty big difference between just replacing parameters, and also having to rewrite the list of returned columns though. But my point is more that it’s an _unnecessary_ extra burden as `ppx_rapper` could do away with it by just changing the syntax a little bit.