Do Yourself a Favor When Writing Connection Strings In Configuration

Heredia, Costa Rica, 2022-11-12

2023-08-19 UPDATE: I see this article trending a bit, and I wonder if you would also like to do this in Node projects. If yes, refer to the wj-config package. This configuration package mimics the capabilities of .Net Configuration closely. You'll be able to do what's explained here for your Node projects.

90% or more of the developers will just take the usual, simplistic approach of having a ConnectionStrings section in the configuration appsettings.json file and go with that. Why am I calling it simplistic? Because there's a better way: The DRY way.

You see, having to specify the entire connection string on your various environment configuration files makes you repeat data such as the database name or your service account name. Since non-DRY advocates pretty much go to hell and deserve no forgiveness, let me show you a DRY approach to database connection strings, so you can all save your souls from eternal damnation.

The IDbConnectionFactory Service Definition

In order to also comply with dependency injection, I'll explain this in a dependency injection-compliant manner. To do this, we will create a special service: The IDbConnectionFactory service will be in charge of creating database connections whenever they are needed.

Here's the definition:

using System.Data;

/// <summary>
/// Service capable of creating database connections on demand.
/// </summary>
public interface IDbConnectionFactory
{
    /// <summary>
    /// Creates a database connections and returns it ready for use (in an open state).
    /// </summary>
    /// <returns>The newly created database connection.</returns>
    Task<IDbConnection> CreateAsync();
}

Great. The CreateAsync() method takes no parameters, so it is very easy to use (the consumer doesn't have to know about anything database-related), and that means the connection factory must know everything that is database connection-related: The connection string.

Let's implement this interface for the arguably most popular RDBMS: Microsoft SQL Server. Before this, though, we must address the DRY-ness of the configuration files.

DRY-ing Connection Strings Up

Knowing the problem makes it easy to work on a solution, and the solution is pretty simple. We will rearrange the elements in the connection string in a manner that can be easily targetted for override by the .Net Configuration system. Something like this for appsettings.json:

{
  "SqlServer": {
    "Server": "my.sqlserver.enterprise.example.com",
    "Port": 1433,
    "Database": "my-db",
    "IntegratedSecurity": false,
    "Username": "sys_username",
    "Password": "undisclosed",
    "Mars": true,
    "ConnectionTimeout": "00:00:10"
  }
}

NOTE: SQL Server's default port number is 1433.

Now that we have this in our all-environment configuration file, we only need to work out the per-environment overrides. For example, usually the Development environment is for running the project locally in the developer's PC. Usually this means that our database server is localhost and that we will use Windows Authentication (integrated security) to connect. So appsettings.Development.json would look like this:

{
  "SqlServer": {
    "Server": "localhost",
    "IntegratedSecurity": true
  }
}

See? We will still have full port, MARS and connection timeout specifications for Development, and we did not have to repeat said values because they have already been specified in the master configuration file. DRY matters.

Now, in order to read this and inject it to our implementation of the IDbConnectionFactory service for SQL Server, we will create a simple POCO class that matches what we have written in configuration.

Configuring .Net Configuration To Meet Our Purposes

This would be the POCO class we would use to read configuration:

public class SqlServerConnectionOptions
{
    #region Properties
    public string Server { get; set; }
    public int Port { get; set; }
    public string Database { get; set; }
    public bool IntegratedSecurity { get; set; }
    public string Username { get; set; }
    public string Password { get; set; }
    public bool Mars { get; set; }
    public TimeSpan? ConnectionTimeout { get; set; }
    public int? MaxPoolSize { get; set; }
    #endregion
}

I even threw a bonus property there to control the maximum number of SQL connections in the ADO.net connection pool.

Now configure it.

IConfigurationSection section = builder.Configuration.GetSection("SqlServer");
builder.Services.Configure<SqlServerConnectionOptions>(section);

Just like that the options are ready to be consumed via dependency injection anywhere by requesting a service of type IOptions<SqlServerConnectionOptions>.

The SqlServerConnectionFactory Service

This would be our culmination of preparations. This will be a class that implements the IDbConnectionFactoryinterface.

using System.Data;
using Microsoft.Data.SqlClient;
using Microsoft.Extensions.Options;

public class SqlServerConnectionFactory : IDbConnectionFactory
{
    #region Fields
    private Lazy<string> _connString;
    #endregion

    #region Constructors
    public SqlServerConnectionFactory(
        IOptions<SqlServerConnectionOptions> options
    )
    {
        _connString = new Lazy<string>(() =>
        {
            var opts = options.Value;
            // We use a special string builder.
            SqlConnectionStringBuilder sb = new SqlConnectionStringBuilder();
            sb.DataSource = $"{opts.Server},{opts.Port}";
            sb.InitialCatalog = opts.Database;
            sb.IntegratedSecurity = opts.IntegratedSecurity;
            if (!opts.IntegratedSecurity)
            {
                sb.UserID = opts.Username;
                sb.Password = opts.Password;
            }
            sb.MultipleActiveResultSets = opts.Mars;
            if (opts.ConnectionTimeout.HasValue)
            {
                sb.ConnectTimeout = (int)opts.ConnectionTimeout.Value.TotalSeconds;
            }
            if (opts.MaxPoolSize.HasValue)
            {
                sb.MaxPoolSize = opts.MaxPoolSize.Value;
            }
            // Return the resulting connection string.
            return sb.ToString();
        });
    }

    #endregion

    #region IDbConnectionFactory
    public async Task<IDbConnection> CreateAsync()
    {
        SqlConnection conn = new(_connString.Value);
        await conn.OpenAsync();
        return conn;
    }
    #endregion
}

This implementation takes the kind offer from Microsoft and makes use of the SqlConnectionStringBuilder class. With this class, we simply specify individual values, that so happen to coincide with what we did in our configuration section, and finally generate the connection string.

The whole connection string building, while it doesn't take much processing time, is here presented inside a Lazy<> object to make sure we don't pay for this processing unless it is really needed.

Connecting IDbConnectionFactory With Your ORM

This depends on the choice of ORM. With my beloved Dapper, all you need is a connection and it works. This means that I usually program Dapper using the Repository Pattern. So my repositories would require an instance of the IDbConnectionFactory and simply append Dapper to the newly created connection.

With EF, I personally would still use the Repository Pattern, but the connection factory is actually injected to the db context, and it is the db context that is injected to the repository.

Dapper Example

Ok, so let's start with full quality:

using System.Data;
using Dapper;

public class PerfectRepository<TEntity>
{
    #region Properties
    private IDbConnectionFactory ConnFactory { get; set; }
    #endregion

    #region Constructors
    public PerfectRepository(
        IDbConnectionFactory connFactory
    )
    {
        ConnFactory = connFactory;
    }
    #endregion

    #region Methods
    public async Task<TEntity> GetAsync(int id)
    {
        using IDbConnection conn = await ConnFactory.CreateAsync();
        return await conn.QuerySingleAsync<TEntity>("select * from myTable where Id = @id;", new { id });
    }
    #endregion
}

Easy stuff, uncomplicated, to the point, etc. etc. Dapper is, in one word, heavenly.

Now let's see about the incarnation of the devil itself: Entity Framework

Entity Framework Example

So, we must start with the DB context. It so happens that the DB context can be initialized with a SQL connection object, so let's take advantage of that.

using Microsoft.Data.SqlClient;
using Microsoft.EntityFrameworkCore;

public class MyDbContext : DbContext
{
    #region Properties
    private IDbConnectionFactory ConnFactory { get; set; }
    #endregion

    #region Constructors
    public MyDbContext(
        IDbConnectionFactory connFactory
    )
    {
        ConnFactory = connFactory;
    }
    #endregion

    #region Methods
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        // For starters, this is not an asynchronous method.  So hack away or make the 
        // IDbConnectionFactory.CreateAsync() method synchronous.  Your choice.
        // I'll hack here.
        var connTask = ConnFactory.CreateAsync();
        connTask.RunSynchronously();
        // We also must cast here.  Right away two things I dislike.
        optionsBuilder.UseSqlServer((SqlConnection)connTask.Result);
    }
    #endregion
}

The above is a DB context class that requests via dependency injection an instance of the IDbConnectionFactory interface. This is then used to obtain a connection to the database.

There are two issues I dislike:

1. The OnConfiguring method is synchronous. This means that we must create the connection synchronously. This is less than ideal for reasons I won't discuss here.
2. The SqlServerDbContextOptionsExtensions.UseSqlServer() extension methods accepts an instance of the DbConnection class, so it forces a cast. 95% of all casts mean we are doing something wrong.

IMPORTANT: All statistical percentage values in this article came out of a very serious survey I made to the programming population in my head. :-)

It is what it is, this spawn from hell called database context. Let's move on.

Now we should be able to create a repository based on the DB context:

using Microsoft.EntityFrameworkCore;

public class TaintedRepository<TEntity>
    where TEntity : class
{
    #region Properties
    private DbSet<TEntity> DbSet { get; }
    #endregion

    #region Constructors
    public TaintedRepository(
        MyDbContext context
    )
    {
        DbSet = context.Set<TEntity>();
    }
    #endregion

    #region Methods
    // See about Model Features to actually make this work.
    public Task<TEntity> GetAsync(int id)
        => DbSet.Where(x => x.Id.Equals(id)).SingleOrDefaultAsync();
    #endregion
}

About Model Features: To actually make the above work, you need Model Features.

---

Conclusion

So there it is. Now you have successfully DRY'ed the connection string up and all is good once more in the DRY world. Most importantly, your soul is now saved.

Now you can also override specific values like database name or server or port without copying and pasting the entire connection string around. This also makes it super simple to provide passwords via environment variables, either manually or via K8s Opaque Secrets. The environment variable name for the password for the example we worked on would be SQLSERVER__PASSWORD.

Let me know in the comments section if you have any doubts or concerns.

---

OPTIONAL: Making it Better for Entity Framework

I gave EF a hard time in this article, I know. I may have purposedly set it up for failure a bit. For example, if we do not enforce opening the connection to the database inside the connection factory, then the CreateAsync() method can be made synchronous. I purposely do this because of my Dapper preference. This way I don't have to open the connection everywhere. I know, I'm a bad person.

As for the forced cast, I think Microsoft could have made an effort and define the parameter to be of type IDbConnection. Honestly, I don't know why they didn't. Maybe there is a good reason for it. Since I am not motivated enough to go through the trouble of finding out, I guess we'll live in the darkness on this one. What one can do is probably make an ISqlServerDbConnectionFactory service that returns a SqlConnection object from CreateAsync(). This way no casting is necessary. It is probably more sane too, if you foresee the need of connecting to other types of database servers, like PostgreSQL or MariaDB. In those cases, it is best to have a connection factory service definition per database engine.