[ANN] fun-sql 0.2.3

Ecosystem
, ,
1

I am happy to announce the initial release of fun-sql, a simple functional-style query library for SQLite and PostgreSQL.

To use it with SQLite: https://ocaml.org/p/fun-sqlite

To use it with PostgreSQL: fun-postgresql 0.2.3 (latest) · OCaml Package

Fun-sql is not an ORM, it’s a query execution and data mapping library (sometimes called a micro-ORM). It does three things:

  1. Create the prepared statement and encode the parameters
  2. Execute the query
  3. Decode the resultset into OCaml types using a set of combinators.

Here’s an example:

open Fun_postgresql

module Note(Db : sig val db : Postgresql.connection end) = struct
  open Db

  type t = { id : int; txt : string }
  let ret = ret (fun row -> { id = int 0 row; txt = text 1 row })

  (* Prepared statement: *)
  let edit = query db "update note set txt = $1 where id = $2"

  (* Use by simply calling it: *)
  let edit id txt = edit ~args:Arg.[text txt; int id] unit
  (* val edit : int -> string -> unit *)

  (* Prepared statement: *)
  let by_id = query db "select id, txt from note where id = $1"

  let by_id id = only (by_id ~args:Arg.[int id] ret)
  (* val by_id : int -> t *)
end

The design enforces the use of prepared statements–indeed, with PostgreSQL, a prepared statement corresponding to a query can be created only once, so you have to ensure that you use a pattern like the above.

MySQL support is also desired and I will get to it at some point unless someone beats me to it!

9 Likes
2

how does this compare to other alternatives? for example, postgresql-ocaml, petrol, caqti, etc?

3

It’s a thin wrapper over postgres-ocaml and sqlite-ocaml. It emphasizes three things:

  1. You write the query yourself, as a prepared statement, using SQL
  2. If the query needs any parameters, you define how they are encoded to be sent to the database
  3. If the query returns any results, you define how they are decoded after being received from the database

These three requirements are made as simple as possible. For example, for writing queries (prepared statements), you only need one function, query. For encoding parameters and decoding results, you only need to use the provided helpers.

That’s really. It doesn’t try to do anything very fancy. Although I do have an unreleased PPX which derives the decoder from the (record) type.

4

If you want a view from of the Caqti+ppx_rapper combo, look my page: index (caqti_ppx_rapper.index)

Petrol seems a level above. You declare tables (fields, types), then use them with some associated queries. But I guess it could be less flexible than handcrafted queries. (On the page petrol/doc/index.mld at master · kiranandcode/petrol · GitHub I haven’t seen a SELECT query with a WHERE clause. From the source it seems to be provided, but I haven’t seen how to use it. The main doc page points to a 404 error page instead of the rest of the doc)