Home

Awesome

Facil

<img src="https://raw.githubusercontent.com/cmeeren/Facil/master/logo/facil-logo-docs.png" width="300" align="right" />

**Facil generates F# data access source code from SQL queries and stored procedures. Optimized for developer happiness. **

Pro-tip: Facil works great with Fling!

Quick start

0. Requirements

1. Install

Install Facil from NuGet.

2. Edit the new config file

You now have a simple facil.yaml file in your project directory (if not, build your project or just add it manually). Edit it – you should at least set a connection string.

As an example, the following is the minimum config for generating code for all stored procedures in the database and all scripts in your project directory:

rulesets:
  - connectionString: YOUR CONNECTION STRING HERE
    procedures:
      - include: .*
    scripts:
      - include: "**/*.sql"

You might consider adding the config file to your project for easy access in Visual Studio.

For more details about configuration, you can find the full config reference here.

3. Build your project and add the generated file

Build your project again. Facil will now generate the code. Add the generated file to your project.

4. Use the generated code

For example:

open DbGen.Procedures.dbo

let getUser (connStr: string) (UserId userId) : Async<User option> =
    GetUserById.WithConnection(connStr).WithParameters(userId).AsyncExecuteSingle()

let searchProducts (connStr: string) (args: ProductSearchArgs) : Async<ResizeArray<Product>> = async {
    let dtoWithPrimitiveParams = ProductSearchArgs.toDto args

    return!
        GetUserById
            .WithConnection(connStr)
            // You can load parameters from any object with the right members
            // instead of passing each parameter manually
            .WithParameters(dtoWithPrimitiveParams)
            .AsyncExecute()
}

5. Profit!

That’s it! Whenever you want to regenerate, simply delete the first line of the generated file and build your project.

Facil will also regenerate on next build if you change the configuration or any scripts. For details, see the FAQ entry When does Facil regenerate files?.

Elevator pitch

Facil is a friendly, flexible, and full-featured fabricator of files for fluent and frictionless facades facilitating the facile and functional fetching of facts.

(“Facts” referring to data stored in SQL server. It would be better if SQL started with an F. Oh well.)

Okay, elevator pitch without the alliteration: Facil works similarly to type providers like FSharp.Data.SqlClient by letting you call SQL scripts and stored procedures in a strongly typed manner, but it avoids a range of type provider issues and hiccups by not being a type provider and actually generating F# code that you check in. (Why not a type provider, you ask? See the FAQ.)

Core features

Production readiness

Facil is production ready.

Facil contains over 2000 tests verifying most functionality in many different combinations, and is used in several mission-critical production services at our company. I’m not claiming it’s perfect, or even bug-free, but it’s well tested, and I have a vested interest in keeping it working properly.

FAQ

Can Facil generate SQL scripts?

Yes, Facil can automatically generate the following simple per-table CRUD scripts, saving you from both writing and configuring them:

For details, see the full config reference and search for tableScripts.

Ooh, neat! Can you auto-generate this and that script too?

Probably not.

Facil’s primary focus is allowing you to call your existing TSQL in the simplest fashion possible. There are no plans on adding more queries or options than the ones currently implemented. Adding ever new script types or making the existing ones more flexible is a bottomless rabbit hole of scope creep, so I have to draw the line somewhere.

I welcome suggestions, but due to my limited capacity for open-source maintenance, any additions or improvements will likely need a very high utility-to-maintenance ratio, or scratch a personal itch of mine.

If the above queries with the available configuration options don’t satisfy your needs, you can, after all, just write the queries manually and consume them using Facil.

Why not a type provider?

Type providers are great in theory, and to a large extent also in practice, but have some notable drawbacks:

Facil, by generating plain old F# code that you check in, sidesteps all of these issues (but still optionally supports forced generation on CI and also optionally failing the build if the generated code has changed).

When does Facil regenerate files?

Facil will regenerate (hitting your DB) before the compilation step of your build if any of the following are true:

If none of the above are true, Facil will skip its build step and thus not hit your DB.

Notably, this means that Facil will not pick up changes to your database. This is by design. If you update the DB that Facil connects to during build, just open the generated file and delete the first line and Facil will re-generate it on the next build.

Can I force Facil to run during CI build and fail if the generated file is not up to date?

Yes. There are two environment variables you can set. You can use either of them on their own, or together:

Why won't the project recompile if I only change facil.yaml or a .sql file?

Depending on how the .sql and facil.yaml file are added to your project in the first place, you may need to add these files to the project's up-to-date check. Simply add this in your .fsproj file:


<ItemGroup>
  <UpToDateCheckInput Include="**\*.sql" />
  <UpToDateCheckInput Include="facil.yaml" />
</ItemGroup>

What can I configure?

See the full YAML config reference for details. Note:

Below are some highlights of what you can configure.

For each file, you can configure:

For each procedure/script (or any set of these that matches a specified regex/glob pattern), you can configure:

For each table DTO, you can configure:

For each table type (automatically included by Facil if used in included procedures/scripts), you can configure:

Type inference limitations in scripts

Type inference in scripts is limited due to limitations in SQL Server's sp_describe_undeclared_parameters , which Facil uses to get parameter information for scripts. Notably, the following does not work out of the box:

To work around this, for each problematic parameter (you don't have to specify the ones that work), you can specify in the config which SQL type the parameter is and whether it is nullable. You can also set this for all parameters at once (and override specific parameters).

How are default and nullable parameter values handled?

All stored procedure parameters that have null as the default value are treated as nullable and wrapped in option (or voption). All other default values for stored procedure parameters are ignored; the parameters will be required and non-nullable.

While parameters with default values could conceivably be generated as optional method parameters, this runs the risk of forgetting to use them when executing the procedure (I’ve been burned by this a lot), and it also won’t mesh that well with the parameter DTO overloads.

If Facil’s current approach does not work for you, please open an issue and describe your use-case.

Can Facil support user-defined functions?

If you need this, I’m willing to hear you out, but I have limited OSS maintenance resources and this isn’t high on my priority list right now. A simple workaround is to simply call the function from a script or stored procedure, and then use Facil with that script/procedure instead.

Does Facil use column names or ordinals?

Facil uses column names at runtime. This means that you are free to reorder the columns returned by stored procedures and scripts (either explicitly, or by reordering table columns returned in a SELECT * query). This does not require re-compilation and will not break existing running apps.

Can I use temp tables?

Yes, Facil supports temp tables in scripts and procedures. In short, configure your script/procedures like this:

scripts:
  - include: "MyScriptUsingTempTables.sql"
    tempTables: # You can supply the definition directly as a string.
      - definition: |
          CREATE TABLE #myTempTable1(
            Col1 INT NOT NULL PRIMARY KEY,
            Col2 NVARCHAR(100) NULL
          )

      # If using a single line, remember to enclose in quotes since '#' starts a YAML comment
      - definition: "CREATE TABLE #myTempTable2(Col1 INT NOT NULL)"

      # You can also specify a path to a SQL file containing the definition
      - definition: path/from/project/to/myTempTable3.sql

Then temp tables will work similarly to TVPs:

MyScriptUsingTempTables
    .WithConnection(connStr)
    .WithParameters(
        tempTable1 = [ MyScriptUsingTempTables.tempTable1.create (Col1 = 1, Col2 = Some "test") ],
        tempTable2 = [ MyScriptUsingTempTables.tempTable2.create (Col1 = 1) ],
        tempTable3 = [ (* ... *) ]
    )

Just like with TVPs, you can use matching DTOs in the calls to create (instead of explicitly passing column parameters as shown above).

Facil uses SqlBulkCopy to load temp tables. You can configure the created SqlBulkCopy using ConfigureBulkCopy.

MyScriptUsingTempTables
    .WithConnection(connStr)
    .ConfigureBulkCopy(fun bc ->
        bc.BatchSize <- 1000
        bc.BulkCopyTimeout <- 0
        bc.NotifyAfter <- 2000
        bc.SqlRowsCopied.Add(fun e -> printfn "%i rows copied so far" e.RowsCopied))
    .WithParameters( (* ... *) )

The configuration will apply to the loading of all temp tables for the script/procedure; please open an issue if you need separate configuration per temp table.

Note that if a procedure uses a global temp table (a temp table that starts with two ## instead of one #), that temp table needs to exist at build-time. (This limitation does not apply to scripts.)

Note also that if you plan to use the same temp table name in another command with the same connection, you need to drop it. The easiest way to do this is to use DROP TABLE #myTempTable as the first line in the temp table definition. (This is automatically done for Facil-generated tableScripts that use temp tables).

Why do the Execute methods return ResizeArray and not an F# list?

The rows have to be read from the DB one at a time without knowing how many rows there are. As far as I know, a ResizeArray<_> (an alias for System.Collections.Generic.List<_>) generally provides the most efficient way (at least in terms of allocations) to build up a collection with an unknown number of items, requiring one allocation and array copy each time the internal array is resized. An F# list would cause one allocation per cell (item), though as far as I know, it would not require any copies (though it would be built up in reverse and therefore require a full traversal when reversing the list at the end).

F# users would normally want a list instead of a ResizeArray, but you can get that trivially by just calling Seq.toList on the result. This is similar to e.g. FSharp.Data.SqlClient. Facil could provide Execute variants that do this for you, but then you’d have twice as many Execute methods to choose from, which would add confusion, and the name prefix/suffix would almost be as verbose as just calling Seq.toList yourself.

If you think that building up a list directly in the read loop would be more efficient as it would avoid the “copy” cost of Seq.toList, then 1) I don’t think that’s correct, because (as mentioned above) an F# list would have to be built up in reverse by prepending each item, and the List.rev at the end would cause at least one “copy” anyway, and

  1. in the rare case that your use-case is so sensitive to performance that you are concerned about the performance impact of Seq.toList, then you should probably just just use the returned ResizeArray directly.

Note that since Facil is a general-purpose data access library, I do not know anything about user workloads, databases or connections, and I have not benchmarked anything. All of the above is going by intuition (admittedly a dangerous thing in the performance world) as well as a desire to keep the internals fairly simple.

If you believe any of the above is incorrect and have either sound arguments or proper benchmark data (e.g. using BenchmarkDotNet) to back it up, please open an issue and we can discuss both the public API and the implementation details.

Can Facil support SSDT (.sqlproj) projects, like SQLProvider?

No, unfortunately that’s not feasible.

SQLProvider uses information about tables and views and lets you write F# queries against them; it doesn’t provide strongly typed access to stored procedures or scripts. This means that SQLProvider “only” has to know the table/view schema, which (at least for tables) can be easily parsed directly from the definition.

Facil, on the other hand, must know the input parameters and output columns of scripts and stored procedures. While it would be fairly easy to get the input parameters of stored procedures by just parsing the procedure definition, Facil has to use SQL Server functionality on the deployed schema to get script input parameters (sp_describe_undeclared_parameters) and script/procedure output columns (sp_describe_first_result_set and SET FMTONLY ON).

The only way for Facil to be able to use SSDT projects directly would be if this SQL type inference functionality was implemented directly in the Facil generator. For all but the most trivial schema, this seems too complicated to be worth the implementation and maintenance effort. Therefore, Facil only works with deployed schema.

The most significant drawback of this limitation is that it is possible to update the SSDT schema but forget to deploy and re-generate. However, that is no different than if you were using FSharp.Data.SqlClient, and can be alleviated by using FACIL_FORCE_REGENERATE and FACIL_FAIL_ON_CHANGED_OUTPUT on your build server.

A bonus, however, is that having to deploy the updated schema to a database during development ensures that the schema actually “compiles”.

Why does Facil not generate result sets for my dynamic SQL query?

TL;DR: Use buildValue in the parameter config for parameters with sort column names etc.

Long version: This can happen when all of the following are true:

In this scenario, the output column parser returns no columns. (Unfortunately Facil can’t easily find out whether this is an error or if the script actually returns no results, and thus can’t display a warning/error instead of silently producing non-query code.)

The fix is simple: In the procedure/script config, add a parameter with buildValue set to a value you know will work:

- for: MyProcedure
  params:
    mySortColParam:
      buildValue: MySortColumn

Another workaround is to use WITH RESULT SETS. This will in many cases enable Facil to use another method of determining the output columns that does not depend on parameter values. However, compared to using buildValue, it is more likely to break (since you must keep it in sync with the SELECT list) and likely more verbose.

Can Facil check my dynamic SQL?

Facil provides some checking of dynamic SQL as long as you don’t use WITH RESULT SETS.

As an example, take the following script:

DECLARE @sql NVARCHAR(MAX) = '
SELECT * FROM dbo.MyTable
WHERE
  1 = 1
'

IF @col1Filter IS NOT NULL
  SET @sql += '
  AND Col1 = @col1Filter
'

IF @requireCol2Zero = 1
  SET @sql += '
  AND Col2 = 0
'

DECLARE @paramList NVARCHAR(MAX) = '
  @col1Filter NVARCHAR(100)
'

EXEC sp_executesql @sql, @paramList, @col1Filter

In order to parse the output columns of dynamic SQL queries, Facil must execute your query and see which columns come back. At build time, Facil generally passes 1 (or "1" etc.) for all parameters when executing the query. In the common case of dynamic filters as shown above, where you use IS NOT NULL or @param = 1 to add filters to the executed SQL, this means that your dynamic SQL will be executed with all the optional filters.

Facil may not completely check your dynamic SQL. For example, you may have a parameter that is used to choose one of several different ORDER BY clauses. In this case, only one of them will be used at build time (and you may be able to specify the parameter value by using buildValue as described previously).

When executing a query at build time as described above, it is executed in a transaction that is rolled back, to ensure that no changes are made to the database during build time.

Can Facil make it easy to save/load domain entities to/from multiple tables?

This is exactly what Fling does. Fling works great with Facil!

Release notes

RELEASE_NOTES.md

A note on versioning

Facil follows SemVer.

Note on what a “breaking change” is: A lot of the generated code needs to be public to support inlining, but is still considered implementation details. These parts of the API are hidden from the IDE using the [<EditorBrowsable>] attribute to ensure you won’t use them by accident, but there’s nothing stopping you from looking at the generated code and referencing these parts of the API in your own code. Don’t do that. These are implementation details and may change at any time.

Contributing

Contributions and ideas are welcome! Please see Contributing.md for details.