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

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

[Martin DeMello]

Hi all,

I need to integrate an sqlite database in an app of mine, and I'm
trying to find existing code I can look at to get some idea of how to
go about it. I tried mirage/orm, but ran into some problems (I
reported the issue, and the author said the code hadn't been
maintained for a while), so I'm falling back on the idea of using
sqlite3 directly.

Things I am interested in:

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?

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?

3. is there a maintained library for generating sql queries in a typed manner?

But mostly, I want to look at someone else's code and get an idea of
how this is done in ocaml; pretty much all my database code to date
has been written in dynamically typed languages and relied on code
generation.

martin

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

[Erkki Seppala]

Hello,

Martin DeMello <martindemello@gmail.com> writes:

> 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?
>
> 3. is there a maintained library for generating sql queries in a typed manner?

You should look into SqlExpr:

  https://github.com/mfp/ocaml-sqlexpr/

I used it recently for a very small project, and it seemed to work
great, even if it doesn't ensure compile-time type safety like PGOCaml.

What I would really like would be a database interface that would work
with both PostgreSQL and SQLite. Apparently ODBC bindings for OCaml
exists, so it would work, but I haven't really tried it out yet.. In
particular I would miss SqlExpr :).

> But mostly, I want to look at someone else's code and get an idea of
> how this is done in ocaml; pretty much all my database code to date
> has been written in dynamically typed languages and relied on code
> generation.

I just write the queries in this small case. It doesn't really allow
dynamically composing new queries out from old ones. For that there is
macaque:

  https://github.com/ocsigen/macaque

I haven't tried it for a long time, apparently it has matured quite
a bit.

-- 
  _____________________________________________________________________
     / __// /__ ____  __               http://www.modeemi.fi/~flux/\   \
    / /_ / // // /\ \/ /                                            \  /
   /_/  /_/ \___/ /_/\_\@modeemi.fi                                  \/

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

[Mauricio Fernández]

On Sun, Jul 05, 2015 at 04:45:14PM +0300, Erkki Seppala wrote:
> Martin DeMello <martindemello@gmail.com> writes:
> 
> > 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?
> >
> > 3. is there a maintained library for generating sql queries in a typed manner?
> 
> You should look into SqlExpr:
> 
>   https://github.com/mfp/ocaml-sqlexpr/
> 
> I used it recently for a very small project, and it seemed to work
> great, even if it doesn't ensure compile-time type safety like PGOCaml.

There's a mechanism to unit test the queries automatically (ensuring that
they're coherent with the DB's schema) which can be integrated in the regular
build process, able to catch many (most) problems (syntax errors,
missing/misnamed/ambiguous columns/tables, etc.), see

  https://github.com/mfp/ocaml-sqlexpr#syntax-extension

It works by collecting all the statements that constitute the schema (marked
as "sqlinit") along with the remaining regular queries ("sql" / "sqlc"), and
then generating functions that initialize an empty DB and prepare all the
statements against it (similarly to what PGOCaml does at compile-time).

This can also serve as "early run-time checking", arguably stronger than
PGOCaml in a way (because you can verify that the queries are valid against
the DB's current schema, not against the one you had when compiling).

If you're using OUnit and all your DB-related code lies in module MyDB,
you can do something like this to unit test the SQL queries:

    open OUnit

    let test_sql f () =
      let b   = Buffer.create 13 in
      let fmt = Format.formatter_of_buffer b in
      let contents b = Format.fprintf fmt "@?"; Buffer.contents b in
        if not (f fmt) then
          assert_failure (contents b)

    let tests =
      [ "SQL tests" >:: test_sql MyDB.auto_check_db;
        ...
      ]
      
    ...
       run_test_tt_main ("All" >::: tests)

-- 
Mauricio Fernández

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

[Oleg]

> 3. is there a maintained library for generating sql queries in a typed manner?

> But mostly, I want to look at someone else's code and get an idea of
> how this is done in ocaml; pretty much all my database code to date
> has been written in dynamically typed languages and relied on code
> generation.

I'd like to point out
        http://logic.cs.tsukuba.ac.jp/~ken/quel/

which is a library for writing query in a typed, functional style and
generate efficient SQL (without nested SELECTs). The library makes SQL
composable, however odd it may seem. One may consider the library
similar to T-LINQ, described by Cheney et al at ICFP 2013 -- only we
use pure OCaml rather than F# and generate a SQL statement that can be
submitted to any database. (We actually used PostgreSQL for testing).
The main theoretical difference is that our normalization rules are
typed and type-preserving by construction -- and extensible, to
compensate for difference among databases.

Here is a simple example

(* Should be automatically generated, but currently isn't *)
module type SCHEMA = sig   
  type 'a repr

  val oid      : <oid:int; pid:int; qty:int> repr -> int repr
  val opid     : <oid:int; pid:int; qty:int> repr -> int repr
  val qty      : <oid:int; pid:int; qty:int> repr -> int repr

  val orders   : unit -> <oid:int; pid:int; qty:int> list
end
module type SYM_SCHEMA = sig
  include SymanticsL
  include SCHEMA  with type 'a repr := 'a repr
end

(* simple query *)

module Q1'(S:SYM_SCHEMA) = struct
  open S
  let table_orders = table ("orders", orders ())

  let res =
      foreach (fun () -> table_orders) @@ fun o ->
      where (oid o =% int 2) @@ fun () ->
      yield o

  let observe = observe
end

let module M = Q1'(GenSQL) in M.observe (fun () -> M.res)
(* "SELECT x.* FROM orders AS x WHERE true AND x.oid = 2" *)

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

[Petter Urkedal]

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