Fun-sql is not an ORM, it’s a query execution and data mapping library (sometimes called a micro-ORM). It does three things:
Create the prepared statement and encode the parameters
Execute the query
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!
It’s a thin wrapper over postgres-ocaml and sqlite-ocaml. It emphasizes three things:
You write the query yourself, as a prepared statement, using SQL
If the query needs any parameters, you define how they are encoded to be sent to the database
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.
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)
This is exactly what I was about to write for myself. It is a seriously lovely, very thin abstraction layer between the underlying database and the developer. Pure convenience (and abstraction from specific DB back-end), no ORM handholding.
One thing which I always end up doing in other languages, but haven’t looked into for OCaml yet, is query fragments. For example in Perl, my pattern tends to be something like:
my $query = 'WHERE x=y';
my @args;
if ($something) {
$query .= ' AND foo=?';
push(@args, $something);
};
And sometimes I also have functions returning ($query,@args) “tuples” which I can insert at the desired place at a higher level. Since sql_fun seems to also essentially pair a string with a list of arguments, it looks like I could follow this pattern in OCaml as well. I think I might just make myself a tuple or record type that associates a partial query with its arguments, maybe a helper function or two to help compose larger fragments from smaller ones and call it a day.
I notice you haven’t touched that repo in a while and that it has some commits after the last release. Do you use it in production or is it more of a proof-of-concept at this stage?
Hi, thanks for the kind words. I really wanted the same thing (query fragments), and after looking into it, I realized that Caqti already has support for it, along with quite a lot of other important things like prepared statements and connection pooling. See here: Caqti_query_fmt (caqti.Caqti_query_fmt)
Despite Caqti’s more complex API, I think it would overall be better to build on top of that foundation rather than reinventing the wheel…
Good point, but I really like that I can more or less grasp the entirety of fun_sql in my head as I work with it. It’s simple enough that I could roll my own similar wrapper in our internal library as a weekend project if need be, to get exactly what I want.
Maybe it’s because none of the examples I found were simple enough for me, but Caqti left me wanting for a much simpler API (and the existence of things like ppx_rapper seem to indicate I may not be alone). If I’d end up wrapping convenience functions around Caqti, might as well wrap around SQLite and PostgreSQL directly, now that you’ve shown this can be done much more painlessly than I would’ve guessed.
Generating OCaml functions from annotated SQL files? That’s a very interesting take on the problem. I’m not sure it would allow query fragments to be composed together but it’s worth keeping an eye on, thanks for the hint!
Because I haven’t found the right wheel so far. Caqti is a kitchen sink, 95% of which I don’t need (I use system threads, have preferred type conversions, etc.) and ppx_rapper says “other features should work with other Caqti backends”, which doesn’t inspire confidence as to its maturity with SQLite. I also don’t think you could compose query fragments with the PPX.
I’m used to writing to/from serialization types like Sexplib.Sexp.t or Yojson.Basic.t (often without PPX) so I just want a “value” variant (like SQLite’s Data.t) and I’ll convert with the types I need according to the conventions of my application, as rows stream in from the back-end. DB I/O as simple as JSON I/O…
That’s why the fun_sql approach seemed sufficient as my starting point. A bit more boilerplate vs a hypothetical [@@deriving caqti_row] but I’m sure there’d be plenty of cases where I’d need to jump in and convert manually anyway, as I already do with Sexp and Yojson stuff.
With ppx_rapper, the SQL statement is validated with pgsqland then must be known at compile time.
I guess that with a variable SQL statement, you need some dynamic typing, (e.g. a Data.t list row type). The idea of ppx_rapper is to use the OCaml static type and then perform implicit conversion. Then it is quite different.
What is Postgresql specific with ppx_rapper is the statement validation with pgsql. Then you won’t be able to execute a specific MySQL statement… But most SQL are generic. I use ppx_rapper with MariaDB without any trouble.
It is a seriously lovely, very thin abstraction layer between the underlying database and the developer. Pure convenience (and abstraction from specific DB back-end), no ORM handholding.
I think I might just make myself a tuple or record type that associates a partial query with its arguments, maybe a helper function or two to help compose larger fragments from smaller ones and call it a day.