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 install --save bs-sql-commonThen add bs-sql-common to your bs-dependencies in your bsconfig.json
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 install --save 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 -> stringendSql.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 endendQueryable 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 -> unitend