SqlHydra

SqlHydra.Cli reads your database schema and generates an F# file containing:

• F# record types: One for each table. Nullable columns are automatically mapped to Option types.
• Table declarations: Used to reference tables within selectTask blocks.
• QueryContextFactory: A factory with a static Create(connectionString: string) method to initialize your database connection.

Example of generated code:

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

If left_joined_views = true is enabled in your .toml config, SqlHydra generates a LeftJoined module for each schema. This module contains records where every column is in its nullable form (e.g., string option).

This allows you to join without wrapping the entire record in an Option, making the on clause much cleaner:

for o in Sales.SalesOrderHeader do
// No .Value or Some required in the ON clause
leftJoin d in Sales.LeftJoined.SalesOrderDetail on (o.SalesOrderID = d.SalesOrderID)
where (d.OrderQty = Some 2s)
select (o, d)

To convert a left-joined view back into a full record Option, use the generated ToOption() extension method:

let orders = rows |> Seq.map (fun (o, d) -> o, d.ToOption())

When implementing a type mapping extension, you receive a TypeMappingContext object. This object provides full schema metadata for the column currently being mapped, allowing you to make decisions based on more than just the provider type name. You can inspect the table name, column name, schema, or other metadata.

Available metadata fields in TypeMappingContext:

• Table: Contains TableSchema (catalog, schema, name, type, and all columns).
• Column: Contains ColumnSchema (name, type, nullability, precision, scale, etc.).

type TypeMappingContext =
    {
        Table: TableSchema   // Table catalog, schema, name, type, and all columns
        Column: ColumnSchema  // Column name, type, nullability, precision, scale, etc.
    }

A CTE creates a named WITH source from a select query. The resulting source can be queried like a table or used in joins.

• Use cte<'T> "name" query when the CTE's row shape matches the inner select's type.
• Use cteFrom<'T> "name" query when the CTE's row shape differs from the inner select (e.g., when using computed columns).

let dallasAddresses =
    select {
        for a in SalesLT.Address do
        where (a.City = "Dallas")
    }

let dallas = cte<SalesLT.Address> "dallas_addresses" dallasAddresses

let getDallasAddressIds (db: QueryContextFactory) = 
    selectTask db {
        for a in dallas do
        select a.AddressID
    }

To perform lateral joins in PostgreSQL, use lateralJoin from SqlHydra.Query.NpgsqlExtensions.

1. Use correlate inside the subquery to reference tables from the outer query.
2. Use lateralCol<'T> "alias" "column" in the outer select to access columns from the joined subquery.

open SqlHydra.Query.NpgsqlExtensions

let latestDetail =
    select {
        for d in sales.salesorderdetail do
        correlate o in sales.salesorderheader
        where (d.salesorderid = o.salesorderid)
        orderByDescending d.modifieddate
        select d.orderqty
        take 1
    }

selectTask db {
    for o in sales.salesorderheader do
    lateralJoin latestDetail "latest"
    select (o.salesorderid, lateralCol<int16> "latest" "orderqty")
}

SqlHydra supports atomic upsert operations across different database providers:

SQL Server

Use insertOrUpdateOnUnique from SqlHydra.Query.SqlServerExtensions. This uses a TRY/CATCH pattern to avoid the pitfalls of the SQL MERGE statement. You must provide the unique key and the tuple of columns to update.

PostgreSQL and SQLite

Use onConflictDoUpdate from SqlHydra.Query.NpgsqlExtensions or SqlHydra.Query.SqliteExtensions.

// SQL Server Upsert
insertTask db {
    for u in dbo.Users do
    entity { Id = user.Id; Username = user.Username; ... }
    insertOrUpdateOnUnique
        u.Id
        (u.Username, u.Email, u.UpdatedDate)
}

// PostgreSQL Upsert
insertTask db {
    for u in dbo.Users do
    entity { ... }
    onConflictDoUpdate
        u.Id
        (u.Username, u.Email, u.UpdatedDate)
}

Type mapping extensions can be distributed as NuGet packages. To use one, add the package as a PackageReference in your project and then register its package ID in your TOML configuration under [extensions].type_mappings. SqlHydra will resolve the assembly from your project's build output and load all IExtendTypeMapping implementations found within it.

[extensions]
type_mappings = ["SqlHydra.Query.Pgvector"]

The select clause in SqlHydra is strictly for selecting columns or tables. Do not perform transformations (like string interpolation or .ToString()) inside the select block, as this will throw a runtime error. Instead, perform transformations in the mapList, mapArray, or mapSeq blocks after the selection.

// Correct way to transform
selectTask db {
    for a in SalesLT.Address do
    select (a.City, a.StateProvince) into (city, state)
    mapList $"City: {city}, State: {state}"
}