mariocatch mariocatch - 2 months ago 48
ASP.NET (C#) Question

Using ASP.NET Core and EntityFramework 7 to update production DB with migration changes in Azure

I have a complete web app working locally, and I am now working on getting it going in production. The app is currently sitting on Azure, and watching a git repo for new deployments, which is working great.

However, the app has a connection in its

appsettings.json
for a connection string, which looks like this:

"database": {
"connection": "Data Source=(localdb)\\mssqllocaldb;Initial Catalog=Foo"
},

// In Startup()
var builder = new ConfigurationBuilder()
.AddJsonFile("appsettings.json")
.AddEnvironmentVariables();

// In ConfigureServices(...)
services.AddEntityFramework()
.AddSqlServer()
.AddDbContext<FooDbContext>(options =>
{
options.UseSqlServer(Configuration["database:connection"]);
});


This is fine for local testing, but now I'm ready to move to production and have some questions (couldn't find good documentation).


  1. How do I use the
    dnx
    command line to push my changes to a production DB? Everything is tied to the DB defined by the app, statically, so it'll by default go to my local DB, always.

  2. Once the DB is provisioned in Azure, do I simply need to modify my connection string in the settings on Azure for the web app, like this?



enter image description here

Answer

Basics

You've said that you have two configs. One for local, one for production, with a connection string in each. In that case, if your production config is called appsettings.production.json you can do this:

Startup()

var builder = new ConfigurationBuilder()
    .AddJsonFile("appsettings.json")
    .AddJsonFile($"appsettings.{env.EnvironmentName}.json", optional: true)
    .AddEnvironmentVariables();

ConfigureServices(...)

services.AddEntityFramework()
    .AddSqlServer()
    .AddDbContext<FooDbContext>(options =>
    {
        options.UseSqlServer(Configuration["database:connection"]);
    });

appsettings.production.json

{
  "database": {
    "connection": "Server=tcp:yr3d5dswl.database.windows.net,1433;Database=EFMigrationDemo;User ID=mvp2015@yr3d5dswl;Password=3f4g%^BD45bcE;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;"
  }
}

Command Line

dotnet ef database update -e Production

This will update your production database.

Advanced

Integrate with your continuous integration. Have the migration update the live database on build by adding a postbuild script to your project.json. Azure will run this on each deployment.

"scripts": {
  "postbuild": [ "dotnet ef database update" ]
}

If you want to use environmental variables instead of a appsettings.production.json file, checkout Setting the SQL connection string for ASP.NET 5 web app in Azure. This keeps your username/password out of your git repo.

References

Comments