Accessing a relational DB with F# type providers

This is going to be a very simple step-by-step guide on how to use an F# type provider to access information in a relational database, specifically MySQL or MariaDB, in Linux. The goal is to get you set up with a working environment so that you can follow more detailed tutorials and examples. A couple of prerequisites:

1. An F# development environment similar to the one we set up here: Mono runtime, F# 4.1 compiler, and VS Code with Ionide extensions.
2. Also you need the MariaDB engine installed and working, with its usual test database.

Once you are past this, we can start our step-by-step:

First: Open a terminal, create a folder, get into it, and start VS Code with a code . command.

Second: Once VS Code is started up, press Ctrl-Shift-P to open the command bar, type F# and then select the F#: New Project option.

In the option bar that appears at the top select the console Console Application option, then press Enter to use the current folder as the parent for the project we are creating, and finally type helloDB as the name of our brand new F# project.

If everything went fine, a confirmation will be displayed and an initial structure with three folders and a couple of files will be shown, like so:

Third: Now we are going to download the two packages that we need to work with MariaDB (or MySQL) from F#:

1. Press Ctrl-Shift-P to open the command bar and then type Paket, select the Paket: Add Nuget Package option, and in the text box that appears at the top enter SqlProvider, this is the F# type provider for working with several relational databases (more information here).

2. For the second library, press Ctrl-Shift-P again, type Paket, select the Paket: Add Nuget Package option, and in the text box enter MySql.Data. This is the .NET library for working with a MySQL (or MariaDB) database engine.

As a sanity check, open the paket.dependencies file by clicking on it, the file should contain these two lines (the order or exact location isn’t important):

Fourth: Now we are finally ready to enter some F# code, let’s create a script file so we can work line by line: right-click on the helloDB folder and choose the New File option from the pop-up menu, then type learning.fsx as the name of the new script file.

Fifth: Create a SQL data provider by typing these 6 lines of code:

Some relevant comments:

1. The first line instructs the F# environment to go ahead and load that assembly, so that we can use the functions defined in it in our code, as usual in Linux be careful with the casing
2. The open at line 3 is similar to a C# using or a Java import
3. The Literal attribute makes ConnString and friends string constants available at compile time (far before runtime), this is a requisite for the type provider to do its magic while we type code
4. Line 12 is the kicker, risky and magical stuff happens as soon as you hit Enter at the end of that line:
   a) The F# environment tries to connect immediately to the test database, if the DB server or the database is not available, or the credentials are wrong, a compilation error is fired, this is the risky part
   b) If the connection goes well, SQLDataProvider is ready to explore the database structure and generate on the fly, as we keep entering code, classes that represent tables, rows, columns, etc. This is the magical part
5. A keen eye would observe that while in line 1 the path ../packages/…starts with two dots, in line 9 the path ./packages/… starts with just one dot. This is indeed an inconsistency, I ignore if it’s caused by the VS Code extensions, a bug in SQLDataProvider, or something else, but at least we have a workaround that works consistently :-)

Sixth: Now the real fun begins, we define a context object that is our gateway to the database, through it we can navigate the database with Intellisense and all, even to the point of primary keys!

Seventh: Now I am pushing into information that is really better explained at the SQLProvider site, but just to be a little complete we can create a new row and save it to the DB table:

As I said at the beginning of the article: this was a step-by-step guide with a really simple escenario, the goal being setting up your Linux F# environment so that you can start using the SQL type provider for some real work. Come to think of it, may be a future post with examples showing some fancy queries, joins and the like, is in order B-)