Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Migrations: Improve existing database support #2167

Open
bricelam opened this Issue May 12, 2015 · 37 comments

Comments

Projects
None yet
@bricelam
Copy link
Member

bricelam commented May 12, 2015

We need to create a good experience to bootstrap an existing database with Migrations.

Things to consider:

  • The database may have been using Migrations in EF6
  • The first migration should be able to re-create the starting database schema
  • The user shouldn't have to jump through hoops like commenting out code or only running part of a script.
@divega

This comment has been minimized.

Copy link
Member

divega commented May 12, 2015

This need came up while prototyping the solutions for aspnet/Identity#457. In that particular scenario a few new columns need to be added to the database as part of the upgrade, which may or may not pose an additional challenge.

In general EF Core Migrations cannot predict the current schema of a database regardless of how it was created and whether there are additional model changes in the upgrade. Even if the database was maintained using EF6 Migrations there are functional differences between the stacks that will make the database look different.

Here is a set of ideas we talked about that could be used to address this:

  1. We add a way to point our reverse engineering engine to the existing database to obtain a model snapshot (and possibly an accompanying initial migration).
  2. The user creates a migration based on the current EF Core model and the snapshot obtained in the first step.
  3. The user looks at this migration and decides which adjustments should be made to the model (e.g. column and tables renames in OnModelCreating()) to better match the existing database.
  4. After each adjustment to the model the user can go back and re-scaffold the migration created in step 2, iterating over steps 2 and 3 as many times as necessary until a migration representing the desired diff is obtained (the migration should be empty if the model was made match the database completely).
  5. If there are outstanding changes and the database needs to be upgraded the user can make any necessary manual edits to the migration and apply it.
  6. At this point the database in the model can be considered in sync and the user can proceed using Migrations normally.

As @bricelam mentioned above, the database could have been created using EF6 Migrations in which case we will need to do something with the migrations history table, i.e. we either make the EF Core table compatible with the EF6 one in which case we can simply add a new row or we may have to drop/move the table and existing migration history rows somewhere else.

@rowanmiller rowanmiller added this to the 7.0.0 milestone May 13, 2015

@divega

This comment has been minimized.

Copy link
Member

divega commented Jul 24, 2015

Noticed this issue has the pri0 but hasn't been assigned. Assigning to @bricelam since he owns our migrations story.

@divega

This comment has been minimized.

Copy link
Member

divega commented Jul 24, 2015

Also, note that the proposal described in a previous comment should be much easier to implement if the IModel that is the output of the reverse engineering pipeline used the same annotations and other constructs that a regular model produced by code first would use (see #2714).

@rowanmiller

This comment has been minimized.

Copy link
Member

rowanmiller commented Dec 10, 2015

Decided we will handle this with docs. Because there are so many nuances to how you might want it to work, we don't think we can build a high level feature that just works. We may drive improvements to the building blocks as we write the docs.

aspnet/EntityFramework.Docs#91

@bricelam bricelam modified the milestone: 7.0.0 Dec 10, 2015

@bricelam bricelam removed their assignment Dec 10, 2015

@rowanmiller rowanmiller changed the title Migrations: Existing database support Migrations: Improve existing database support Jan 12, 2016

@rowanmiller rowanmiller removed the pri0 label Jan 12, 2016

@rowanmiller rowanmiller added this to the Backlog milestone Jan 12, 2016

@rowanmiller rowanmiller reopened this Jan 12, 2016

@ToddThomson

This comment has been minimized.

Copy link

ToddThomson commented Jul 12, 2016

Is there any current guidance for porting/migrating ASP.NET MVC Identity 2 sites with existing databases ( and migrations ) to use Identity 3?

@bricelam

This comment has been minimized.

Copy link
Member Author

bricelam commented Jul 12, 2016

The EF6 documentation Code First Migrations with an existing database is still relevant. The -IgnoreChanges parameter on Add-Migration doesn't exist, but you can just delete the contents of the Up and Down methods if you go with that option.

@bricelam

This comment has been minimized.

Copy link
Member Author

bricelam commented Jul 12, 2016

Although there are a few more steps involved since the ASP.NET Identity schema also changed. @HaoK, is there a script somewhere to migrate from v2 to v3?

@HaoK

This comment has been minimized.

Copy link
Member

HaoK commented Jul 12, 2016

No script currently, @ToddThomson can you try out the prerelease compat package to migrate (assuming your app looks fairly similar to the Identity 2 template app)?

  • Run Enable-Migrations and ensure that an initial migration is created
  • Install AspNet.Identity.CoreCompat and include the compat namespace to rebase your Identity POCO
  • Run migrations using EF6 to update schema to V3
@theArianit

This comment has been minimized.

Copy link

theArianit commented Jul 12, 2016

Will Visual Studio's reverse engineer (model from database) wizard come back (anytime soon) for Asp.Net Core web apps? Cuz we need it, we Visual Studio developers

@ToddThomson

This comment has been minimized.

Copy link

ToddThomson commented Jul 12, 2016

@HaoK I take it that you can start with the generic ASP.NET MVC Web Application with Nuget package Identity 2.2.1 installed. The AspNet.Identity.CoreCompat must then be a nuget package? I cannot locate this package name. Where can this package be found?

@HaoK

This comment has been minimized.

@ToddThomson

This comment has been minimized.

Copy link

ToddThomson commented Jul 13, 2016

@hoak I get the following error during the install of AspNetCoreCompat:

Failed to add reference. The package 'Microsoft.AspNet.Identity.AspNetCoreCompat' tried to add a framework reference to 'System.Collections' which was not found in the GAC. This is possibly a bug in the package. Please contact the package owners for assistance.

@HaoK

This comment has been minimized.

Copy link
Member

HaoK commented Jul 13, 2016

Hrm package might not be updated correctly, in the mean time you can just try manually picking up the files it includes in your project and seeing if migrations works once you have your Identity POCOs derive from them (all these do is add the V3 schema changes basically)

https://github.com/aspnet/Identity/tree/dev/src/Microsoft.AspNet.Identity.AspNetCoreCompat

@ToddThomson

This comment has been minimized.

Copy link

ToddThomson commented Jul 13, 2016

@HaoK Since I couldn't install the AspNetCoreCompat package, I tried another approach. I created an ASP.NET generic web app with user accounts. I ran the web app which created the Identity v2 tables. I then created a generic ASP.NET Core web app. I used the same database connection string for both web apps. Using the ef dbcontext scaffold command I generated a dbcontext for the v2 schema and a v2 schema migration.

With a few changes to the v2 schema migration class I obtained the IdentityV2Schema migration within ASP.NET Core web app. By commenting out the up method I updated the database with this migration.

With the V2 schema migration I then added the IdentityV3Schema migration. The resulting migration needs to be changed slightly so that the alterations are done before the new tables are created. Updating the database now with the IdentityV3Schema migration yields a Identity V3 schema set of tables.

The IdentityV3Schema migration ( v2 to v3 really ) is everything that most people would need.

To login with the ASP.NET Core web app now, it appears that the dataprotection mechanism has changed. Would you please let me know how to use Identity v2 dataprotection with v3 Identity?

@ToddThomson

This comment has been minimized.

Copy link

ToddThomson commented Jul 13, 2016

@HaoK Sorry, I just notice that you posted a link to the AspNetCoreCompat source above. I see how the v2 data protection is used.

@bricelam

This comment has been minimized.

Copy link
Member Author

bricelam commented Sep 8, 2017

We closed #8645 today, but we'll need to re-consider it as part of this work.

@louislewis2

This comment has been minimized.

Copy link

louislewis2 commented Sep 28, 2018

Hi guys, is there any guidance on this topic yet?

My scenario is simply, that a product I am working on, always connects to an existing database.
The application is meant to simply add a single table. Migrations fail stating the database already exists.

I am using the latest EF Core bits.

Thanks

@ajcvickers

This comment has been minimized.

Copy link
Member

ajcvickers commented Oct 1, 2018

@louislewis2 If you have an existing database and a model that maps to it, then scaffold an initial migration, but remove all the code in the Up and Down methods before applying it. This will get the model snapshot and history table in a state that is in-sync with the database. Now you can move forward with changing the model and applying migrations as normal.

@bricelam bricelam removed their assignment Oct 12, 2018

@fizxmike

This comment has been minimized.

Copy link

fizxmike commented Nov 2, 2018

This is a solved problem in Django ORM using --fake-initial flag when applying migrations. @bricelam is your -BootstrapMigrations idea equivalent?

@bricelam

This comment has been minimized.

Copy link
Member Author

bricelam commented Nov 5, 2018

It’s similar except we’d scaffold the initial migration (and fake-apply it) for you.

@fizxmike

This comment has been minimized.

Copy link

fizxmike commented Nov 6, 2018

@bricelam sounds cool. I have some IMHOs to add...

Lets say I have a legacy pre-production (clone of production) database that I dotnet ef dbcontext scaffold from. I create the initial migration with your proposed flag dotnet ef migrations add InitialCreate -BootstrapMigrations, update the model, then create a new migration and apply it no problem.

How would I move to production? I'd expect to just be able to run dotnet ef database update targeting the legacy production database in a continuous delivery task. This would seem to work fine using the current workaround (comment out Up and Down methods). But the workaround prohibits me from easily creating a DB from scratch (for integration testing).

I'd expect dotnet ef migrations add InitialCreate -BootstrapMigrations to not touch the database. And if it did, have a way to reproduce it's "fake-apply" behavior, perhaps with a new flag here. But, if fake-apply functionality were available, it seems -BootstrapMigrations becomes redundant and of limited use (case where I'm dev'ing in production?).

Just having a --fake-apply flag is sufficient for the desired behavior (which is why I think Django settled with the --fake-initial approach). Sure, your first deployment with the new ORM will require a special flag, but all future migrations will work fine (including creating test databases from scratch).

... of course, I could be misunderstanding something about EF or your idea. Please feel free to correct my thinking.

@marchy

This comment has been minimized.

Copy link

marchy commented Feb 7, 2019

@bricelam is the '-BootstrapMigrations' idea being planned for any particular EFCore release yet?

The community needs SOME official answer for all this, and the current one-paragraph guidance in the docs is completely unsatisfactory for this.

We need something that supports both initial database creation and subsequent incremental migrations.

@rbluethl

This comment has been minimized.

Copy link

rbluethl commented Feb 12, 2019

We're having the same problem with an existing database. When trying to perform context.Database.Migrate() on an existing database, the error

There is already an object named '...' in the database.

is thrown. In this issue I saw that the only option is to add a record to the __EFMigrationsHistory table manually. Is this still the case? It would be very cool to have a better option available. Thanks.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.