This beta release adds type generation support to SqlHydra.Cli for MySQL. (Thanks to @RJSonnenberg for the contribution!)

SqlHydra.Query v2.5.0

• In a select query, specifying a table or tables using the select keyword now explicitly selects all the columns in the table record; previously, it would issue a select tbl.* query. It is now recommended to always explicitly select a table. (If you do not explicitly select, it will generate a select tbl.* style query which will be less performant.)

Ex:

❌ This will issue a SELECT * query which will result in a table scan which will result in slightly worse performance.

    let! results = 
        selectTask' openContext {
            for p in Person.Person do
            take 10
        }

✅ This will explicity select all columns in the Person table, which will result in slightly better performance.

    let! results = 
        selectTask' openContext {
            for p in Person.Person do
            take 10
            select p
        }

SqlHydra.Cli v2.5.0

• The generated HydraReader now filters out any columns that do not exist in the selected table record(s). (This fixes a bug that could happen when a column was added to a table and the types were not regenerated.

NOTE: You must upgrade both SqlHydra.Query and SqlHydra.Cli at the same time to v2.5.x.

SqlHydra.Query

• Added implicit conversions to ContextType.

The ContextType (Shared, Create, CreateAsync and CreateTask) is now implicitly converted.
This allows you to either pass in a QueryContext or a function that returns a QueryContext to your selectTask and selectAsync expressions. This is a huge quality-of-life improvement, IMO!

See Select Builders in readme for more examples.

Old:

let getErrorNumbers () =
    selectAsync HydraReader.Read (Create openContext) {
        for e in dbo.ErrorLog do
        select e.ErrorNumber
    }

Becomes:

let getErrorNumbers () =
    selectAsync HydraReader.Read openContext {
        for e in dbo.ErrorLog do
        select e.ErrorNumber
    }

Old:

let getErrorNumbers (ctx: QueryContext) =
    selectAsync HydraReader.Read (Shared ctx) {
        for e in dbo.ErrorLog do
        select e.ErrorNumber
    }

Becomes:

let getErrorNumbers (ctx: QueryContext) =
    selectAsync HydraReader.Read ctx {
        for e in dbo.ErrorLog do
        select e.ErrorNumber
    }

If you create a shortcut select CE that embeds the generated HydraReader.Read function, it becomes even more succinct:

let selectTask' ct = selectTask HydraReader.Read ct

let distinctCustomerNames (ctx: QueryContext) = 
    selectTask' ctx {
        for c in SalesLT.Customer do
        select (c.FirstName, c.LastName)
        distinct
    }

SqlHydra.Cli

• Rewrote code generation using Fabulous.AST!

• Added new mutable_properties option to toml which makes all record properties mutable. 🙀 (defaults to false.)

• Added new nullable_property_type option to toml which allows nullable columns to be generated as either F# option types (default) or System.Nullable properties. The latter can be useful for C# interop or CRUD scenarios where you want to data bind generated types directly to UI controls. (Generally, I would recommend mapping generated types to domain entities or DTOs, but it's nice to have the option to do things in a quick and dirty way.)

  • mutable_properties = true
  • nullable_property_type = "Nullable"

• Improved CLI Output

  • The entire TOML configuration is now printed to CLI
  • TOM include/exclude filters are now printed to CLI, along with the number of tables generated (post-filter) and the total number of tables found (pre-filter) to the console. #88
    

• All providers will now ignore any tables / views with no columns (just in case)

• Postgres "time with time zone" columns now generate DateTimeOffset properties

• Postgres now generates materialized views (requires net8 and npgsql v8 or greater). #84

• Updated CLI to latest "Microsoft.Data.SqlClient" to fix warnings

SqlHydra.Query

• Changes to support the new System.Nullable column/properties in the Linq query syntax

• Changes to support the new System.Nullable column/properties query parameters

• Adds CreateTask and CreateAsync cases to the ContextType discriminated union. #89

• Exposed KataQuery property on all queries (the resulting type of query builders) to make it possible to manipulate the underlying SqlKata query for inserts, updates and selects before executing the query.

• Added head custom operation to the SelectBuilder for queries where you know there should always be at least one result. (Before you had to use tryHead, and then manually extract the value.)

SqlHydra.Query

• Adds CreateTask and CreateAsync cases to the ContextType discriminated union.

SqlHydra.Cli

• Updates to the latest version of Fabulous.AST for code generation

SqlHydra.Cli

• Now prints the active filters, number of tables generated (post-filter) and the total number of tables found (pre-filter) to the console. #88

SqlHydra.Query

• No changes.

SqlHydra.Cli

• Rewrote the code gen. using Fabulous.AST

SqlHydra.Query

• No changes

SqlHydra.Cli

• Added new tom configuration options to generate mutable properties and System.Nullable properties. This can be useful for CRUD scenarios where you want to data bind generated types directly to UI controls. (Generally, I would recommend mapping generated types to domain entities or DTOs, but it's nice to have the option to do things in a quick and dirty way.)
  • mutable_properties = true
  • nullable_property_type = "Nullable"

SqlHydra.Query

• Added head custom operation to the SelectBuilder for queries where you know there should always be at least one result. (Before you had to use tryHead, and then manually extract the value.)
• Changes to support the new System.Nullable column/properties in the Linq query syntax
• Changes to support the new System.Nullable column/properties query parameters

• Adds mappings for the materialized view columns
• "time with time zone" columns now generate DateTimeOffset properties

SqlHydra.Cli

• Added new "mutable_properties" option to toml "general" section which makes all record properties mutable. 🙀 (defaults to false, of course 😉). This is for in-memory apps that want to bind to generated types directly for edits.
• Postgres now generates materialized views (requires net8 and npgsql v8 or greater). #84
• All providers will now ignore any tables / views with no columns (just in case)

SqlHydra.Query

• Added an experimental Diff utility to assist with saving batch changes. #83
• Exposed KataQuery property on all queries (the resulting type of query builders) to make it possible to manipulate the underlying SqlKata query for inserts, updates and selects before executing the query.

Adds support for .NET 8

NOTE: Npgsql v8.0.0 fails when inserting an enum, so use Npgsql v7.0.0 for now.

• Fixes: #71 - selected columns of left joined tables can now be marked as optional by wrapping them with Some.
• In addition to isNullValue and isNotNullValue functions, added support for the built-in isNull function

SqlHydra.Cli

• SQL Server - All table properties now have a generated parameter type attribute
• Npgsql - All table properties now have a generated parameter type attribute

SqlHydra.Query

• Fixed bug #68 where QueryContext Logger was sometimes not showing the final query (for inserts with manually modified query)
• Improved Logger output so that query shows parameter names and parameter values are listed below query
• Improved Logger output so that query parameters show their generated column/parameter type

SqlHydra.Cli

• The TOML configuration [filters] section now has a new restrictions setting that allows passing restrictions (filters) to GetSchema. This gives you the ability to pre-filter the queried schema. For example, you can pre-filter to only pull tables in a certain schema. (These filters are not as configurable as the existing include and exclude glob patterns, but they have been added to help pre-filter extremely large databases to reduce generation times. Unless you are experiencing performance issues, you should use the glob include and exclude post-filters instead.)
  See TOML configuration docs here: https://github.com/JordanMarr/SqlHydra/wiki/TOML-Configuration#schema-restrictions
• TOML configuration glob filters are now applied to filter tables before columns are collected / processed for each table. This is a small efficiency change.

SqlHydra.Query

• Boolean unary expressions are now supported (without having to explicitly assign = true or = false)

✅ This is now valid and doesn't require an extra parameter!

    select {
        for o in Sales.SalesOrderHeader do
        where o.OnlineOrderFlag
    }

✅ This is now valid and doesn't require an extra parameter!

    select {
        for o in Sales.SalesOrderHeader do
        where (not o.OnlineOrderFlag)
    }

Adds the kata custom operation to the select computation expression.
This provides direct access to the underlying SqlKata.Query object within your query.

let getCustomers filters = 
  select {
      for c in main.Customer do
      where (c.FirstName = "John")
      kata (fun query -> 
          match filters.LastName with
          | Some lastName -> query.Where("c.LastName", lastName)
          | None -> query
      )
      kata (fun query -> 
          query.OrderBy(filters.SortColumns)
      )
  }

#58 Now sets SQL Server DateOnly and TimeOnly parameter types to DbType.Date and DbType.Time to improve query performance.

• Bug fix #57
• Consolidates generated ColumnReader methods

New in v2.0

• All generator tools have been consolidated into SqlHydra.Cli.
  • Note that this changes command line syntax from dotnet sqlhydra-npgsql to dotnet sqlhydra npgsql
• SqlHydra.SqlServer, SqlHydra.Npgsql, SqlHydra.Sqlite and SqlHydra.Oracle tools have been deprecated on NuGet.
• Support for net5.0 has been dropped (use the legacy tools if you still need it).
• SqlHydra.Query has been updated to v2.0 to maintain parity with SqlHydra.Cli.

New for v1.2.1:

SqlHydra.Query

• Support for correlated subqueries (#51). Thanks to @ntwilson for this feature!
  readme

SqlHydra CLI

• Fixes issue #50 for the SqlHydra CLI generators.

SqlHydra v1.2.0 now generates a table declaration for each table, along with the generated table record.

For example, if you have a table, Sales.Person, it will now generate a table declaration, Sales.Person.
Behind the scenes in the generated code, this look like this:

module Sales =

    [<CLIMutable>]
    type Person = { FName: string; LName: string }
    
    let Person = SqlHydra.Query.Table.table<Person>

You can then use this in your queries:

  select {
      for p in Sales.Person do
      select p
  }

How to enable feature

Starting a new project

If running the sqlhydra CLI tool for the first time, you will be prompted to "Select a use case" with three options:

• "SqlHydra.Query integration (default)"
• "Other data library"
• "Standalone"

This option will only be enabled if you choose "SqlHydra.Query integration (default)".

Upgrading from a previous version

If upgrading a previous version, you will need to manually edit your .toml file in your project folder to add table_declarations = true within the sqlhydra_query_integration section.

Example:

[general]
connection = "Data Source=TestData/AdventureWorksLT.db"
output = "Sqlite/AdventureWorksNet6.fs"
namespace = "Sqlite.AdventureWorksNet6"
cli_mutable = true
[sqlhydra_query_integration]
provider_db_type_attributes = true
table_declarations = true
[readers]
reader_type = "System.Data.Common.DbDataReader"
[filters]
include = [ "*" ]
exclude = [ "hr/*" ]

See .toml configuration docs for more details: