search

dotnet / efcore

EF Core is a modern object-database mapper for .NET. It supports LINQ queries, change tracking, updates, and schema migrations.
https://docs.microsoft.com/ef/
MIT License
13.5k stars 3.13k forks source link

Improve experience deploying databases created by Migrations #19587

Open ajcvickers opened 4 years ago

ajcvickers commented 4 years ago

This is a grouping of related issues. Feel free to vote (👍) for this issue to indicate that this is an area that you think we should spend time on, but consider also voting for individual issues for things you consider especially important.

Currently, many developers migrate their databases at application startup time. This is easy but is not recommended because:

We want to deliver a better experience here that allows an easy way to migrate the database at deployment time. This should:

The result is likely to be many small improvements in EF Core (for example, better Migrations on SQLite), together with guidance and longer-term collaborations with other teams to improve end-to-end experiences that go beyond just EF.

Done in 6.0

Planned for 9.0

Backlog

ErikEJ commented 4 years ago

sqlpackage / dacpac already does much of this - but sadly currently only for SQL Server.

BEagle1984 commented 4 years ago

In our latest project we integrated the DB migrations in the deployment pipeline simply by generating an idempotent migration script (for all migration steps) via dotnet-ef and leveraging sqlcmd to execute it against the appropriate sql server instance. We do this in Jenkins, from a Linux slave. I honestly don’t think our solution is too bad.

bricelam commented 4 years ago

Some related issues:

macsux commented 4 years ago

So I've built something that helps with this that was contributed to project SteelToe. It allows tasks to be bundled with app and launched via special command argument. Ef migration is one of them. Is you're not familiar with SteelToe, the site is https://steeltoe.io

These are the relevant sources you may consider

https://github.com/SteeltoeOSS/Management/tree/dev/src/Steeltoe.Management.CloudFoundryTasks

https://github.com/SteeltoeOSS/Connectors/blob/dev/src/Steeltoe.CloudFoundry.Connector.EFCore/MigrateDbContextTask.cs

johnnyreilly commented 4 years ago

Thanks for linking to this @AndriySvyryd - great to see this is being actively worked on

dazinator commented 4 years ago

I ended up architecting my app to address these points, and putting all the logic in a reusable library which I might open source in future.

There was quite a bit to it in the end. I havent addressed rollback.. but being able to rollback is not necessarily safe if data is going to be lost in the process, so the safest policy for me tends to be to only allow roll forward, and make sure migrations are tested adequately before rolling to a production environment.

One thing I like about this approach is that I can deploy a new version of my application using azure app service for containers, and when the new instance is swapped in and it has pending migrations, anyone currently using the app will now get presented with a UI that says an upgrade is in progress. Once its applied they can use the app again. Contrast this to upgrading the database during the deployment: if the database is upgraded prior to the new version of the app being swapped in, then current users will be using an older version of the app whilst its database is being upgraded to a newer version from under it - which could introduce errors. If the database is upgraded by the deployment process after the newer version of the app is swapped in, then unless the application is taken fully down until the db upgrade is complete then users will be potentially using a new version of the app before the db upgrade is complete which again can cause errors. With my approach, the app is not taken fully offline which allows me to provide a nicer experience for end users whilst the upgrade is taking place. Likewise if there are mvc methods that I know have no dependency on the database I can optionally keep them functional during the upgrade by excluding them from the action filter.

Basically unless EF migrations can be committed in a transaction and the corresponding version of the app swapped in to replace the old version in a single atomic operation, then you've got the potential for version mismatch for some period of time. I dont think the above is possible with app service for containers but if anyone knows different please do share!

ajcvickers commented 4 years ago

@dazinator Thanks for sharing that. There is certainly some interesting things there--we talked about going in this kind of direction before, but robustness has always been a concern. If you do create a package I would be interested in taking a look.

syndicatedshannon commented 4 years ago

This product is pretty strong with this latest 3.0 release. It's great to see, since we decided not to bail on it during the rewrite.

I'm disheartened, though, that this capability is slated for 5.0, but still very little attention is being given to contract-first, conceptual model-first, database-first, or SSDT/.SQLPROJ-authoritative development. Is it just that there are insufficient requests for it, or is EF not intended for that market?

The semantics of up/down are great for early development, and I appreciate that they pull a lot of teams into EF during envisioning stages. Also, the syntax of EF's C# attribute-modelled and fluent definition are well-designed. Even so, this leaves a lot of gaps in both relational and constraint capability and support for collaboration with data designers. SQL code embedded as text strings in fluent definitions (without even schema validation), to me, means something is wrong. Which team writes that code? The team I have to allocate with both expert level C# lambda AND T-SQL background?

I know this all reveals my personal stance on code-first data development. The point is, I wonder where this feature is heading. Who needs to do this; who needs DACPAC-like deployment but wouldn't be better suited to getting SQL schema definitions out of C# and into source-controlled SQL, or a contract-first toolset?

Thanks for listening.

ErikEJ commented 4 years ago

@syndicatedshannon I assume you are aware that EF Core Power Tools supports exactly that workflow (database first from a .dacpac/ .sqlproj)

syndicatedshannon commented 4 years ago

@ErikEJ - Thank you. Our team has visited that tool often in the past, starting when you first suggested it years ago. We had various challenges along the way. At last visit, I believe we did not see how to make it work for us to get schema revisions applied to our models as part of our toolchain. My recollection is there is an effortful process to replace the model each time, even if we weren't concerned about losing model customizations. Since then we purchased LLBLGen, but I would love to know if I'm missing something.

syndicatedshannon commented 4 years ago

@ErikEJ If you think I'm mistaken, and it does indeed support exactly this workflow, please LMK. Would love to hear I've missed something.

ErikEJ commented 4 years ago

@syndicatedshannon The workflow you describe works fine for me with EF Core Power Tools. You can extend the generated POCO classes with NonMapped properties, as these classes are partial, and you can modify the DbContext model by implementing the partial OnModelCreatingPartial method in a partial DbContext class. But of course if you start treating your DAL as a Domain model, things could start falling apart. Please let me know what is blocking you from adpoting this, or if anything I wrote is unclear :-)

syndicatedshannon commented 4 years ago

Thank you Erik, I will try to understand, and follow up on #4321 when I have a handle on it, referencing your name. Apologize to others for cluttering this topic.

GeeWee commented 4 years ago

Just want to chime in on this, as I've been researching this a bit. It seems like most of the stuff re: parallel migrations can be solved by taking out exclusive locks.

I can see rails does it with advisory locks for postgres and mysql, FluentMigrator and Flyway does it with sp_getapplock for SQL Server, etc.

It seems that using the database locking can mitigate running migrations in parallel, thus making it safe to run on Startup. I'm not sure if this is true for all databases, but it seems to be for the big ones.

However, this of course only deals with half of the issue, because e.g. for rolling updates there's still an issue if some apps expect a different schema. Of course you can get around this by planning your migrations and releases well.

ondrej-marinak commented 4 years ago

Feedback regarding migrations and deployment experience

Setup:

Recently, I spent a lot of time trying to solve the migrations and deployment case in the Azure Pipelines. I’ve tried to solve a straight forward case i.e. to update a database during the deployment stage.

  1. My first idea was to split the whole into two stages, build and deploy.

    • Build stage: generate migration script usingdotnet ef migrations script --output script.sql --idempotent
    • Deployment stage should download the artifact (sql script) and execute it.

    Problem: It’s not possible to execute the script using dotnet ef or using some extensions. The extensions that I tried didn't work. There are probably some extension but are working well only with MS SQL. The solution could be to install db specific executor on the agent machine.

  2. The second approach I tried is to migrate directly from assemblies

    • Build stage: nothing to do here
    • Deployment stage: execute dotnet exec --depsfile [path] --runtimeconfig [path] [ef path] etc.

    Problems:

    • Command to execute is not that simple
    • Need to know the path to ef.dll
    • Error saying that Microsoft.EntityFrameworkCore.Design nuget package is not found even if it was referenced in the startup project explicitly

    I also abandoned this approach because I was constantly stumbling upon new hurdles.

  3. The third approach, unfortunately, needs to pull and build source code once again in the deployment stage, on the other hand, it is just dotnet ef database update command. I stuck with this approach for now since it's the easiest one and doesn't require a lot of plumbing, I don't like it, though.

JonPSmith commented 4 years ago

Can I add one issue I have had when updating a database with migrations that might be added in a CI/CD solution.

With a client an update to EF Core 3 required some lambda properties to be added to the database. The classic example is adding a FullName string property to replace the lambda property that created the FullName from the FirstName and LastName.

The migration was easy, but updating the thousands of rows to have the correct FullName was a problem. My client's system used 'migrate on startup' and I added code to run code when the specific migration was applied. This code then filled in the new FullName from the FirstName and LastName columns. The problem was Azure WebApp timed out because the ASP.NET Core app took too long to start.

If the CI/CD migration could call code to run after the migration and provide names of migration just applied to the database, then this would handle this sort of issue.

joaopgrassi commented 4 years ago

The migration was easy, but updating the thousands of rows to have the correct FullName was a problem. My client's system used 'migrate on startup' and I added code to run code when the specific migration was applied. This code then filled in the new FullName from the FirstName and LastName columns. The problem was Azure WebApp timed out because the ASP.NET Core app took too long to start.

I think doing this at startup blocking is not very good.. maybe you could create a HostedService and do it in the background so the main app is not blocked. Not sure this is a migration or even CI/CD problem.

JonPSmith commented 4 years ago

Hi @joaopgrassi,

I did consider using a BackgroundService, but that would mean that the application would start with inaccurate data. I deemed that was not a good idea.

The other option was IHostedService, which runs before the main ASP.NET Core is run (since version 3.0). But I thought that Azure would still time out because it wasn't responding to HTTP requests (I didn't try that - its pretty hard to try these things as you have to reset the database every time).

vzakanj commented 4 years ago

@ondrej-marinak we are doing it using the first approach you've listed, only with PostgreSQL. It boils down to installing PostgreSQL command line tools on the agent and caching it with the Azure DevOps cache task so we don't have to install every time the build runs.

  1. use psql to query the _EFMigrationsHistory in the db to get the last migration applied for the environment
  2. generate idempotent script from the last applied migration to the latest one
  3. after all builds are completed, apply the script with psql again

All of the above are done with PowerShell scripts, but I'd like to see this built into dotnet ef tooling. e.g.

  1. not having to manually install psql and query _EfMigrationsHistory + pass the migration as a parameter to dotnet ef migrations script
  2. possibly running the generated script with a separate ef command
nick5454 commented 3 years ago

@ondrej-marinak I create the ef script in the build pipeline(Cake.DotNetCoreEf) and use the Sql Deploy task in the release and it works fine. The issue I have that if my file has a lot of schema changes it completely fails. Looking to see if I can create a DacPac from a migration after building.

Tried putting a log of GO's in the script and it still fails. Thinking about using the "list" command and somehow doing a foreach on all the migrations. That way in the release I can run all of them and the -idenpotent will decide if it should run individually.

ajcvickers commented 3 years ago

Unfortunately, while we have made good progress in understanding the space here, we are scoping this issue for the 5.0 release. The migrations bundles feature (#19693) will be deferred until after the EF Core 5.0 release. However, several other targeted improvements related to migrations will be included in EF Core 5.0, including:

JonPSmith commented 3 years ago

Can I ask if this might get into a later EF Core 5 release, say 5.1, or will it move to EF Core 6. I only ask this question to know whether I might include information about this in the book that I am updating.

An answer of "don't know" is fine, and in that case I won't mention it in the EF Core 5 update.

ajcvickers commented 3 years ago

@JonPSmith Currently there are no plans for a release between EF Core 5.0 and EF Core 6.0. We're aligned to the yearly cadence of .NET.

JonPSmith commented 3 years ago

Thanks.

Great work on EF Core 5. I will look forward to this feature in the future.

alrz commented 3 years ago

I didn't find this mentioned but it would be nice to also improve version control experience.

Currently if more than one dev add migrations the second dev usually needs to redo changes.

Saibamen commented 3 years ago

@alrz: +1 for this

zejji commented 3 years ago

@ajcvickers @bricelam - I recently worked on a microservice-based project that depended on making EF Core migrations production-ready and thought it might be worthwhile sharing my experience and thoughts that have been developed over a long period of time.

At a very high level, our aim was to have each microservice be capable of independently initializing itself and its infrastructure (i.e. the autopilot pattern), so that deployment was as simple as starting one or more microservice containers. This massively simplifies the deployment process as the knowledge required to deploy a service can be encapsulated within the service itself.

More specifically, we needed each service to be able to:

We were happy for the initialization tasks to run synchronously on startup, as the old container would not be stopped until the new container had completed initialization (i.e. we were using rolling updates with overlap). I do not like the idea of using an IHostedService for startup tasks where the tasks need to have completed before serving requests. Interestingly, the approach we ultimately settled on bears some resemblance to a significantly extended version of Steeltoe's management tasks API (source code here), although we only became aware of its existence after completing our implementation.

The approach we settled on had the following core features.

  1. The infrastructure initialization feature could be added using a simple IHost extension method (cf. Steeltoe's equivalent here, although it is missing features 2 and 4 below):

    public static void Main(string[] args)
{
CreateHostBuilder(args).Build().RunUsingInfrastructureInitializer();
}

    The default behaviour was to run the initialization tasks and then boot the web application. However, configuration (e.g. environment variables) could also be used to change the default behaviour either by (i) bypassing the infrastructure initializer; or (ii) only running the infrastructure initializer, but not then continuing to boot the web application.

  2. Under the hood, an IInfrastructureInitializer abstraction was responsible for running one or more IInfrastructureInitializationTasks that had been registered with the DI container. The IInfrastructureInitializer used an IDistributedLockProvider (see e.g. the DistributedLock library) to prevent parallel execution of tasks by multiple application instances. The interface was as follows:

    public interface IInfrastructureInitializer
{
public void RunTasks();
...
}

One complication we had to solve was, when running the very first migration for a new service, we needed to ensure that the database (in our case, SQL Server) actually existed to take a lock out on! We did this by allowing the lock provider to be able to create the database in a concurrency-safe way (using a poll loop with error handling).

  1. An IInfrastructureInitializationTask abstraction was used for the tasks themselves. We implemented several common tasks, such as DatabaseInitializationTask (which allowed the target migration to be configurable - this is absolutely crucial for rollback!) and ConnectorRegistrationTask for registration of Kafka connectors and added extension methods to make it easy to register these tasks with the DI container, but developers also had the flexibility to implement and register any other tasks desired. 
    public interface IInfrastructureInitializationTask
{
public void Run();
}

When using a DbContext within the DatabaseInitializationTask (see point 4 below), in order to ensure that the runtime DbContext did not have all the permissions available to the migration-time DbContext, we had separate RuntimeAppDbContext and MigrationAppDbContext classes inheriting from a base class. This was a bit ugly and EF Core could definitely make it easier to use a different connection string for the same DbContext!

  1. The final part of the jigsaw was to allow dependency injection to be used in migrations. We needed this specifically in order to move data from a legacy system at the same time as the migrations.

As an aside - in my view, migrations can be useful as a broader tool to ensure that any task is performed (i) only once in the entire history of an application and (ii) in a specific order. I can see no good reason to limit their use to only making database schema migration changes! I have experience of using migrations in this way in other languages and frameworks and have found it to work well. I know this does not play nicely with converting migrations to SQL scripts (which was not a requirement for us), but I think the benefits of having the flexibility to use migrations in this way far outweighs the downsides.

Permitting the use of DI in migrations required a custom MigrationsAssembly as follows:

#pragma warning disable EF1001 // Internal EF Core API usage.
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Diagnostics;
using Microsoft.EntityFrameworkCore.Infrastructure;
using Microsoft.EntityFrameworkCore.Migrations;
using Microsoft.EntityFrameworkCore.Migrations.Internal;
using Microsoft.Extensions.DependencyInjection;
using System;
using System.Linq;
using System.Reflection;

namespace MyCompany.InfrastructureInitialization.Database
{
    /// <summary>
    /// A custom MigrationsAssembly which is modified to allow dependency injection
    /// to be used in migrations.
    /// </summary>
    public class DependencyInjectionEnabledMigrationsAssembly : MigrationsAssembly
    {
        private readonly IServiceProvider _serviceProvider;

        public DependencyInjectionEnabledMigrationsAssembly(
            ICurrentDbContext currentContext,
            IDbContextOptions options,
            IMigrationsIdGenerator idGenerator,
            IDiagnosticsLogger<DbLoggerCategory.Migrations> logger,
            IServiceProvider serviceProvider)
            : base(currentContext, options, idGenerator, logger)
        {
            _serviceProvider = serviceProvider;
        }

        /// <summary>
        /// This method is responsible for creating instances of the migration classes.
        /// </summary>
        /// <param name="migrationClass">The TypeInfo for the migration class to create</param>
        /// <param name="activeProvider">The name of the current database provider e.g. Microsoft.EntityFrameworkCore.SqlServer</param>
        /// <returns>A migration</returns>
        public override Migration CreateMigration(TypeInfo migrationClass, string activeProvider)
        {
            if (activeProvider == null)
            {
                throw new ArgumentNullException(nameof(activeProvider));
            }

            var constructors = migrationClass.GetConstructors();
            var hasConstructorWithParameters = constructors.Any(ci => ci.GetParameters().Length > 0);

            if (hasConstructorWithParameters)
            {
                var instance = ActivatorUtilities.CreateInstance(_serviceProvider, migrationClass.AsType());

                if (instance == null)
                {
                    throw new InvalidOperationException($"Could not create migration of type {migrationClass.Name}");
                }

                var migration = (Migration)instance;
                migration.ActiveProvider = activeProvider;
                return migration;
            }

            return base.CreateMigration(migrationClass, activeProvider);
        }
    }
}
#pragma warning restore EF1001 // Internal EF Core API usage.

Developers could then use any desired dependency within a migration by modifying the call to .AddDbContext() as follows:

services.AddDbContext<MigrationAppDbContext>((serviceProvider, options) =>
    options
        .UseSqlServer(configuration.GetConnectionString("testDatabase"))
        .UseInternalServiceProvider(new ServiceCollection()
             .AddEntityFrameworkSqlServer()
             .AddScoped<IMigrationsAssembly, DependencyInjectionEnabledMigrationsAssembly>()
             // add any additional dependencies you wish to inject into migrations here, for example:
             .AddTransient<IDependency, Dependency>()
             .BuildServiceProvider());

While I am sure there other ways of achieving our goals, the above solution actually worked nicely for our purposes and involved a relatively small number of high-level abstractions which were easy to work with. Adding the common set of initialization tasks in a safe manner only required a few lines of non-library code.

Happy to answer any questions if the above is of any interest.

joaopgrassi commented 3 years ago

@zejji whoa this looks really interesting! thank you for sharing it!

roji commented 3 years ago

I know this does not play nicely with converting migrations to SQL scripts (which was not a requirement for us), but I think the benefits of having the flexibility to use migrations in this way far outweighs the downsides.

My general recommendation would be to avoid any sort of automated application of migrations to production database without previously confirming that the actual SQL to be executed is what you want; EF does its best to produce correct migration SQL, but in some cases scripts need to be corrected, and blindly applying them could result in pretty serious data loss (the typical example is an intended column rename which causes the old column to be dropped and a new one to be created).

in most scenarios, this means manual inspection of the SQL scripts - unless you invest in automated application of migrations to a test database already containing data, and thorough automated testing of your application against that database (this is non-trivial).

zejji commented 3 years ago

@roji - Yes, it probably goes without saying that the process described in my post above depends on going through several stages of automated and manual testing before going anywhere near production. However, I don't see the difference between reading a migration file and reading the SQL, provided you understand and have verified the behavior, and in either case it is not enough in the absence of tests. I would never ship a SQL script without having tested it on a database with existing data either - would you? :)

I'm also pretty sure that running migrations on production databases is standard in other frameworks; cf e.g. Ruby on Rails or CakePHP. Again, testing is key, as well as having a sensible backup strategy. And if you can't trust migrations which have been tested on the basis that they are not raw SQL, should you never use an ORM to manipulate data either?

JonPSmith commented 3 years ago

@zejji. I really like your approach. Can you provide a link to the code that ensures only one instance of a multi-instance web service would apply a migration.

zejji commented 3 years ago

@JonPSmith - The basic idea of the InfrastructureInitializer class itself is pretty simple. It made use of a custom wrapper round the DistributedLock library which overrode the CreateLock method to add the ability to auto-create the lock database if it didn't exist already. This would only ever be required on running the app for the very first time.

Ensuring the lock database is created was slightly tricky, again because of the need to handle race conditions, but we ended up using a poll loop and appropriate error handling and it worked acceptably even in the face of multiple instances being fired-up simultaneously for test purposes (which is more onerous than what is likely to occur in production because it is sensible to configure a container orchestrator to stagger startups).

A much simpler alternative - if one's use case does not require the app to be 100% self-initializing (which ours did, as it is provided to third parties to deploy) - would be to simply require that the database exists and throw an exception if not.

The InfrastructureInitializer code looked as follows:

public class InfrastructureInitializer : IInfrastructureInitializer
{
    private const string GlobalLockName = "InitializationLock";
    private const int DefaultLockTimeoutInSeconds = 120;

    private readonly IDistributedLockProvider _synchronizationProvider;
    private readonly IEnumerable<IInfrastructureInitializationTask> _initializationTasks;
    private readonly int _lockTimeoutInSeconds;

    public InfrastructureInitializer(
        IDistributedLockProvider synchronizationProvider,
        IEnumerable<IInfrastructureInitializationTask> initializationTasks,
        IOptions<InfrastructureInitializerSettings> options)
    {
        _synchronizationProvider = synchronizationProvider;
        _initializationTasks = initializationTasks;
        _lockTimeoutInSeconds = options?.Value?.AcquireLockTimeoutInSeconds ?? DefaultLockTimeoutInSeconds;
    }

    public void RunTasks()
    {
        var globalLock = _synchronizationProvider.CreateLock(GlobalLockName);
        using (globalLock.Acquire(TimeSpan.FromSeconds(_lockTimeoutInSeconds)))
        {
            foreach (var initializationTask in _initializationTasks)
            {
                initializationTask.Run();
            }
        }
    }
}

The tests proving that it works were naturally much more involved than the code itself, as they involved e.g. (i) verifying the lock worked by creating multiple service providers and triggering the initialization jobs in a Parallel.ForEach; and (ii) ensuring that initialization tasks were executed in the right order.

The tasks themselves obviously need to be idempotent (which database migrations should be by default). The database initialization task was as follows:

public class DatabaseInitializationTask : IInfrastructureInitializationTask
{
    private readonly IMigrationDbContext _context;
    private readonly string? _targetMigration;

    public DatabaseInitializationTask(
        IMigrationDbContext context,
        IOptions<DatabaseInitializationTaskSettings> options)
    {
        _context = context;

        _targetMigration = options?.Value?.TargetMigration;
        if (string.IsNullOrWhiteSpace(_targetMigration))
        {
            _targetMigration = null;
        }
    }

    public void Run()
    {
        // Cf. https://blog.rsuter.com/automatically-migrate-your-entity-framework-core-managed-database-on-asp-net-core-application-start/
        var migrator = _context.Database
            .GetInfrastructure()
            .GetRequiredService<IMigrator>();

        if (migrator != null)
        {
            // See https://docs.microsoft.com/en-us/dotnet/api/microsoft.entityframeworkcore.migrations.imigrator.migrate?view=efcore-5.0
            // All migrations will be run if targetMigration is null.
            // If targetMigration is a string (e.g. "2021xxxxxxxxx_InitialCreate") to migrate to a specific target.
            migrator.Migrate(_targetMigration);
        }
    }
}

Finally, some service collection extension methods were created to make life easier for the end user, e.g.:

public static IServiceCollection AddDefaultInfrastructureInitializer(this IServiceCollection services, IConfiguration configuration)
{
    return services
        .AddInfrastructureInitializer(configuration)
        .AddSqlServerLockProvider(configuration)
        .AddDatabaseInitializationTask(configuration);
}

public static IServiceCollection AddInfrastructureInitializer(this IServiceCollection services, IConfiguration configuration)
{
    return services
        .AddTransient<IInfrastructureInitializer, InfrastructureInitializer>()
        .Configure<InfrastructureInitializerSettings>(options =>
            configuration
                .GetSection(nameof(InfrastructureInitializer))
                .Bind(options));
}

public static IServiceCollection AddSqlServerLockProvider(this IServiceCollection services, IConfiguration configuration)
{
    return services
        .AddSingleton<IDistributedLockProvider, SqlServerLockProvider>()
        .Configure<SqlServerLockProviderSettings>(options =>
            configuration
                .GetSection(nameof(SqlServerLockProvider))
                .Bind(options));
}

public static IServiceCollection AddDatabaseInitializationTask(this IServiceCollection services, IConfiguration configuration)
{
    return services
        .AddTransient<IInfrastructureInitializationTask, DatabaseInitializationTask>()
        .Configure<DatabaseInitializationTaskSettings>(options =>
            configuration
                .GetSection(nameof(DatabaseInitializationTask))
                .Bind(options));
}
roji commented 3 years ago

@zejji reading what I wrote, my intention was indeed more to stress the importance of inspecting before applying - whether the SQL script is inspected or the migration is indeed less important; I should have been more clear on that point. We get lots of issues from people who seem to apply migrations blindly (and upon program startup), so this point is always worth stressing.

I do still believe inspecting SQL is valuable in this context, since migration code doesn't always translate to SQL in a completely transparent way; but a proper testing infrastructure and deployment process can probably be sufficient.

And if you can't trust migrations which have been tested on the basis that they are not raw SQL, should you never use an ORM to manipulate data either?

It's not quite a matter of trust - synchronization of schema changes contain some inherent difficulties which don't exist in data manipulation (the column renaming is a good example). It's simply a riskier thing.

zejji commented 3 years ago

@roji - Agree with everything you said and perhaps I was being a little flippant. Column renaming and similar gotchas certainly exist but, from experience, provided you do not assume auto-generated migrations can be trusted, this risk is entirely manageable.

ajcvickers commented 3 years ago

@zejji This is all really interesting; we should see what we can do about getting this kind of approach added to our guidance. Also, would you be interested in coming on the EF Core Community Standup to share this with others?

/cc @JeremyLikness @bricelam and also @glennc since this is very microservices related.

zejji commented 3 years ago

@ajcvickers - I'd be very happy to share on the EF Core Community standup if it's useful to people; just let me know what's needed and I'll prepare something accordingly.

cResults commented 3 years ago

not sure whether or not this is the correct place for this feedback, but being able to more easily apply EF Migrations to all database instances associated with a DbContext within Elastic Database shards would be a huge benefit to developers using the database per tenant modal of multi-tenancy. We're currently working to write a process to run via Azure DevOps pipeline that will use ef cli connected to one instance of our dbs to create the delta script and then use powershell to create an Elastic Job to apply that diff script to all dbs.

We're still primarily EF 6.x, but we have a fair number of new services that are ef core.

If there is an easier way to apply EF migration updates to 100's of the same database in a sharded environment, please point us to it. If an easier way doesn't exist and you'd like to build it let us know, we may be willing to help.

ajcvickers commented 3 years ago

@zejji If you haven't already and you are still interested, then could you go to https://aka.ms/efstanduprequest so we can get this show on the schedule. /cc @JeremyLikness

nick5454 commented 3 years ago

I created a Cake package the simply calls EF's command line to get a list of all migrations in the project and then create a script file for every migration. On the release side, I then deploy a small powershell script that checks for the migration and if not exists we deploy the script. This way no matter what server we deploy to, ef will update to be current.

Works really well without needed to have an EF live database connection.

I prefer using the script as to have another library binding, because in a release pipeline the less dependencies the better. And we only use private nuget's, nothing public. Which public now seems to be the new malware attempts.

Debug("Starting Migration");
        if(!string.IsNullOrWhiteSpace(BuildParameters.DbContext) &&
            !string.IsNullOrWhiteSpace(BuildParameters.StartupProject) &&
            !string.IsNullOrWhiteSpace(BuildParameters.DomainProject))
        {
            string zipDirectory = $"{BuildParameters.Artifacts}\\zip\\";

            //string efOutput = $"{zipDirectory}\\{domainOutputSql}";
            int efCount = 0;
            string result = "";

            while( string.IsNullOrWhiteSpace(result) && efCount < 5) {
                var efSettings = new DotNetCoreEfMigrationListerSettings() {
                    StartupProject = GetRelativePathToProject(BuildParameters.StartupProject).ToString(),
                    Project = GetRelativePathToProject(BuildParameters.DomainProject).ToString(),
                    Configuration = BuildParameters.Configuration,
                    NoBuild = true,
                    Verbose = true

                };
                Debug($"Trying EF Migration list time {efCount}");

                Debug($"Start EF call");
                Debug($"Project Collection: {GetRelativePathToProject(BuildParameters.DomainProject).ToString()}");
                result = await DotNetCoreEfMigrationList(GetRelativePathToProject(BuildParameters.DomainProject).ToString(), efSettings);
                Debug($"result = {result}");
                Debug($"End EF Call");

                efCount++;

            }
            Debug($"result = {result}");

            if(!string.IsNullOrWhiteSpace(result)) {
                //DotNetCoreEfMigrationScript("/", efSettings);
                var migrations = Newtonsoft.Json.JsonConvert.DeserializeObject<List<MigrateValueObject>>(result);

                Debug("Looping migration");
                EnsureDirectoryExists($"{zipDirectory}SQL");

                string lastMigrate = "0";

                foreach(MigrateValueObject migrate in migrations) {

                    try {           
                        string efOutput = $"{zipDirectory}SQL\\{migrate.id}.sql";

                        var efMigrateSettings = new DotNetCoreEfMigrationScriptSettings() {
                            StartupProject = GetRelativePathToProject(BuildParameters.StartupProject).ToString(),
                            Context = BuildParameters.DbContext,
                            Project = GetRelativePathToProject(BuildParameters.DomainProject).ToString(),
                            Configuration = BuildParameters.Configuration,
                            Output = efOutput,
                            Idempotent = true,
                            NoBuild = true,
                            From = lastMigrate,
                            To = migrate.id
                            };

                        DotNetCoreEfMigrationScript("/", efMigrateSettings);
                    } catch ( Exception ex ) {
                        Debug($"Error '{migrate.id} Exception: {ex}");
                    }

                    lastMigrate = migrate.id;
                }
            }

            //DotNetCoreEfDatabaseUpdate("/", efSettings);
        } else {
            Debug("EF BUILD: Not all values exist. Skipping... ");
        }

        Debug("migration complete");

and the powershell 

param ($connString, $sqlFolder)

  Write-Host "Connstring=$($connString)"
  Write-Host "Folder=$($sqlFolder)"

try {

   # Invoke-Sqlcmd -ConnectionString

  $query = "Select * from Users"

 # Invoke-Sqlcmd -ConnectionString $connString -Query $query

  $table = get-childitem -recurse | where {! $sqlFolder}
  $table = Get-ChildItem -Path $sqlFolder -File 
  $exists = "false"

  $timeout = New-Object System.TimeSpan -ArgumentList @(5,0,0) # five hours
  $options = [System.Transactions.TransactionScopeOption]::Required

  # all scripts in 1 transaction. If any fail then rollback ALL of THEM

  foreach($row in $table) 
  {
     $file = (Split-Path $row.FullName -Leaf).Split('.')[0]
     Write-Host $file

     Write-Host "Determining if we should fail migration"

    Write-Host $connString

     $scope = New-Object -TypeName System.Transactions.TransactionScope -ArgumentList @($options, $timeout)

    # Invoke-Sqlcmd -ConnectionString $connString -InputFile $row.FullName
    $DS = Invoke-Sqlcmd -ConnectionString $connString -Query "SELECT Count(*) as Total FROM [__EFMigrationsHistory] WHERE MigrationId = '$($file)'"

    if( $DS.Total -eq 0 ) {
        $exists = "false"
    } else {
        $exists = "true"
    }

    Write-Host $exists

    # if exists then bypass migration
    if( $exists -eq "false") {
        Invoke-Sqlcmd -ConnectionString $connString -InputFile $row.FullName -ea stop 

    } 

    $scope.Complete()
    $scope.Dispose() 
  }

} catch [System.Net.WebException],[System.IO.IOException] {
  Write-Host "Errors"
} catch {
  Write-Host "Error executing script"
  Write-Host $_

  if ($exists -eq "false") 
  { 
    Write-Host "Error with new migration. Exiting..."
    break 
  }
} finally {
    $scope.Dispose() 
}
JonPSmith commented 3 years ago

Hi @zejji,

Your solution to ensure only one application migrates the database allowed me to (finally) implement an open-source library that provides another way to handle authorization in ASP.NET Core.

But for this library to work I need cover all of the ASP.NET Core startup scenarios, including multiple applications happening at the same time. When you described your approach you said:

... we ended up using a poll loop and appropriate error handling and it worked acceptably even in the face of multiple instances being fired-up simultaneously for test purposes.

I wonder if you could provide some more information on what the "poll loop and appropriate error handling" needs to do, as checking these race states are difficult to create and test.

zejji commented 3 years ago

@JonPSmith - Since writing the above post, I have moved to a new role and unfortunately no longer have access to the original code outlined above.

If I recall correctly, the approach I took was to use an instance of EF Core's internal IRelationalDatabaseCreator, e.g. the SqlServerDatabaseCreator, to create the database if it didn't exist. This functionality is used internally by EF's Migrator class (see lines 111-114). It is useful as it contains all the required logic to check whether a database exists and/or create it. However, since it is an internal API, the downside is that it is potentially subject to change.

Obviously it's not possible to create a distributed lock before the database exists, but I established through testing that (i) an exception will always be thrown by EF - I can't recall offhand whether it was a SqlException or something more specific - when using SQL Server if two services attempt to create the same database at the same time, (ii) this exception can be handled gracefully; and (iii) the database is nonetheless always created in a consistent state despite the race condition. Effectively, therefore, I was relying on the database's demonstrated ability to handle multiple concurrent creation attempts in a consistent and correct manner.

So, in summary, I took the approach of running the following steps in a loop (using a try-catch) with a max number of retries:

  1. checking whether the database existed (and, if so, exiting the loop);
  2. if the database did not exist, attempting to create the database;
  3. if an exception was thrown of a type indicating that another service was also attempting to create the database, waiting 5 or so seconds before trying again from step 1.

Obviously the database creation step only needs to happen once at the very beginning, so there was no risk of data loss. This approach proved to be quite adequate for our purposes. However, I'd definitely recommend writing tests to establish that parallel attempts to create a database from multiple threads works acceptably with whatever underlying database technology is used.

JonPSmith commented 3 years ago

Hi @zejji,

Thanks for getting back to me, and your explanation is a great help. I'll work on this in my library and once its done I'll let you know.

PS. I managed to add a link to your comment on this approach to chapter on EF Core migrations my book, Entity Framework Core in Action, 2nd edition just before it went to print. If you contact me via my web site I'll see if I can get you a free ebook.

dazinator commented 3 years ago

@zejji @JonPSmith With respect to the distributed locks database, one option is to use a file based distributed lock implementation instead, and configure a central file share as the locks directory. This should be enough to establish a lock to create any SQL databases and run the infrastructure tasks. Creating of the a SQL server based locks database could then be one of these tasks running within the file based lock supposing you really wanted to use SQL server database for distributed locks within your application from that point onwards. Your IDistributedLockProvider could be registered with a factory that returns a file based implementation when the infrastructure tasks are incompleted, and returns a SQL based lock provider when the setup tasks are complete. Bit more complicated but might be more suitable if you already have a file share in place.

JonPSmith commented 3 years ago

@dazinator @zejji,

Thanks @dazinator for taking the time to look at this. I like it, but for a library which could be used in monoliths, microservices, serverless, containers etc. I can't be sure there is a common file location. I need to think a bit more on this, but do add anything if you think it would help.

I'm also thinking of building a library just for EF Core create/migrate usage, as I think others would like it too. @zejji, I don't want to take over your idea, so if you want to do something yourself then I'm happy to not do that. @bricelam, @ajcvickers I assume the EF Core team isn't planning to pick this up??

zejji commented 3 years ago

@JonPSmith - Thanks for your kind words and glad to hear it's been helpful. 😃

@dazinator - Yes, that's definitely a good approach if some other locking mechanism is already available prior to the database creation (e.g. Azure blob leases). The most flexible approach would, as you say, be to work in a configuration option so an alternative locking mechanism could be used for the initial database creation, falling back to the default of a poll loop. Obviously the trade-off would be a bit of extra complexity.

zejji commented 3 years ago

@JonPSmith - I agree that it would be useful to put the logic in a publicly-available library, as I think it could be useful wherever people are running migrations in a service with potentially more than one replica (and want to avoid complex deployment scripts). I don't have access to the original source code anymore, so would need to recreate it. However, I'm not precious about ownership so if you are already well-advanced I certainly wouldn't object to your using the idea 😃

JonPSmith commented 3 years ago

@zejji, I haven't started on it yet, but was getting input for when I do have to build it. I'm busy with the library that needs this feature so if you have the time/inclination to build such a library, then by all means do that. I am happy to collaborate with you, but we should wait for feedback from @bricelam, @ajcvickers in case the EF Core team want to pick this up.

Also @zejji, @dazinator. I agree that providing a way for the user to use one of the other DistributedLock approaches should give people enough options to get around the 'no database' case.

One thing I have been thinking about is your InitializationTask concept which allows other, one-off tasks that need to be executed within the lock - for instance my library would want to seed the database using setup data in the appsettings.json after the migration was finished. These InitializationTasks could be run in the order that they were registered with the DI provider, but a more secure way would be to make the IInitializationTask have a public Type RunThisAfterTask = typeof(<task that should precede this task>) property to ensure the tasks are run in the right order.

roji commented 3 years ago

I am happy to collaborate with you, but we should wait for feedback from @bricelam, @ajcvickers in case the EF Core team want to pick this up.

We generally recommend applying migrations as part of the application deployment process, and not as part of application startup. Among other things, this side-steps the whole distributed locking question, since that step is being performed at one place and not concurrently. Even if you have a distributed locking mechanism, you must still likely ensure that application instances are down or waiting for the migration to be applied, otherwise you get different versions of your application trying to access a different database schema... And that begs the question of why do it as part of application startup in the first place...

Even if you want to use distributed locking, the specific mechanisms for doing so are very different across databases (and don't necessary exist on all of them). I'm not sure what EF Core should or could contribute here.

zejji commented 3 years ago

Even if you have a distributed locking mechanism, you must still likely ensure that application instances are down or waiting for the migration to be applied, otherwise you get different versions of your application trying to access a different database schema...

@roji - The approach we took was that zero-downtime deployments were fine as long as the expand-contract pattern was used, whereas any other migrations would require instances to be brought down. Since, in practice, most migrations tend to fall within the former category, I still think there's a strong argument for doing the changes at application startup.

JonPSmith commented 3 years ago

hi @roji,

Thanks for clarifying that - I should have remembered the EF Core philosophy for migrations. And yes, the DistributedLock library only supports SQL Server and Postgresql databases, which wouldn't match the EF Core team's aims.

@zejji and/or me will look at this because I still think @zejji approach is really useful. As well as making migrations possible on startup it also allows an action that has to be run once on startup - in ASP.NET Core authorization library I am building there is a need to seed the database with certain settings provided by the appsettings.json file, which most be only done once.