How to create initializer to create and migrate mysql database?

Question

I have been learning how to use EF for a week or so now and am stuck on the issue of creating/updating my database. I am able to create an initializer to create the database if it is not there:

static class Program
{
    static void Main()
    {
        Database.SetInitializer<GumpDatabase>(new GumpDatabaseInitializer());
....

class GumpDatabaseInitializer : CreateDatabaseIfNotExists<GumpDatabase>
{
    public GumpDatabaseInitializer()
    {
    }
    protected override void Seed(GumpDatabase context)
    {
        context.Database.ExecuteSqlCommand("CREATE UNIQUE INDEX Name ON Stations (Name)");
        // Other stuff
    }
}

Or I can create a Configuration to migrate the db

static class Program
{
    static void Main()
    {
        Database.SetInitializer<GumpDatabase>(new MigrateDatabaseToLatestVersion<GumpDatabase, Configuration>());
....

internal sealed class Configuration : DbMigrationsConfiguration<GumpDatabase>
{
    public Configuration()
    {
        AutomaticMigrationsEnabled = true;
        SetSqlGenerator("MySql.Data.MySqlClient", new MySql.Data.Entity.MySqlMigrationSqlGenerator()); 
    }

    protected override void Seed(GumpDatabase context)
    {

    }

Each works correctly but I haven't figured out a way to do both. I can switch between the two initializers by changing the SetInitializer call but if I want to create the database if it is not there and also migrate it if it is what do I do? Do I need to create a custom initializer?

Thanks

Edit based on NSGaga answer

class CreateOrMigrateDatabaseInitializer<TContext, TConfiguration> : CreateDatabaseIfNotExists<TContext>, IDatabaseInitializer<TContext>
    where TContext : DbContext
    where TConfiguration : DbMigrationsConfiguration<TContext>, new()
{
    private readonly DbMigrationsConfiguration _configuration;
    public CreateOrMigrateDatabaseInitializer()
    {
        _configuration = new TConfiguration();
    }
    public CreateOrMigrateDatabaseInitializer(string connection)
    {
        Contract.Requires(!string.IsNullOrEmpty(connection), "connection");

        _configuration = new TConfiguration
        {
            TargetDatabase = new DbConnectionInfo(connection)
        };
    }
    void IDatabaseInitializer<TContext>.InitializeDatabase(TContext context)
    {
        Contract.Requires(context != null, "context");

        if (context.Database.Exists())
        {
            if (!context.Database.CompatibleWithModel(throwIfNoMetadata: false))
            {
                var migrator = new DbMigrator(_configuration);
                migrator.Update();
            }
        }
        else
        {
            context.Database.Create();
            Seed(context);
            context.SaveChanges();
        }


    }
    protected virtual void Seed(TContext context)
    {
    }
}

and

internal sealed class Configuration : DbMigrationsConfiguration<GumpDatabase>
{
    public Configuration()
    {
        AutomaticMigrationsEnabled = true;
        AutomaticMigrationDataLossAllowed = false;
        SetSqlGenerator("MySql.Data.MySqlClient", new MySql.Data.Entity.MySqlMigrationSqlGenerator()); 
    }

    protected override void Seed(GumpDatabase context)
    {
    }
}

and

class GumpDatabaseInitializer : CreateOrMigrateDatabaseInitializer<GumpDatabase,Gump.Migrations.Configuration>
{
    public GumpDatabaseInitializer()
    {
    }
    protected override void Seed(GumpDatabase context)
    {
        context.Database.ExecuteSqlCommand("CREATE UNIQUE INDEX Name ON Stations (Name)");
        context.Database.ExecuteSqlCommand("CREATE UNIQUE INDEX Name ON Sequences (Name)");
        context.Database.ExecuteSqlCommand("CREATE UNIQUE INDEX StationPartNumber ON StationPartNumbers (StationId,PartNumberId)");
    }
}

and finally

static void Main()
{
    Database.SetInitializer<GumpDatabase>(new GumpDatabaseInitializer());

Answer 1

I think you're pretty much there - you can lookup the source code for MigrateDatabaseToLatestVersion (it's open source http://entityframework.codeplex.com/) - it's pretty simplistic, what it does pretty much is call the DbMigrator - as far as I could see.

All you have to do seems is to merge the two - use one or the other as a basis, add other functionality in there - that should work fine I think.

class CreateAndMigrateDatabaseInitializer<TContext, TConfiguration> : CreateDatabaseIfNotExists<TContext>, IDatabaseInitializer<TContext> 
    where TContext : DbContext
    where TConfiguration : DbMigrationsConfiguration<TContext>, new()
{
    private readonly DbMigrationsConfiguration _configuration;
    public CreateAndMigrateDatabaseInitializer()
    {
        _configuration = new TConfiguration();
    }
    public CreateAndMigrateDatabaseInitializer(string connection)
    {
        Contract.Requires(!string.IsNullOrEmpty(connection), "connection");

        _configuration = new TConfiguration
        {
            TargetDatabase = new DbConnectionInfo(connection)
        };
    }
    void IDatabaseInitializer<TContext>.InitializeDatabase(TContext context)
    {
        Contract.Requires(context != null, "context");

        var migrator = new DbMigrator(_configuration);
        migrator.Update();

        // move on with the 'CreateDatabaseIfNotExists' for the 'Seed'
        base.InitializeDatabase(context);
    }
    protected override void Seed(TContext context)
    {
    }
}

call it like this...

Database.SetInitializer(new CreateAndMigrateDatabaseInitializer<GumpDatabase, YourNamespace.Migrations.Configuration>());

...actually, override it (since it's generic implementation) like you were doing for CreateDatabaseIfNotExists (you just have extra 'param' for Configuration) - and just supply the 'Seed'.

class GumpDatabaseInitializer : CreateAndMigrateDatabaseInitializer<GumpDatabase, YourNamespace.Migrations.Configuration>
{
    protected override void Seed(GumpDatabase context)
    {
        context.Database.ExecuteSqlCommand("CREATE UNIQUE INDEX Name ON Stations (Name)");
    }
}

...and call it something like

Database.SetInitializer(new GumpDatabaseInitializer());

EDIT: Based on the comments - DbMigrator should not run twice. It always checks (spends a bit of time) and does a 'blank' update and moves on. However just in case if you'd like to remove that and 'check' before entering - this should work (change the similar piece above)...

var migrator = new DbMigrator(_configuration);
if (!context.Database.CompatibleWithModel(throwIfNoMetadata: false))
    if (migrator.GetPendingMigrations().Any())
        migrator.Update();

(this is a redundant / double-check - one of the if-s should be enough. Put a break there - and see exactly what's happening, it should not get in - once Db is migrated. As I mentioned, works fine when I test it.

EDIT:

Replace the inside of InitializeDatabase with...

var doseed = !context.Database.Exists();
// && new DatabaseTableChecker().AnyModelTableExists(context);
// check to see if to seed - we 'lack' the 'AnyModelTableExists' - could be copied/done otherwise if needed...

var migrator = new DbMigrator(_configuration);
// if (doseed || !context.Database.CompatibleWithModel(throwIfNoMetadata: false))
    if (migrator.GetPendingMigrations().Any())
        migrator.Update();

// move on with the 'CreateDatabaseIfNotExists' for the 'Seed'
base.InitializeDatabase(context);
if (doseed)
{
    Seed(context);
    context.SaveChanges();
}

This works around (half-way) not-seeding - if migration goes first. And migrations have to be first, otherwise you have issues.

You still need to do it properly - this is the gist if not all you might need - but if any issues w/ MySQL etc., probably some more leg work here.

Note: Still seeding doesn't call if you have a db, but it's empty. Problem is mixing of the two different initializers. So you'll have to work that out - either by implementing what Create... does inside (that call we can't call) or something else.

Answer 2

Actually it should be:

var migrator = new DbMigrator(_configuration);
if (!context.Database.CompatibleWithModel(false) || migrator.GetPendingMigrations().Any())
    migrator.Update();

because if we have a migration, that is not related to our db model, for example inserting a row in any of our tables, the migration won't be executed.