StringThing.FSharp.Sqlite 2.0.3

StringThing.FSharp.Sqlite

Injection-safe interpolated SQL for SQLite via Microsoft.Data.Sqlite, F# edition. Part of StringThing.FSharp.

Install

dotnet add package StringThing.FSharp.Sqlite

The package bundles the matching analyzer DLL — installing it activates the analyzer automatically. It recovers compile-time parameter-type checking and fragment-provenance enforcement that the FormattableString-based runtime dispatch otherwise defers to runtime.

Quick start

open Microsoft.Data.Sqlite
open StringThing.FSharp
open StringThing.FSharp.Sqlite

use connection = new SqliteConnection("Data Source=:memory:")
connection.Open()

let userId = 42L
let name : string =
    connection.QueryStringSingle Row.scalar $"""
        SELECT name FROM users WHERE id = {userId}
        """

Parameters are positional (@p0, @p1, ...). The call site looks like a C#-style interpolated string, but the values stay typed at runtime — F# $"" lowers to FormattableString, which StringThing walks to bind each {value} hole to a typed SqliteParameter.

Use F#'s triple-quoted form $""" """ for multi-line SQL — the regex parser ignores whitespace, and the SQL engine ignores extra newlines.

Result mapping

Build a row reader with the row { let! ... and! ... return ... } computation expression:

type User = { Id: int64; Name: string; Email: string option }

let userRow : RowReader<User> =
    row {
        let! id    = Row.int64 "id"
        and! name  = Row.string "name"
        and! email = Row.stringOption "email"
        return { Id = id; Name = name; Email = email }
    }

let user =
    connection.QueryStringSingle userRow $"""
        SELECT id, name, email
        FROM users
        WHERE id = {userId}
        """

let users =
    connection.QueryString userRow $"""
        SELECT id, name, email
        FROM users
        ORDER BY id
        """
    |> Seq.toList

Connection methods: QueryStringSingle, QueryStringSingleOrDefault, QueryString (returns lazy seq<'T>), ExecuteString, ExecuteStringScalar.

Ordinals are resolved once per (format string, row type) pair and cached, so GetOrdinal runs at most once per call site per row type.

Scalar queries

When the query returns a single column, pass Row.scalar (or Row.scalarOption for nullable columns):

let count : int64 =
    connection.QueryStringSingle Row.scalar $"""
        SELECT COUNT(*) FROM users
        """

let email : string option =
    connection.QueryStringSingle Row.scalarOption $"""
        SELECT email FROM users WHERE id = {userId}
        """

Supported parameter types

bool, int, int64, float, string, byte[], Guid, DateTime, and 'T option for any of the above. Embedded SqliteFragment values (see below) and obj returns from Sqlite.unsafe / Sqlite.inList / Sqlite.insertRows.

Unsupported types throw InvalidOperationException at runtime. Install StringThing.FSharp.Sqlite.Analyzers to make this a compile-time error.

SqliteStatement and SqliteFragment

Two nominal wrapper types signal intent and let the analyzer prove provenance:

• SqliteStatement — a complete top-level query. Build with Sqlite.statement $"...".
• SqliteFragment — a composable piece spliced into a larger statement. Build with Sqlite.fragment $"...".

Both are aliases for the generic SqlStatement<SqliteParameter> / SqlFragment<SqliteParameter> types from StringThing.FSharp. They have op_Implicit conversions to FormattableString, so they flow naturally into connection methods that take FormattableString. The generic-on-'TParameter shape means every provider shares the same nominal types — only the parameter type differs.

let userById id =
    Sqlite.statement $"""
        SELECT id, name, email
        FROM users
        WHERE id = {id}
        """

let user = connection.QueryStringSingle userRow (userById 42L)

Suppressing the FS3391 advisory

The op_Implicit conversion from SqliteStatement / SqliteFragment to FormattableString is F# 9's "additional implicit conversions" feature. The compiler emits an advisory warning each time it fires:

warning FS3391: This expression uses the implicit conversion
'SqlStatement<SqliteParameter> -> FormattableString'.

The warning exists because implicit conversions are unusual in F# and the compiler wants you to know one happened. In this library it's expected at every call site that passes a wrapper to a connection method, so the advisory is noise. Suppress it file-wide:

#nowarn "3391"

at the top of any source file that uses these wrappers. Only suppresses the implicit-conversion advisory; doesn't change runtime behaviour.

Fragment composition

Embed a SqliteFragment value inside another $"":

let activeAdults : SqliteFragment =
    Sqlite.fragment $"age >= 18 AND active = 1"

let users =
    connection.QueryString userRow $"""
        SELECT id, name, email
        FROM users
        WHERE {activeAdults}
        """

Inline composition also works — embed a $"" directly inside another $"":

let minId = 1L
let users =
    connection.QueryString userRow $"""
        SELECT id, name, email
        FROM users
        WHERE {$"id > {minId}"}
        """

The embedded fragment's parameters renumber and splice in place automatically.

Multi-row insert

type InsertUser = { Id: int64; Name: string; Email: string option }

let insertUserRow (u: InsertUser) : SqliteFragment =
    Sqlite.fragment $"({u.Id}, {u.Name}, {u.Email}, 1)"

let users = [
    { Id = 1L; Name = "alice"; Email = Some "alice@example.com" }
    { Id = 2L; Name = "bob"; Email = None }
]

connection.ExecuteString $"""
    INSERT INTO users (id, name, email, active)
    VALUES {Sqlite.insertRows insertUserRow users}
    """
    |> ignore

IN list

let ids = [1L; 2L; 3L]

let users =
    connection.QueryString userRow $"""
        SELECT id, name, email
        FROM users
        WHERE id IN {Sqlite.inList ids}
        """
    |> Seq.toList

Unsafe escape hatch

let tableName = Sqlite.unsafe "users"
let userId = 42L

let user =
    connection.QueryStringSingle userRow $"""
        SELECT id, name, email
        FROM {tableName}
        WHERE id = {userId}
        """

Sqlite.unsafe splices raw, unparameterized SQL — the caller takes responsibility for safety.

Built by Immersus Machina