Ask database modelling conventions

There's more than one way to model your database in OCaml with Ask. The following defines a simple conventions you can follow.

These conventions are followed by the ask-sqlite3 tool when it outputs the OCaml code needed to support interaction with a pre-existing database schema, except for the naming conventions which respect those found in the schema.

Table and column names

If you are not using a pre-existing schema:

Tables representation

Given a table named n with columns c0, c1, … define a module N (n capitalized) for it. This module should have

Note that once you have modelled your tables you can use Ask.Sql.create_schema to output the corresponding schema in SQL's data definition language.

Example

Consider a person table which has three columns id and first_name and last_name columns. The following interface represents such a table according to the convention.

(** Persons. *)
module Person : sig

  type t
  (** The type for persons. *)

  val v : id:int -> first_name:string -> last_name:string -> t
  (** [v ~id ~first_name ~last_name] is a person with given attributes. *)

  val row : int -> string -> string -> t
  (** [row id first_name last_name] is a person with given attributes. *)

  val id : t -> int
  (** [id p] is the unique identifier of [p]. *)

  val first_name : t -> string
  (** [first_name p] is the first name of [p]. *)

  val last_name : t -> string
  (** [last_name p] is the last name of [p]. *)

  (** {1:table Table} *)

  open Ask

  val id' : (t, int) Col.t
  (** [id'] is the {!id} column. *)

  val first_name' : (t, string) Col.t
  (** [first_name'] is the {!first_name} column. *)

  val last_name' : (t, string) Col.t
  (** [last_name'] is the {!last_name} column. *)

  val table : t Table.t
  (** [table] is the person table. *)
end

The simplest way of implementing this signature is by using OCaml records. For example:

module Person = struct
  type t = { id : int; first_name : string; last_name : string }

  let v ~id ~first_name ~last_name = { id; first_name; last_name }
  let row id first_name last_name = { id; first_name; last_name }

  let id r = r.id
  let first_name r = r.first_name
  let last_name r = r.last_name

  open Ask

  let id' = Col.v "id" ~params:[Sql.Col_primary_key] Type.Int id
  let first_name' = Col.v "first_name" Type.Text first_name
  let last_name' = Col.v "last_name" Type.Text last_name

 let table =
   Table.v "person" Row.Cols.(unit row * id' * first_name' * last_name')
end