About PG'OCaml

PG'OCaml provides an interface to PostgreSQL databases for OCaml applications. It uses Camlp4 to extend the OCaml syntax, enabling one to directly embed SQL statements inside the OCaml code. Moreover, it uses the describe feature of PostgreSQL to obtain type information about the database. This allows PG'OCaml to check at compile-time if the program is indeed consistent with the database structure. This type-safe database access is the primary advantage that PG'OCaml has over other PostgreSQL bindings for OCaml.


let () =
    let dbh = PGOCaml.connect () in

    let insert name salary email =
        PGSQL(dbh) "insert into employees (name, salary, email) values ($name, $salary, $?email)"
        insert "Ann" 10_000_l None;
        insert "Bob" 45_000_l None;
        insert "Jim" 20_000_l None;
        insert "Mary" 30_000_l (Some "mary@example.com");
    let print_row (id, name, salary, email) =
        let email = match email with Some email -> email | None -> "-"
        in Printf.printf "%ld %S %ld %S\n" id name salary email in

    let rows =
        PGSQL(dbh) "select id, name, salary, email from employees"
    in List.iter print_row rows;
    PGOCaml.close dbh


The latest version is 2.1. Note that PG'OCaml comes in two varieties, described below. Most users should pick the pgocaml variant, since it is the only one actively developed.


This variant is the successor to the older versions of PG'OCaml, developed by Richard W.M. Jones. The code base is unchanged and the API remains the same. If you have your own patches to PG'OCaml, then for compatibility reasons this is the code you want. Note, however, that this branch is no longer updated with new features, and no version higher than 1.3 is available.

Download pgocaml-classic-1.3.tgz (For OCaml >= 3.10)

Download pgocaml-classic-0.8.tgz (If you are still using OCaml < 3.10)


This branch began as a contribution by Jérôme Vouillon, and refactors PG'OCaml's internals and API to work in a monadic style (a compatibility mode with the old API is also available). Though flexible enough to be used with any monad, the most obvious use of this branch is to integrate PG'OCaml with an application that uses the light-weight threads of the Lwt library (a common example are applications developed to run with the Ocsigen web server). Furthermore, it includes a compatibility interface that allows you to write applications in a non-monadic fashion or to link existing applications without requiring any changes to their code. This branch is the only one actively developed, and the one recommended for all users!

Download pgocaml-2.1.tgz (Note: it requires OCaml >= 3.10)

Changelog for release 2.1:

  • Dario Teixeira: add List.iteri and List.mapi for compatibility with older versions of OCaml.

Changelog for release 2.0:

  • Dario Teixeira & Jacques-Pascal Deplaix: remove dependencies on ExtLib or Batteries. Auxilliary functions which used to be provided by Batteries are located in the newly created PGOCaml_aux module.
  • Dario Teixeira and Jacques-Pascal Deplaix: fixing issues with arrays. This requires all array types to change from 'a array to 'a option array, which breaks backward compatibility.
  • Dario Teixeira's patch making PostgreSQL's NUMERIC type be converted to/from OCaml strings. This change is not backward's compatible, requiring a bump in the major version number (though there seems to be no actual code in the wild relying on the previous behaviour).
  • Dario Teixeira's patch adding function 'uuid', which exposes the unique connection handle identifier.
  • Jacques-Pascal Deplaix's patches adding 'catch', 'transact', 'alive', 'inject', and 'alter' functions.

Changelog for release 1.7.1:

  • Fixed missing dependency in _oasis file.

Changelog for release 1.7:

  • Build system now uses OASIS.
  • Directory tree reorganisation.
  • Now using Batteries only.
  • Migration to Batteries 2.0.

Changelog for release 1.6:

  • Fixed Makefile: it should now work with any OCaml version.
  • Richard Jones' patch converting all references of 'loc' into '_loc'. The former has been deprecated for a while now.

Changelog for release 1.5:

  • Dario Teixeira's patch adding support for more array types, namely bool[], int8[], text[], float4[], and float8[].
  • Michael Ekstrand's patch to make PG'Ocaml work with batteries, if so requested (it still uses ExtLib by default).
  • Dario Teixeira's patch adding support for Hstore.
  • David Allsopp's patch fixing connection on Windows.
  • David Allsopp's patch for better reporting of nullable results.
  • Dario Teixeira's patch adding support for the 'hex' serialisation format introduced with PostgreSQL 9.0.
  • Matías Giovannini's patch adding support for cursors.
  • Dario Teixeira's patch adding support for the various transaction options in function 'begin_work'.

You can also obtain the latest development snapshot from the project's page at GitHub.

PG'OCaml Copyright © 2006 Merjis Ltd, Richard W.M. Jones (rich@merjis.com)

This software is distributed under the GNU LGPL with the OCaml linking exception. Please see the file COPYING.LIB for full license.

Package availability

There are packages of PG'OCaml available in OPAM and in the repositories of some Linux distributions. This is the easiest way of installing the library. Check the following list for your distro: (please let us know of others)

Building instructions

If there are no binary packages available for your distro or you prefer to compile it yourself anyway, note that PG'OCaml has the following requirements: (all dependencies are available on the major distros)

The build system uses OASIS. Issue the customary ./configure, make, and make install to build and perform a findlib installation.

Reporting bugs and submitting patches

You may consult the current Changelog for an up-to-date summary of the changes in each release and on the Git master.

The preferred method of interaction between developers and users is via the pgocaml-general mailing-list. You are welcome to send us patches, suggestions, or error reports. However, please do so on the mailing-list.


Dario Teixeira wrote a tutorial for PG'OCaml: pgoctut.pdf

There is also the API documentation included with the package (issue make doc), although it is rather sparse. (This documentation is also available online, for your convenience.)

Also see the source for COCANWIKI.

Differences from other PostgreSQL bindings

Please note that this is not the first or only PostgreSQL binding for OCaml. Here are some others you may wish to consider:

PG'OCAML is different from the above bindings:

  1. It is not just a wrapper around the C libpq library. Instead it's a pure OCaml library that talks the frontend/backend protocol directly with the database.
  2. It has a Camlp4 layer which lets you write SQL statements directly in your code, type safe at compile time, with type inference into the SQL, and using the full PostgreSQL SQL grammar (sub-selects, PG-specific SQL, etc.). But the flip side of this is that you need to have access to the database at compile time, so the type checking magic can be done; also if you change the database schema, you will need to recompile your code to check it is still correctly typed.
  3. It requires PostgreSQL >= 7.4. It's also synchronous, so if you want to have an interactive interface you'll need to use threads.
  4. It does not work with other databases, nor will it ever work with other databases.


Hard problems

It is fairly common to construct SQL statements from string fragments, as in this pseudocode example:

let order_clause = match key, reverse with
    | `Author, false -> "author asc"
    | `Author, true -> "author desc"
    | `Title, false -> "title asc"
    | `Title, true -> "title desc" in
let sql = "select title, author from books order by " ^ order_clause

Such statement-building is not currently permitted by PG'OCaml, unless you ditch the Camlp4 extension and use the low-level, unsafe interface. It would be nice to have some sort of "fragment constructor" operator to allow the above to be expressed in a type-safe way. However because it is not possible to compile the fragments, it doesn't look like such a constructor could be written. If anyone has any ideas about this, please contact us.