Databases and OCaml

I’ve been hovering around for the past few weeks looking for an adequate database binding library for use with OCaml. So far the most supported ones seems to be PGOCaml, postgresql-ocaml, and ocaml-mysql. The latter two are bindings for the respective C APIs (libpq and libmysqlclient), which seems to be too low-level (and blocking)… and PGOCaml, while widely suggested, uses the deprecated camlp4 which doesn’t seem to play well with jbuilder (it could be done I think, just some extra work is involved).

This came as a bit of a shock since I thought interacting with databases is quite a common task in any programming language.

Is there an inherent problem with modelling database interactions with OCaml that makes it hard to build a proper abstractions around it? Are there simply very few OCaml devs dealing with databases? Or are the solutions above deemed adequate enough to fulfill the use-cases?

Also, if you are writing (or have written) OCaml programs that uses databases in some way, what lib do you use and why?

6 Likes

I use sqlexpr, as it gives a nice ppx extension for typed requests, as well as support for using it with lwt.

EDIT: Note that sqlexpr is a wrapper around ocaml-sqlite

2 Likes

For low-level there’s also ocaml-mariadb (based on libmariadbclient-dev), which provides access to both traditional MySQL blocking api and the nonblocking api introduced in MariaDB.

There’s also the Sequoia project (of the same author) that is a high level type-safe query builder. It imposes some limitations on your project and in the type of queries you can do, but it’s a good example of the type of abstractions that can be made on top of OCaml type system.

4 Likes

ocaml-mysql and many other bindings to C libraries can be used concurrently with other OCaml code by using native threads. I do this in Lwt-based applications quite often.

Lwt_preemptive provides a nice interface for mixing native threads with Lwt promises.

2 Likes

This is a bit off topic.

Having a quick glance over the sequoia API, I was intrigued by the way type information is vehicle along the query under construction. An acknowledgment at the end of the README unveils the mechanisms.

This is really bright, and I promised myself to take a few hours to play with the two solutions proposed in this thread!

2 Likes

Too bad sqlexpr only supports sqlite currently, would be great to have it support other DBs too.

Sequoia’s API looks wonderful! Reminds me of Elixir’s Ecto library. Will definitely check it out.

Yes I’ve played around with Lwt_preemptive although haven’t got anything running with postgresql-ocaml. Will keep trying and perhaps publish it if I managed to generalize it as a Lwt-wrapped postgresql-ocaml. I’m still not sure what the tradeoffs for using Lwt_preemptive are though.

Really appreciate the replies! If you have more please keep 'em coming :slight_smile:

Arena is working on an OCaml-native PostgreSQL library (a fork of PGOcaml). I’m trying to nail down the interface so we can open it up without having a lot of interface churn.

In my opinion, writing a database library in OCaml is pretty nice. I suspect the lack of them is mainly because there’s just not that many people using OCaml at all. While it’s annoying that the core developers haven’t “blessed” an async implementation, it’s cool that we can use functors to write a database client that works for both Lwt and Async.

5 Likes

Ooh I’m looking forward to this :slight_smile:

Yeah functors are definitely interesting. I’m also trying to wrap postgresql-ocaml with a friendlier API, specifically Lwt/Async-friendly. Still in early stages, though.

There are two PPX for PGOCaml… If I remember well, they are referenced there: https://github.com/darioteixeira/pgocaml/issues/9.

1 Like

So I managed to build something that I’m quite satisfied of (although still much room for improvement), Ezpostgresql (name shamelessly inspired by Ezjsonm, as I am bad at giving names):

At this point it’s only compatible with Lwt since I’m not sure how to make one that can work with both Async and Lwt. You can see the examples on the README for more info. And yes, it also supports connection pooling out of the box via Lwt_pool. Do note it’s still WIP and by no means stable yet!

Feedbacks are welcome :slight_smile:

5 Likes

postgresql-ocaml also has async bindings which can be used to implement concurrent interfaces without preemptive threading. This takes a bit more work, of course, so it may be a second iteration. I’m using it for the PostgrSQL driver of Caqti. On the other hand I had to resort to preemptive threading for the SQLite3 driver.

3 Likes

Good point, yeah I’d like to explore that too, but that means reading more C docs :sweat_smile: I aimed to flesh out the API first and see if I can get it usable.

Caqti looks really cool! I missed it on my first search. Will take a look and see if I can steal some ideas (with proper credits of course!).

I should have mentioned Caqti myself when I saw this thread, though haven’t tried to promote it, since I consider making intrusive changes to the API. In case anyone want’s to contribute their opinion, we could discuss the gory details under the Stabilize the API issue.

If anyone is interested, I put Arena’s new Postgres library (PGX) on GitHub. There’s still a lot of work to do before I make a bigger announcement, but if you want something similar to PG’OCaml with a higher-level interface and more tests it might be for you.

At this point we’re definitely planning to change the interface in non-backwards-compatible ways, and we’re also looking for feedback on the interface. Our goals (in order of priority) are:

  • Safety – the library should prevent you from doing things wrong whenever possible
  • Ease of use – Try to reduce boilerplate as much as possible and make it obvious how to do things right
  • Speed – If we can get it without hurting the other two
3 Likes

On the minimalist side of things, there is a dbm package in opam.
“Binding to the NDBM/GDBM Unix databases”

https://github.com/paurkedal/ocaml-caqti is now on opam, btw. kudos to @paurkedal! Hopefully the community will rally around it as a common interface to DBs, as in other languages.

5 Likes

The PG’OCaml package requires campl4 to be installed. This is an annoyance, but to be clear, you don’t have to use the camlp4 aspects of the library for it to be useful. You can simply call its pure ocaml functions.