SQL statement typing howto

Binding basics

The Rel_sql.Stmt module provides a low-level mecanism to type the parameters and results of SQL prepared statements.

Using this mecanism results in a binding function that binds OCaml values to SQL parameters and returns a value of type 'a Stmt.t that specifies the type of rows 'a returned by the statement. The type unit is used for the empty row.

For example if you have this SQL statement:

SELECT name FROM person WHERE age = ? OR surname = ?

Assuming age is an integer and name and surname are text, a binding function for that statement would have type int -> string -> string Rel_sql.Stmt.t. It can be typed as follows:

let names_with_age_or_surname : int -> string -> string Rel_sql.Stmt.t =
  let sql = "SELECT name FROM person WHERE id = ? OR surname = ?" in
  let name = Row.Quick.(t1 @@ text "name") in
  let typ = Rel_sql.Stmt.(int @-> text @-> ret name) in
  Rel_sql.Stmt.func sql typ

Note that the binding function is only positional in nature it always binds arguments from left to right. This means you need to be careful if you later reorder parameters either in your binding function description or in your SQL.

Binding projections

If SQL parameters values are defined by projecting components of an OCaml values, you want to avoid having to repeat that value in the binding function.

For example if we have the statement:

UPDATE person SET age = ? WHERE name = ?

which we update by projecting values from an OCaml value of type person we do not want the type:

person -> person -> unit Rel_sql.Stmt.t 

we want the type person -> unit Rel_sql.Stmt.t.

This can be achieved with these combinators as follows:

type person = string * int
let set_age : person -> unit Rel_sql.Stmt.t =
  let sql = "UPDATE person SET age = ? WHERE name = ?" in
  let typ = Rel_sql.Stmt.(proj snd int @@ proj fst text @@ nop @@ unit) in
  Rel_sql.Stmt.func sql typ