Idea for another type safe PostgreSQL interface

[Richard Jones <rich@annexia.org>]

  [I just throwing this idea out there to see if people find it
  interesting, or want to shoot it down ...  There're only fragments of
  working code at the moment]

I'm thinking about a type safe interface to PostgreSQL.  One such
interface at the moment is in Xcaml, but it only supports a very small
subset of SQL, and I like to use complex SQL.  It seems that there is
a way to support the whole of PostgreSQL's language from within OCaml
programs, in a type safe way.

The general plan would be to have a camlp4 extension which would use
Postgres's new "PREPARE" feature to actually prepare the statements,
and Postgres's other new feature, "Describe Statement", to pull out
the parameter types and result types from the prepared statement.
This allows the camlp4 extension to replace the statement string with
a type safe expression, and allow type inference to find mismatches.
How a typical program would look is shown at the end of this message.

The advantages are:

 * Complete support for PostgreSQL statements with hardly any
   programming effort.
 * Type safe.
 * Schema is stored in one place - the database - and doesn't need
   to be duplicated.

The obvious disadvantages stem from the fact that at _compile time_,
you need to have access to the database.  Perhaps others will think of
other disadvantages.

(I currently don't care about databases which aren't Postgres ...)

Thoughts?

Rich.

----------------------------------------------------------------------
open Printf
open Postgresql_typed

(*
  create temporary test (
    id serial not null primary key,
    str text not null,
    num int4 not null
  )
*)

let () =
  let dbh = new Postgresql.connection ~dbname:"rich" in
  let insert t i =
    PGSQL(dbh) "insert into test (str, num) values ($t, $i)"
  in
  insert "hello" 10;
  insert "world" 20;
  let rows = PGSQL(dbh) "select id, str, num from test order by 3, 2, 1" in
  (* 'rows' would have a type like (int, string, int) Rows.t *)
  Rows.iter (
    fun (id, text, num) ->
      printf "id = %ld, text = %s, num = %ld\n" id text num
  ) rows

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