Published: January 31 2022

.NET 6.0 - Database Migrations to Different DB Per Environment (SQLite in Dev, SQL Server in Prod)

Tutorial built with .NET 6.0

Other versions available:

In this post we'll go through an example of how to setup a .NET 6.0 project with Entity Framework Migrations that supports multiple different database providers.

The code snippets in this post are taken from an example .NET API I posted recently that supports multiple databases for different environments, specifically SQLite in development and SQL Server in production. For more info or to download and test the API locally see .NET 6.0 - User Registration and Login Tutorial with Example API.

The below steps show how to use a SQLite database in development and a SQL Server database in production, but you could change these to any other database providers that are supported by Entity Framework.


Create Main Entity Framework DbContext for SQL Server

Create the main database context class that defines the entities available in the database via public DbSet<TEntity> properties, and configure it to connect to the production database (SQL Server in this case).

Below is the main database context from the example .NET 6.0 API linked above, it has the class name DataContext and is located in the /Helpers directory of the project, but you can choose any class name and directory you prefer.

namespace WebApi.Helpers;

using Microsoft.EntityFrameworkCore;
using WebApi.Entities;

public class DataContext : DbContext
{
    protected readonly IConfiguration Configuration;

    public DataContext(IConfiguration configuration)
    {
        Configuration = configuration;
    }

    protected override void OnConfiguring(DbContextOptionsBuilder options)
    {
        // connect to sql server database
        options.UseSqlServer(Configuration.GetConnectionString("WebApiDatabase"));
    }

    public DbSet<User> Users { get; set; }
}


Create Dev DbContext for SQLite

Create a development database context that inherits from the main context and changes the database provider by overriding the OnConfiguring() method.

Below is the dev database context from the example .NET 6.0 API, it inherits from DataContext above and changes the database provider to SQLite. Having a second Entity Framework Context that inherits from the main context is what enables the project to support multiple different databases.

namespace WebApi.Helpers;

using Microsoft.EntityFrameworkCore;

public class SqliteDataContext : DataContext
{
    public SqliteDataContext(IConfiguration configuration) : base(configuration) { }

    protected override void OnConfiguring(DbContextOptionsBuilder options)
    {
        // connect to sqlite database
        options.UseSqlite(Configuration.GetConnectionString("WebApiDatabase"));
    }
}


Install dotnet ef tools

The .NET Entity Framework Core tools (dotnet ef) are used to generate EF Core migrations, to install the EF Core tools globally run dotnet tool install -g dotnet-ef, or to update run dotnet tool update -g dotnet-ef.

For more info on EF Core tools see https://docs.microsoft.com/ef/core/cli/dotnet.

For more info on EF Core migrations see https://docs.microsoft.com/ef/core/managing-schemas/migrations.


Generate SQLite Entity Framework Migrations

Run the following command to generate Entity Framework Migrations for SQLite and store them in their own folder.

dotnet ef migrations add InitialCreate --context SqliteDataContext --output-dir Migrations/SqliteMigrations


Generate SQL Server Entity Framework Migrations

Run the following command to generate Entity Framework Migrations for SQL Server and store them in their own folder.

The environment variable ASPNETCORE_ENVIRONMENT needs to be set to Production so the SQL Server DataContext class is configured with the .NET dependency injection (DI) system, see the ConfigureServices() method below.

Configuring environment variables from the command line is slightly different between Windows Command, Windows PowerShell and MacOS.

Windows Command

set ASPNETCORE_ENVIRONMENT=Production
dotnet ef migrations add InitialCreate --context DataContext --output-dir Migrations/SqlServerMigrations


Windows PowerShell

$env:ASPNETCORE_ENVIRONMENT="Production"
dotnet ef migrations add InitialCreate --context DataContext --output-dir Migrations/SqlServerMigrations


MacOS

ASPNETCORE_ENVIRONMENT=Production dotnet ef migrations add InitialCreate --context DataContext --output-dir Migrations/SqlServerMigrations


Configure Program.cs to use SQLite in Development and SQL Server in Production

Below is the Program.cs file from the example .NET 6.0 API.

Lines 14 - 17 configure which type of data context is injected by the .NET dependency injection system when a DataContext instance is required by a class. In production an instance of the main DataContext class is used which connects to SQL Server, otherwise (i.e. in development) an instance of the SqliteDataContext is used.

Lines 36 - 40 manually get an instance of the DataContext class from the .NET DI system to apply database migrations if required, including initial database creation if the db doesn't exist yet.

using Microsoft.EntityFrameworkCore;
using WebApi.Authorization;
using WebApi.Helpers;
using WebApi.Services;

var builder = WebApplication.CreateBuilder(args);

// add services to DI container
{
    var services = builder.Services;
    var env = builder.Environment;
 
    // use sql server db in production and sqlite db in development
    if (env.IsProduction())
        services.AddDbContext<DataContext>();
    else
        services.AddDbContext<DataContext, SqliteDataContext>();
 
    services.AddCors();
    services.AddControllers();

    // configure automapper with all automapper profiles from this assembly
    services.AddAutoMapper(typeof(Program));

    // configure strongly typed settings object
    services.Configure<AppSettings>(builder.Configuration.GetSection("AppSettings"));

    // configure DI for application services
    services.AddScoped<IJwtUtils, JwtUtils>();
    services.AddScoped<IUserService, UserService>();
}

var app = builder.Build();

// migrate any database changes on startup (includes initial db creation)
using (var scope = app.Services.CreateScope())
{
    var dataContext = scope.ServiceProvider.GetRequiredService<DataContext>();    
    dataContext.Database.Migrate();
}

// configure HTTP request pipeline
{
    // global cors policy
    app.UseCors(x => x
        .AllowAnyOrigin()
        .AllowAnyMethod()
        .AllowAnyHeader());

    // global error handler
    app.UseMiddleware<ErrorHandlerMiddleware>();

    // custom jwt auth middleware
    app.UseMiddleware<JwtMiddleware>();

    app.MapControllers();
}

app.Run("http://localhost:4000");

 


Need Some .NET Help?

Search fiverr for freelance .NET developers.


Follow me for updates

On Twitter or RSS.


When I'm not coding...

Me and Tina are on a motorcycle adventure around Australia.
Come along for the ride!


Comments


Supported by