Thin F# API for SqlClient for easy data access to ms sql server with functional seasoning on top
Branch: master
Clone or download
Fetching latest commit…
Cannot retrieve the latest commit at this time.
Permalink
Type Name Latest commit message Commit time
Failed to load latest commit information.
.github
.paket
docs/coverage
src/DustyTables Bump version to 0.5.0 Feb 6, 2019
tests/DustyTables.Tests
tools
.editorconfig
.gitattributes
.gitignore
.travis.yml Initial commit Jan 29, 2019
DustyTables.sln
LICENSE.md Initial commit Jan 29, 2019
README.md
RELEASE_NOTES.md Bump version to 0.5.0 Feb 6, 2019
appveyor.yml Initial commit Jan 29, 2019
build.cmd Initial commit Jan 29, 2019
build.fsx Bump version to 0.4.0 Feb 6, 2019
build.sh Update build script permission, run dotnet build on CI Jan 30, 2019
fsc.props Initial commit Jan 29, 2019
netfx.props Initial commit Jan 29, 2019
paket.dependencies Initial commit Jan 29, 2019
paket.lock Initial commit Jan 29, 2019

README.md

DustyTables

Functional wrapper around plain old (dusty?) SqlClient to simplify data access when talking to MS Sql Server databases.

Install

# nuget client
dotnet add package DustyTables

# or using paket
.paket/paket.exe add DustyTables --project path/to/project.fsproj

Query a table

open DustyTables
open DustyTables.OptionWorkflow

// get the connection from the environment
let connectionString() = Env.getVar "app_db"

type User = { Id: int; Username: string }

let getUsers() : User list = 
    connectionString()
    |> Sql.connect
    |> Sql.query "select * from dbo.[users]"
    |> Sql.executeTable 
    |> Sql.mapEachRow (fun row -> 
        option {
            let! id = Sql.readInt "user_id" row
            let! username = Sql.readString "username" row
            return { Id = id; Username = username }
        })

Notice that we are using the option workflow which means if any row has "user_id" or "username" as NULL it will be skipped

Handle null values from table columns:

open DustyTables
open DustyTables.OptionWorkflow

// get the connection from the environment
let connectionString() = Env.getVar "app_db"

type User = { Id: int; Username: string; LastModified : Option<DateTime> }

let getUsers() : User list = 
    connectionString()
    |> Sql.connect
    |> Sql.query "select * from dbo.[users]"
    |> Sql.executeTable 
    |> Sql.mapEachRow (fun row -> 
        option {
            let! id = Sql.readInt "user_id" row
            let! username = Sql.readString "username" row
            // using "let" instead of "let!"
            let lastModified = Sql.readDateTime "last_modified" row
            return { 
                Id = id; 
                Username = username
                LastModified = lastModified  
            }
        })

Providing default values for null columns:

open DustyTables
open DustyTables.OptionWorkflow

// get the connection from the environment
let connectionString() = Env.getVar "app_db"

type User = { Id: int; Username: string; Biography : string }

let getUsers() : User list = 
    connectionString()
    |> Sql.connect
    |> Sql.query "select * from dbo.[users]"
    |> Sql.executeTable 
    |> Sql.mapEachRow (fun row -> 
        option {
            let! id = Sql.readInt "user_id" row
            let! username = Sql.readString "username" row
            let userBiography = Sql.readString "bio" row
            return { 
                Id = id; 
                Username = username
                Biography = defaultArg userBiography ""
            }
        })

Query a scalar value safely:

open DustyTables
open DustyTables.OptionWorkflow

// get the connection from the environment
let connectionString() = Env.getVar "app_db"

let pingDatabase() : Option<DateTime> = 
    connectionString()
    |> Sql.connect
    |> Sql.query "select getdate()"
    |> Sql.executeScalarSafe 
    |> function 
        | Ok (SqlValue.DateTime time) -> Some time
        | otherwise -> None

Query a scalar value asynchronously

open DustyTables
open DustyTables.OptionWorkflow

// get the connection from the environment
let connectionString() = Env.getVar "app_db"

let pingDatabase() : Async<Option<DateTime>> = 
    async {
        let! serverTime = 
            connectionString()
            |> Sql.connect
            |> Sql.query "select getdate()"
            |> Sql.executeScalarSafeAsync
        
        match serverTime with 
        | Ok (SqlValue.DateTime time) -> return Some time
        | otherwise -> return None
    }

Execute a parameterized query

open DustyTables
open DustyTables.OptionWorkflow

// get the connection from the environment
let connectionString() = Env.getVar "app_db"

// get product names by category
let productsByCategory (category: string) : string list = 
    connectionString()
    |> Sql.connect
    |> Sql.query "select name from dbo.[products] where category = @category"
    |> Sql.parameters [ "@category", SqlValue.String category ]
    |> Sql.executeTable
    |> Sql.mapEachRow (Sql.readString "name")

Executing a stored procedure with parameters

open DustyTables

// get the connection from the environment
let connectionString() = Env.getVar "app_db"

// check whether a user exists or not
let userExists (username: string) : Async<bool> = 
    async {
        let! userExists = 
            connectionString()
            |> Sql.connect
            |> Sql.storedProcedure "user_exists"
            |> Sql.parameters [ "@username", SqlValue.String username ]
            |> Sql.executeScalarAsync 
        
        return Sql.toBool userExists 
    }

Running Tests locally

You only need a connection string to a working database, no tables/stored procedures/anything is requires. Just set environment variable DUSTY_TABLES_DB To your connection string and run the tests

Builds

MacOS/Linux Windows
Travis Badge Build status
Build History Build History

Nuget

Stable Prerelease
NuGet Badge NuGet Badge

Building

Make sure the following requirements are installed in your system:

> build.cmd // on windows
$ ./build.sh  // on unix

Environment Variables

  • CONFIGURATION will set the configuration of the dotnet commands. If not set it will default to Release.
    • CONFIGURATION=Debug ./build.sh will result in things like dotnet build -c Debug
  • GITHUB_TOKEN will be used to upload release notes and nuget packages to github.
    • Be sure to set this before releasing

Watch Tests

The WatchTests target will use dotnet-watch to watch for changes in your lib or tests and re-run your tests on all TargetFrameworks

./build.sh WatchTests

Releasing

git add .
git commit -m "Scaffold"
git remote add origin origin https://github.com/user/MyCoolNewLib.git
git push -u origin master
paket config add-token "https://www.nuget.org" 4003d786-cc37-4004-bfdf-c4f3e8ef9b3a
  • Create a GitHub OAuth Token

    • You can then set the GITHUB_TOKEN to upload release notes and artifacts to github
    • Otherwise it will fallback to username/password
  • Then update the RELEASE_NOTES.md with a new version, date, and release notes ReleaseNotesHelper

#### 0.2.0 - 2017-04-20
* FEATURE: Does cool stuff!
* BUGFIX: Fixes that silly oversight
  • You can then use the Release target. This will:
    • make a commit bumping the version: Bump version to 0.2.0 and add the release notes to the commit
    • publish the package to nuget
    • push a git tag
./build.sh Release

Code formatting

To format code run the following target

./build.sh FormatCode

This uses Fantomas to do code formatting. Please report code formatting bugs to that repository.