Ezpostgresql

Lwt-friendly wrapper for postgresql-ocaml which supports connection pooling.

Motivation

Problem

Using databases (in particular, postgresql) in OCaml is not straightforward.

Some libraries, such as the popular PG'OCaml, implements sophisticated compile-time type-safety while hiding the cruft in a now-deprecated camlp4 syntax extension. Others, such as postgresql-ocaml goes even lower as a wrapper for libpq, the C client lib for postgres. To use it, one must be familiar with how libpq works, which means reading the docs with examples written in C. What's more, in case of postgresql-ocaml, errors are found in the form of thrown exceptions, which is undesirable and demands users' discipline for handling it.

Another problem that usually comes when building apps that comunicates with databases is to have a pooled DB connections. Most libraries don't give this support out of the box, and there doesn't seem to be any generic resource pool library for OCaml that I'm aware of except for Lwt_pool from Lwt, and the example of its usage is less documented.

Solution

This library is a wrapper to the low-level postgresql-ocaml lib that aims to give users a friendlier interface in using postgres database by making them non-blocking (via Lwt). By friendlier, it means that we give up many points of sophisticated type safety that other libs provide and stick with writing SQL query strings with params and returning string arrays, in plain OCaml (without any syntax extension). This enables a consistent and easy-to-grasp API.

Ezpostgresql also provides a better error handling mechanism by utilizing the result monad. Most of the APIs have the return type of (t, Postgresql.error) result Lwt.t. This way, users are "forced" to handle error cases, enforced by the compiler. The use of Lwt_result is recommended to ease dealing with the return values as well as chaining operations (see examples below).

This lib also provides an easy way of using pools of postgres connections powered by Lwt_pool. The API of using pooled connection is analogous to the one for single connection.

If you want more type-safe queries, then this lib is most likely not for you.

The name was inspired by the awesome Ezjsonm library.

Features

• Non-blocking, lwt-friendly interface
• Transactions
• Pooled connections
• Error handling with Result monad
• Consistent API for single connection, connection pools, and transactions

Usage

This library is still a work in progress! Consider yourself warned for breaking changes.

To use this library, install via opam:

opam pin add ezpostgresql https://github.com/bobbypriambodo/ezpostgresql.git

Examples

Single connection

let () =
  /* Brings >>= into scope for dealing with ('a, 'b) result Lwt.t. */
  Lwt_result.Infix.(
    Ezpostgresql.(
      Lwt_main.run(
        [%lwt
          {
            let operation_result =
              /* Connect to a database. `conninfo` is the usual postgres conninfo string. */
              connect(~conninfo="host=localhost", ())
              >>= (
                (conn) =>
                  /* Run a command. The passed ~params is guaranteed to be escaped. */
                  command(
                    ~query="INSERT INTO person (name, age) VALUES ($1, $2)",
                    ~params=[|"Bobby", string_of_int(19)|],
                    conn
                  )
                  >>= (
                    () =>
                      /* Run a query returning one result. */
                      one(
                        ~query="SELECT name, age FROM person WHERE name = $1",
                        ~params=[|"Bobby"|],
                        conn
                      )
                      >>= (
                        (row_opt) => {
                          /* `row_opt` will be a `string array option` containing the values in order of select.
                             It will have the value of `Some row` if the record is found, `None` otherwise. */
                          let () =
                            switch row_opt {
                            | Some(row) =>
                              print_endline(row[0]); /* outputs Bobby */
                              print_endline(row[1]); /* outputs 19 */
                            | None => failwith("Record not found!")
                            };
                          /* Run a query returning multiple result. You may provide optional ~params. */
                          all(~query="SELECT name, age FROM person", conn)
                          >>= (
                            (rows) => {
                              /* `rows` will be a `string array array` (array of entries). */
                              print_endline(string_of_int @@ Array.length(rows)); /* outputs 1 */
                              /* Close the connection. */
                              finish(conn);
                            }
                          );
                        }
                      )
                  )
              );
            /* Handling of errors. `operation_result` has the type `('a, Postgresql.error) result`. */
            switch operation_result {
            | Ok () => print_endline("Operations were successful!") |> Lwt.return
            | Error(e) => print_endline(Postgresql.string_of_error(e)) |> Lwt.return
            };
          }
        ]
      )
    )
  );

Pooled connections

let () = {
  open Lwt_result.Infix;
  open Ezpostgresql.Pool;
  /* Create a pool of DB connections with size 10. */
  let pool = create(~conninfo="host=localhost", ~size=10, ());
  Lwt_main.run(
    [%lwt
      {
        let operation_result =
          /* Run a command. The passed ~params is guaranteed to be escaped. */
          command(
            ~query="INSERT INTO person (name, age) VALUES ($1, $2)",
            ~params=[|"Bobby", string_of_int(19)|],
            pool
          )
          >>= (
            () =>
              /* Run a query returning one result. */
              one(~query="SELECT name, age FROM person WHERE name = $1", ~params=[|"Bobby"|], pool)
              >>= (
                (row_opt) => {
                  /* `row_opt` will be a `string array option` containing the values in order of select.
                     It will have the value of `Some row` if the record is found, `None` otherwise. */
                  let () =
                    switch row_opt {
                    | Some(row) =>
                      print_endline(row[0]); /* outputs Bobby */
                      print_endline(row[1]); /* outputs 19 */
                    | None => failwith("Record not found!")
                    };
                  /* Run a query returning multiple result. You may provide optional ~params. */
                  all(~query="SELECT name, age FROM person", pool)
                  >>= (
                    (rows) => {
                      /* `rows` will be a `string array array` (array of entries). */
                      print_endline(string_of_int @@ Array.length(rows)); /* outputs 1 */
                      Lwt_result.return();
                    }
                  );
                }
              )
          );
        /* Handling of errors. `operation_result` has the type `('a, Postgresql.error) result`. */
        switch operation_result {
        | Ok () => print_endline("Operations were successful!") |> Lwt.return
        | Error(e) => print_endline(Postgresql.string_of_error(e)) |> Lwt.return
        };
      }
    ]
  );
};

Transactions

let () =
  Lwt_result.Infix.(
    Ezpostgresql.Transaction.(
      Lwt_main.run(
          {
            let%lwt operation_result =
              /* Given that we have a connection... */
              Ezpostgresql.connect(~conninfo="host=localhost", ())
              >>= (
                (conn) =>
                  /* Begin the transaction block. */
                  begin_(conn)
                  >>= (
                    (trx) =>
                      /* Issue multiple commands. You can also use `one` or `all`. */
                      command(
                        ~query="INSERT INTO person VALUES ($1, $2)",
                        ~params=[|"Bobby", string_of_int(19)|],
                        trx
                      )
                      >>= (
                        () =>
                          command(
                            ~query="INSERT INTO person VALUES ($1, $2)",
                            ~params=[|"Bobby", string_of_int(19)|],
                            trx
                          )
                          >>= (
                            () =>
                              /* Commit the transaction. */
                              commit(trx)
                          )
                      )
                  )
              );
            /* You can rollback using
                 rollback trx
               and all commands issued with trx will be canceled. */
            /* If you want to use pool, rather than `begin_` you may use
                 Pool.begin_ pool
               the rest of the commands are the same. */
            /* Handling of errors. */
            switch operation_result {
            | Ok () => print_endline("Operations were successful!") |> Lwt.return
            | Error(e) => print_endline(Postgresql.string_of_error(e)) |> Lwt.return
            };
          }
      )
    )
  );