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

Question

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).

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.
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?

This post explains EF7 migrations with dnx. Check it msdn.microsoft.com/en-us/magazine/mt614250.aspx — Gandhali Samant
– Gandhali Samant, Commented Apr 6, 2016 at 16:42
That article doesn't explain how to apply a database update to a production DB. — mariocatch
– mariocatch, Commented May 2, 2016 at 13:43
Yes, I have two configs. One for local, one for production, with a connection string in each. — mariocatch
– mariocatch, Commented May 2, 2016 at 14:20

Community · Accepted Answer · 2017-05-23 12:01:46Z

6

+50

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

edited May 23, 2017 at 12:01

CommunityBot

11 silver badge

answered May 3, 2016 at 2:26

Shaun Luttin

143k93 gold badges442 silver badges495 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

mariocatch Over a year ago

How would you test a production Azure SQL connection string locally? I set an environment variable of set database:connection=my_production_connection_string and my Configuration.Get<string>("database:connection") fails to find any environment variable. If I inspect the environment variables, I see one with this though: Data:database:connection:ConnectionString. with the same value that I just set for database:connection. Why is it transformed like that? And how can I possibly access that from my code while still allowing my Development environment to work too?

Shaun Luttin Over a year ago

What method are you using to inspect environmental variables?

Shaun Luttin Over a year ago

@mariocatch While I am not sure why it is transformed, it does seem related to conventions. Try accessing it with Configuration.Get("Data:database:connection"). Also set your config.json value to "Data" : { "database" : { "connection" : "..." } }. There are more details here stackoverflow.com/questions/31097933/…

mariocatch Over a year ago

Yep, the key looks to be to use the format "Data": { "my_db": { "ConnectionString": "..." } }. I am testing how this works from Azure now and will respond back if there's an issue.

mariocatch Over a year ago

Looks like the postbuild step of dnx ef database update isn't running. My DB is not created with that step in there. Also, after inspecting the Kudu SCM environment variables, the connection string that I setup in my Azure Web App connection strings UI is: SQLAZURECONNSTR_Data:my_database:ConnectionString. So, that may be why all my connection string lookups are failing in production? Although I think the provider is supposed to trim the azure prefix off and still find/use it.

|

Collectives™ on Stack Overflow

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

1 Answer 1

Basics

Advanced

References

9 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Basics

Advanced

References

9 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related