SqlHydra

Type-safe SQL generation for F#. Generate types from your database, query with strongly-typed computation expressions.

Supported Databases: SQL Server | PostgreSQL | SQLite | Oracle | MySQL

---

Quick Start

1. Install the CLI tool locally:

dotnet new tool-manifest
dotnet tool install --local SqlHydra.Cli

2. Generate types from your database:

dotnet sqlhydra mssql    # or: npgsql, sqlite, oracle, mysql

The wizard will prompt you for connection string, output file, and namespace.

3. Install the query library:

dotnet add package SqlHydra.Query

4. Configure Query Context:

SqlHydra.Cli now generates a DB‑specific QueryContextFactory for each generated database (perfect for DI injection).

Use it to create a strongly‑typed query context:

let db = AdventureWorks.QueryContextFactory.Create(connStr, printfn "SQL: %O") // Optional SQL output logging

5. Write your first query:

open SqlHydra.Query
open AdventureWorks

// Query with full type safety
let getProducts minPrice =
    selectTask db {
        for p in SalesLT.Product do
        where (p.ListPrice > minPrice)
        orderBy p.Name
        select p
    }

Note: All query builders have both Task and Async variants: selectTask/selectAsync, insertTask/insertAsync, updateTask/updateAsync, deleteTask/deleteAsync.

That's it! Your queries are now type-checked at compile time.

---

What Gets Generated?

SqlHydra.Cli reads your database schema and adds a generated file to your project that contains:

• F# record types for each table (with Option types for nullable columns)
• Table declarations for use in queries
• QueryContextFactory with a static Create(connectionString: string) method.

// Generated from your database schema:
module SalesLT =
    type Product =
        { ProductID: int
          Name: string
          ListPrice: decimal
          Color: string option }  // nullable columns become Option

    let Product = table<Product>  // table declaration for queries

---

<details> <summary><h2>SqlHydra.Cli Reference</h2></summary>

Installation

Local Install (recommended):

dotnet new tool-manifest
dotnet tool install SqlHydra.Cli

Running the CLI

dotnet sqlhydra mssql     # SQL Server
dotnet sqlhydra npgsql    # PostgreSQL
dotnet sqlhydra sqlite    # SQLite
dotnet sqlhydra oracle    # Oracle
dotnet sqlhydra mysql     # MySQL

• If no .toml config exists, a wizard will guide you through setup
• If a .toml config exists, it regenerates code using that config
• Generated .fs files are automatically added to your .fsproj as Visible="false"

Configuration Wizard

The wizard prompts for:

1. Connection String - Used to query your database schema
2. Output Filename - e.g., AdventureWorks.fs
3. Namespace - e.g., MyApp.AdventureWorks
4. Use Case:
   • SqlHydra.Query integration (default) - Generates everything needed for SqlHydra.Query
   • Other data library - Just the record types (for Dapper.FSharp, Donald, etc.)
   • Standalone - Record types + HydraReader (no SqlHydra.Query metadata)

For advanced configuration, see the TOML Configuration Reference.

Auto-Regeneration (Build Event)

To regenerate on Rebuild in Debug mode:

<Target Name="SqlHydra" BeforeTargets="Clean" Condition="'$(Configuration)' == 'Debug'">
  <Exec Command="dotnet sqlhydra mssql" />
</Target>

Multiple TOML Files

You can have multiple .toml files for different scenarios:

dotnet sqlhydra sqlite -t "shared.toml"
dotnet sqlhydra mssql -t "reporting.toml"

Useful for data migrations or generating types with different filters.

</details> <details> <summary><h2>Select Queries</h2></summary>

Basic Select

let getProducts (db: QueryContextFactory)  =
    selectTask db {
        for p in SalesLT.Product do
        select p
    }

Where Clauses

let getExpensiveProducts (db: QueryContextFactory) minPrice =
    selectTask db {
        for p in SalesLT.Product do
        where (p.ListPrice > minPrice)
        select p
    }

Where operators: | Operator | Function | Description | |----------|----------|-------------| | \|=\| | isIn | Column IN list | | \|<>\| | isNotIn | Column NOT IN list | | =% | like | LIKE pattern | | <>% | notLike | NOT LIKE pattern | | = None | isNullValue | IS NULL | | <> None | isNotNullValue | IS NOT NULL |

// Filter where City starts with 'S'
let getCitiesStartingWithS (db: QueryContextFactory)  =
    selectTask db {
        for a in SalesLT.Address do
        where (a.City =% "S%")
        select a
    }

Conditional Where (v3.0+)

Use && to conditionally include/exclude where clauses:

let getAddresses (db: QueryContextFactory) (cityFilter: string option) (zipFilter: string option) =
    selectTask db {
        for a in Person.Address do
        where (
            (cityFilter.IsSome && a.City = cityFilter.Value) &&
            (zipFilter.IsSome && a.PostalCode = zipFilter.Value)
        )
    }

If cityFilter.IsSome is false, that clause is excluded from the query.

Joins

// Inner join
let getProductsWithCategory (db: QueryContextFactory)  =
    selectTask db {
        for p in SalesLT.Product do
        join c in SalesLT.ProductCategory on (p.ProductCategoryID.Value = c.ProductCategoryID)
        select (p, c.Name)
        take 10
    }

// Left join (joined table becomes Option).
// You can use `|> Option.map` to select specifc left joined columns.
let getCustomerAddresses (db: QueryContextFactory)  =
    selectTask db {
        for c in SalesLT.Customer do
        leftJoin a in SalesLT.Address on (c.AddressID = a.Value.AddressID)
        select (
            c.Email, 
            a |> Option.map _.State
        ) into selected
        mapList (
            let email, stateMaybe = selected
            let state = stateMaybe |> Option.defaultValue "N/A"
            $"Customer: {email}, State: {state}"
        )
    }


// Improved join syntax with `join'` and `leftJoin'` lets you use full predicates in `on'` clauses.
// * Makes multi-column joins much cleaner (no need for tuple comparison).
// * Allows full predicates (e.g., AND/OR) in join conditions.
// * Optional cheeky usage of `;` if you want `on'` on the same line!
selectTask db {
    for o in Sales.SalesOrderHeader do
    join' d in Sales.SalesOrderDetail; on' (o.ID = d.OrderID && o.Status = "Completed")
    select o
}

Note: In join on clauses, put the known (left) table on the left side of the =.

Selecting Columns

// Select specific columns
let getCityStates (db: QueryContextFactory)  =
    selectTask db {
        for a in SalesLT.Address do
        select (a.City, a.StateProvince)
    }

// Transform results with mapList
let getCityLabels (db: QueryContextFactory)  =
    selectTask db {
        for a in SalesLT.Address do
        select (a.City, a.StateProvince) into (city, state)
        mapList $"City: {city}, State: {state}"
    }

Aggregates

let getCategoriesWithHighPrices (db: QueryContextFactory)  =
    selectTask db {
        for p in SalesLT.Product do
        where (p.ProductCategoryID <> None)
        groupBy p.ProductCategoryID
        having (avgBy p.ListPrice > 500M)
        select (p.ProductCategoryID, avgBy p.ListPrice)
    }

// Count
let getCustomerCount (db: QueryContextFactory)  =
    selectTask db {
        for c in SalesLT.Customer do
        count
    }

Aggregate functions: countBy, sumBy, minBy, maxBy, avgBy

Warning: If an aggregate might return NULL (e.g., minBy on an empty result set), wrap in Some:

select (minBy (Some p.ListPrice))  // Returns Option

SQL Functions

SqlHydra.Query includes built-in SQL functions for each supported database provider. These can be used in both select and where clauses.

Setup:

// Import the extension module for your database provider:
open SqlHydra.Query.SqlServerExtensions  // SQL Server
open SqlHydra.Query.NpgsqlExtensions     // PostgreSQL
open SqlHydra.Query.SqliteExtensions     // SQLite
open SqlHydra.Query.OracleExtensions     // Oracle
open SqlHydra.Query.MySqlExtensions      // MySQL

open type SqlFn  // Optional: allows unqualified access, e.g. LEN vs SqlFn.LEN

Use in select and where clauses:

// String functions
selectTask db {
    for p in Person.Person do
    where (LEN(p.FirstName) > 3)
    select (p.FirstName, LEN(p.FirstName), UPPER(p.FirstName))
}
// Generates: SELECT ... WHERE LEN([p].[FirstName]) > 3

// Null handling - ISNULL accepts Option<'T> and returns unwrapped 'T
selectTask db {
    for p in Person.Person do
    select (ISNULL(p.MiddleName, "N/A"))  // Option<string> -> string
}

// Date functions
selectTask db {
    for o in Sales.SalesOrderHeader do
    where (YEAR(o.OrderDate) = 2024)
    select (o.OrderDate, YEAR(o.OrderDate), MONTH(o.OrderDate))
}

// Compare two functions
selectTask db {
    for p in Person.Person do
    where (LEN(p.FirstName) < LEN(p.LastName))
    select (p.FirstName, p.LastName)
}

Built-in functions include string functions (LEN, UPPER, SUBSTRING, etc.), null handling (ISNULL/COALESCE with overloads for Option<'T> and Nullable<'T>), numeric functions (ABS, ROUND, etc.), and date/time functions (GETDATE, YEAR, MONTH, etc.).

See the full list