PGOCaml discovery - Documentation and sample code

[MdeLv]

The main landing pages for a new pgocaml user seem to be: http://pgocaml.forge.ocamlcore.org/ A brief introduction to PG'OCaml (based on macros for camlp4) API: PGOCaml_generic.PGOCAML_GENERIC https://github.com/darioteixeira/pgocaml master/doc/HOW_IT_WORKS.txt (still based on macros for camlp4) Of course, there are other files to explore but what is the latest documentation is not obvious.

What should be considered as the valid pgocaml and pgocaml_ppx documentation pages and files? Is it planned to update these different pages?

Discovering pgocaml

Let's assume I'm a total beginner at pgocaml, however with some normal knowledge of OCaml and basic knowledge of psql. When I read the sample code in the documentation Using the PPX, I can recognize some OCaml syntax, some % ppx syntax and some SQL syntax. I know that I must first create a db because I've read that "TYPE SAFE at compile time, with TYPE INFERENCE into the SQL" and that "you need to have access to the database at compile time" (BTW need to should be replaced with must).

Could you detail prerequisites? Which psql exact database should be created (tables, user, etc.) before playing with the demo code?

Here is what I could do without prerequisites: I could create a database 'test' (with default owner = postgres), create a user 'test', give password 'test' and grant him all privileges on this db:

postgres=# create database test;
postgres=# create user test;
postgres=# \password test
postgres=# grant all privileges on database test to test;

Of course, I could not create a database handler with no parameters as shown in the example pgocaml ppx code

let dbh = PGOCaml.connect ()
Exception:
PGOCaml_generic.Make(Thread).Error "PGOCaml: Could not connect to database".

The API says:

val connect : ?host:string ->
              ?port:int ->
              ?user:string ->
              ?password:string ->
              ?database:string ->
              ?unix_domain_socket_dir:string ->
              unit ->
              'a t monad

Connect to the database.
The normal $PGDATABASE, etc. environment * variables are available.

I could create a database handler from the Toplevel:

let dbh = PGOCaml.connect 
  ~host:"localhost" 
  ~port:5432 
  ~database:"test"
  ~user:"test" 
  ~password:"test"
  ()
val dbh : '_weak6 PGOCaml.t PGOCaml.monad = <abstr>

Of course, the db still needs to be configured with the relevant tables and fields. However, the first use of the value dbh raised an error because (at least) some host=unix is used instead of user=localhost:

let insert name salary =
[%pgsql dbh "insert into employees (name, salary) VALUES ($name, $salary)"] 
Characters 27-32:
  [%pgsql dbh "insert into employees (name, salary) VALUES ($name, $salary)"] ;;
    ^^^^^
PG'OCaml PPX error: Could not make the connection host=unix, port=5432, user=test,
password=*****, database=test, error: PGOCaml_generic.Make(Thread).Error
("PGOCaml: Could not connect to database")

Where does this 'host=unix' parameter call come from? It looks like this quite low-level pgocaml discovery needs to be turned in something more friendly. Hence this post. Do you think it's an environment variables problem?

Thanks.

[MdeLv]

1/ I read again the documentation: Usage. I was able to connect to the db using the suggested environment variables. PGOCaml.connect () creates a db handler.

However, I would like to be able to use any authentication mechanism used by the OCaml/PGOCaml program, without being forced to talk to the underlying Unix system which is plenty of time-consuming surprises.

How do you cleanly proceed without using these environment variables?

2/ Executing the example code gives that message:

ERROR: 42P01: relation "employees" does not exist
Exception:
PGOCaml_generic.Make(Thread).PostgreSQL_Error
 ("ERROR: 42P01: relation \"employees\" does not exist", _).

I could run it by removing the TEMP sql parameter

let () = [%pgsql dbh
  "execute" 
  "CREATE  TABLE IF NOT EXISTS employees (name TEXT , salary FLOAT)"]

To simulate the TEMP parameter and avoid that each function call adds a same record, I also added the following command before closing connection:

[%pgsql dbh "execute" "DROP TABLE IF EXISTS employees"] ;
PGOCaml.close dbh

What happens when you run the published example code?

[wrmack]

@MdeLv thank you for your comments. I am also new to this and was struggling. Your comments were helpful.

Information I have since found useful:

1. Dario's explanation of statement flags, environment variables and the connection handle at 4.1, 4.2 here.

First, bear in mind that the statement flags are valid only at compile-time, while the parameters to connect are used only at runtime. Second, though environment variables can be used both at compile and runtime, they require an action by the user to set them up.

If you do not use environment variables but rely on a connection handle then it seems you have to use statement flags in order to compile correctly and the connection handle will only have effect at runtime. Something like the following will compile OK and create a table at runtime:

open PGOCaml

let () =
  let dbh = PGOCaml.connect
      ~host: "localhost"
      ~port: 5432
      ~user: "test"
      ~password: "test
      ~database: "postgres"
      () 
  in

  let () = [%pgsql dbh "host=localhost" "password=test" "database=postgres"
      "execute"
      "CREATE TABLE IF NOT EXISTS employees (
        name TEXT PRIMARY KEY,
        salary FLOAT)"]
  in

  PGOCaml.close(dbh)

2. Ocsigen's Eliom-distillery uses PGOCaml. It is instructive to look at how they do it. Environment variables are created in Makefiles.

Local variables are set by the developer in Makefile.options. For example:

## The host database.
DB_HOST               := localhost

## The port of the database server
DB_PORT               := 3000

## The database name for the project. By default, it's the project name.
DB_NAME               := %%%PROJECT_NAME%%%

Makefile.db then converts these to environment variables:

export PGHOST     := $(DB_HOST)
export PGDATABASE := $(DB_NAME)
export PGPORT     := $(DB_PORT)
export PGUSER     := $(DB_USER)
export PGPASSWORD := $(DB_PASSWORD)

[yawaramin]

Can I add one more point here–how to configure the PPX with dune? It's not always immediately obvious to users how to set up a PPX–e.g. what's the exact name to use in the dune file? Etc.