Document pitfalls of migrating database when application starts

[caztial]

Need more code examples to understand how to use the migrations at runtime

---

Document Details

⚠ Do not edit this section. It is required for docs.microsoft.com ➟ GitHub issue linking.

• ID: 6e28b0a8-b7e4-4c07-9e60-0df403bbee28
• Version Independent ID: 12300b31-6f7b-b75c-970d-693365e77013
• Content: Migrations - EF Core
• Content Source: entity-framework/core/managing-schemas/migrations/index.md
• Product: entity-framework
• GitHub Login: @bricelam
• Microsoft Alias: bricelam

[ajcvickers]

@caztial Can you be more specific about what exactly you want to do at runtime?

[Rick-Anderson]

Applying migrations in production

[divega]

I think it is as important to align our guidance (including the guidance provided by the ASP.NET Core documentation) on when and why not to do this.

[jwbats]

I'd love to get some more feedback on why not to use EnsureCreated() and/or Migrate(). What's so robust about generating sql scripts instead? If the ef migrations tool is smart enough to generate 'robust' sql scripts, then why is it not simply using the same robustness in its Migrate() call?

What if you use a staging server to try out your Migrate() call on and it works well? Why not keep using Migrate() in production until your staging shows you a problem is developing?

[ajcvickers]

@jwbats The point is that the database migration must be done in a controlled way. The main things to guard against are:

• Multiple threads/processes/servers attempting to migrate the database concurrently.
• Applications trying to access the database while this is happening. (Unless you have very carefully planned migrations that explicitly allow for this.)

Other considerations are:

• Often the database permissions to modify the schema should not be given as part of normal application execution.
• It's sensible to have a clear plan for reverting the migration (and indeed the entire deployment) if it goes wrong.

It's possible to use Migrate and satisfy all these things. However, just running Migrate when your application starts clearly does not. We would love it if it were easy to run Migrate as part of a controlled application deployment. Realistically, for most people and deployments, a SQL script is easier to integrate into a controlled deployment.

[MaklaCof]

Applying migrations at runtime would be awesome feature. Together with System.Linq.Dynamic.Core we could easily implement custom columns.

For example: let's say you have a table Partners.
One customer needs additional column PaymentDeadline, so you could add it only to this customer and not to all.
Another would need SomeOtherColumn.

It is useless to add all this column to entity. You quickly get to the point where your table Partners have over 200 columns. But most of them are used by 1 or 2 customers.

[Rick-Anderson]

Allowing customers to add columns to a DB at run time is ultra niche and not something we'd recommend for the majority of apps.

[MaklaCof]

Agree. This would be done in startup and only by admin. Thinking something like this:

• in configuration file admin would add settings for additional fields
• in startup (before application starts) migrations would be applied based on configuration file

The point is not when migration is performed, but that you do not change the entity class.

[jwbats]

• Multiple threads/processes/servers attempting to migrate the database concurrently.
• Applications trying to access the database while this is happening. (Unless you have very carefully planned migrations that explicitly allow for this.)

For this reason, I put my migration update statement in the startup.cs file, before anything accesses it.

How else? A database needs to be updated before the new release runs on it. So I do not see a problem here.

Other considerations are:

• Often the database permissions to modify the schema should not be given as part of normal application execution.

Being a full time dev, I never cared much for doing db adminstration. Whenever I have access to the db, I give the db user the db_owner role. Whether my application changes the database or not, depends on what its code does. If I don't provide schema changing code in a part of the application that is not startup.cs (read: the parts that users actually use), then obviously nobody will be able to change the schema by using my application as intended.

I don't see much point in db user restrictions. Maybe it's because I never write enterprise stuff. But I see no need for it in my own situation.

• It's sensible to have a clear plan for reverting the migration (and indeed the entire deployment) if it goes wrong.

I'd use a staging server to test it, first. If it goes well on staging, why would it fail on production?

Also, having your application update the schema, does not prevent you from updating the schema manually with ef-generated sql scripts.

And what reason do we have to think that ef's generated scripts modifies the schema in a safer manner than ef's update statement in startup.cs?

It's possible to use Migrate and satisfy all these things. However, just running Migrate when your application starts clearly does not. We would love it if it were easy to run Migrate as part of a controlled application deployment. Realistically, for most people and deployments, a SQL script is easier to integrate into a controlled deployment.

After deliberation of the above, I'm putting the update statement in startup.cs. I can find no downsides to this for my use case.

[GF-Huang]

Is there any examples how to migrations in docker-compose.yml which using MySql and Asp.Net Core ? (If do not call Database.Migrate() as you say)

[dazinator]

@ajvickers @divega

on when and why not to do this.

+1
If you can spell out the pitfalls and edge cases around why you might not want to do this (without the assertion that you shouldn't), that would be useful. Developers can draw their own conclusions - It's possible for this still to be the best option for many projects, for reasons I have mentioned on a previous revision of this comment and have now edited out to cut the chatter :-)

More specifically here are some behaviours I was interested to read up on but couldn't find much / any documentation on to do with migrations:

1. What happens if multiple nodes (in a farm) try to Migrate at the same time? (I think it is mentioned that you should guard against this but it would be useful if this scenario was outlined in a bit more detail)
2. What kind of things can you do with migrations in code - i.e can you get a list of the pending migrations before applying them via Migrate() etc etc? Can you apply 1 at a time? Multiple in one go up to a particular pending migration but no further? Can you roll back a migration just applied if for example you then do some smoke test?
3. Can you call Migrate() on different types of DbContexts (FooDbContext, BarDbContext) that all happen to point to the same database instance (i.e same connection string)? Can you do that concurrently (i.e if if the Models don't overlap at all?)

[johnnyreilly]

Hey all,

I've been reading around on best approaches for applying migrations for an ASP.NET Core application in Docker.

I've been somewhat confused from what I've read. From what @ajcvickers says here: #814 (comment) the suggestion is that applying migrations upon app startup is not considered good practice.

A particular concern is where you have multiple containers running your app. What if they all attempted migration at once? Given the .Migrate() is not (to my knowledge) idempotent that's unsafe. As @ajcvickers says:

Multiple threads/processes/servers attempting to migrate the database concurrently.

But oddly enough, the Microsoft docs seem to advise applying migrations on startup in a docker container. Consider this piece of documentation by the wonderful @julielerman which advocates this approach:

https://docs.microsoft.com/en-us/archive/msdn-magazine/2019/april/data-points-ef-core-in-a-docker-containerized-app#migrating-the-database

Despite @ajcvickers advice, there don't seem to be any docs out there showing a good way to apply migrations upon deployment using Docker.

Are there plans to add some? Or are they out there somewhere and I just haven't stumbled upon them? This question has been looked at by @bricelam before but it doesn't look like that's turned into standard advice that I can see: dotnet/dotnet-docker-samples#89

[dazinator]

Also is multiple Migrate() calls happening at once the only concern?

[AndriySvyryd]

We are actively working on this: dotnet/efcore#19587