Secure way to do database connection strings for a web service.

[Richard G]

Hi guys

I wanted to open up a discussion on something that has wasted hours of my time.

That is the old matter of database connection strings.

I've got to produce a web API to talk to a database - needless to say I wanted to follow a reasonable practice for the connection string.

However, I hit a brick wall when I tried to use Appsettings.config (or any similar such config files). Either I couldn't access the file from within F# or the type provider (FSharp.Configuration) doesn't give you the value in a form that can be consumed by a database connector. It's a static property.

So, I'm pulling my hair out - I don't want to revert to C#/VB, I want the nice convenient syntax for building a decent webservice that F# offers.

Yet, at the first hurdle, I can't find any standard help on how this would be done commercially.

Within seconds I can easily talk to the server using code like this:

 

open System

open System.Linq

open FSharp.Data.Sql

open MySql.Data

[<Literal>]

let c = @"Server=SomeServer; Port = 3306;Database=raspivend;Uid=SomeUser;Pwd=SomePass"

  

type sql = SqlDataProvider<  ConnectionString = c,

                             DatabaseVendor = Common.DatabaseProviderTypes.MYSQL, 

                             ResolutionPath = @"C:\Program Files (x86)\MySQL\MySQL Connector Net 6.9.7\Assemblies\v4.5\" >

let data = sql.GetDataContext()

But that's not the code I'd want to deploy server side.

Any guidance offered would be highly appreciated!

[Gauthier Segay]

Hello Richard,

first keep in mind that the connection string you provide in the F#
code is only for compile time, at runtime you'll pass your own
connection.

I'm not acquainted with F# SQLProvider, I'm using SqlClient which is
limited to MSSQL, although looking at the code:

https://github.com/fsprojects/SQLProvider/blob/54436832fae55b44fce215a4272956204221b705/src/SQLProvider/SqlDesignTime.fs#L407

It seems you can pass the connection as a string parameter to
GetDataContext (there are 3 overloads for this method).

so in your production code, you never call GetDataContext without any
parameters, always pass the connection string you need to use.

Does that make sense?

> --
> --
> To post, send email to fsharp-o...@googlegroups.com
> To unsubscribe, send email to
> fsharp-opensou...@googlegroups.com
> For more options, visit this group at
> http://groups.google.com/group/fsharp-opensource
> ---
> You received this message because you are subscribed to the Google Groups
> "F# Discussions" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to fsharp-opensou...@googlegroups.com.
> For more options, visit https://groups.google.com/d/optout.

[Paul Blair]

Here's something similar I did recently:

open System

open FSharp.Data.Sql

[<Literal>] 

let compileTimeConnectionString = """

Host=127.0.0.1;

Port=5432;

Database=mydb;

Username=buser;

Password=pass

"""

type Db = SqlDataProvider<DatabaseVendor    = Common.DatabaseProviderTypes.POSTGRESQL, 

                          ConnectionString  = compileTimeConnectionString,

                          ResolutionPath    = "packages/Npgsql/lib/net45/",

                          UseOptionTypes    = true>

let runtimeConnectionString = 

    let fromEnvironment = System.Environment.GetEnvironmentVariable("DB_CONNECT_STRING")

    match fromEnvironment with

        | null -> compileTimeConnectionString

        | _    -> fromEnvironment

let dataContext = fun () -> Db.GetDataContext(runtimeConnectionString)

[Ross Mckinlay]

Please note that for you compile time string, you can also use the static parameter ConnectionStringName rather than ConnectionString, which will lookup a named connection string from the current app.config file.

--

[Gauthier Segay]

Hi Ross, just a note about app.config:

If the code using the provider is meant only for compiled assemblies,
then it's convenient to rely on app.config, but from .fsx files or
interactive session, I've experienced annoying issues with relying on
app.config.

[Ross Mckinlay]

Gauthier,

Sure, but if you are using fsx it is not going to be a compiled assembly anyway so you would just use a connection string literal.   The app.config is very useful if you have multiple branches of code that need pointing at a different database during compilation.

We give you both options to use as your use case best fits.

If all else fails you can use the mixin type provider to generate the connection string literal for you based on any logic from anywhere. 

Ross

[Gauthier Segay]

Hi Ross, thanks for describing the matter with more depth.

The concern I was pointing with .fsx and app.config is that .fsx is a
convenient way to sketch new code, if in this code you need to refer
to compiled assembly or even .fs files which use the app.config
mechanism, this will be difficult.

I thought it was good to mention it as a potential drawback, but it's
also something useful.

On Fri, Sep 11, 2015 at 3:45 PM, 'Ross Mckinlay' via F# Discussions