TheSharperDev

Educating about C# and F#

Introduction to F# Type Providers

Photo by freestocks.org on Unsplash

This post is one of many in the FSAdvent Calendar. A December’s worth of blog articles all about FSharp. Many thanks to Sergey Tihon for putting this all together.

Coming from the world of C#, one of the things I’ve never heard of before was a type provider. I was pretty surprised and blown away the first time I saw one demoed.

Today I wanted to give an introduction FSharp.Data.SqlClient, one of the popular type providers for F#.

What Is A Type Provider?

So, what is a type provider? A type provider is a library that provides types to use in your application.

What kind of types?

That’s where it gets interesting. At times in my career I’ve had to hard code some SQL into C# code. I don’t prefer doing that, one reason is if I made any typos I wouldn’t figure it out until run time.

Since that SQL was just a string, the compiler didn’t care.

When leveraging a type provider, if provides compile time guarantees to your SQL.

Take for instance, the following SQL statement.

SQL Snippet

This works great, returns the records that I’m interested.

Say I wanted to add an extra column and accidentally misspelled it?

Misspelled SQL Snippet

Now Visual Studio gives me the red squiggles, with the following message:

The type provider ‘FSharp.Data.SqlCommandProvider’ reported an error: Invalid column name ‘SellEndDat’.

~Type Provider

It tells me that I misspelled my column. The type provider takes my SQL query and checks that against the database. Giving me immediate feedback if my SQL is invalid.

That’s the power of using a Type Provider. Giving you types for data sources that generally have been untyped.

Type providers exist to give F# types for the following data sources:

  • SQL
  • JSON, XML, CSV, and HTML document formats and resources
  • Azure related resources
  • GraphQL

TypeProviders are very useful libraries used in functional languages.

Setting Everything Up

Lets see how easy it is to get started with a SQL Type Provider.

First, have a SQL store you’d like to connect to. I’m using an AdventureWorks database to run all my examples against, specifically the AdventureWorks2017 (Download Link).

Second, create a new F# project, and install the FSharp.Data.SqlClient nuget package.

Then create a simple query like below and run.

Then you’ll see data printed to the console.

If you need a subset of records in a table please use query parameters:

You can either pass the parameters via name or via position, both work the same.

Then inserting and deleting is also straight forward:

That covers the four cases for the SqlCommandProvider, lets move onto to demoing the other Type Providers contained in this nuget package.

Other Type Providers in FSharp.Data.SqlClient

The FSharp.Data.SqlClient actually contains three type providers.

  • SqlCommandProvider – type-safe access to full set of T-SQL language
  • SqlProgrammabilityProvider – quick access to Sql Server functions, stored procedures and tables in idiomatic F# way
  • SqlEnumProvider – generates enumeration types based on static lookup data from any ADO.NET compliant source

So far we’ve just used the SqlCommandProvider. Lets take a look at the others.

SqlProgrammabilityProvider

The SqlProgrammabilityProvider allows you to use database resources like any other F# type. First thing to do is create a type for your database.

Then I can use that type to access my database resources. For example, notice I get intellisense of everything that’s under the dbo schema.

I can see my tables and all the stored procedures I could call. Here’s how you would call a stored procedure.

Here is an example of inserting records using the ProgrammabilityProvider.

I think this makes stored procedures easier to use, but when inserting records I prefer the SqlCommandProvider.

SqlEnumProvider

And lastly, there’s the SqlEnumProvider. The problem it’s trying to solve is giving you a better way to use reference data in a sql database.

In the AdventureWorks database there’s a PhonePersonType table with the following values.

Then that’s referenced from the PhonePerson table via a FK.

Say I wanted to select PhonePerson records via type. Do we query one table via an int? Or two tables via a string?

Where do we keep a reference of this int or string? How soon does that reference go out of date?

These are the problems that SqlEnumProvider is meant to solve.

First we create a mapping of the reference table, by convention the selected columns are Name then the reference ID of the table.

Once we’ve created the mapping, we now have intellisense of the reference values by name.

Then when it is used in a query, the provider will automatically map from Name to ID value for you.

Thoughts On Usage

As cool as Type Providers are, there are things that make them a little more difficult to work with.

The first being a connectable database is needed in order to build your project. Because the TypeProvider provides compile time guarantees of your SQL resources, whenever a compile happens it needs a database to go check against.

For a CI/CD pipeline, that means always having an available database, which can get complicated.

Another is now you’re storing your SQL queries in code. Generally I’ve avoided storing raw SQL in the C# apps I’ve worked with in the past. But given the power of Type Providers, I can see where it’s more nuanced because it’s not the same as raw SQL.

Lastly for me, I’m not sure what the real world usage of this Type Providers are? What are the performance of these libraries? Have bigger organizations successfully used them in Production? From what I’ve found, there’s not a ton of documentation on more advanced use cases or problems people have run into as I would find for something like EntityFrameworkCore.

Wrapping Up

This article offered an introduction into the usage of the Sql TypeProviders available in the FSharp.Data.SqlClient. Type Providers can be very useful libraries for data manipulation, and something pretty unique to functional languages.

Sources