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
1. Install the CLI tool:
dotnet new tool-manifest
dotnet tool install SqlHydra.Cli2. Generate types from your database:
dotnet sqlhydra mssql # or: npgsql, sqlite, oracle, mysqlThe wizard will prompt you for connection string, output file, and namespace.
3. Install the query library:
dotnet add package SqlHydra.Query4. Write your first query:
open MyApp.AdventureWorks // Your generated namespace
open MyApp.AdventureWorks.HydraBuilders
let openContext () =
let compiler = SqlKata.Compilers.SqlServerCompiler()
let conn = new SqlConnection("your connection string")
conn.Open()
new QueryContext(conn, compiler)
// Query with full type safety
let getProducts minPrice =
selectTask openContext {
for p in SalesLT.Product do
where (p.ListPrice > minPrice)
orderBy p.Name
select p
}Note: All query builders have both
TaskandAsyncvariants:selectTask/selectAsync,insertTask/insertAsync,updateTask/updateAsync,deleteTask/deleteAsync.
That's it! Your queries are now type-checked at compile time.
SqlHydra.Cli reads your database schema and generates:
- F# record types for each table (with Option types for nullable columns)
- Table declarations for use in queries
- HydraReader for efficiently reading query results
// 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 queriesLocal Install (recommended):
dotnet new tool-manifest
dotnet tool install SqlHydra.Clidotnet sqlhydra mssql # SQL Server
dotnet sqlhydra npgsql # PostgreSQL
dotnet sqlhydra sqlite # SQLite
dotnet sqlhydra oracle # Oracle
dotnet sqlhydra mysql # MySQL- If no
.tomlconfig exists, a wizard will guide you through setup - If a
.tomlconfig exists, it regenerates code using that config - Generated
.fsfiles are automatically added to your.fsprojasVisible="false"
The wizard prompts for:
- Connection String - Used to query your database schema
- Output Filename - e.g.,
AdventureWorks.fs - Namespace - e.g.,
MyApp.AdventureWorks - 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.
To regenerate on Rebuild in Debug mode:
<Target Name="SqlHydra" BeforeTargets="Clean" Condition="'$(Configuration)' == 'Debug'">
<Exec Command="dotnet sqlhydra mssql" />
</Target>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.
let getProducts () =
selectTask openContext {
for p in SalesLT.Product do
select p
}let getExpensiveProducts minPrice =
selectTask openContext {
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 () =
selectTask openContext {
for a in SalesLT.Address do
where (a.City =% "S%")
select a
}Use && to conditionally include/exclude where clauses:
let getAddresses (cityFilter: string option) (zipFilter: string option) =
selectTask openContext {
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.
// Inner join
let getProductsWithCategory () =
selectTask openContext {
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)
let getCustomerAddresses () =
selectTask openContext {
for c in SalesLT.Customer do
leftJoin a in SalesLT.Address on (c.AddressID = a.Value.AddressID)
select (c, a)
}Note: In join
onclauses, put the known (left) table on the left side of the=.
// Select specific columns
let getCityStates () =
selectTask openContext {
for a in SalesLT.Address do
select (a.City, a.StateProvince)
}
// Transform results with mapList
let getCityLabels () =
selectTask openContext {
for a in SalesLT.Address do
select (a.City, a.StateProvince) into (city, state)
mapList $"City: {city}, State: {state}"
}let getCategoriesWithHighPrices () =
selectTask openContext {
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 () =
selectTask openContext {
for c in SalesLT.Customer do
count
}Aggregate functions: countBy, sumBy, minBy, maxBy, avgBy
Warning: If an aggregate might return NULL (e.g.,
minByon an empty result set), wrap inSome:select (minBy (Some p.ListPrice)) // Returns Option
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.LENUse in select and where clauses:
// String functions
selectTask openContext {
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 openContext {
for p in Person.Person do
select (ISNULL(p.MiddleName, "N/A")) // Option<string> -> string
}
// Date functions
selectTask openContext {
for o in Sales.SalesOrderHeader do
where (YEAR(o.OrderDate) = 2024)
select (o.OrderDate, YEAR(o.OrderDate), MONTH(o.OrderDate))
}
// Compare two functions
selectTask openContext {
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 for each provider:
Define custom functions:
You can easily define your own SQL function wrappers using the sqlFn helper:
// Define a wrapper - the function name becomes the SQL function name
let SOUNDEX (s: string) : string = sqlFn
let DIFFERENCE (s1: string, s2: string) : int = sqlFn
// Use in queries
selectTask openContext {
for p in Person.Person do
where (SOUNDEX(p.LastName) = SOUNDEX("Smith"))
select p.LastName
}Note: The
sqlFnhelper returnsUnchecked.defaultof<'Return>- the function is never executed at runtime. The expression visitor translates the function name and arguments to SQL. If you use an invalid function name, you'll get a database error at runtime.
// Subquery returning multiple values
let top5Categories =
select {
for p in SalesLT.Product do
groupBy p.ProductCategoryID
orderByDescending (avgBy p.ListPrice)
select p.ProductCategoryID
take 5
}
let getTopCategoryNames () =
selectTask openContext {
for c in SalesLT.ProductCategory do
where (Some c.ProductCategoryID |=| subqueryMany top5Categories)
select c.Name
}
// Subquery returning single value
let avgPrice =
select {
for p in SalesLT.Product do
select (avgBy p.ListPrice)
}
let getAboveAverageProducts () =
selectTask openContext {
for p in SalesLT.Product do
where (p.ListPrice > subqueryOne avgPrice)
select p
}// Ordering
selectTask openContext {
for p in SalesLT.Product do
orderBy p.Name
thenByDescending p.ListPrice
select p
}
// Conditional ordering with ^^
let getAddresses (sortByCity: bool) =
selectTask openContext {
for a in Person.Address do
orderBy (sortByCity ^^ a.City)
select a
}
// Pagination
selectTask openContext {
for p in SalesLT.Product do
skip 10
take 20
select p
}
// Distinct
selectTask openContext {
for c in SalesLT.Customer do
select (c.FirstName, c.LastName)
distinct
}
// Get single/optional result
selectTask openContext {
for p in SalesLT.Product do
where (p.ProductID = 123)
select p
tryHead // Returns Option
}The select clause only supports selecting columns/tables - not transformations like .ToString() or string interpolation.
Correct: Transform in mapList/mapArray/mapSeq:
selectTask openContext {
for a in SalesLT.Address do
select (a.City, a.StateProvince) into (city, state)
mapList $"City: {city}, State: {state}"
}Incorrect: Transforming in select throws at runtime:
// DON'T DO THIS - will throw!
selectTask openContext {
for a in SalesLT.Address do
select ($"City: {a.City}")
}// Simple insert
let! rowsInserted =
insertTask openContext {
into dbo.Person
entity { ID = Guid.NewGuid(); FirstName = "John"; LastName = "Doe" }
}
// Insert with identity column
let! newId =
insertTask openContext {
for e in dbo.ErrorLog do
entity { ErrorLogID = 0; ErrorMessage = "Test"; (* ... *) }
getId e.ErrorLogID // Returns the generated ID
}
// Multiple inserts
match items |> AtLeastOne.tryCreate with
| Some items ->
insertTask openContext {
into dbo.Product
entities items
}
| None ->
printfn "Nothing to insert"open SqlHydra.Query.NpgsqlExtensions // or SqliteExtensions
insertTask openContext {
for a in Person.Address do
entity address
onConflictDoUpdate a.AddressID (a.City, a.PostalCode, a.ModifiedDate)
}// Update specific fields
updateTask openContext {
for e in dbo.ErrorLog do
set e.ErrorMessage "Updated message"
set e.ErrorNumber 500
where (e.ErrorLogID = 1)
}
// Update entire entity
updateTask openContext {
for e in dbo.ErrorLog do
entity errorLog
excludeColumn e.ErrorLogID // Don't update the ID
where (e.ErrorLogID = errorLog.ErrorLogID)
}
// Update all rows (requires explicit opt-in)
updateTask openContext {
for c in Sales.Customer do
set c.AccountNumber "123"
updateAll
}deleteTask openContext {
for e in dbo.ErrorLog do
where (e.ErrorLogID = 5)
}
// Delete all rows (requires explicit opt-in)
deleteTask openContext {
for c in Sales.Customer do
deleteAll
}let getUserWithOrders email = task {
use ctx = openContext()
let! user = selectTask ctx {
for u in dbo.Users do
where (u.Email = email)
tryHead
}
let! orders = selectTask ctx {
for o in dbo.Orders do
where (o.CustomerEmail = email)
select o
}
return (user, orders)
}For operations not directly supported, use the kata operation:
select {
for c in main.Customer do
where (c.FirstName = "John")
kata (fun query ->
query.OrderByRaw("LastName COLLATE NOCASE")
)
}let getTop10Products (conn: SqlConnection) = task {
let sql = "SELECT TOP 10 * FROM Product"
use cmd = new SqlCommand(sql, conn)
use! reader = cmd.ExecuteReaderAsync()
let hydra = HydraReader(reader)
return [
while reader.Read() do
hydra.``dbo.Product``.Read()
]
}open SqlHydra.Query.SqlServerExtensions
let! (created, updated) =
insertTask openContext {
for p in dbo.Person do
entity person
output (p.CreateDate, p.UpdateDate)
}Enum Types: Postgres enums are generated as CLR enums. Register them with Npgsql:
let dataSource =
let builder = NpgsqlDataSourceBuilder("connection string")
builder.MapEnum<ext.mood>("ext.mood") |> ignore
builder.Build()Arrays: text[] and integer[] column types are supported.
SQLite uses type affinity. Use standard type aliases in your schema for proper .NET type mapping. See: SQLite Type Affinity
If you get SSL certificate errors, append ;TrustServerCertificate=True to your connection string.
(Fixed in Microsoft.Data.SqlClient v4.1.1+)
- .NET 8, .NET 9, and .NET 10 are supported
- For .NET 5 support, use the older provider-specific tools (
SqlHydra.SqlServer, etc.)
- Uses VS Code Remote Containers for dev environment with test databases
- Or run
docker-composemanually with your IDE - See Contributing Wiki
💻 |
💻 |
💻 |
💻 |
💻 |
💻 |
💻 |
💻 |
💻 |
💻 |