bs-sql-common

A common interface for SQL-based Node.js drivers.

Why?

To provide a common interface for MySQL, PostgreSQL and sqlite implementations.

Version 3

A rewrite of the entire package to expose it as a Functor that can accept any module which implements the Queryable interface.

• Use Belt.Result for responses so to better integrate with then BuckleScript ecosystem.

• Provide response decoding and inspection functions so that the user has a consistent view into responses from any library.

• Provide an ID type that properly encodes large integers as strings.

• Provide batch inserts and queries

Status

The standard things are there and this library is being used live within several production projects.

• Query parameter substitution
• Named parameters
• Promise based interface.
• Connection pooling
• Custom Streams

Installation

Inside of a BuckleScript project:

yarn add bs-sql-common

Then add bs-sql-common to your bs-dependencies in your bsconfig.json

{
  "bs-dependencies": [ "bs-sql-common" ]
}

Then add a bs-sql-common compatible package to your repository or create your own. All of the examples use the bs-mysql2 package, here are the requirements to use that package:

yarn add bs-mysql2

{
  "bs-dependencies": [ "bs-sql-common", "bs-mysql2" ]
}

module Sql = SqlCommon.Make(MySql2)

let db = Sql.Connection.connect
  ~host="127.0.0.1"
  ~port=3306
  ~user="root"
  ()

Sql.query ~db ~sql:"SHOW DATABASES" (fun res ->
  match res with
  | Belt.Result.Error e -> raise e
  | Belt.Result.Ok select ->
    select
    |. Sql.Response.Select.flatMap (Json.Decode.dict Json.Decode.string)
    |. Belt.Array.map (fun x -> Js.dict.unsafeGet x "Database")
    |. Expect.expect
    |> Expect.toContain @@ "test"
)

Usage

Note: All of the examples use the bs-mysql2 package as the connection provider. Any other provider should have the same behavior with differing connection creation requirements.

Create a connection and customized module

The following connection and module will be use within the rest of the examples.

module Sql = SqlCommon.Make(MySql2);

let db = Sql.Connection.connect(~host="127.0.0.1", ~port=3306, ~user="root", ());

Assume the following statement occurs at the end of each example.

Sql.Connection.close(conn);

Standard Callback Interface

Standard Query Method

Sql.query(~db, ~sql="SHOW DATABASES",
  fun
  | Belt.Result.Error e => Js.log2("ERROR: ", e)
  | Belt.Result.Ok select =>
    select
    |. Sql.Response.Select.rows
    |. Js.log2("RESPONSE ROWS: ", _)
);

Sql.mutate(
  ~db,
  ~sql="INSERT INTO test (foo) VALUES (?)",
  ~params=Sql.Params.positional(Json.Encode.([|string("bar")|] |. array)),
  (res) =>
    fun
    | Belt.Result.Error => Js.log2("ERROR: ", e)
    | Belt.Result.Ok mutation =>
      mutation
      |. Sql.Response.Mutation.insertId
      |. Js.log2("INSERT ID: ", _)
);

Prepared Statements - Named Placeholders

let json = Sql.Params.named(
  Json.Encode.(object_([
  ("x", int(1)),
  ("y", int(2)),
  ]))
));

let decoder = Json.Encode.array(Json.Encode.int)

Sql.query(~db, ~sql:"SELECT :x + :y AS z", ~params, (res) =>
  switch res {
  | Belt.Result.Error => Js.log2("ERROR: ", e)
  | Belt.Result.Ok select =>
    select
    |. Sql.Response.flatMap(decoder)
    |. Js.log2("DECODED ROWS: ", _)
  }
);

Sql.mutate(~db, ~sql:"INSERT INTO test (foo, bar) VALUES (:x, :y)", ~params, (res) =>
  switch res {
  | Belt.Result.Error => Js.log2("ERROR: ", e)
  | Belt.Result.Ok mutation =>
    mutation
    |. Sql.Response.Mutation.insertId
    |. Js.log2("INSERT ID: ", _)
  }
);

Prepared Statements - Positional Placeholders

let params = Sql.Params.positional(
  Json.Encode.(array(int, [|5,6|]))
));

Sql.query(~db, ~sql:"SELECT 1 + ? + ? AS result", ~params, (res) =>
  switch res {
  | Belt.Result.Error => Js.log2("ERROR: ", e)
  | Belt.Result.Ok select =>
    select
    |. Sql.Response.rows
    |. Js.log2("RAW ROWS: ", _)
  }
);

Sql.mutate(~db, ~sql:"INSERT INTO test (foo, bar) VALUES (?, ?)", ~params, (res) =>
  switch res {
  | Belt.Result.Error => Js.log2("ERROR: ", e)
  | Belt.Result.Ok mutation =>
    mutation
    |. Sql.Response.Mutation.insertId
    |. Js.log2("INSERT ID: ", _)
  }
);

Promise Interface

let params = Sql.Params.positional(
  Json.Encode.(array(int, [|"%schema"|]))
));

Sql.query(~db, ~params, ~sql="SELECT ? AS search")
|> Js.Promise.then_(select =>
  select
  |. Sql.Response.rows
  |. Js.log2("RAW ROWS: ", _)
  |. ignore
)
|> Js.Promise.catch(err =>
  Js.log2("Failure!!!", err)
  |. ignore
)

Sql.Id

module Id: sig
  type t = Driver.Id.t

  val fromJson : Js.Json.t -> Driver.Id.t

  val toJson : Driver.Id.t -> Js.Json.t

  val toString : Driver.Id.t -> string
end

Sql.Response

module Response: sig
  module Mutation: sig
    val insertId : Driver.Mutation.t -> Id.t option

    val affectedRows: Driver.Mutation.t -> int
  end

  module Select: sig
    module Meta : sig
      val schema : Driver.Select.Meta.t -> string

      val name : Driver.Select.Meta.t -> string

      val table : Driver.Select.Meta.t -> string
    end

    val meta : Driver.Select.t -> Driver.Select.Meta.t array

    val concat : Driver.Select.t -> Driver.Select.t -> Driver.Select.t

    val count : Driver.Select.t -> int

    val flatMap :
      Driver.Select.t ->
      (Js.Json.t -> Driver.Select.Meta.t array -> 'a) ->
      'a array

    val flatMap : Driver.Select.t -> (Js.Json.t -> 'a) -> 'a array

    val rows : Driver.Select.t -> Js.Json.t array
  end
end

Queryable Interface

module type Queryable = sig
  module Connection : sig
    type t

    val connect :
      ?host:string ->
      ?port:int ->
      ?user:string ->
      ?password:string ->
      ?database:string ->
      unit -> t

    val close : t -> unit
  end

  module Exn : sig
    val fromJs : Js.Json.t -> exn
  end

  module Id : sig
    type t

    val fromJson : Js.Json.t -> t

    val toJson : t -> Js.Json.t

    val toString : t -> string
  end

  module Mutation : sig
    type t

    val insertId : t -> Id.t option

    val affectedRows : t -> int
  end

  module Params : sig
    type t

    val named : Js.Json.t -> t

    val positional : Js.Json.t -> t
  end

  module Select : sig
    type t

    module Meta : sig
      type t

      val schema : t -> string

      val name : t -> string

      val table : t -> string
    end

    val meta : t -> Meta.t array

    val concat : t -> t -> t

    val count : t -> int

    val flatMapWithMeta : t -> (Js.Json.t -> Meta.t array -> 'a) -> 'a array

    val flatMap : t -> (Js.Json.t -> 'a) -> 'a array

    val rows : t -> Js.Json.t array
  end

  type response =
    [
    | `Error of exn
    | `Mutation of Mutation.t
    | `Select of Select.t
    ]

  type callback = response -> unit

  val execute : Connection.t -> string -> Params.t option -> callback -> unit
end