dotnet ef separate database provider for dev/prod-CodePudding

Trying to use sqlite in local developemnt, and sqlserver for production.
If I have the provider switched depending on environment, there's still the issue of migrations only being generated for one of the providers (sqlite migrations don't work on sqlserver, and vice versa).

What's a viable way of using a separate db for dev than in prod?

CodePudding user response：

The trick will be to create a separate dbcontext for dev, having it inherit the main dbcontext and just changing the connection string. From there, it's a matter of generating migrations for the primary dev dbcontexts, then picking which context to use based on environment.

Code setup

The main dbcontext will use dependency injection to get the configuration containing your connection strings.

using app.Models;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Configuration;

namespace app.Data
{
    public class AppDbContext : DbContext
    {
        protected readonly IConfiguration Configuration;

        public AppDbContext(IConfiguration config)
        {
            Configuration = config;
        }

        protected override void OnConfiguring(DbContextOptionsBuilder options)
        {
            options.UseSqlServer(Configuration["SQLConnectionString"]);
        }

        // whatever your app does
        public DbSet<Dog> Dogs { get; set; }
    }
}

The dev dbcontext will just inherit the main one, with a different configuring block to specify sqlite instead.

using app.Models;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Configuration;

namespace app.Data
{
    public class DevAppDbContext : AppDbContext
    {
        public DevAppDbContext(IConfiguration config) : base(config)
        {
        }

        protected override void OnConfiguring(DbContextOptionsBuilder options)
        {
            options.UseSqlite(Configuration.GetConnectionString("app"));
        }
    }
}

To our appsettings.Development.json we will add the dev db connection string

...
  "ConnectionStrings": {
    "app": "Data Source=app.db"
  }
}

In startup.cs we will add the IWebHostEnvironment via dependency injection to get the environment. If in dev, then we will specify the additional generic parameter specifying the dev dbcontext as the implementation.

from the docs in vscode, the method signature is: public static IServiceCollection AddDbContext<TContextService, TContextImplementation>


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

        private readonly IConfiguration _config;
        private readonly IWebHostEnvironment _env;

        public void ConfigureServices(IServiceCollection services)
        {
            if (_env.IsDevelopment())
                services.AddDbContext<AppDbContext, DevAppDbContext>();
            else
                services.AddDbContext<AppDbContext>();
            services.AddDatabaseDeveloperPageExceptionFilter();

Adding migrations

Adding migrations now requires switching environments and specifying the dbcontext we are creating migrations for. This can be abstracted to a fairly simple powershell script:

[CmdletBinding()]
param(
    [string]$Name
);

$prev = $Env:ASPNETCORE_ENVIRONMENT;
Write-Host "Previous env=""$prev""";
try {
    Write-Host "Adding migration for production";
    $Env:ASPNETCORE_ENVIRONMENT = "Production";
    dotnet ef migrations add $Name `
        --context "AppDbContext" `
        --output-dir "Migrations/SqlServerMigrations";
        
    Write-Host "Adding migration for development";
    $Env:ASPNETCORE_ENVIRONMENT = "Development";
    dotnet ef migrations add $Name `
        --context "DevAppDbContext" `
        --output-dir "Migrations/SqliteMigrations";
}
finally {
    Write-Host "Restoring env=""$prev""";
    $Env:ASPNETCORE_ENVIRONMENT = $prev;
}

Example usage:

> .\scripts\add-migration.ps1 -Name init
Previous env=""
Adding migration for production
Build started...
Build succeeded.
Done. To undo this action, use 'ef migrations remove'
Adding migration for development
Build started...
Build succeeded.
Done. To undo this action, use 'ef migrations remove'
Restoring env=""

Applying migrations

Same as creating migrations, we need to switch contexts depending on the environment. This time though, we will only update a single environment as specified by the user when invoking the script.

[CmdletBinding()]
param(
    [string]
    [ValidateSet("Development", "Production")]
    $Environment
)

$context = @{
    "Development" = "DevAppDbContext" 
    "Production"  = "AppDbContext"
}.$Environment;

$prev=$Env:ASPNETCORE_ENVIRONMENT;
try {
    $Env:ASPNETCORE_ENVIRONMENT=$Environment;
    dotnet ef database update --context $context;
}
finally {
    Write-Host "Restoring env=""$prev""";
    $Env:ASPNETCORE_ENVIRONMENT = $prev;
}

Usage example:

> .\scripts\apply-migrations.ps1 -Environment Development
Setting environment to Development
Build started...
Build succeeded.
No migrations were applied. The database is already up to date.
Done.
Restoring env=""

Conclusion

This should be enough to get started with using sqlite for local development, and sqlserver for live. Some limitations of this approach is that sqlite doesn't support all the features of sqlserver, so you will either have to set up a dev sqlserver db as well, or will need to decide to avoid using those features.

Code setup

Adding migrations

Applying migrations

Conclusion

Further reading