Interfacing OCaml with PostgreSQL

In the last post I talked about building an OCaml project using Dune.  In this post I continue with a more complex project.  The project interfaces OCaml and PostgreSQL (a database system) with Caqti (a third-party library that provides type-safe abstraction for interfacing with databases).  I loosely follow this tutorial, but instead of using Jbuilder (Dune’s predecessor), I use Dune to build the project.  I also share some additional advice from my own experience of building the project.  Explanations of the code is not the focus here, please refer back to the tutorial for those.

Overview of the project

We’re going to build a todo list library!  Here are the steps we will follow:

  1. Have a local PostgreSQL instance up and running.
  2. Create a todolist directory (our project directory) and install all required dependencies in it.
  3.  Write:
    • the dune file for our project.
    • the interface (.mli) file.
    • the implementation (.ml) file.
  4. Invoke utop using dune to test the library

Step 1: Get a PostgreSQL Instance Running

You can skip this section if you already know PostgreSQL.  I did not know PostgreSQL before this, and find this quick start guide helpful.  In short, in Debian, run the following to get a PostgreSQL server running:

  • Installing PostgreSQL on Debian includes 2 steps.

    1. Add APT repository:
      sudo sh -c 'echo "deb `lsb_release -cs`-pgdg main" >> /etc/apt/sources.list.d/pgdg.list'
      wget -q -O - | sudo apt-key add -
    2. Install PostgreSQL:
      sudo apt update
      sudo apt install postgresql postgresql-contrib postgresql-client libpq-dev
  • Set password for postgres (PostgreSQL root) role

    sudo -u postgres psql 
  • Show clusters

    sudo pg_lsclusters

    The result shows the Port and the Status.  A cluster should be created by default.  You can also create a cluster with

    sudo pg_createcluster <version> <cluster name>

    Note the port number, which we will use in our code later.  The status should be online, like below:

    Ver Cluster Port Status Owner    Data directory               Log file
    10  main    5432 online postgres /var/lib/postgresql/10/local /var/log/postgresql/postgresql-10-local.log
  • Create a role

    On the command line

    sudo -u postgres createuser [role_name] -d -P
  • Create a database

    On the command line

    sudo -u postgres createdb [name] -O [role_name]

    Check that you can connect to it by running “psql” in a terminal.  If it doesn’t complain to you, then you’re connected!  “\q” to quit.

  • Edit the pg_hba.conf file

    We are using the Caqti_lwt module‘s connect_pool function to connect to the database, without entering a password.  To allow connection without a password, I edit the authentication config file of Postgresql.  It’s path is

    /etc/postgresql/[version number]/[name of the cluster, by default “main”]/pg_hba.conf

    Change the method (last) column to “trust“.

Step 2:  Install Dependencies

Create a directory for the project and go in it:

$ mkdir todolist
$ cd todolist

Install the dependencies by running opam install or writing a .opam file with the following content:

opam-version: "1.2"
name: "todolist"
version: "1.0.0"
maintainer: "Name <email>"

depends: [
"dune" {build}

See the tutorial for what the dependencies are.  Note that we specify dune instead of jbuilder like in the tutorial.  After you have saved your “todolist.opam” file in your todolist directory, follow the “Pinning your package and installing dependencies” section of the tutorial to get OPAM to install the packages as described in our .opam file.

Step 3a: Write the Dune File

As before, we tell Dune about the dependencies in a dune file like below:

  (name lib)
  (libraries lwt caqti caqti-lwt caqti-driver-postgresql)
  (preprocess (pps lwt_ppx)))

Because this file is to specify the library, we put it in the todolist/lib directory.  Note that this has a different syntax (fewer sets of brackets) than the jbuilder file in the tutorial.

In this dune file there is only one stanza: library.  The name of the library is lib.  dune will use all the .ml/.re files in the same directory as the dune file by default.  The stanza has two optional fields: libraries and preprocess.  The library itself (as you will see in the file below) depends on other libraries.  They are listed in the optional field libraries (notice the plural, as opposed to the singular in the stanza name).  In the preprocess optional field, we tell Dune to preprocess files using the ppx rewriter lwt_ppx.

Step 3b: Write the .mli File

In the last post I introduced interface files but did not use any.  Recall that these files provide the implementation details of a corresponding implementation (.ml) file.  Here we are writing an interface file todos.mli for our main library

type todo = {
id: int;
content: string;

type error =
| Database_error of string

(* Migrations-related helper functions. *)
val migrate : unit -> (unit, error) result Lwt.t
val rollback : unit -> (unit, error) result Lwt.t

(* Core functions *)
val get_all : unit -> (todo list, error) result Lwt.t
val add : string -> (unit, error) result Lwt.t
val remove : int -> (unit, error) result Lwt.t
val clear : unit -> (unit, error) result Lwt.t

In this interface, we first specify the types of todo and error.  Then we specify the signatures of the helper functions and core functions.

Turtles on top of each other.

Step 3c: Write the .ml file

Now we write the file that contains all the core functions.  Note that in the first line:

let connection_url = "postgresql://localhost:5432"

The number after “localhost:” has to be the same as the number your PostgreSql port number.  See the tutorial for explanation of the code.

Step 4: Invoke utop Using Dune

We want to launch a utop instance with all the relevant libraries loaded.  We can do so using Dune with the syntax:

$ dune utop <dir> --<arg>

Where <dir> is a directory containing a dune file defining all the libraries that will be loaded (lib in our case).  If you want to pass arguments to the utop command, you can do so with the <arg> field.  We don’t need any though, so:

$ dune utop lib

utop should be running.  In it, remember to open the lib module with “open Lib;;”.  Then, you can call any of the function in and it should work!

I hope in this post, I have cleared up any difficulties you may have following the original tutorial.  Now that the library for a todo list app is written, one can play around with adding a web or app interface on top!  Should be fun!






Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.