Awesome
Fable.SqlClient 
Fable binding for node-mssql, Microsoft SQL Server client library with an idiomatic and type-safe F# API to be used from Fable Node applications.
Installation
Install the Fable binding from Nuget
# using nuget
dotnet add package Fable.SqlClient
# or with paket
paket add Fable.SqlClient --project /path/to/project.fsproj
Install the actual node-mssql from Npm
npm install --save node-mssql
Getting started
First of all, configure the connection using SqlConfig:
open Fable.SqlClient
let connectionConfig =
[ SqlConfig.User "admin"
SqlConfig.Password "str0ngPa$$word"
SqlConfig.Host "localhost"
SqlConfig.Database "AdventuresWorks"
SqlConfog.Port 1433 ]
Sql.readRows querying a tabular result set
open Fable.SqlClient
open Fable.SqlClient.OptionWorkflow
type User = { Id: int; Name: string }
let getUsers() : Async<Option<User list>> =
async {
let! usersResult =
connectionConfig
|> Sql.connect
|> Sql.query "SELECT id, name from [dbo].[Users]"
|> Sql.readRows (fun row ->
option {
let! id = Sql.readInt "id" row
let! name = Sql.readString "name" row
return { Id = id; Name = name; }
})
match usersResult with
| Ok users -> return Some users
| Error sqlError -> return None
}
Here Sql.readRows takes a transformer function that maps every row into an Option<'t>. Because we are using the option workflow, the mapping is done safely and if either id or name is null, then the row is skipped.
If you want to handle the null values manually, then simply use let instead of let! in combination with defaultArg. For example, if you want to use an empty string as a default for the name column, then you simply write the following:
async {
let! usersResult =
connectionConfig
|> Sql.connect
|> Sql.query "SELECT id, name from [dbo].[Users]"
|> Sql.readRows (fun row ->
option {
let! id = Sql.readInt "id" row
// notice: no ! in the let binding
let name = Sql.readString "name" row
let nameOrEmpty = defaultArg name ""
return { Id = id; Name = nameOrEmpty; }
})
match usersResult with
| Ok users -> return Some users
| Error sqlError -> return None
}
Sql.readRows from a parameterized query
Queries can be parameterized with named parameters to avoid SQL injections:
open Fable.SqlClient
open Fable.SqlClient.OptionWorkflow
let userByUsername (username: string) : Async<User option> =
async {
let! results =
connectionConfig
|> Sql.connect
|> Sql.query "SELECT TOP 1 id, name from [dbo].[Users] where name = @name"
|> Sql.parameters [ SqlParam.From ("@name", username) ]
|> Sql.readRows (fun row ->
option {
let! id = Sql.readInt "id" row
let! name = Sql.readString "name" row
return { Id = id; Name = name }
})
match results with
| Ok (user :: _) -> return Some user
| _ -> return None
}
Sql.readScalar querying a scalar value
open Fable.SqlClient
let pingDatabase() : Async<DateTime option> =
async {
let! serverTime =
connectionConfig
|> Sql.connect
|> Sql.query "SELECT GETDATE()"
|> Sql.readScalar
match serverTime with
| Ok (SqlValue.Date time) -> return Some time
| _ -> return None
}
Sql.storedProcedure
executing a stored procedure with parameters
let userExists (name: string) : Async<bool> =
async {
let! exists =
connectionConfig
|> Sql.connect
|> Sql.storedProcedure "user_exists"
|> Sql.parameters [ SqlParam.From("@name", name) ]
|> Sql.readScalar
match exists with
| Ok (SqlValue.Bool value) -> return value
| _ -> return false
}
Sql.readAffectedRows
Returns the number of rows affected. For example when you execute a DELETE, INSERT or UPDATE statements, the rows affected will the ones that were deleted, inserted or updated. If you read the rows affected by a SELECT statements, the row count is returned.
/// delete events older than 2 months
let deleteOldEvents() : Async<Result<int, string>> =
async {
let! eventsDeleted =
connectionConfig
|> Sql.connect
|> Sql.query "DELETE FROM [dbo].[Events] WHERE DateAdded < @TwoMonthsAgo"
|> Sql.parameters [ SqlParam.From("@TwoMonthsAgo", DateTime.Now.AddMonths(-2)) ]
|> Sql.readAffectedRows
match eventsDeleted with
| Ok count -> return Ok count
| Error error ->
// Extract info from the SqlError
let (errType, errMsg, errStack) = error.Info()
return Error errMsg
}
Sql.readJson
Since Microsoft SQL Server supports JSON natively, you can query the database and have it return the result set as a single JSON string. Sql.readJson is a utility function that extracts the JSON from the scalar value. You can then parse the resulting serialized JSON using your favorite Json library:
async {
let! json =
connectionConfig
|> Sql.connect
|> Sql.query "SELECT id, name FROM (VALUES(42, N'Fable')) as TableName(id, name) FOR JSON PATH"
|> Sql.readJson
match json with
| Ok serialized =
let values = Json.parseAs<{| id: int; name: string |} array> serialized
let value = values.[0]
printfn "Id = %d and Name = %s" value.id value.name
| Error error ->
printfn "Something went wrong..."
}
API definitions
val Sql.readRows : ((string * SqlValue) list -> Option<'t>) -> (props: ISqlProps) -> Async<Result<'t list, SqlError>>
val Sql.readAffectedRows (props: ISqlProps) -> Async<Result<int, SqlError>>
val Sql.readJson (props: ISqlProps) -> Async<Result<string, SqlError>>
val Sql.readScalar (props: ISqlProps) -> Async<Result<SqlValue, SqlError>>
where the important types are defined as follows. The types within SqlValue are those that you can read from Sql Server.
type SqlValue =
| TinyInt of uint8
| SmallInt of int16
| Int of int
| Bool of bool
| Date of DateTime
| UniqueIdentifier of Guid
| BigInt of int64
| Decimal of decimal
| String of string
| Number of float
| Binary of byte[]
| Null
type SqlError =
| ConnectionError of message: string * stack: string
| TransactionError of message: string * stack: string
| RequestError of message: string * stack: string
| ApplicationError of message: string * stack: string
| GenericError of errorType: string * message: string * stack: string
the type ISqlProps is just a helper type that accumulate the configuration for a query using a fluent syntax
Development and Testing
The test project is in the test directory and includes integration tests. To run these tests on your local machine, you will need to setup a couple of environment variables:
SQLCLIENT_DATABASE: the name of the database to run the tests against. The tests don't require a specific database, you can just usemaster.SQLCLIENT_SERVER: the IP address of the hosting machine, if you have a local MSSQL server, thenlocal/{instance}will doSQLCLIENT_USER: the username to log in withSQLCLIENT_PASSWORD: the password of the user
After you have set up these variables, you can run the commands:
npm install
npm test
This will compile the test the project and runs tests using Mocha.
Known issues
Reading DateTimeOffset directly is not supported. It has to be converted to nvarchar first and parsed from SqlValue.String value. However, a DateTimeOffset value can still be used as a parameter value directly. The following test demonstrates what's supported:
testCaseAsync "DateTimeOffset round trip works" <| fun () ->
async {
let input = DateTimeOffset.UtcNow
let! value =
defaultConfig
|> Sql.connect
// convert the value to nvarchar
|> Sql.query "SELECT CONVERT(nvarchar(100), @DateTimeOffset) as [Value]"
|> Sql.parameters [ SqlParam.From("@DateTimeOffset", input) ]
|> Sql.readScalar
match value with
// parse the value here
| Ok (SqlValue.String serialized) ->
let deserialized = DateTimeOffset.Parse serialized
areEqual input deserialized
| otherwise -> return! failwithf "Unexpected results:\n%s" (Json.stringify otherwise)
}