# \[ANN\] fun-sql 0.2.3 **URL:** \ **Category:** Ecosystem\ **Tags:** sqlite, postgresql, sql\ **Created:** [June 17, 2024, 10:00pm UTC](https://discuss.ocaml.org/t/ann-fun-sql-0-2-3/14806 "2024-06-17T22:00:33Z")\ **Posts on this page:** 12\ **Page:** 1
**Author:** ![yawaramin](https://sea2.discourse-cdn.com/flex020/user_avatar/discuss.ocaml.org/yawaramin/32/3384_2.png) [@yawaramin](https://discuss.ocaml.org/u/yawaramin)\ **Post date:** [June 17, 2024, 10:00pm UTC](https://discuss.ocaml.org/t/ann-fun-sql-0-2-3/14806/1 "2024-06-17T22:00:33Z")
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](https://ocaml.org/p/fun-sqlite) To use it with PostgreSQL: [fun-postgresql 0.2.3 (latest) · OCaml Package](https://ocaml.org/p/fun-postgresql) 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: ```auto 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! ---
**Author:** ![espioka](https://avatars.discourse-cdn.com/v4/letter/e/71c47a/32.png) [@espioka](https://discuss.ocaml.org/u/espioka)\ **Post date:** [August 10, 2024, 9:25pm UTC](https://discuss.ocaml.org/t/ann-fun-sql-0-2-3/14806/2 "2024-08-10T21:25:47Z")
how does this compare to other alternatives? for example, postgresql-ocaml, petrol, caqti, etc? ---
**Author:** ![yawaramin](https://sea2.discourse-cdn.com/flex020/user_avatar/discuss.ocaml.org/yawaramin/32/3384_2.png) [@yawaramin](https://discuss.ocaml.org/u/yawaramin)\ **Post date:** [August 11, 2024, 3:09am UTC](https://discuss.ocaml.org/t/ann-fun-sql-0-2-3/14806/3 "2024-08-11T03:09:28Z")
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. ---
**Author:** ![Frederic\_Loyer](https://sea2.discourse-cdn.com/flex020/user_avatar/discuss.ocaml.org/frederic_loyer/32/3472_2.png) [@Frederic\_Loyer](https://discuss.ocaml.org/u/Frederic_Loyer)\ **Post date:** [August 11, 2024, 8:13pm UTC](https://discuss.ocaml.org/t/ann-fun-sql-0-2-3/14806/4 "2024-08-11T20:13:02Z")
If you want a view from of the Caqti+ppx\_rapper combo, look my page: [index (caqti\_ppx\_rapper.index)](https://www.sinerj.org/~loyer/caqti_ppx_rapper/caqti_ppx_rapper/index.html) 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](https://github.com/kiranandcode/petrol/blob/master/doc/index.mld) 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) ---
**Author:** ![VPhantom](https://sea2.discourse-cdn.com/flex020/user_avatar/discuss.ocaml.org/vphantom/32/1396_2.png) [@VPhantom](https://discuss.ocaml.org/u/VPhantom)\ **Post date:** [October 8, 2025, 12:49pm UTC](https://discuss.ocaml.org/t/ann-fun-sql-0-2-3/14806/5 "2025-10-08T12:49:02Z")
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: ```perl 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? ---
**Author:** ![yawaramin](https://sea2.discourse-cdn.com/flex020/user_avatar/discuss.ocaml.org/yawaramin/32/3384_2.png) [@yawaramin](https://discuss.ocaml.org/u/yawaramin)\ **Post date:** [October 8, 2025, 6:49pm UTC](https://discuss.ocaml.org/t/ann-fun-sql-0-2-3/14806/6 "2025-10-08T18:49:42Z")
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)](https://paurkedal.github.io/ocaml-caqti/index.html/caqti/Caqti_query_fmt/index.html#val-query) 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… ---
**Author:** ![VPhantom](https://sea2.discourse-cdn.com/flex020/user_avatar/discuss.ocaml.org/vphantom/32/1396_2.png) [@VPhantom](https://discuss.ocaml.org/u/VPhantom)\ **Post date:** [October 8, 2025, 7:31pm UTC](https://discuss.ocaml.org/t/ann-fun-sql-0-2-3/14806/7 "2025-10-08T19:31:13Z")
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. 😬 ---
**Author:** ![Khady](https://sea2.discourse-cdn.com/flex020/user_avatar/discuss.ocaml.org/khady/32/469_2.png) [@Khady](https://discuss.ocaml.org/u/Khady)\ **Post date:** [October 9, 2025, 2:42am UTC](https://discuss.ocaml.org/t/ann-fun-sql-0-2-3/14806/8 "2025-10-09T02:42:52Z")
you might want to have a look at [sqlgg](https://github.com/ygrek/sqlgg/blob/master/doc.md) ---
**Author:** ![VPhantom](https://sea2.discourse-cdn.com/flex020/user_avatar/discuss.ocaml.org/vphantom/32/1396_2.png) [@VPhantom](https://discuss.ocaml.org/u/VPhantom)\ **Post date:** [October 9, 2025, 11:45am UTC](https://discuss.ocaml.org/t/ann-fun-sql-0-2-3/14806/9 "2025-10-09T11:45:00Z")
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! ---
**Author:** ![Frederic\_Loyer](https://sea2.discourse-cdn.com/flex020/user_avatar/discuss.ocaml.org/frederic_loyer/32/3472_2.png) [@Frederic\_Loyer](https://discuss.ocaml.org/u/Frederic_Loyer)\ **Post date:** [October 9, 2025, 1:10pm UTC](https://discuss.ocaml.org/t/ann-fun-sql-0-2-3/14806/10 "2025-10-09T13:10:20Z")
It is already done by `ppx_rapper` Why reinventing the wheel ? ---
**Author:** ![VPhantom](https://sea2.discourse-cdn.com/flex020/user_avatar/discuss.ocaml.org/vphantom/32/1396_2.png) [@VPhantom](https://discuss.ocaml.org/u/VPhantom)\ **Post date:** [October 9, 2025, 2:47pm UTC](https://discuss.ocaml.org/t/ann-fun-sql-0-2-3/14806/11 "2025-10-09T14:47:41Z")
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. 😬 ---
**Author:** ![Frederic\_Loyer](https://sea2.discourse-cdn.com/flex020/user_avatar/discuss.ocaml.org/frederic_loyer/32/3472_2.png) [@Frederic\_Loyer](https://discuss.ocaml.org/u/Frederic_Loyer)\ **Post date:** [October 9, 2025, 3:55pm UTC](https://discuss.ocaml.org/t/ann-fun-sql-0-2-3/14806/12 "2025-10-09T15:55:13Z")
With `ppx_rapper`, the SQL statement is validated with `pgsql`and 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.