ocaml-mssql: An OCaml repository from xclerc

Mssql is an Async OCaml SQL Server library, currently using FreeTDS.

Features

Queries run in a background thread and play nicely with Async (assuming you're pinning freetds so it releases the runtime lock)
Supports single connections or connection pools
Supports automatic conversions for common data types
Supports parameterized queries (although internally it's not very nice; we parse the query for $ parameters and then insert quoted params)
We have workarounds for FreeTDS madness, like how there's no simple way to know what date format FreeTDS was configured with
Intellegently handles concurrent usage for both executing queries and with transactions
Heavily tested

Regarding concurrent usage, the following code is safe:

let%map res1 = Mssql.execute db "SELECT * FROM table_a"
and res2 = Mssql.execute db "SELECT * FROM table_b"
in
...

Since we don't support actual concurrency, this will run one query and then the other (order is not defined).

This is also safe and will never cause a duplicate primary key error, since we prevent concurrent usage of a connection outside of a transactio if a transaction is in progress on that connection:

Mssql.execute_unit "CREATE TABLE x (id INT PRIMARY KEY)"
>>= fun () ->
let%map () =
  Mssql.with_transaction db (fun db ->
    Mssql.execute_unit db "INSERT INTO x (id) VALUES (1)"
    >>= fun ()->
    Mssql.execute_unit db ~params:Mssql.Param.[Some (Int 1)]
      "DELETE FROM x WHERE id = $1")
and Mssql.execute db "INSERT INTO x (id) VALUES (1)"

Obviously this only works if we know about the transaction, so using Mssql.begin_transaction or Mssql.execute_unit "begin" won't have this feature.

Contributions

This library is heavily optimized for our use-case, but we would love to see contributions to:

Support Lwt and blocking IO
Support parameterized queries in a better way
Switch to pure OCaml and not use FreeTDS

This is not an exhaustive list -- feel free to create an issue if you're considering making a new feature and want to know if we'll accept it, or just open a pull request.

Installation

This is not on opam yet, but you can pin the dev repo:

opam pin add mssql https://github.com/arenadotio/ocaml-mssql.git

We also recommend pinning freetds to our version which releases the global runtime lock during IO until this pull request is merged:

opam pin add freetds https://github.com/arenadotio/ocaml-freetds.git#release-lock-during-io

Usage

See the .mli files for more info (we'll upload generated docs at some point).

The tests are full of examples.

Single connection

Mssql.with_conn ~host ~db ~user ~password (fun db ->
  Mssql.execute_unit db
    "CREATE TABLE example (id INT NOT NULL, value INT NOT NULL)"
  >>= fun () ->
  Mssql.execute_unit db "INSERT INTO example (id, value) VALUES (1, 2)"
  >>= fun () ->
  Mssql.execute db ~params:Mssql.Param.[Some (Int 1)]
    "SELECT id FROM example WHERE id = $1"
  >>| function
  | [ row ] ->
    let id = Mssql.Row.int_exn row "id"
    and value = Mssql.Row.int_exn row "value" in
    printf "Got row with id=%d value=%d" id value
  | _ -> assert false)

Connection pool