Connection strings in Suave on Azure

Olav Nybø

I recently used the suave web development F# library to develop a proof of concept application.
Of course we wanted to deploy the application to azure and this looked very easy as both Tomas Petricek (Dojo suave) and Scott Hanselmann had posted samples on how to do this (Running Suave.io and F# ....).

Everything started out real smooth

The Suave library is a pleasure to work with and the scripted setup, using a FSX file for the Suave code, makes for a really pleasant developer experience. Experimenting, changing and trying the code is super fast. Change and save the script, refresh the browser to instantly see the result. Great!
We needed our web site to persist and retrieve data from an SQL-server.
We added a SqlDataProvider using the SQLProvider library from nuget.
Again a great experience. Getting instant intellisense for your database is awesome.
Everything was working, now we just had to get the application up in azure.
This was when we hit a stumbling block. But first lets take a look at F# TypeProviders and how they are initialized.

F# type providers

An F# type provider is a special type that is used to generate types from various data-sources. It can be used to provide strongly typed access to data-sources such as json files, json over http, csv files or as in our case to SQL databases.
If you have used code generators like svcutil for Web services or SQLMetal for databases, you may think that this isn't that special, but one huge difference is that most Type providers don't rely on code generation. A typical F# type provider can generate the types on the fly.
And they do this from within the IDE. Just add the Type provider with the proper connection string (for SQL type providers) to your code in Visual studio and it will automatically read the schema information from the database and create the appropriate types for you tables.

This first step is called design time. This is when you write your code. When you compile the code, the generated types will be compiled into your application. This means that if the database change after your code has compiled, i.e. at run time it will of course fail because the types has already been created and the rest of your application is dependent on the types that was created at design time.

Type providers in scripts

This may be stating the obvious, but scripts are not compiled. But what this means for use of type providers may not be as obvious, at least it wasn't immediately clear to me.
When we deployed the Suave fsx file containing the SQL Type provider to our Azure web site it did nothing for a long time and then it just produced a error message about not being able to contact the database.
I could not understand it as we had not even tried any of the web methods that was supposed to read or write to the database.
After some tests we found out that it was the Type provider that caused the problem.
type db = SqlDataProvider<connStr, Common.DatabaseProviderTypes.MSSQLSERVER, ResolutionPath = resolutionFolder>

The connStr is a connection string to our local development database, but the document said that this was only used at design time. At run time the provider will use the connection string passed in to the GetDataContext like this:
let ctx = db.GetDataContext(Environment.GetEnvironmentVariable("SQLAZURECONNSTR_QUERIES"))

We used SQLAZURECONNSTR_QUERIES environment variable that in an azure website will get its value from the connection string setting configured in the Azure management settings for the site and we thought this would work just fine.
It was not until I got a great answer to my SO Question from Petricek that I figured this out.

It is of course because when executing as a script the types are regenerated each time the script runs, and the design time connection string passed in to the Type provider has to be accessible when the script is executing.

The solution

Petricek suggested three solutions.

  • Compile the code before deploying to azure
  • Modify the connection string during deploy to match the SQLAZURECONNSTR_QUERIES environment variable
  • Implement support for environment variables in the type provider

I liked the ease of using a script so I decided to try one of the two latter approaches. Rewriting the type provider seemed to be a bit larger task so I decided to try to fix the connection string at deploy time.

As it turned out this was a really simple task.
The samples that we started from are already using FAKE to deploy the scripts.
Fake is a F# based tool similar the provides functionality similar to PSake and make. The build and deploy process is described using regular F# code and predefined tasks.

First I put the declaration of the connection string into a separate file I named connectionstring.fsx. This file contains the connection string I used to when testing the script locally:

module Connection
let [] ConnString = "Server=(localdb)\\v11.0;Initial Catalog=My_Local_Database;Integrated Security=true;"

Then I basically just had to add a call to the FAKE function ReplaceInFiles in my deploy target in build.fsx.
After the change it looked like this:

Target "deploy" (fun _ ->
  let sourceDirectory = __SOURCE_DIRECTORY__
  let wwwrootDirectory = __SOURCE_DIRECTORY__ @@ "../../wwwroot"
  let connectionStringFile = sourceDirectory @@ "/connectionstring.fsx" 
  let connectionString = Environment.GetEnvironmentVariable "SQLAZURECONNSTR_QUERIES"
  let developmentConnectionString = """Server=(localdb)\\v11.0;Initial Catalog=My_Local_Database;Integrated Security=true;"""
  CleanDir wwwrootDirectory
  ReplaceInFiles [(developmentConnectionString, connectionString)] [connectionStringFile]
  CopyRecursive sourceDirectory wwwrootDirectory false |> ignore
)

When the "deploy" target is executed on the azure site during deployment the ReplaceInFiles function will scan the connectionstring.fsx file and replace the development connection string with the contents of the environment variable.
When it finally copies all the source files to the web site the connectionstring will already be pointing to the correct azure sql database and everything works as it is supposed to.

Next step will be to try to change the Type provider as I think that would be the best solution. But that will have to wait till another day.