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.

Image for post
Image for post

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.

Image for post
Image for post

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:

Image for post
Image for post

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).
Image for post
Image for post
Image for post
Image for post

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):

Image for post
Image for post

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.

Image for post
Image for post

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

Image for post
Image for post

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!

Image for post
Image for post
Image for post
Image for post

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:

Image for post
Image for post

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-)

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store