Rel SQLite3 quick start

Get the data

We use the Chinook sample database which represents a digital media store. First download the latest Chinook_Sqlite.sqlite file.

Get a feel of the schema

You can to this with the Rel library itself but for now the simplest is to use the rel tool distributed with the library and graphviz's dot tool in your PATH.

rel schema -f dot Chinook_Sqlite.sqlite | dot -Tsvg | show-url -t ch.svg

Note the few columns where option types show up where you might not expect them. These columns lack a NOT NULL directive so they might be NULL which Rel represents by option types.

If you don't have dot you can simply look here. Or simply read the SQL data definitions:

rel schema -f sqlite3 Chinook_Sqlite.sqlite
sqlite3 Chinook_Sqlite.sqlite -- .schema

Quick extraction

These steps can be invoked an ocaml REPL in which the rel.sqlite3 library is loaded and with the Chinook_Sqlite.sqlite file in the current working directory.

First let's make a bracket for using the database with a function and printing out errors.

let ( let* ) = Result.bind
let with_db ?(mode = Rel_sqlite3.Read ) f =
  Result.fold ~ok:Fun.id ~error:prerr_endline @@
  Rel_sqlite3.string_error @@ Result.join @@
  let* db = Rel_sqlite3.open' ~mode "Chinook_Sqlite.sqlite" in
  let finally () = ignore (Rel_sqlite3.close db) in
  Ok (Fun.protect ~finally @@ fun () -> f db)

Then we devise this query that selects the identifier and title column of the Album table

TODO. it would be nice to avoid the raw sql while not getting directly into formal schema modelling.

TODO. Also add a parameter to the query.

let print_albums db =
  let row = Rel.Row.(t2 (int "id") (text "title")) in
  let sql = "select AlbumId, Title from Album" in
  let st = Rel_sql.Stmt.(func sql @@ ret row) in
  let* ps = Rel_sqlite3.fold db st List.cons [] in
  Format.printf "%a@." (Rel.Row.value_pp_list ~header:true row) (List.rev ps);
  Ok ()

Here they are:

let () = with_db print_albums