Repository files navigation

Please note that this is not the first or only PGSQL bindings forOCaml. Here are the others which you may want to consider:

• postgresql-ocamlPostgreSQL-OCaml by Markus Mottl

• ocamlodbcODBC bindings by Max Guesdon which can be used to access PostgreSQL

• ocamldbi a Perl-likeDBI layer by the present author

PG'OCAML is different than the above bindings:

• It ISN'T just a wrapper around the C libpq library. Instead it's a pureOCaml library which talks the frontend/backend protocol directly with thedatabase.

• It has a PPX (macro) layer which lets you write SQL statements directly in yourcode, TYPE SAFE at compile time, with TYPE INFERENCE into the SQL, and usingthe full PostgreSQL SQL grammar (sub-selects, PG-specific SQL, etc.). Butthe flip side of this is that you need to have access to the database atcompile time, so the type checking magic can be done; also if you changethe database schema, you will need to recompile your code to check it isstill correctly typed.

• (A minor point) - It requires PostgreSQL >= 7.4. The default interface(PGOCaml) provided is synchronous. But it also supports any asynchronousinterface that implements thePGOCaml_generic.THREAD signature.

• It doesn't work with other databases, nor will it ever work with otherdatabases.

PG'OCaml uses environment variables (or in-code parameters, which are [ill advised](https://hackernoon.com/how-to-use-environment-variables-keep-your-secret-keys-safe-secure-8b1a7877d69c))to connect to your database both at compile-time and at runtime.

The PPX aims to be more or less a carbon copy of the former extension.

let()=let dbh=PGOCaml.connect()inletinsertnamesalary=    [%pgsql dbh"insert into employees (name, salary) VALUES ($name, $salary)"]in  ignore(insert"Chris"1_000.0);letgetname=    [%pgsql dbh"select salary from employees where name = $name"]inlet()= [%pgsql dbh"execute""CREATE TEMP TABLE IF NOT EXISTS employees (        name TEXT PRIMARY KEY,        salary FLOAT)"]inlet name="Chris"inlet salary= get name|>List.hd|>function|Some(x) -> x|None -> raise(Failure"The database is probably broken.")inPrintf.printf"%s's salary is %.02f\n" name salary;PGOCaml.close(dbh)

The PPX allows you to specify that queries returning results should be returned asobjects, rather than tuples.

let%lwt res=  [%pgsql.object dbh"SELECT * FROM employees"]inList.iter  (funrow ->Printf.printf"%s makes $%f\n" row#name row#salary)  res

The PPX now also supports${...} expansions.

(* where [e] is a row returned by a [pgsql.object] query*)let%lwt incr_sal e=  [%pgsql dbh"UPDATE employees SET salary = ${e#salary +. 1.0}"]

You may wish to print all the fields of an object for debugging purposes.

let%lwt rows= [%pgsql.object dbh"show""SELECT * FROM employees"]inList.iter  (funrow -> print_endline row#show)  rows

The above code will not work if one of the selected fields is namedshow. ThePPX allows one to explicitly name the pretty-printer method as follows:

let%lwt rows= [%pgsql.object dbh"show=pp""SELECT * FROM employees"]inList.iter  (funrow -> print_endline row#pp)  rows

It's important to note that theshow directive causes values to be printed inthe same format used by the Postgres API, so things likeCalendar.t values andcustom converters (see below) may not work as expected.

Custom serializers and deserializers may be provided in a configuration filespecified byPGCUSTOM_CONVERTERS_CONFIG (see above). An example configurationfile follows:

( ( (Or      ( (Rule (typnam userid)); userid is a fully abstract type        (Rule (colnam userid))      )    )    ( (serialize Userid.to_string)      (deserialize Userid.from_string)    )  )  ( (Or      ( (Rule (typnam cash_money)); for strings beginning with a $ and possibly needing to be trimmed        (And; there exists a column elsewhere also named salary, but it has a different type          ( (Rule (typnamfloat))            (Rule (colnam salary))          )        )      )    )    ( (serialize"fun x -> String.(sub x 1 (length x - 1)) |> String.trim")      (deserialize"fun x ->\"$\" ^ x")    )  ))

In case you're working on a large project, and don't want to write manyconvoluted rules to play nicely with your existing database structure, you canselectively enable custom serialization for individual queries:

let rows=  [%pgsql.object    dbh"load_custom_from=tests_ppx/config.sexp""show""SELECT * FROM customtable"]in...

PG'OCaml's PPX is not given type information by the compiler, so it willsometimes have trouble figuring the correct types for arguments. While one mayfind a parameter's serializer based in its name, this is not ideal,particularly when using${...} parameters. In cases like this, you may use asyntax similar to OCaml's native type constraint:

let()= [%pgsql dbh"INSERT INTO users VALUES ${u : userid}"]in...

Please note that the${u : userid} above will NOT be compiled into an OCamltype constraint. Its only effect is to supplyuserid as thetypnam to theserializer resolver.

PG'OCaml (C) Copyright 2005-2009 Merjis Ltd, Richard W.M. Jones (rich@annexia.org)and other authors (see CONTRIBUTORS.txt for details).

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

For an example, please seetests