SQLite CREATE, INSERT, SELECT using ppx_rapper_lwt
Task
Database / SQLite / SQLite CREATE, INSERT, SELECT
Opam Packages Used
- ppx_rapper_lwt Tested with version: 3.1.0 — Used libraries: ppx_rapper_lwt
- ppx_rapper Tested with version: 3.1.0 — Used libraries: ppx_rapper
- caqti-driver-sqlite3 Tested with version: 1.9.0 — Used libraries: caqti-driver-sqlite3
- caqti-lwt Tested with version: 1.9.0 — Used libraries: caqti-lwt
- lwt Tested with version: 5.7.0 — Used libraries: lwt, lwt.unix
Code
The Caqti/ppx_rapper combo uses an Lwt environment.
Let operators ( let* ) and ( let*? ) are defined as usual for Lwt, to have a clean
notation for chaining promises. ( let*? ) extracts the result from a returned Ok result or
stops the execution in case of an Error err value.
let ( let* ) = Lwt.bind
let ( let*? ) = Lwt_result.bind
The helper function iter_queries sequentially schedules a list of queries.
Each query is a function that takes the
connection handle of the database as an argument.
let iter_queries queries connection =
List.fold_left
(fun a f ->
Lwt_result.bind a (fun () -> f connection))
(Lwt.return (Ok ()))
queries
The %rapper node here makes ppx_rapper generate code, such that, when applying
the create_employees_table () connection function,
the provided SQL CREATE query will be run without any parameters and without
receiving any data from the database.
In case of successful execution of the query, we get back an Ok () value, otherwise
we get an Error value.
let create_employees_table =
[%rapper
execute {sql| CREATE TABLE employees
(name VARCHAR,
firstname VARCHAR,
age INTEGER)
|sql}
]
type employee =
{ name:string; firstname:string; age:int }
let employees = [
{name = "Dupont"; firstname = "Jacques"; age = 36};
{name = "Legendre"; firstname = "Patrick"; age = 42}
]
For the SQL INSERT query, ppx_rapper generates a function insert_employee (p: employee) connection.
The tag record_in tag tells ppx_rapper to read the values name, firstname,
and age from the provided record value, while the %[TYPE_NAME]{[INPUT_FIELD_NAME]} notation specifies
which conversions to perform on the input values.
let insert_employee =
[%rapper
execute
{sql| INSERT INTO employees VALUES
(%string{name},
%string{firstname},
%int{age})
|sql}
record_in
]
The get_many tag makes ppx_rapper generate code that queries the database and
receives a list of values. The record_out tag specifies that each list item
will be a record.
The @[TYPE_NAME]{[COLUMN_NAME]} notation specifies
which conversions to perform on the output values.
let get_all_employees =
[%rapper
get_many
{sql|SELECT
@string{name},
@string{firstname},
@int{age}
FROM employees
|sql}
record_out
]
Here's another example query that selects a single row via the SQL WHERE clause, using the get_opt tag.
This query has both input (name) and output values (name, firstname, age).
Here the absence of the record_in tag makes ppx_rapper generate code where the
input values are passed as named arguments.
The get_opt tag means that the result will be an option: None if no rows matching the criteria
is found, and Some r if a row match the criteria.
let get_employee_by_name =
[%rapper
get_opt
{sql|SELECT
@string{name},
@string{firstname},
@int{age}
FROM employees
WHERE name=%string{name}
|sql}
record_out
]
All query functions generated by ppx_rapper take an argument and a connection parameter.
The function insert_employee must be called with
a value of type employee and connection. To insert multiple records from
a list, we use List.map to create a list
of functions. Each of these functions will execute its
associated query when called. The function iter_queries runs
the queries in sequence.
Note that, if you have to insert many records, it makes sense to perform a bulk insert query instead.
let execute_queries connection =
let*? () = create_employees_table () connection in
let*? () =
iter_queries
(List.map insert_employee employees)
connection
in
let*? employees = get_all_employees () connection in
employees |> List.iter (fun employees ->
Printf.printf
"name=%s, firstname=%s, age=%d\n"
employees.name
employees.firstname
employees.age);
let*? employees =
get_employee_by_name ~name:"Dupont" connection
in
match employees with
| Some employees' ->
Printf.printf
"found:name=%s, firstname=%s, age=%d\n"
employees'.name
employees'.firstname
employees'.age;
Lwt_result.return ()
| None ->
print_string "Not found";
Lwt_result.return ()
The main program starts by establishing an Lwt environment.
The function with_connection opens the database,
executes a function with the connection database handle,
and closes the database connection again, even when an exception is raised.
let () =
match Lwt_main.run @@
Caqti_lwt.with_connection
(Uri.of_string "sqlite3:essai.sqlite")
execute_queries
with
| Result.Ok () ->
print_string "OK\n"
| Result.Error err ->
print_string (Caqti_error.show err)
Discussion
The Caqti library permits portable programming
with SQLite, MariaDB, and PostgreSQL.
ppx_rapper converts annotated SQL strings into Caqti queries.
This preprocessor makes all type conversions transparent and leverages OCaml's strong typing.
It also checks the SQL syntax of the given query.
See the Caqti reference page
and the ppx_rapper reference page.