Make it easier to work with database migrations

[ivanmonteiro]

Overview of the feature request

Lets make working with databases and migrations more easy specially for new users.
Some of the problems I found could be stated as pre-requisites on the project documentation (such as the global install of .net core ef tool), so If you find it interesting I can provide a pull request adding those pre-requisites to the docs.

For me, the current workflow is:
1 - generate application with no other entities

2 - Install the ef tool for .net core: dotnet tool install --global dotnet-ef --version 3.1.4
Suggestion: add this to pre-requisites on the documentation

3 - Add ef design package to project so migrations can be generated: dotnet add package Microsoft.EntityFrameworkCore.Design --version 3.1.4
Suggestion: add this package to the .csproj when creating the project using jhipster

4 - after that generate the initial migrations. They would contain all that tables required by the .net core identity to work (user, role, userrole, etc).
Suggestion: make it automatic after generator run

5 - make sure to execute migrations before first application run (otherwise .net core will auto generate identity tables and the migrations will fail)

6 - create other entities

7- create migration containing new entities tables
Suggestion: make it automatic after generator run

8 - run migrations

Motivation for or Use Case

Starting with the mysql database, I hit a few roadblocks on the way:

1 - dotnet ef command was not found.
I found out that in .net core v3 it was separated from the main component and now requires a nuget package install.
So I had to install it to be able to use migrations:
dotnet tool install --global dotnet-ef --version 3.1.4

2 - Migrations would not run because Microsoft.EntityFrameworkCore.Design package was required to create the migrations and was not in the project.
dotnet add package Microsoft.EntityFrameworkCore.Design --version 3.1.4

3 - After creating a new jhipster project with no entities, and running it for the first time,
I found out that the tables of ASP.NET Core Identity tables (User, role, roleuser, etc) were automatically created and my migrations would not run because the tables were alredy created. So migrations did not work because they were creating those exact same tables.

Related issues or PR

This is related to issue #98

• Checking this box is mandatory (this is just to show you read everything)

[nicolas63]

You're absolutely right !

Just few remarks/tips :

dotnet add package Microsoft.EntityFrameworkCore.Design --version 3.1.4
we can add Microsoft.EntityFrameworkCore.Tools

3 - After creating a new jhipster project with no entities, and running it for the first time, I found out that the tables of ASP.NET Core Identity tables (User, role, roleuser, etc) were automatically created and my migrations would not run because the tables were alredy created. So migrations did not work because they were creating those exact same tables.

we can replace context.Database.EnsureCreated(); by context.Database.Migrate();
in DatabaseStartup

7- create migration containing new entities tables Suggestion: make it automatic after generator run

As discuss in #98 i prefer that the user manages his migrations but we can discuss of the advantages or disadvantages if you want

Can you do a PR with documentation and generator modification ?

[ivanmonteiro]

You're absolutely right !

Just few remarks/tips :

dotnet add package Microsoft.EntityFrameworkCore.Design --version 3.1.4
we can add Microsoft.EntityFrameworkCore.Tools

3 - After creating a new jhipster project with no entities, and running it for the first time, I found out that the tables of ASP.NET Core Identity tables (User, role, roleuser, etc) were automatically created and my migrations would not run because the tables were alredy created. So migrations did not work because they were creating those exact same tables.

we can replace context.Database.EnsureCreated(); by context.Database.Migrate();
in DatabaseStartup

7- create migration containing new entities tables Suggestion: make it automatic after generator run

As discuss in #98 i prefer that the user manages his migrations but we can discuss of the advantages or disadvantages if you want

Can you do a PR with documentation and generator modification ?

I sure can help with:

• documentation on migrations

• and add missing package required for migration on the csproj (and thanks for the suggestion of using Microsoft.EntityFrameworkCore.Tools it works and does resolve the error of missing Microsoft.EntityFrameworkCore.Design package).

But the automation of generating and running migrations is something that can be tricky - as per discussion on issue #98 - and adds the possibility of data loss - and I think it is something that needs more time than I have available right now.

[nicolas63]

Yes, i think too the automation is harder and we need to design it before implement that.

[ivanmonteiro]

dotnet add package Microsoft.EntityFrameworkCore.Design --version 3.1.4
we can add Microsoft.EntityFrameworkCore.Tools

On further inspection, the Microsoft.EntityFrameworkCore.Tools package seems to be related to the commands related to visual studio (Add-Migration, Scaffold-DbContext, etc...) and since I'm using VS Code I'm using the cli instead (dotnet ef migrations add InitialCreate and dotnet ef database update) so I dont really need that package.

I DO need the Microsoft.EntityFrameworkCore.Design package since without it the commands will not run with the message that Microsoft.EntityFrameworkCore.Design was not found.

@nicolas63 Do we need Microsoft.EntityFrameworkCore.Tools package on the .csproj? What do you think?

[nicolas63]

Yes Microsoft.EntityFrameworkCore.Tools is a superset of design package. I don't really need it but i prefer add it rather than design for cover all use case.

[ivanmonteiro]

Yes Microsoft.EntityFrameworkCore.Tools is a superset of design package. I don't really need it but i prefer add it rather than design for cover all use case.

Since it does solve the missing Desing package I'll be adding the Tools package