Suave.IO introduction and example - Part 4: Database access

Suave.IO introduction and example - Part 4: Database access

-

This post of part of a larger series of posts about creating a small Suave.IO application and hosting it in Azure. By the end of the previous post Part 3 - Requests and routing we actually had a tiny working MVP todo-app (almost ready to start chasing investor capital!).

In this post we will change our application to allow it to persist todo-items to a database. I wasn't sure if I wanted to write a part about database access, because it isn't really specific to creating a Suave.IO app.

But then I thought, maybe that is the point?

Database access from a Suave.IO app isn't any different from database access from any other .NET / F# app you write.

It really is just a .NET application. So I decided to leave it in, but keep it short and sweet. So let's get right to it.

Adding SqlDataConnection type provider

There is a million ways to skin data access. I've opted for using a plain old SQL database (well, an Azure SQL database). The first thing we need to do is add the SqlDataConnection type provider. Let's add a couple of lines to our paket.dependencies file.

nuget FSharp.Data
nuget FSharp.Data.TypeProviders

Now when we remove the lock-file and re-run ./local.cmd we should have access to everything we need to access a database.

Set up a database

Next we create a database and add a table for storing our todo-items.

create table TodoItems
(
    TodoItemId int identity(1,1) primary key,
    Todo nvarchar(255),
);

Changing the application

Now we are ready to change our application code to store todo-items in a database rather than locally in a mutable list. Let's go ahead.

type Sql = 
    SqlDataConnection<"YOUR CONNECTION STRING">

let getDb () =
    Sql.GetDataContext()

let getTodos () =
    let db = getDb () 
    query {
        for t in db.TodoItems do
        select (t.TodoItemId, t.Todo)
    } 
    |> Seq.toList
    |> List.map (fun t -> sprintf "{ \"id\": %d, \"text\": \"%s\" }" (fst t) (snd t))
    |> String.concat ","
    |> sprintf "{ \"todos\": [ %s ] }" 

let add (text : Choice<string, string>) =
    match text with
    | Choice1Of2 t -> 
        let db = getDb ()
        let record = new Sql.ServiceTypes.TodoItems(Todo = t)
        db.TodoItems.InsertOnSubmit(record)
        db.DataContext.SubmitChanges()
        ()
    | Choice2Of2 t -> 
        ()

let remove id = 
    let db = getDb () 
    let deleteRowsFrom (table:Table<_>) rows = table.DeleteAllOnSubmit(rows)
    query {
        for t in db.TodoItems do
        where (t.TodoItemId.Equals(id))
        select t
    } |> deleteRowsFrom db.TodoItems
    db.DataContext.SubmitChanges()
    ()

There we have it. It might not be the most elegant solution in the world, but our todo application is now storing todo-items in a database. If you need to see the application code in it's entirety, remember that it is available on GitHub.

Getting it out there

Now we have a complete application, with persistence and all. We only need to get it out there to the thousands of people who desperately want to get their hands on it. To deal with the inevitable popularity and demand we should probably host our application in the cloud.

Check out the last post in the series Part 5 - Deploying to Azure for an idea of how we can achieve that.

View Comments