Re: [Caml-list] looking for "real world" sqlite3 examples

[Petter Urkedal <paurkedal@gmail.com>]

I have two projects which may also be of interest.  I haven't announced
them earlier, but I'm using them in almost production ready code:

Caqti [1] is a common interface to database client libraries, currently
supporting the postgresql and sqlite3 bindings.  It supports monad-based
cooperative threading, including lwt and async.  MySQL/MariaDB and ODBC
would be nice additions, but it would be preferable to have async
bindings for these, to avoid resorting to preemptive threading.

Caqti can be used directly (see e.g. [2]) though it does not provide a
high-level type-safe interface.  The high-level interface is omitted
since I think there are different approaches depending on the problem
and the programmer's taste, and it would be nice if the high-level
interfaces used a common library to connect and communicate with the
database.

epiSQL [3] is a tool which parses SQL definitions and re-emits it as XML
or generates code for Macaque or Caqti.

On 2015-07-04, Martin DeMello wrote:
> 1. database schema, versioning and migrations - will i need to do
> those independently via sql/shell scripts, or is there some good way
> to integrate them into my ocaml code?

The Caqti_sql_utils module provides a function to read SQL statements
from a file and sending them to the database.  It's used in load_sql in
[4].  Then one needs a schema version, and a function which iterates
over DB updates from that version.  I have though about adding something
like that, but I'd like more experience in the end-application before
moving code to Caqti.

It would be nice to also have a developer tool to verify that the
updates correspond to the changes in the schema.  This would fit into
epiSQL, but takes a bit though and work to implement.

> 2. type conversions - in the absence of an orm, do i have to write my
> own by hand per resultset. or is there some intermediate-level library
> that i haven't found that would automate some of it?

In Caqti you have to pass a function to extract fields from the returned
tuple, as in `C.Tuple.(fun tup -> int 0 tup, text 1 tup)`, or when
folding, `C.Tuple.(fun tup acc -> (int 0 tup, text 1 tup) :: acc)`. So,
it does part of the job, but requires care due to the lack of
type- and bounds-checks.

[1] https://github.com/paurkedal/ocaml-caqti
[2] https://github.com/paurkedal/subsocia/blob/master/lib/data/subsocia_direct.ml
[3] https://github.com/paurkedal/episql
[4] https://github.com/paurkedal/subsocia/blob/master/bin/subsocia_main.ml