Type-safe interface to Postgres's SQL

Type-safe interface to Postgres's SQL

[Richard Jones]

Last year I wrote about my idea for a type safe interface to
PostgreSQL here:

http://caml.inria.fr/pub/ml-archives/caml-list/2005/07/857083d0d55ca642e4452ab82abd02cc.en.html
(See also Alex Baretta's criticism in the same thread).

Well, I actually implemented this after a lot of pain:

http://merjis.com/tmp/pgocaml-0.1.tar.gz

A typical type-safe program is attached to the end of this message.

There are a number of unanticipated problems with the implementation:

(1) Error reporting is a bit confusing.  Actually I think this is an
issue for any non-trivial camlp4 extension to the language.  You get a
message which doesn't relate to code which you actually wrote, but
instead to the code expanded from the macro.

(2) PostgreSQL doesn't track NULL types properly so we have to do a
bit of non-trivial guesswork to try to find out if a database field
could contain a NULL or not (and therefore whether to map its type to
'a or 'a option).  For parameters this isn't possible at all, so all
parameter variables must have an option type.  For result fields, it
is usually possible to tell if the result corresponds to a known
column in the database, which is the case in ninety percent of
queries.

(3) You need to have access to the real database at compile time, as
discussed in my original message.  This is because we prepare the
statements on the database engine and then ask the database engine to
tell us the types of the parameters / results, and use this to
generate code which makes the magic of type inference work on the
OCaml side.  Of course we don't _execute_ the statements at compile
time - which would have horrible side-effects.  Except that in some
cases we need to execute the statement at compile time.  The
particular case is when the statement is "CREATE TEMPORARY TABLE",
because subsequent code needs to actually have the temporary table
there in order to check later statements which access this table.  So
I've added a special case to cope with "CREATE TEMPORARY TABLE", which
might be regarded as something of a hack.  You would probably never
need to use this hack in Real Code, but the nature of test scripts
like the one attached is that they do create temporary tables.

BTW, I don't think any of this is necessarily "better" or "worse" than
the existing PG interfaces out there -- just different.

Rich.

----------------------------------------------------------------------
(* Example program using typesafe calls to PostgreSQL.
 * $Id: test.ml,v 1.3 2006/01/31 13:22:36 rich Exp $
 *)

open Printf

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

  PGSQL_EXECUTE_ON_COMPILE(dbh) "create temporary table employees (
     id serial not null primary key,
     name text not null,
     salary int4 not null,
     email text
  )";

  let insert name salary =
    PGSQL(dbh) "insert into employees (name, salary)
                values ($name, $salary)"
  in
  insert (Some "Ann") (Some 10_000_l);
  insert (Some "Bob") (Some 45_000_l);
  insert (Some "Jim") (Some 20_000_l);
  insert (Some "Mary") (Some 30_000_l);

  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 "%ld %S %ld %S\n" id name salary email
  ) rows;

  PGOCaml.close dbh


-- 
Richard Jones, CTO Merjis Ltd.
Merjis - web marketing and technology - http://merjis.com
Team Notepad - intranets and extranets for business - http://team-notepad.com

Re: [Caml-list] Type-safe interface to Postgres's SQL

[Richard Jones]

On Tue, Jan 31, 2006 at 02:07:33PM +0000, Richard Jones wrote:
> (2) PostgreSQL doesn't track NULL types properly so we have to do a
> bit of non-trivial guesswork to try to find out if a database field
> could contain a NULL or not (and therefore whether to map its type to
> 'a or 'a option).  For parameters this isn't possible at all, so all
> parameter variables must have an option type.  For result fields, it
> is usually possible to tell if the result corresponds to a known
> column in the database, which is the case in ninety percent of
> queries.

FWIW I have solved this problem with a small syntactic change.  You
now use $varname when you don't want NULLs and $?varname when you
do.[1] A modified example program is attached.

You can also download the full program from here:

http://merjis.com/tmp/pgocaml-0.2.tar.gz

Rich.

[1] It is still possible to insert a NULL into a NOT NULL field, as
was the case before, but I can't see any way to solve that without
rewriting Postgres.

----------------------------------------------------------------------
(* Example program using typesafe calls to PostgreSQL.
 * $Id: test.ml,v 1.4 2006/02/18 15:54:06 rich Exp $
 *)

open Printf

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

  PGSQL_EXECUTE_ON_COMPILE(dbh) "create temporary table employees (
     id serial not null primary key,
     name text not null,
     salary int4 not null,
     email text
  )";

  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 "%ld %S %ld %S\n" id name salary email
  ) rows;

  PGOCaml.close dbh


-- 
Richard Jones, CTO Merjis Ltd.
Merjis - web marketing and technology - http://merjis.com
Team Notepad - intranets and extranets for business - http://team-notepad.com