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
{
"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 install --save 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
endSql.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
endQueryable 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
