Efcore: Migrations Bundles

[bricelam]

We currently have dotnet ef migrations script and database update to apply migrations. Unfortunately, they each have their shortcomings.

migrations script is pretty good at producing SQL scripts (there are some known issues with the scripts), but figuring out what to do with those scripts may not be obvious. Publish from Visual Studio wraps these scripts up in a web deployment package and uses MSDeploy to run them on the server. This works well for SQL Server on Windows, but nothing else.

database update is a lot better at applying migrations. It doesn't have any of the issues the SQL script does. Unfortunately, it requires the .NET Core SDK, your app's source code, and a direct connection to the production database. Having all of these things available where you need them is't always possible.

I propose we introduce a new migrations bundle command. This would create a self-contained EXE that you could use to apply migrations. It would behave the same as database update and let you specify the connection string. This would make SSH/Docker/PowerShell deployment a lot easier. You wouldn't need to copy your source files to a server inside the firewall and you wouldn't need to install the .NET Core SDK. Here is an example of what it might look like to use SSH:

dotnet ef migrations bundle
scp bundle [email protected]:./
ssh [email protected] "./bundle --connectionString ${DEPLOYMENT_CONNECTION_STRING}"
ssh [email protected] "rm bundle"

Taking this further, Visual Studio could do all of this for you like they do today on MSDeploy but when you're deploying to Docker. They could even create a new SSH deployment experience.

[tibitoth]

@bricelam Why not use dacpac? I have not seen any better approach for MSSQL.

We try to embrace sqlproj (in this sense it's DB first) + reverse engineer code first on every change + dacpac migrations.

[bricelam]

dacpac is perfect for DB-first; keep using it there. However, it's fundamentally different from Migrations which are designed for a code-first workflow. Also, dacpac is SQL Server-only and a big part of these bundles is to improve the experience when using other databases like PostgreSQL, MySQL, and Oracle.

Fun fact, the first prototype of Migrations was based on dacpac.

[ErikEJ]

FWIW, "EF Core Power Tools" supports Reverse Engineering directly from a dacpac / SQL Database project (.sqlproj)

[leealexander]

migrations script is pretty good at producing SQL scripts (there are some known issues with the scripts)

Could you elaborate on the issues?

database update is a lot better at applying migrations.

In what way(s) is update better?

I take it the SQL isn't the same if I was to look at trace logs of the two different update methods?

[bricelam]

The SQL is the same, the differences (and issues) stem from things like when transactions begin and end, the if blocks (some statements aren’t allowed inside), and how the script is parsed (some objects are created at the beginning of the script but the parser/compiler errors because later statements reference them.

[bricelam]

Items to address issues with the script: #7681 #12911 #14746 #19740

[ajcvickers]

Team note: make sure to consider how bundles interact with continuous deployment systems. See #9841.

[ajcvickers]

Unfortunately, while we have made good progress in understanding the space here, we are deferring release of the migrations bundles feature until after the EF Core 5.0 release to provide more time for feedback. The current plan is to get something into preview near the beginning of the EF Core 6.0 milestone so that we have enough time for feedback, integration, and testing before committing.

[ivanmonteiro]

I'm not sure if it is the right place, but here are my considerations:
It would be interesting if the migrations bundle could be built and run on both Windows/Linux platforms.

Currently I'm developing using a Windows machine and deploying to a Linux server. Also, my CI workflow uses Linux machines under the covers. So being able to choose the target platform would be great.

[JohnnySixStrings]

This is currently an issue I'm interested in we currently work with multiple databases including postgres, mysql, sql server.
Using azure pipelines we have not found a suitable way to deploy migrations since migration script will not work with postgres.

[roji]

@JohnnySixStrings migration scripts generally work fine with PostgreSQL - I'm guessing you're running into issues with maintaining migrations with multiple providers (see this doc page).

If you're encountering specific issues with PostgreSQL migrations, that probably isn't related to this issue - can you please open an issue on https://github.com/npgsql/efcore.pg?