Developers/PGOCaml

PG'OCaml is a type-safe, simple interface to PostgreSQL from OCaml. It lets you embed SQL statements directly into OCaml code.

New web page / project

Please note that there's a new project page for PG'OCaml here:

http://developer.berlios.de/projects/pgocaml/

The current page will go away soon, so update your bookmarks, links etc. to point above.

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 rows =
     PGSQL(dbh) "select id, name, salary, email from employees" in
   List.iter (
     fun (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
   ) rows;
 
   PGOCaml.close dbh

Differences from other PostgreSQL bindings

Please note that this isn't the first or only PGSQL binding. Here are some others you may wish to consider:

PG'OCAML is different from the above bindings:

It isn't just a wrapper around the C libpq library. Instead it's a pure OCaml library which 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.
(A minor point) - 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 doesn't work with other databases, nor will it ever work with other databases.

Download

These versions are now historical. Latest versions can be obtained from this site: http://developer.berlios.de/projects/pgocaml/

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

pgocaml-1.0.tar.gz (approx 35K) (for OCaml 3.10.0)
pgocaml-0.9.tar.gz (approx 35K) (for OCaml 3.10.0)
pgocaml-0.8.tar.gz (approx 33K) (for OCaml <= 3.09)

Requirements

findlib
For versions up to 0.8: OCaml 3.08 or 3.09
For versions 0.9 and above: OCaml >= 3.10.0
ExtLib
PCRE
Calendar
CSV library

Database configuration

Gabriel de Perthuis writes:

I'd just like to add this to the requirements: Users should make themselve postgresql users, like this:
sudo su postgres -c "createuser $USER"
and allow themselves to create databases. This must be done even before make is called in the pgocaml sources.

Documentation

Dario Teixeira wrote a tutorial for PG'OCaml: http://dario.dse.nl/projects/pgoctut/

There is also documentation included with the package, although it is rather sparse.

Also see the source for COCANWIKI.

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 I'm not aware of any implementation of this function in OCaml. Anyway, crypt is insecure.
Many data types are not implemented. I am adding them as I use them. 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"
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 through the feedback form on this page.

Developers » PGOCaml