Fumble

Functional wrapper around plain old Sqlite to simplify data access when talking to SQLite databases.

Available Packages:

| Library | Version | | ------------- | ------------- | | Fumble | |

Install

# nuget client
dotnet add package Fumble

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

Query a table

open Fumble

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

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

let getUsers() : Result<User list, exn> =
    connectionString()
    |> Sql.connect
    |> Sql.query "SELECT * FROM dbo.[Users]"
    |> Sql.execute (fun read ->
        {
            Id = read.int "user_id"
            Username = read.string "username"
        })

Handle null values from table columns:

open Fumble

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

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

let getUsers() : Result<User list, exn> =
    connectionString()
    |> Sql.connect
    |> Sql.query "SELECT * FROM dbo.[users]"
    |> Sql.execute(fun read ->
        {
            Id = read.int "user_id"
            Username = read.string "username"
            // Notice here using `orNone` reader variants
            LastModified = read.dateTimeOrNone "last_modified"
        })

Providing default values for null columns:

open Fumble

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

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

let getUsers() : Result<User list, exn> =
    connectionString()
    |> Sql.connect
    |> Sql.query "select * from dbo.[users]"
    |> Sql.execute (fun read ->
        {
            Id = read.int "user_id";
            Username = read.string "username"
            Biography = defaultArg (read.stringOrNone "bio") ""
        })

Execute a parameterized query

open Fumble

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

// get product names by category
let productsByCategory (category: string) : Result<string list, exn> =
    connectionString()
    |> Sql.connect
    |> Sql.query "SELECT name FROM dbo.[Products] where category = @category"
    |> Sql.parameters [ "@category", Sql.string category ]
    |> Sql.execute (fun read -> read.string "name")

Supported Data Types

Basic Types

| F# Type | Parameter | Reader | |---------|-----------|--------| | int | Sql.int | read.int | | int16 | Sql.int16 | read.int16 | | int64 | Sql.int64 | read.int64 | | string | Sql.string | read.string | | bool | Sql.bool | read.bool | | decimal | Sql.decimal | read.decimal | | double | Sql.double | read.double | | float | Sql.double | read.float | | float32 | Sql.double | read.float32 | | Guid | Sql.uniqueidentifier | read.uniqueidentifier | | byte[] | Sql.bytes | read.bytes | | DateTime | Sql.dateTime | read.dateTime | | DateTimeOffset | Sql.dateTimeOffset | read.dateTimeOffset |

New in v2.0

| F# Type | Parameter | Reader | Storage | |---------|-----------|--------|---------| | TimeSpan | Sql.timeSpan | read.timeSpan | INTEGER (ticks) | | DateOnly | Sql.dateOnly | read.dateOnly | TEXT (yyyy-MM-dd) | | TimeOnly | Sql.timeOnly | read.timeOnly | INTEGER (ticks) | | byte | Sql.byte | read.tinyint | INTEGER | | uint32 | Sql.uint32 | read.uint32 | INTEGER | | uint64 | Sql.uint64 | read.uint64 | INTEGER |

All types have OrNone variants for nullable columns (e.g., Sql.intOrNone, read.intOrNone).

New in v2.0: Additional Features

Execute Scalar Queries

// Get a single value
let count =
    connectionString()
    |> Sql.connect
    |> Sql.query "SELECT COUNT(*) FROM Users"
    |> Sql.executeScalar<int64>
    // Returns: Result<int64 option, exn>

// Check if data exists
let exists =
    connectionString()
    |> Sql.connect
    |> Sql.query "SELECT 1 FROM Users WHERE Username = @name"
    |> Sql.parameters [ "@name", Sql.string "john" ]
    |> Sql.executeExists
    // Returns: Result<bool, exn>

// Get count directly
let userCount =
    connectionString()
    |> Sql.connect
    |> Sql.query "SELECT COUNT(*) FROM Users"
    |> Sql.executeCount
    // Returns: Result<int64, exn>

Pagination

// Using take (LIMIT)
let firstTen =
    connectionString()
    |> Sql.connect
    |> Sql.query "SELECT * FROM Users ORDER BY Id"
    |> Sql.take 10
    |> Sql.execute (fun read -> read.string "Username")

// Using paginate (LIMIT + OFFSET)
let page2 =
    connectionString()
    |> Sql.connect
    |> Sql.query "SELECT * FROM Users ORDER BY Id"
    |> Sql.paginate 2 10  // page 2, 10 items per page
    |> Sql.execute (fun read -> read.string "Username")

In-Memory Database

// Create an in-memory database
Sql.connectInMemory ()
|> Sql.query "CREATE TABLE Test (Id INTEGER PRIMARY KEY, Name TEXT)"
|> Sql.executeNonQuery

// Shared in-memory database (accessible from multiple connections)
Sql.connectInMemoryShared "mydb"
|> Sql.query "SELECT * FROM Test"
|> Sql.execute (fun read -> read.string "Name")

SQLite Pragmas and Introspection

// Get SQLite version
let version =
    connectionString()
    |> Sql.connect
    |> Sql.sqliteVersion
    // Returns: Result<string option, exn>

// List all tables
let tables =
    connectionString()
    |> Sql.connect
    |> Sql.listTables
    // Returns: Result<string list, exn>

// Get table schema info
let columns =
    connectionString()
    |> Sql.connect
    |> Sql.tableInfo "Users"
    // Returns column info with Name, Type, NotNull, DefaultValue, IsPrimaryKey

// Enable WAL mode for better concurrency
connectionString()
|> Sql.connect
|> Sql.enableWalMode

// Enable foreign keys
connectionString()
|> Sql.connect
|> Sql.enableForeignKeys

// Database maintenance
connectionString()
|> Sql.connect
|> Sql.vacuum  // Rebuild database file

connectionString()
|> Sql.connect
|> Sql.analyze  // Update statistics

Bulk Operations

// Bulk delete
connectionString()
|> Sql.connect
|> Sql.bulkDelete "Users" "Id" [1; 2; 3]
// Deletes users with Id 1, 2, or 3

// Upsert (INSERT OR REPLACE)
type User = { Id: int; Name: string; Email: string }

let users = [
    { Id = 1; Name = "John"; Email = "john@example.com" }
    { Id = 2; Name = "Jane"; Email = "jane@example.com" }
]

connectionString()
|> Sql.connect
|> Sql.upsert "Users" users
// Inserts or replaces based on primary key

Auto-generate CREATE TABLE

type User = {
    Id: int
    Username: string
    Email: string option
    CreatedAt: DateTime
}

// Creates: CREATE TABLE IF NOT EXISTS [Users]
//    ([Id] INTEGER,
//     [Username] TEXT,
//     [Email] TEXT NULL,
//     [CreatedAt] TEXT)
connectionString()
|> Sql.connect
|> Sql.commandCreate<User> "Users"
|> Sql.executeCommand

Executing a stored procedure with parameters

open Fumble

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

// check whether a user exists or not
let userExists (username: string) : Async<Result<bool, exn>> =
    async {
        return!
            connectionString()
            |> Sql.connect
            |> Sql.storedProcedure "user_exists"
            |> Sql.parameters [ "@username", Sql.string username ]
            |> Sql.execute (fun read -> read.bool 0)
            |> function
                | Ok [ result ] -> Ok result
                | Error error -> Error error
                | unexpected -> failwithf "Expected result %A"  unexpected
    }

Running Tests locally

You only need a working local Sqlite. The tests will create databases when required and dispose of them at the end of the each test.

dotnet run --project tests/Fumble.Tests/FumbleTests.fsproj