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.

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

scull7/bs-sql-common