- Queries run in a background thread and play nicely with Async (assuming
freetdsso 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.execute_unit "begin" won't have this
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.
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
See the .mli files for more info (we'll upload generated docs at some point).
The tests are full of examples.
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)
Mssql.Pool.with_pool ~host ~db ~user ~password ~max_connections:10 (fun p -> Mssql.Pool.with_conn p (fun db -> (* same as above *)))