lmortimer / fsharp-dapper-sqlite-example Goto Github PK

#r "nuget: System.Data.SQLite" 
#r "nuget: FSharp.Data.Dapper" 

open System.Data.SQLite
open FSharp.Data.Dapper

module Connection =
    let private mkConnectionString (dataSource : string) =
        sprintf 
            "Data Source = %s; Mode = Memory; Cache = Shared;" 
            dataSource

    let private mkOnDiskConnectionString (dataSource: string) =
        sprintf
            "Data Source = %s;"
            dataSource

    let mkShared () = new SQLiteConnection (mkConnectionString "MASTER")
    let mkOnDisk () = new SQLiteConnection (mkOnDiskConnectionString "./example.db")

module Types =

    [<CLIMutable>]
    type Bird = {
        Id : int64
        Name: string
        Alias: string option
    }

module Queries = 
    let private connectionF () = Connection.SqliteConnection (Connection.mkOnDisk())

    let querySeqAsync<'R>    = querySeqAsync<'R> (connectionF)
    let querySingleAsync<'R> = querySingleOptionAsync<'R> (connectionF)

    module Schema = 
        let CreateTables = querySingleAsync<int> {
            script """
                DROP TABLE IF EXISTS Bird;
                CREATE TABLE Bird (
                    Id INTEGER PRIMARY KEY,
                    Name VARCHAR(255) NOT NULL,
                    Alias VARCHAR(255) NULL
                );
            """
        }

    module Bird = 

        let New name alias = querySingleAsync<int> {
            script "INSERT INTO Bird (Name, Alias) VALUES (@Name, @Alias)"
            parameters (dict ["Name", box name; "Alias", box alias])
        }

        let GetSingleByName name = querySingleAsync<Types.Bird> {
            script "SELECT * FROM Bird WHERE Name = @Name LIMIT 1"
            parameters (dict ["Name", box name])
        }

        let GetAll() = querySeqAsync<Types.Bird> { script "SELECT * FROM Bird" } 

        let UpdateAliasByName name alias = querySingleAsync<int> {
            script "UPDATE Bird SET Alias = @Alias WHERE Name = @Name"
            parameters (dict ["Alias", box alias; "Name", box name])
        }

        let DeleteByName name = querySingleAsync<int> {
            script "DELETE FROM Bird WHERE Name = @Name"
            parameters (dict ["Name", box name])
        }

Queries.Schema.CreateTables
|> Async.RunSynchronously
|> ignore

let addKereru = Queries.Bird.New "Kereru" "Wood Pigeon" |> Async.RunSynchronously 
let addKea = Queries.Bird.New "Kea" None |> Async.RunSynchronously 
let addDoose = Queries.Bird.New "Doose" None |> Async.RunSynchronously 

printfn "== GetAll"
Queries.Bird.GetAll()
|> Async.RunSynchronously
|> Seq.iter (printfn "%A")

let newKea = Queries.Bird.UpdateAliasByName "Kea" "Mountain Jester" |> Async.RunSynchronously 

match Queries.Bird.GetSingleByName "Kea" |> Async.RunSynchronously with
| Some(bird) -> printfn "Kea alias is now %s" bird.Alias.Value
| None -> printfn "Kea record does not exist"

let deleteDoose = Queries.Bird.DeleteByName "Doose" |> Async.RunSynchronously 

let deleteBob = Queries.Bird.DeleteByName "Bob" |> Async.RunSynchronously 

match Queries.Bird.GetSingleByName "Doose" |> Async.RunSynchronously with
| Some(bird) -> printfn "%A" bird
| None -> printfn "Doose record does not exist"

== GetAll
{ Id = 1L
  Name = "Kereru"
  Alias = Some "Wood Pigeon" }
{ Id = 2L
  Name = "Kea"
  Alias = None }
{ Id = 3L
  Name = "Doose"
  Alias = None }
Kea alias is now Mountain Jester
Doose record does not exist
module Connection =
  val private mkConnectionString: dataSource: string -> string
  val private mkOnDiskConnectionString: dataSource: string -> string
  val mkShared: unit -> System.Data.SQLite.SQLiteConnection
  val mkOnDisk: unit -> System.Data.SQLite.SQLiteConnection
module Types =
  type Bird =
    {
      Id: int64
      Name: string
      Alias: string option
    }
module Queries =
  val private connectionF: unit -> FSharp.Data.Dapper.Connection
  val querySeqAsync<'R> :
    FSharp.Data.Dapper.QuerySeqAsyncBuilder.QuerySeqAsyncBuilder<'R>
  val querySingleAsync<'R> :
    FSharp.Data.Dapper.QuerySingleOptionAsyncBuilder.QuerySingleOptionAsyncBuilder<'R>  module Schema =
    val CreateTables: Async<int option>
  module Bird =
    val New: name: 'a -> alias: 'b -> Async<int option>
    val GetSingleByName: name: 'a -> Async<Types.Bird option>
    val GetAll:
      unit -> Async<System.Collections.Generic.IEnumerable<Types.Bird>>
    val UpdateAliasByName: name: 'a -> alias: 'b -> Async<int option>
    val DeleteByName: name: 'a -> Async<int option>
val addKereru: int option = Some 0
val addKea: int option = Some 0
val addDoose: int option = Some 0
val newKea: int option = Some 0
val deleteDoose: int option = Some 0
val deleteBob: int option = Some 0
val it: unit = ()