Basically I am trying to use SqlLite3 from my ocaml code. this is what I have done so far
opam install sqlite3
utop
#require sqlite3
open Sqllite3
et db = db_open "test.db"
let create_table_query = {| create table person (name text not null, age integer not null) |}
match exec db create_table_query with
| Rc.Ok -> "table created" |> print_endline
| r -> r -> Rc.to_string |> print_endline
let insert_query = {| insert into person (name, age) values ("abhi", 25) |}
match exec db insert_query with
| Rc.OK -> "row inserted" |> print_endline
| r -> r -> Rc.to_string |> print_endline
So far so good. I got messages “table created” and “row inserted”
Now I want to query the data which I inserted. By googling, I can write code like
let query = "select name, age from person"
exec_not_null_no_headers db querty ~cb:(fun row ->
Array.iter (fun row -> print_string (row ^ " ")) row;
print_char '\n')
but what about the type? I want to extract name as string and age as int. is there a type safe way of extracting the data from the columns?
There is Caqti and ppx_rapper which can access a database (Mariadb, Postgresql, Squlite). Ppx_rapper is compatible with Casti and transform an annotated SQL query into a query and a typed binding.
Yes I was able to find caqti in my queries but before I switch to those libraries I just wanted to make sure that with this library (SqlLite) there is no type Safeway and everything is returned as a string and I must typecast everything manually before using it. Am I correct? if yes, then I will move to a Caqti tutorial
According to the documentation, you should be able to use fold, iter or a row at a time approach and get each row as a Data.t array (which seems to be what you want). However, this only works with stmt not queries in plain strings (so you have to prepare the statement before hand).
‘Type safe’ here is a very strong criterion, most of the helper libraries that people will point you to only help you assert that the expected types are what you want, otherwise raise an exception. There are fewer ways to ensure real type safety, one is to statically analyze your SQL statement and infer the types from it. Something like sqlgg does this.
Yep, I have made a new type-safe SQLite and Postgres OCaml library Petrol (announcement, repo).
The constraint is that you have to declare your tables in OCaml directl[1], but using this, we can get truly ‘Type safe’ queries (as @yawaramin alludes to), with your query types being constrained to be correct w.r.t your tables by construction:
(* 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
As for whether it works for real world projects, I’ve been using it to run an activitypub server for almost a month now, and things are going great!
[1] (although means you get access to migration support for free)
