Heyo! Pleased to annouce the release of a new library that I’ve been working on for some personal projects, but thought might be useful for others.
Petrol is a Free software library that provides a high-level OCaml API for interacting with SQL databases. The aim of this interface is to provide a type-safe API to allow developers to define their SQL tables and queries directly in OCaml, thereby avoiding the impedence mismatch and fragility that comes with having to directly write SQL code, as is typical in a normal Caqti-based project.
open Petrol
(* define a new schema *)
let schema = StaticSchema.init ()
(* declare a table *)
let example_table, Expr.[name; age] =
StaticSchema.declare_table schema ~name:"example"
Schema.[
field "name" ~ty:Type.text;
field "age" ~ty:Type.int
]
Petrol’s DSL allows you to express complex SQL queries as simple OCaml
function compositions:
(* create a query *)
let insert_person ~name:n ~age:a db =
Query.insert ~table:example_table
~values:Expr.[
name := s n;
age := i a
]
|> Request.make_zero
|> Petrol.exec db
Another selling point of Petrol is that it comes out of the box with a versioning system to gracefully allow changing the schemas in your application over time - this and more is described in the documentation.
Anyway, as usual, I’ve tried to spend some quality time tuning the documentation to make it as easy to pick up as possible, so feel free to have a gander: index (petrol.index)
As someone just starting to explore OCaml and, in particular, from the web development side, I’d like to say thank you so much for publishing this. There’s definitely a gap here in the ecosystem. I hope to play around with it very soon.
How much is Petrol tied to Lwt? The examples are using Lwt but is this a hard requirement stemming from Caqti or could this be used without Lwt/Async code as well?
The core of the library is almost entirely independent of Lwt and could be changed to Async or Eio — the only places where Lwt shows up is a) when executing Caqti requests through small wrappers around Caqti_lwt that would be easy to change, and b) when executing migrations (slightly more involved to refactor, although nothing particularly dependent on Lwt semantics).
As it so happens, there’s actually some ongoing work by @dangdennis to perform this refactoring, which I have been procrastinating on responding to… (I should be able to get some time to work on this next week).
Haha procrastinate away It’s the free open source life.
The work on my end of integrating it with eio is almost done. The same can be easily done for async. Pretty straight forward, but for those that wants to use eio, we’re just waiting until caqti-eio is available. Otherwise, we’re stuck with having to opam pin things, and I haven’t yet figured out how to have dune and opam do that automatically as a part of opam install . --deps-only. @yawaramin pointed me to .opam.template but still learning.
The template should work, provided you ran dune build and checked in the resulting opam file into the repo. Then the standard opam install --deps-only .
Apologies if this is off topic – it is not obvious to me whether the following problem has the same “core problem” that Petrol solves.
If we squint, an array of records looks like a table. I’ve been looking for an easy way to expose OCaml array-of-records to the runtime “REPL”, where in the “runtime REPL”, we can write SQL queries to read/update the OCaml-array-of-records.
Does the metaprogramming Petrol does with regards to records/tables is useful for the above.
The question is somewhat vague, so it’s not easy to answer it precisely. Let me try my best.
Petrol provides a DSL for constructing SQL queries — for your case, this DSL, or a DSL inspired by it, could be used to represent the queries over your table data.
However, Petrol, (and most Sql libraries in OCaml), don’t implement any SQL operations themselves – they instead call out to an underlying database via ffi, to execute the SQL operations. In your case, if you wanted to use Petrol, you’d need to write your own SQL execution engine over the records, which may or may not be so easy.
An easy shortcut would be to use an in-memory SQLite database. So there wouldn’t be an OCaml-managed array of records any more, there would be an in-memory SQLite table. But given the right functions/combinators, we could access it as if it were normal OCaml data.
SQL repl over OCaml Array of Records to be < 10k OCaml LOC
known in-memory-database to be > 100k LOC of non-OCaml
There’s also the issue that I have not found a single SQL db that handles variants conveniently. It’s generally enum → integer + nullable columns or separate-table-for-each-variant-branch
If you have an array of records in memory that you need to access, then it’s hard for me to imagine the benefit of using a SQL-style query to access them versus just using normal OCaml functions (Array.find_opt, Array.map, etc.). Any query would need to call those functions anyway, so why add the intermediate step?
I’m not sure it’s still working and was rather thinking about phasing it out before the first release but in rel you can use the embedded query language to query lists of values that you decided to use to represent the rows of your tables.
This looks cool! I would recommend having an example using an update query. I find that a lot of SQL DSLs are not very expressive in terms of doing updates. I guess it depends on how much type safety you want to give your clients when you are doing SQL updates.
Sorry for not being clear, I want to do this at RUNTIME.
We have an OCaml program that is a long running server.
While the program is running, I want to run SQL read queries to get info, and SQL updates to reconfigure the system.
To make this work, the OCaml program exposes some array of records, we do some magic to view them as SQL tables, and they we have a REPL that we can use at RUNTIME.
Yes, you are correct on all points. Personally, that doesn’t really change the equation for me. I still recommend SQLite over even <10kloc of a custom-written SQL implementation in OCaml. YMMV.
It’s the free open source life.