SqlHydra
SqlHydra is a suite of NuGet packages for working with databases in F#.
Generation Tools
- SqlHydra.SqlServer is a dotnet tool that generates F# records for a SQL Server database.
- SqlHydra.Npgsql is a dotnet tool that generates F# records for a PostgreSQL database
- SqlHydra.Sqlite is a dotnet tool that generates F# records for a SQLite database.
Query Library
- SqlHydra.Query is an F# query generator computation expression powered by SqlKata.
- SqlHydra.Query is specifically designed to take advantage of SqlHydra generated types. (If you don't want to use generated types, then I would recommend checking out Dapper.FSharp instead.)
- SqlKata officially supports SQL Server, SQLite, PostgreSql, MySql, Oracle and Firebird; however, SqlHydra.Query does not yet have generators for MySql, Oracle and Firebird. Please submit an issue if you are interested in contributing a generator for one of these!
Notes
- The generation libraries can be useful with any query library for creating strongly typed table records and data readers.
- The SqlHydra.Query library is designed to work in conjunction with one of the generation libraries.
- All SqlHydra NuGet packages will be released with matching major and minor version numbers.
Contributing
- This project uses the vs-code Remote-Containers extension to spin up a dev environment that includes databases for running the Tests project.
- To initialize SQL Server after the
mssql
container spins up, open the CLI and runbash install.sh
.
Local Install (recommended)
Run the following commands from your project directory:
dotnet new tool-manifest
dotnet tool install SqlHydra.SqlServer
Configure and Run
Run the tool from the command line (or add to a .bat|.cmd|.sh file):
dotnet sqlhydra-mssql
- The configuration wizard will ask you some questions, create a new .toml configuration file for you, and then run your new config.
- If a .toml configuration file already exists, it will run.
- The generated .fs file will automatically be added to your .fsproj as
Visible="false"
.
Build Event (optional)
To regenerate after a Rebuild, you can run SqlHydra from an fsproj build event:
<Target Name="SqlHydra" BeforeTargets="Clean">
<Exec Command="dotnet sqlhydra-mssql" />
</Target>
Local Install (recommended)
Run the following commands from your project directory:
dotnet new tool-manifest
dotnet tool install SqlHydra.Npgsql
Configure / Run
Run the tool from the command line (or add to a .bat|.cmd|.sh file):
dotnet sqlhydra-npgsql
- The configuration wizard will ask you some questions, create a new .toml configuration file for you, and then run your new config.
- If a .toml configuration file already exists, it will run.
- The generated .fs file will automatically be added to your .fsproj as
Visible="false"
.
Build Event (optional)
To regenerate after a Rebuild, you can run SqlHydra from an fsproj build event:
<Target Name="SqlHydra" BeforeTargets="Clean">
<Exec Command="dotnet sqlhydra-npgsql" />
</Target>
Local Install (recommended)
Run the following commands from your project directory:
dotnet new tool-manifest
dotnet tool install SqlHydra.Sqlite
Configure / Run
Run the tool from the command line (or add to a .bat|.cmd|.sh file):
dotnet sqlhydra-sqlite
- The configuration wizard will ask you some questions, create a new .toml configuration file for you, and then run your new config.
- If a .toml configuration file already exists, it will run.
- The generated .fs file will automatically be added to your .fsproj as
Visible="false"
.
Build Event (optional)
To regenerate after a Rebuild, you can run SqlHydra from an fsproj build event:
<Target Name="SqlHydra" BeforeTargets="Clean">
<Exec Command="dotnet sqlhydra-sqlite" />
</Target>
Example Output for AdventureWorks
// This code was generated by SqlHydra.SqlServer.
namespace SampleApp.AdventureWorks
module dbo =
type ErrorLog =
{ ErrorLogID: int
ErrorTime: System.DateTime
UserName: string
ErrorNumber: int
ErrorMessage: string
ErrorSeverity: Option<int>
ErrorState: Option<int>
ErrorProcedure: Option<string>
ErrorLine: Option<int> }
type BuildVersion =
{ SystemInformationID: byte
``Database Version``: string
VersionDate: System.DateTime
ModifiedDate: System.DateTime }
module SalesLT =
type Address =
{ City: string
StateProvince: string
CountryRegion: string
PostalCode: string
rowguid: System.Guid
ModifiedDate: System.DateTime
AddressID: int
AddressLine1: string
AddressLine2: Option<string> }
type Customer =
{ LastName: string
PasswordHash: string
PasswordSalt: string
rowguid: System.Guid
ModifiedDate: System.DateTime
CustomerID: int
NameStyle: bool
FirstName: string
MiddleName: Option<string>
Title: Option<string>
Suffix: Option<string>
CompanyName: Option<string>
SalesPerson: Option<string>
EmailAddress: Option<string>
Phone: Option<string> }
// etc...
Strongly Type Data Readers
The generated HydraReader
class works in tandem with SqlHydra.Query for reading queried entities, but it can also be used on its own with any query library that returns an IDataReader.
- Using HydraReader automatically with SqlHydra.Query
- Using HydraReader manually with other query libraries
TOML Configuration Reference
SqlHydra.Query wraps the powerful SqlKata query generator with F# computation expression builders for strongly typed query generation. It can create queries for the following databases: SQL Server, SQLite, PostgreSql, MySql, Oracle, Firebird. SqlHydra.Query can be used with any library that accepts a data reader; however, is designed pair well with SqlHydra generated records and readers!
Setup
open SqlHydra.Query
// Tables
let customerTable = table<SalesLT.Customer> |> inSchema (nameof SalesLT)
let customerAddressTable = table<SalesLT.CustomerAddress> |> inSchema (nameof SalesLT)
let addressTable = table<SalesLT.Address> |> inSchema (nameof SalesLT)
let productTable = table<SalesLT.Product> |> inSchema (nameof SalesLT)
let categoryTable = table<SalesLT.ProductCategory> |> inSchema (nameof SalesLT)
let errorLogTable = table<dbo.ErrorLog>
/// Opens a connection and creates a QueryContext that will generate SQL Server dialect queries
let openContext() =
let compiler = SqlKata.Compilers.SqlServerCompiler()
let conn = openConnection()
new QueryContext(conn, compiler)
Select Builder
The following select queries will use the HydraReader.Read
method generated by SqlHydra.*
when the Readers option is selected.
HydraReader.Read
infers the type generated by the query and uses the generated reader to hydrate the queried entities.
Selecting city and state columns only:
use ctx = openContext()
let cities =
select {
for a in addressTable do
where (a.City = "Seattle")
select (a.City, a.StateProvince)
}
|> ctx.Read HydraReader.Read
|> List.map (fun (city, state) -> $"City, State: %s{city}, %s{state}")
Special where
filter operators:
isIn
or|=|
isNotIn
or|<>|
like
or=%
notLike
or<>%
isNullValue
or= None
isNotNullValue
or<> None
subqueryMany
subqueryOne
Select Address
entities where City starts with S
:
let addresses =
select {
for a in addressTable do
where (a.City =% "S%")
}
|> ctx.Read HydraReader.Read
Joins
Select top 10 Product
entities with inner joined category name:
let! productsWithCategory =
select {
for p in productTable do
join c in categoryTable on (p.ProductCategoryID.Value = c.ProductCategoryID)
select (p, c.Name)
take 10
}
|> ctx.ReadAsync HydraReader.Read
Select Customer
with left joined Address
where CustomerID
is in a list of values:
(Note that left joined tables will be of type 'T option
, so you will need to use the .Value
property to access join columns.)
let! customerAddresses =
select {
for c in customerTable do
leftJoin ca in customerAddressTable on (c.CustomerID = ca.Value.CustomerID)
leftJoin a in addressTable on (ca.Value.AddressID = a.Value.AddressID)
where (c.CustomerID |=| [1;2;30018;29545]) // two without address, two with address
orderBy c.CustomerID
select (c, a)
}
|> ctx.ReadAsync HydraReader.Read
To perform a join with multi-columns, use tuples:
select {
for o in orderHeaderTable do
join d in orderDetailTable on ((o.SalesOrderID, o.ModifiedDate) = (d.SalesOrderID, d.ModifiedDate))
select o
}
Aggregates
Aggregate functions (can be used in select
, having
and orderBy
clauses):
countBy
sumBy
minBy
maxBy
avgBy
// Select categories with an avg product price > 500 and < 1000
select {
for p in productTable do
where (p.ProductCategoryID <> None)
groupBy p.ProductCategoryID
having (minBy p.ListPrice > 500M && maxBy p.ListPrice < 1000M)
select (p.ProductCategoryID, minBy p.ListPrice, maxBy p.ListPrice)
}
|> ctx.Read HydraReader.Read
|> Seq.map (fun (catId, minPrice, maxPrice) -> $"CatID: {catId}, MinPrice: {minPrice}, MaxPrice: {maxPrice}")
|> Seq.iter (printfn "%s")
Alternative Row Count Query:
let! customersWithNoSalesPersonCount =
select {
for c in customerTable do
where (c.SalesPerson = None)
count
}
|> ctx.CountAsync
WHERE Subqueries
Use the subqueryMany
function for subqueries that return multiple rows for comparison:
// Create a subquery that gets top 5 avg prices by category ID:
let top5CategoryIdsWithHighestAvgPrices =
select {
for p in productTable do
where (p.ProductCategoryID <> None)
groupBy p.ProductCategoryID
orderByDescending (avgBy p.ListPrice)
select p.ProductCategoryID
take 5
}
// Get category names where the category ID is "IN" the subquery:
let top5Categories =
select {
for c in categoryTable do
where (Some c.ProductCategoryID |=| subqueryMany top5CategoryIdsWithHighestAvgPrices)
select c.Name
}
|> ctx.ReadAsync HydraReader.Read
Use the subqueryOne
function for subqueries that return a single value for comparison:
// Create a subquery that gets the avg list price (a single value):
let avgListPrice =
select {
for p in productTable do
select (avgBy p.ListPrice)
}
// Get products with a price > the average price
let productsWithAboveAveragePrice =
select {
for p in productTable do
where (p.ListPrice > subqueryOne avgListPrice)
select (p.Name, p.ListPrice)
}
|> ctx.ReadAsync HydraReader.Read
Distinct Query:
let! distinctCustomerNames =
select {
for c in customerTable do
select (c.FirstName, c.LastName)
distinct
}
|> ctx.ReadAsync HydraReader.Read
Dos and Don'ts
💥 The select
clause currently only supports tables and fields for the sake of modifying the generated SQL query and the returned query type 'T
.
Transformations (i.e. .ToString()
or calling any functions is not supported and will throw an exception.
💥 The where
clause will automatically parameterize your input values. However, similar to the select
clause, the where
clause does not support calling an transformations (i.e. .ToString()
). So you must prepare any parameter transformations before the builder.
✔️ CORRECT:
let city = getCity() // DO prepare where parameters above and then pass into the where clause
let cities =
select {
for a in addressTable do
where (a.City = city)
select (a.City, a.StateProvince)
}
|> ctx.Read HydraReader.Read
|> List.map (fun (city, state) -> $"City: %s{city}, State: %s{state}") // DO transforms after data is queried
❌ INCORRECT:
let cities =
select {
for a in addressTable do
where (a.City = getCity()) // DO NOT perform calculations or translations within the builder
select ("City: " + a.City, "State: " + a.StateProvince) // DO NOT perform translations within the builder
}
|> ctx.Read HydraReader.Read
|> List.map (fun (city, state) -> $"%s{city}, %s{state}")
Insert Builder
Simple Inserts
For simple inserts with no identity column and no included/excluded columns, use the into _
syntax:
let rowsInserted =
insert {
into personTable
entity
{
dbo.Person.ID = Guid.NewGuid()
dbo.Person.FirstName = "Bojack"
dbo.Person.LastName = "Horseman"
dbo.Person.LastUpdated = DateTime.Now
}
}
|> ctx.Insert
printfn "Rows inserted: %i" rowsInserted
Insert with an Identity Field
If you have an Identity column or if you want to specify columns to include/exclude, use the for _ in _ do
syntax.
By default, all record fields will be included as insert values, so when using an identity column, you must handle it in one of two ways:
- Mark it with
getId
. This will prevent it from being added as an insert value, and it will also select and return the identity field. - Mark it with
excludeColumn
to prevent it from being added as an insert value.
let errorLogID =
insert {
for e in errorLogTable do
entity
{
dbo.ErrorLog.ErrorLogID = 0 // Adding `getId` below will ignore this value.
dbo.ErrorLog.ErrorTime = System.DateTime.Now
dbo.ErrorLog.ErrorLine = None
dbo.ErrorLog.ErrorMessage = "TEST"
dbo.ErrorLog.ErrorNumber = 400
dbo.ErrorLog.ErrorProcedure = (Some "Procedure 400")
dbo.ErrorLog.ErrorSeverity = None
dbo.ErrorLog.ErrorState = None
dbo.ErrorLog.UserName = "jmarr"
}
getId e.ErrorLogID
}
|> ctx.Insert
printfn "ErrorLogID Identity: %i" errorLogID
Multiple Inserts
To insert multiple entities in one query, use the entities
operation.
NOTE: getId
is not supported for multiple inserts with entities
! So if you are inserting multiple entities that have an identity field, you must use excludeColumn
on the identity column.
let currencies =
[ 0..2 ]
|> List.map (fun i ->
{
Sales.Currency.CurrencyCode = $"BC{i}"
Sales.Currency.Name = "BitCoin"
Sales.Currency.ModifiedDate = System.DateTime.Now
}
)
let! rowsInserted =
insert {
into currencyTable
entities currencies
}
|> ctx.InsertAsync
Update Builder
Update Individual Fields
To update individual columns, use the set
operation.
let rowsUpdated =
update {
for e in errorLogTable do
set e.ErrorNumber 123
set e.ErrorMessage "ERROR #123"
set e.ErrorLine (Some 999)
set e.ErrorProcedure None
where (e.ErrorLogID = 1)
}
|> ctx.Update
Update Entire Record
To update an entire record, use the entity
operation.
You may optionally use includeColumn
to specify an allow list of one or more columns on the record to include in the update.
You may optionally use excludeColum
to specify a deny list of one or more columns on the record to exclude from the update.
NOTE: You may use includeColumn
or excludeColumn
multiple times - once for each column to include/exclude.
let rowsUpdated =
update {
for e in errorLogTable do
entity
{
dbo.ErrorLog.ErrorLogID = 0 // Add `excludeColumn` below to ignore an identity column
dbo.ErrorLog.ErrorTime = System.DateTime.Now
dbo.ErrorLog.ErrorLine = None
dbo.ErrorLog.ErrorMessage = "TEST"
dbo.ErrorLog.ErrorNumber = 400
dbo.ErrorLog.ErrorProcedure = (Some "Procedure 400")
dbo.ErrorLog.ErrorSeverity = None
dbo.ErrorLog.ErrorState = None
dbo.ErrorLog.UserName = "jmarr"
}
excludeColumn e.ErrorLogID // Exclude the identity column
where (e.ErrorLogID = errorLog.ErrorLogID)
}
|> ctx.Update
If you want to apply an update to all records in a table, you must use the updateAll
keyword or else it will throw an exception (it's a safety precaution that may save you some trouble. 😊):
update {
for c in customerTable do
set c.AccountNumber "123"
updateAll
}
Delete Builder
let rowsDeleted =
delete {
for e in errorLogTable do
where (e.ErrorLogID = 5)
}
|> ctx.Delete
printfn "Rows deleted: %i" rowsDeleted
If you want to delete all records in a table, you must use the deleteAll
keyword in lieu of a where
statement or else it will not compile:
delete {
for c in customerTable do
deleteAll
}