Running & Scripting Migrations from Code

Posted on February 9, 2012. Filed under: Entity Framework, Visual Studio | Tags: , , , , |

Code First Migrations is included as part of Entity Framework starting with the EF 4.3 release.

Migrations are normally created and run from the Package Manager Console in Visual Studio. These commands are just thin wrappers over public APIs that you can call directly from your own code. In this post you’ll see how to kick-off the migrations process from code. You’ll also see how to get a SQL script that represents the changes from a set of migrations.

I’m going to assume you have a working knowledge of Code First Migrations… if you don’t, take a minute to read the Code-Based Migrations Walkthrough.

 

Running Migrations from Code

Running migrations is actually very simple. You need to instantiate an instance on the Configuration class that was added to your project when you enabled migrations, this is then used to construct a DbMigrator. Calling the Update method will then update the target database to the latest migration. There is also an overload that allows you to specify a target migration to upgrade/downgrade to.

var configuration = new Configuration();
var migrator = new DbMigrator(configuration);
migrator.Update();

The above code will update that database that your context targets. You can override the database that will be migrated by setting the TargetDatabase property on the configuration.

var configuration = new Configuration();
configuration.TargetDatabase = new DbConnectionInfo(
    "Server=MyServer;Database=MyDatabase;Trusted_Connection=True;", 
    "System.Data.SqlClient");

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

 

Scripting Migrations from Code

Scripting migrations is very simiar, except you wrap the DbMigrator in a MigratorScriptingDecorator. You can then use the ScriptUpdate method to get the resulting SQL script as a string. The source and target migrations allow you to get a script between any two given migrations. If you supply null an empty database is used for the source migration and the latest migration is used for the target.

var configuration = new Configuration();
var migrator = new DbMigrator(configuration);

var scriptor = new MigratorScriptingDecorator(migrator);
string script = scriptor.ScriptUpdate(sourceMigration: null, targetMigration: null);

 

Summary

Our team has been careful to ensure that all the operations available as Power Shell commands can be easily performed from your code. This post showed how to use code to perform the operations available via the Update-Database command.

About these ads

Make a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

26 Responses to “Running & Scripting Migrations from Code”

RSS Feed for RoMiller.com Comments RSS Feed

Hello not know it was possible to do this, very cool, I’ll test it, and congratulations to the EF team who are doing a great job.

How does the DbMigrator know which DbMigration to use and how does it keep track of versioning? I don’t want to use the powershell to do the migrations since I want to build the migrations dynamically then run them on the next application startup.

DbMigrator scans the assembly to look for classes deriving from DbMigration. By default it looks in the same assembly that the configuration class is defined in, but there is a MigrationsAssembly property that you can set on the configuration. Migrations maintains a table in the database to keep track of the migrations that have already been applied.

This is exactly what I needed. Thanks!

I don’t see any way to migrate all the way down to an empty $InitialDatabase from code, like you can do in the Package Console. It appears that Update(string) only lets you downgrade to a previously “named” migration. Is that the case?

There is a public constant DbMigrator.InitialDatabase that you can pass as the target migration id.

I have my migrations in a class library. If I want to run them from my Web project in the Global.asax Application_Start method, there doesn’t appear to be any way of doing that without marking the Configuration class public (by default is it marked internal). I did that and it seems to work okay. Can any harm and/or bad juju result from making the Configuration class public?

There is no issue making the class public.

This is definitely very interesting. However, it seems that this process would encourage people to allow their “Application Pool Account” to have SYSADMIN or DBO permissions, instead of a set of restricted permissions … that just seems bad juju … IMHO …

Getting a very weird issue. I created a branch in Mercurial to do some experimenting. I inadvertently removed a navigation relationship from one of my entities and was warned on the next run that I need to add a migration. Since I never meant to remove that relationship, I did a complete revert on that entity (restoring it to its original state), but EF still insisted that a migration was needed. The migration that resulted was a complete mess — it tried to add back the relationship I had originally removed (and now restored). Naturally the migration did not work, because it was now trying to add the same FK constraint twice.

In short, it seems there is no way to make EF “forget” that I temporarily changed my model and now want to leave it as it was. What is the mechanism by which EF senses that the model has changed? Is there any way to tell EF that NO, I DIDN’T mean to change my model after all, and that in fact no migration is actually required?

Rowan, when I try to Script a migration that isn´t applied yet, it says that it don´t exists. And if I pass null to target, it says that I need to set Auto Migrations. For some reason it doesn´t detect my last migration:

var configuration = new DbMigrationsConfiguration();
var migrator = new DbMigrator(configuration);

var scriptor = new MigratorScriptingDecorator(migrator);
string script = scriptor.ScriptUpdate(sourceMigration: null, targetMigration: null);

I did a test, and my class is accessible:

new FujiyBlog.Core.Migrations.Teste()

Looks awesome, thanks!

1. I see in the derived DbMigration class a timestamp, if we change the value in IMigrationMetadata.Id, does that change the order in which the migrations occur? Would we have to change the Id and the filename (which includes the timestamp). I’ll assume we do not ever touch the cryptic IMigrationMetadata.Target value.

2. I’m curious though on how you’d recommend a clean way to perform migrations on multiple databases. In my case the (SQLite) database is accessed via a File->Open menu, and each time I open a different database I want to perform the (UpgradeToLatest) migrations.

The ‘cryptic’ Target property for a migration is the edmx xml for the model that has been gzipped and then base64 encoded – so you actually have the whole model definition in there.

An odd question but what does “latest” really mean? The latest the database knows about or the latest the code knows about?
Scenario: I have released changes to my app that have included new tables and copied over data from table to another via migrations, and the customer has been also been entering data into these new tables. Then the customer finds some fatal crash in a newer exe and goes back to using an older exe.

1. If all apps say to migrate to the latest and the app is version 5 and the database is version 7 (for simplistic terms), it will never perform the Down() operations unless I explicitly tell it to, correct?

2. In the case where they were at application version 7, used an older app version 5 temporarily, and then installed application version 8, will the migrations for version 6, and 7 be performed again – to ensure that the migrations to copy data between tables was performed for all rows?

Hi, Ron!
I have started a thread on Microsoft forums (http://social.msdn.microsoft.com/Forums/en-US/adodotnetentityframework/thread/09ad08e6-e97d-4352-853e-b5097865eac8), but got no answers so far. Basically, I would like to know if this scenario is possible (with or without Migrations):

I have a project Core, with a DbContext and some entities. I am using CreateDatabaseIfNotExists initializer, and the first time I access data, it creates the tables for me. So far, so good.

Suppose some of my entities are meant to be extended, that is, I design them to allow hypothetical derived types with additional properties.

I want to be able to dynamically discover types in other assemblies that extend those types from project Core that allow extensibility, perhaps using Managed Extensibility Framework, or something else.

Since these hypothetical types only add properties to the base ones, they can be represented in my database as new tables, which are connected to the existing ones through foreign keys. This is the Table Per Type inheritance strategy.

What I want is, on the OnModelCreating method of my DbContext, to look up these new types, and add their corresponding tables to the database, if any is found. I was wondering if Code First Migrations allows this: matching registered entities with existing tables and deciding if new tables need to be added.

Any ideas?
Thanks!
RP

[...] code to automatically change the database when new classes or properties are added to the model. My recent blog post has more details on invoking Migrations from [...]

I can see from this post how to automate the ‘update-database’ command to create migration scripts for later use. Thank you very much.

How can I automate the ‘add-migration’ command from code. We would like the developers not to use the PackageManager shell _ever_. So, we’d like to be able to run the add-migration command from code, either as part of the build process, or as part of application startup. What are the public APIs that we need to access to make that happen?

Did you ever come up with a solution of how to script the add-migration command?

Hello Rowan, I have a question. I just need help if I am correct in the following assertions. I want to create a DDD application. I am separating my model and my entities. I create a edmx file and I decide to move the tt related to the model in other project. I am interested in create different contexts. My first question is can I create a context with some tables that ignore the navigation properties and do not pull other tables related? Or should I create a mix with code first when the tables that I need to use individually are in a context where the navigations properties are commented out. am I doing this the right way? Or am I making a bigger mess?

Hopefully you receive this comment and better yet have time to answer it. Thank you very much for making EF and excellent framework.

Regards,

Richard Valdivieso

I have not been able to get “You can override the database that will be migrated by setting the TargetDatabase property on the configuration.” to work. The problem is also described here: http://stackoverflow.com/questions/11863301/entity-framework-4-3-1-migrations-always-invokes-default-constructor-and-ignores

I have a context called EducationDataContext, and no matter what i specify in the TargetDatabase property of the configuration, I can’t get it to migrate to a database with any other name. Incredibly aggravating.

Thank you Rowan for your most valuable article how to manage migrations in runtime. We have our application published at several deployment destinations (staging/production). Is it possible to backup and restore production database to staging SQL server with disabled automatic migrations if I use migrator.Update() method and application code is the same at staging and production deployment destinations? I’m aware of different values stored in Model column (_MigrationHistory table).

[...] and restart. I just happened to see Rowan in the kitchen and he suggested I look at his blog Running & Scripting Migrations From Code. I copy/pasted the following lines from his blog into the about method of my controller and was [...]

[...] They’re using DbMigrator to run these migrations from code – for more info on running migrations from code see Running & Scripting Migrations from Code. [...]

How can i get errors or output from DbMigrator.Update()? It doesn’t seem to throw on an error in my migrations where a sequence already exists so it fails silently.

There appears to be a bug in this part of the framework

var configuration = new TMigrationsConfiguration();
configuration.TargetDatabase = new DbConnectionInfo(context.Database.Connection.ConnectionString,”System.Data.SqlClient”);
var migrator = new DbMigrator(configuration);
migrator.Update();

Does not act the same as

var configuration = new TMigrationsConfiguration();
var migrator = new DbMigrator(configuration);
migrator.Configuration.TargetDatabase = new DbConnectionInfo(context.Database.Connection.ConnectionString,”System.Data.SqlClient”);
migrator.Update();

That is – the Target database is ignored if changed after creating the migrator.


Where's The Comment Form?

    About

    Rowan works as a Program Manager for the ADO.NET Entity Framework team at Microsoft. He speaks at technical conferences and blogs at romiller.com. Rowan lives in Seattle, Washington with his wife Athalie. Prior to moving to the US he resided in the small state of Tasmania in Australia. Outside of technology Rowan's passions include snowboarding, mountain biking, horse riding, rock climbing and pretty much anything else that involves being active. The primary focus of his life, however, is to follow Jesus.

    RSS

    Subscribe Via RSS

    • Subscribe with Bloglines
    • Add your feed to Newsburst from CNET News.com
    • Subscribe in Google Reader
    • Add to My Yahoo!
    • Subscribe in NewsGator Online
    • The latest comments to all posts in RSS

    Meta

Liked it here?
Why not try sites on the blogroll...

Follow

Get every new post delivered to your Inbox.

Join 146 other followers

%d bloggers like this: