SQL statement typing howto

Note that this is a rather low-level mecanism that uses raw SQL strings. If possible you should prefer the higher level functions of Ask.Sql.

Binding basics

The Ask.Sql.Stmt module provides a low-level mecanism to type the parameters and results of SQL 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 Sql.Stmt.t. It can be typed as follows:

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

Note that the binding function is only positional in nature it always binds arguments from left to right. You then get the arguments in that order but reversed, which your database driver should bind sequentially according to its scheme. This also 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 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 Sql.Stmt.t 

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

This can be achieved with these combinators as follows:

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