ASP.Net Core Web Api - Use production and test database connection strings

Question

I am building a web api with asp.net core and I wanted to ask how you would handle production and test environments inside the api. The goal would be to publish the web api on the IIS and then for it to use the production db connection string. When I start my api locally I would like my api to connect to the test database without me changing a lot of stuff in the code.

In my Startup.cs I use Entity Framework like this:

services.AddDbContextFactory<ProdContext>(options =>
{
    string decrypted = ConnStringSecurity.Decrypt(Configuration.GetConnectionString("ProdDBConnection"));

    options.UseSqlServer(decrypted,
    sqlServerOptionsAction: sqlOptions =>
    {
        sqlOptions.EnableRetryOnFailure(
        maxRetryCount: 10,
        maxRetryDelay: TimeSpan.FromSeconds(5),
        errorNumbersToAdd: null
        );
    });
});

services.AddDbContextFactory<TestContext>(options =>
{
    string decrypted = ConnStringSecurity.Decrypt(Configuration.GetConnectionString("TestDBConnection"));

    options.UseSqlServer(decrypted,
    sqlServerOptionsAction: sqlOptions =>
    {
        sqlOptions.EnableRetryOnFailure(
        maxRetryCount: 10,
        maxRetryDelay: TimeSpan.FromSeconds(5),
        errorNumbersToAdd: null
        );
    });
});

In the Configure method I see that you can differentiate between development and production but I can't quite imagine how the DbContexts can be integrated in the if statement:

public void Configure(IApplicationBuilder app, IWebHostEnvironment env, ILoggerFactory loggerFactory)
{
    if (env.IsDevelopment())
    {
        //Use Test Db globally
        app.UseDeveloperExceptionPage();
    }
    else if(env.IsProduction())
    {
       //Use Prod Db globally
    }


}

Also in every Controller I inject the Context that I need so to make this happen I would have to check on every endpoint if I am currently in development or production. Is there a more efficient way to do this? The 'lazy' approach would be to publish two instances of my api, one for prod and one for test where the code is changed accordingly.

https://www.tutorialsteacher.com/core/aspnet-core-environment-variable — MichaelMao, Aug 05 '22 at 06:23
Just start with different connectionstring in your appsettings.json files. After that you can start further improve that and move connectionstrings for example to keyvault or whatever you prefer. — user743414, Aug 05 '22 at 06:40
Please refer this as well to use a DBContext common for all controllers https://stackoverflow.com/questions/62411410/is-there-any-better-way-to-add-the-dbcontext-to-a-asp-core-mvc-controller — Ananth MK, Aug 05 '22 at 07:22

score 2 · Accepted Answer · answered Aug 05 '22 at 07:16

You can inject IWebHostEnvironment to the Startup class by its constructor, which is the same one as you have among the parameters of Startup.Configure method. Then, you can use env.IsDevelopment() in the Startup.ConfigureServices method, where you set up DB context.

I wonder if you really want to different DB contexts in the development and production environment.

So, having a single context MyDbContext for both environments, Startup class becomes like below. Then, your controllers will get MyDbContext instance injected which connects to a different DB by the environment.

    public class Startup
    {
        public IConfiguration Configuration { get; }
        private readonly IWebHostEnvironment _env;

        public Startup(IConfiguration configuration, IWebHostEnvironment env)
        {
            Configuration = configuration;
            _env = env;
        }

        public void ConfigureServices(IServiceCollection services)
        {
            services.AddDbContext<MyDbContext>(options =>
            {
                string decrypted = ConnStringSecurity.Decrypt(Configuration.GetConnectionString(
                    _env.IsDevelopment() ?  "TestDBConnection" : "ProdDBConnection"));
                options.UseSqlServer(decrypted,
                    sqlOptions =>
                    {
                        sqlOptions.EnableRetryOnFailure(
                            maxRetryCount: 10,
                            maxRetryDelay: TimeSpan.FromSeconds(5),
                            errorNumbersToAdd: null
                            );
                    });
            });

Sil · Answer 2 · 2022-08-05T07:18:51.783

The Microsoft docs article Use multiple environments in ASP.NET Core contains an example:

var builder = WebApplication.CreateBuilder(args);

// Add services to the container.
builder.Services.AddRazorPages();

var app = builder.Build();

// Configure the HTTP request pipeline.
if (!app.Environment.IsDevelopment())
{
    app.UseExceptionHandler("/Error");
    // The default HSTS value is 30 days. You may want to change this for production scenarios, see https://aka.ms/aspnetcore-hsts.
    app.UseHsts();
}

app.UseHttpsRedirection();
app.UseStaticFiles();

app.UseRouting();

app.UseAuthorization();

app.MapRazorPages();

app.Run();

EDIT

I did not notice you have a DbContexts for each environment so I didn't take that into consideration in my answer. You should really just stick with one single DbContext instead of one for each environment unless you have a very good reason not to.

So, if you get rid of the "one DbContext per environment" idea you can just:

var builder = WebApplication.CreateBuilder(args);

builder.Services.AddDbContextFactory<MyDbContext>(options =>
{

    var cs = builder.Environment.IsDevelopment() ? "TestDBConnection" : "ProdDBConnection";

    string decrypted = ConnStringSecurity.Decrypt(Configuration.GetConnectionString(cs));

    options.UseSqlServer(decrypted,
    sqlServerOptionsAction: sqlOptions =>
    {
        sqlOptions.EnableRetryOnFailure(
        maxRetryCount: 10,
        maxRetryDelay: TimeSpan.FromSeconds(5),
        errorNumbersToAdd: null
        );
    });
});

And finallly in your controller you can just take a dependency on a single DbContext


class MyController 
{
    public MyController(MyDbContext dbContext) { ... }
}

EDIT 2

My solution is dotnet core 6.0 (net6.0) and above. For net5 go with yas-ikeda's answer instead.

score 0 · Answer 3 · answered Aug 05 '22 at 06:36

0

firstly, its is not a good practice to store you database connection strings in configuration files where you push them into source code management (ex: github, devops, etc).

when you are developing and running your app locally, then you can make use of the "Secret.json" file to store sensitive data like db connection strings which will not be pushed to repository and will not even appear in git changes.

you can create this file by right clicking on the API project -> select "Manage user secrets". this will create a file in your pc. this is similar to appsetting.json but will be only available on your pc while developing locally.

you can set the db connection string like same as in appsetting.json as show below.

you can add all your sensitive key value pairs on the secrets.json when developing. the same keys in the appsetting.json will be overridden by secrets.json if they have the same key.

for production you can create application level variables by the same key value pair or use key-vault, or use app configuration if you are hosting in azure web app service.

answered Aug 05 '22 at 06:36

thanzeel

431
4
12

Hi thanks for your suggestion but the connection strings are stored in an encrypted way inside my application.json. There is no plain text connection string which a human could read. – Florent Aug 05 '22 at 06:54
@Florent ,in that case, you can still use environment variables in your production. set an env variable with the same key for your db connection and set the value to the connections string. at runtime the configuration will be read in a precedence order. so the env variables have higher precedence that appsetting.json. so at runtime the application will choose the connection string which is production if you have set the env variable correctly – thanzeel Aug 05 '22 at 08:15
Yes I know that I could do that but you should imagine that I now have the ProdContext and the TestContext. At this point both Contexts are injected inside my controller. I know that I can get the information if I am on development or production but thats not the issue here. The issue is that I now have to check on very endpoint if I am on dev or prod in order for me to execute functions from the TestContext or the ProdContext. That seems to me a bit too much to do. I want to know if I can avoid making changes to all my endpoints or I should say solve this problem in a more efficient way – Florent Aug 05 '22 at 10:40
@Florent, why do you have 2 db ocntext? is there a special case? you can use just one context (using the test and prod have the same database schema) – thanzeel Aug 05 '22 at 10:46
The schemas and data in the prod db are backed up and then inserted into the test server on a daily basis. So maybe I should try it with only one DbContext actually. But the back up happens daily in the evening so if I make changes to the prod db then the schemas of the prod db would differ from the schemas in the test db. Would that be a considerable problem? – Florent Aug 05 '22 at 10:51
why would you change the prod db before applying the schema changes to the test db? both schemas must be the same unless the query which does the querying of data from the production and insert commands does not break. may be you must decouple these scenario from the application. you can write a console app pointing to the backup db or if you have azure, then azure data factory will be a good fit. – thanzeel Aug 05 '22 at 10:56

ASP.Net Core Web Api - Use production and test database connection strings

3 Answers3