package mssql

  1. Overview
  2. Docs
Async SQL Server client using FreeTDS

Install

Dune Dependency

Authors

Maintainers

Sources

1.1.tar.gz
md5=852ce4937004895f47fbfd2ae223062e
sha512=8a8c5414e59a4414c9d7fcb84b0f836d94098e083a8792d2d1d7b3411948594847eee695967d65454d063a002d0980cfc1c7d2f3b8fb46bb7e3d5c4df38f7561

Description

Mssql wraps FreeTDS in a nicer and safer interface, with support for parameterized queries, thread-based async IO, and a thread pool.

Published: 28 Jan 2019

README

README.md

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

Mssql.Pool.with_pool ~host ~db ~user ~password ~max_connections:10 (fun p ->
  Mssql.Pool.with_conn p (fun db ->
    (* same as above *)))

Dependencies (11)

  1. dune-configurator
  2. dune >= "1.4"
  3. bisect_ppx build & >= "1.3.1"
  4. text >= "0.8.0"
  5. logs
  6. ounit
  7. ocaml >= "4.06.1"
  8. freetds
  9. ppx_jane < "v0.13" & < "v0.15"
  10. bignum < "v0.13" & < "v0.15"
  11. async < "v0.13" & < "v0.15"

Dev Dependencies

None

Used by

None

Conflicts

None

OCaml

Innovation. Community. Security.