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.

Example

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

    let insert name salary email =
        PGSQL(dbh) "insert into employees (name, salary, email) values ($name, $salary, $?email)"
    in
        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

Download

The latest version is 1.7.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.

pgocaml-classic

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)

pgocaml

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-1.7.1.tgz (Note: it requires OCaml >= 3.10)

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 SVN versions from the project's page at the OCaml Forge.

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 for some Linux distributions, for OPAM, and for GODI. This is the easiest way of installing the library. Check the following list for your distro: (please let us know of others)

OPAM: package page at the OPAM package database
GODI: package page at the GODI package database
Debian: package page at the Debian tracking system
Fedora: package page at the Fedora package database
Arch Linux: package page at the Arch User Repository (AUR)

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 Debian/Fedora/GODI)

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 SVN trunk.

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.

Documentation

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

There is also documentation included with the package (issue make doc), although it is rather sparse.

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:

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.
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.
It requires PostgreSQL >= 7.4. It's also synchronous, so if you want to have an interactive interface you'll need to use threads.
It does not work with other databases, nor will it ever work with other databases.

Status

SSL is not implemented. Adding this requires using OCaml-SSL and reading the SSL section of the protocol manual carefully.
Kerberos authentication isn't implemented, and probably can't be because there aren't any Kerberos client libraries for OCaml.
crypt(3) isn't implemented, because we're not aware of any implementation of this function in OCaml. Anyway, crypt is insecure.
Many data types are not implemented. We are adding them as the need arises. Adding a data type is pretty simple: look at /usr/include/postgresql/catalog/pg_type.h to find the OID for your type, edit pGOCaml.ml:name_of_type function to add the appropriate OID to type name mapping, and then add two functions type_of_string and string_of_type to perform the translation to and from strings.

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.