Petrol

Petrol is a Free software library that provides a high-level OCaml API for interacting with SQL databases (currently Sqlite3 and Postgres are supported). The aim of this interface is to provide a type-safe API to allow developers to define their SQL tables and queries directly in OCaml, thereby avoiding the impedence mismatch and fragility that comes with having to directly write SQL code, as is typical in a normal Caqti-based project.

open Petrol
open Petrol.Sqlite3 (* or Petrol.Postgres  *)

(* define a new schema *)
let schema = StaticSchema.init ()

(* declare a table *)
let example_table, Expr.[name; age] =
    StaticSchema.declare_table schema ~name:"example"
    Schema.[
        field "name" ~ty:Type.text;
        field "age" ~ty:Type.int
    ]

Petrol's DSL allows you to express complex SQL queries as simple OCaml function compositions:

(* create a query *)
let insert_person ~name:n ~age:a db =
    Query.insert ~table:example_table
        ~values:Expr.[
            name := s n;
            age := i a
         ]
    |> Request.make_zero
    |> Petrol.exec db

The rest of this page will provide a simple quick-start guide to using Petrol. Advanced users may instead want to check out the API documentation.

A 3 step guide to Petrol

Petrol aims to make the process of writing SQL in OCaml as simple as possible. To this end, you can include in your project, with just 3 steps:

1. Declaring a schema and its tables
2. Defining queries over the tables
3. Initialising the schema at startup

1. Declare schema and tables

The first step to using Petrol is to declare a Schema (either versioned or static);

open Petrol

(* schema version 1.0.0 *)
let version = VersionedSchema.version [1;0;0]

(* define a schema *)
let schema = VersionedSchema.init version ~name:"my_application"

Once you have a schema defined, you can add tables using the VersionedSchema.declare_table function:

open Petrol
open Petrol.Sqlite3

(* declare a table, returning the table name and fields *)
let my_table, Expr.[id_field; text_field] =
  VersionedSchema.declare_table schema ~name:"my_table"
     Schema.[
        field ~constraints:[primary_key ~auto_increment:true ()] "id" ~ty:Type.int;
        field "name" ~ty:Type.text;
     ]

2. Define your queries

Having declared a table, you can now define your SQL queries using Petrol's Query and Expr combinators:

open Petrol
open Petrol.Sqlite3

(* define an query to insert new rows *)
let insert_text ~text:txt db =
    Query.insert ~table:example_table
        ~values:Expr.[
            text_field := s txt;
         ]
    |> Request.make_zero
    |> Petrol.exec db

(* define an query to collect all rows *)
let collect_all db =
    Query.select Expr.[id_field; text_field] ~from:example_table
    |> Request.make_many
    |> Petrol.collect_list db
    |> Lwt_result.map (List.map (fun (id, (text, ())) ->
        (id,text)
    ))

Note that the Request.make_* functions cache their results, so it's safe and efficient to call them directly.

3. Initialise the schema at startup

Finally, just make sure to initialise the table during startup when you establish a connection to the SQL server:

(* ... somewhere at the entry point *)
let () =
   let open Lwt_result.Syntax in
   (* ... *)
   let* conn = Caqti_lwt.connect url in
   let* () =
      Petrol.VersionedSchema.initialise
        DB.db conn in
   (* ... *)

Versioned vs Static Schemas

Another key selling point of Petrol is that it comes out of the box with a nifty versioning system to gracefully allow changing the schemas in your application over time.

When initialising the Petrol.VersionedSchema type, you must provide a version number that represents the current version of your application:

open Petrol

(* schema version 1.0.0 *)
let version = VersionedSchema.version [1;0;0]

(* define a schema *)
let schema = VersionedSchema.init version ~name:"my_application"

Version numbers are ordered lexiographically (i.e version [2] > version [1;0;0]).

Then, if you use the Petrol.VersionedSchema schema, then you will notice that both the init and declare_table functions allow you to also pass in an optional ~migrations argument.

open Petrol
open Petrol.Sqlite3

(* declare a table with a migration for a new [age] column in version 1.2.0 *)
let t, Expr.[id;age;url] =
   VersionedSchema.declare_table db ~name:"person"
     Schema.[
       field ~constraints:[primary_key ~name:"bookmark_id" ()] "id" ~ty:Type.int;
       field "age" ~ty:Type.int;
       field "url" ~ty:Type.text;
     ]
     ~migrations:[v_1_2_0, [
       Caqti_request.Infix.(Caqti_type.unit ->. Caqti_type.unit)
         {sql|ALTER TABLE person ADD COLUMN age INTEGER DEFAULT 1000|sql}
     ]]

If a table only becomes available after a particular version, then declare_table also has a ~since:version argument that allows you to inform the migration engine to create the table during migrations.

The appropriate migrations to run are automatically determined and run during initialisation when you call initialise.

As migrations may be destructive, Petrol also provides a migrations_needed function that can be called before initialise to test whether the current database requires migrations or not -- i.e so that you can ask the user to confirm whether to perform the operation.