Magic Free Code First Migrations

Posted on July 29, 2011. Filed under: Entity Framework, Visual Studio | Tags: , , , , |

This post is now out of date.

Be sure to check out EF 4.3 Beta 1.

 

<transparency>I work for Microsoft</transparency> (just kidding… but I couldn’t leave the tag open)

We recently released a very early preview of Code First Migrations and it’s been getting some mixed feedback, the negative feedback falls into three buckets:

  1. Stuff we have on the list but just haven’t done yet (i.e. Provider model, upgrade to specific named version, downgrade, outside of Visual Studio experience)
  2. Things you want us to do differently (i.e. Get rid of those SQL scripts… I’m a developer not a DBA)
  3. Functionality we did a bad job of explaining in the original posts (i.e. You don’t have to use the auto-magic behavior… you can have a script for every step if you want)

This post is going to walkthrough what it looks like to use migrations without the magic, a.k.a “Rails style migrations”. Code First Migrations also doesn’t lock you into one approach so you could always decide that you trust the magic in the future, or you can trust the magic for certain migrations.

One HUGE CAVEAT on this post is that all the scripts are going to be in SQL… one overwhelming bit of feedback we’ve heard is ‘get rid of the SQL… give me code’. You’re probably scratching you head asking ‘what exactly where those guys thinking when they used SQL scripts?’… we were thinking that folks would be more comfortable with the magic and that adding ‘custom scripts’ was going to be reserved for complex migration steps when you would likely need to drop to SQL anyway. Looks like we got that wrong (but that’s exactly why we ship CTPs). The plans we have around a provider model would make it very easy to swap in some other means of expressing the non-magical migrations… based on what we are hearing it looks we should be using code instead.

 

Getting a Model

  • Create a new console application and install the EntityFramework.SqlMigrations package from ‘Package Manager Console’, this will also install the EntityFramework package:

PM> Install-Package EntityFramework.SqlMigrations

  • Lets build a simple console app with a Code First model. Note that I’m also getting rid of other Code First magic by switching off database initializers and getting rid of the EdmMetadata table:

    using System;
    using System.Data.Entity;
    using System.Data.Entity.Infrastructure;
    
    namespace NoMagic
    {
        class Program
        {
            static void Main(string[] args)
            {
                // Don't do any database creation magic
                Database.SetInitializer<MyContext>(null);
    
                using (var db = new MyContext())
                {
                    foreach (var b in db.Books)
                    {
                        Console.WriteLine(b.Name);
                    }
                }
            }
        }
    
        public class MyContext : DbContext
        {
            public DbSet<Book> Books { get; set; }
    
            protected override void OnModelCreating(DbModelBuilder modelBuilder)
            {
                // No EdmMetadata table please
                modelBuilder.Conventions.Remove<IncludeMetadataConvention>();
            }
        }
    
        public class Book
        {
            public int BookId { get; set; }
            public string Name { get; set; }
        }
    }

 

First Migration

  • We’re not using any magic, so we want a script that will handle database creation, we use the Add-CustomScript command for that:
PM> Add-CustomScript –Name:”InitialCreate”
Scaffolding custom script by comparing current model to database.
Added custom script: Migrations\20110730045135_InitialCreate

 

This adds a Migrations folder to our project with a sub-folder representing our script. The folder contains our script and also a Target.xml file… the Target.xml file is there to facilitate downgrade with automatic migrations… it’s always there in the CTP but in the next release we’ll only leave Target.xml around when you are actually using automatic migrations.

InitialCreate

The script is scaffolded with what migrations would have done if you wanted it to run automatically… I won’t cover the SQL vs Code point again here, see the top. I could edit the script at this point if I don’t like what it is doing. The script is quite verbose too… I’m just showing the bit that actually creates our table (Beware: Don’t try and remove all that SET <blah, blah, blah> stuff from the script… did I mention this is a super early preview). There is also a create statement for the __ModelSnapshot table which is how the database knows what version it is at etc.

CREATE TABLE [dbo].[Books] (
    [BookId] INT            IDENTITY (1, 1) NOT NULL,
    [Name]   NVARCHAR (MAX) NULL,
    PRIMARY KEY CLUSTERED ([BookId] ASC)
);

 

  • Now we can use the Update-Database command to run the script
PM> Update-Database
1 pending custom scripts found.
Processing custom script [20110730045135_InitialCreate].
 - Executing custom script [20110730045135_InitialCreate].
  - Creating NoMagic.MyContext...
  - Creating [dbo].[Books]...
  - Update complete.
Ensuring database matches current model.
 - Database already matches model.
Update-Database : Cannot open database "NoMagic.MyContext" requested by the login. The login failed.
Login failed for user 'REDMOND\rowmil'.
At line:1 char:16
+ Update-Database <<<< 
    + CategoryInfo          : NotSpecified: (:) [Update-Database], SqlException
    + FullyQualifiedErrorId : System.Data.SqlClient.SqlException,System.Data.Entity.Migrations.Commands.MigrateCommand

 

Ok, so the error is an annoying bug… but everything actually worked ok. It errored out during the automatic upgrade process… which wasn’t going to do anything because we aren’t using the magic. You’ll get this error when you run the first migration against a database that doesn’t exist. Migrations will actually create the empty database for you… is that more magic that you should be able to switch off?

One thing that occurred to me while writing this post is that it would be great to have a way to prevent the automatic pipeline from ever kicking in just incase I accidently let it do something. Sounds like we need to have some settings defined in an xml config file your code.

That’s pretty much it, we just repeat that process indefinitely.

 

Second Migration

  • Let’s rename the Book.Name property to Title:
public class Book
{
    public int BookId { get; set; }
    public string Title { get; set; }
}

 

  • We could just let migrations scaffold the script with a drop/add column and then manually edit the script… but let’s tell it to take the rename into account while scaffolding:
PM> Add-CustomScript -Name:"RenameBookName" -Renames:"Book.Name=>Book.Title"
Scaffolding custom script by comparing current model to database.
Added custom script: Migrations\20110730051326_RenameBookName

 

You’ll notice that migrations has added a Model.refactorlog file to your project… Model.refactorlog shouldn’t be there since we scaffolded a script rather than doing an automatic upgrade… another CTP thing that we’ll fix.

RenameName

 

Looking at the scaffolded script we see that it contains a rename rather than a drop create:

EXECUTE sp_rename @objname = N'[dbo].[Books].[Name]', @newname = N'Title', @objtype = N'COLUMN';

 

  • Now we can use Update-Database to run our script:
PM> Update-Database
1 pending custom scripts found.
Processing custom script [20110730051326_RenameBookName].
 - Executing custom script [20110730051326_RenameBookName].
  - Rename [dbo].[Books].[Name] to Title
  - Caution: Changing any part of an object name could break scripts and stored procedures.
  - Update complete.
Ensuring database matches current model.
 - Database already matches model.

 

Conclusion

Code First Migrations supports automatic upgrade, custom scripts or a combination of both. We use SQL for the custom scripts at the moment but you are telling us they need to be code based. This is an early CTP which means it is full of rough edges… but that also means we are early enough in the release cycle to change things Smile

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

15 Responses to “Magic Free Code First Migrations”

RSS Feed for RoMiller.com Comments RSS Feed

Hi Rowan – I like the post :). I’m not ranting – hopefully not moaning or complaining. Caveats aside – the only feedback I have to give is that this stuff isn’t new. It’s been going on for years and years. I’ve actually written 3 migration tools myself – the last one was (literally) on a plane on my way home from MIX.

It’s not that hard. And you guys are a LOT smarter than me.

I understand you probably don’t want me to focus on the SQL scripting but… dude seriously. It doesn’t make a good demo if I throw a shoe on a plate and cover it with whip cream and call it a cake (this, I say with a smile… can you see it?).

I know you guys are somewhat limited with what you can see, codewise, in other projects. However that doesn’t stop you from looking at how they work. The customer has spoken *already* – it’s easy to see what they want. You really don’t need to “solicit feedback” as it’s already *been solicited* in those other projects. And they’ve made it.

Just make it for .NET as a starting point.

And I think you’re team is smart – but I think you guys talk to each other too much. This is a solved problem – you should be on auto-pilot right now as your awesome team raises the temperature in Building 7 with your huge brain power.

So – as politely as I can put it: Migrations have been around for 7 years. Just do what they do already – it’s a proven pattern. Call it CTP 2 and I promise I won’t make fun of you :).

Hi Rob,

Thanks for taking the time to read and comment, totally hear you on all your comments. We’re taking a look at things at the moment and we’ll post with where we landed shortly.

It seems that having code based scripts and the ability to switch off the automatic stuff will give most folks what they want. The places we are trying to do more than the existing migrations solutions is by integrating with Code First so that we can scaffold scripts for you based on the changes you made to classes and give you the *option* to let some parts be done automatically. If you take these out of the picture then there isn’t really much value in us rolling yet another migrations solution . I think we probably got over focused on these additions though and didn’t focus enough on getting the experience right for the bits that align with existing solutions. Obviously the SQL/code thing was just something we got wrong, the rest of the experience you and others are asking for is already there in the CTP (unless I am missing something else?).

I think the other thing we did wrong was starting with the ‘voodoo magic’ in our walkthroughs etc. We should start with the familiar experience that just introduces the fact that we scaffold the scripts for you as a starting point. Then, for folks who want to, we should introduce the option to have parts of the migration process occur automatically.

We’ll make sure we get the provider model into the next CTP too ;)

Thanks again for your feedback… keep it coming!

~Rowan

Compile list of all features of the major existing migrations frameworks.
Let community vote on most wanted features.
Implement.

I couldn’t agree more with Rob, innovate and bring more to the .NET party that makes people go “wow look what those guys are doing!” but the main features should speak for themselves

I am not going to put it politely. I am emotionally upset. Ive been developing .net for past 10 years since the beta. I love it but i hate how ms reinvents the wheel all the time again.

Just like the other commenter. Stick to proven technology and make it better. You already got mvc, which is rails. Ef codefirst which resembles activerecord, nuget thats the gem system. These things are succesfull because they are proven.

Raila migrations are code based, and just one file. You can make it better if you really want it that bad by generating a migration file from detected model change.

If you say that you listen, please listen.

Sorry for my tone but i think it need to be said. .net is a beatiful platform it should be a shame if it would go to waste.
am leaving the platfom for this kind of solutions? Why does your te

Oops, Emile has left the platform!

Sorry couldnt resist ;)

Yes lol, typing on iphone’s hard when just woke up :)

Honestly I have to wonder why everyone is so surprised, CTP1 is a natural extension to EF Code First – a framework that magically creates the database from the given models, there must be a fair amount of code behind that feature which isn’t going to be thrown out. There was every reason to expect automatic updating of the database rather than hand-coded change scripts.

I’m not saying this is what I wanted, but it is what I expected.

What I was hoping for (but haven’t had the time to check if it exists yet) is more customizable initializers. Last I checked I had to write a hefty amount of code to make what should be a simple change to the default provider.

IMHO a provider model (read extensibility and flexibility) should not be considered a Feature, as in “we’ll get to it”. It should have been the core on which this is built.

[...] Magic Free Code First Migrations – Rowan Miller responds to some of the feedback about the Entity Framework Migrations CTP, discussing some of the design decisions, and outlining some of the use cases for the tool, urging people to give good constructive feed back through the official channels to help the focus future CTP efforts. [...]

In response to your response: I think perhaps the impression that you guys aren’t listening comes from the fact that we don’t know if you’ve heard us until a release happens. Case in point, the migrations feature was first mentioned on the EF Design Blog in October of 2010. Then, we get total radio silence on the feature for about 8 months, then the CTP is released.

I think that if you were to write a follow-up blog post, reiterating the most common feedback, and giving some indication whether any of those points will affect the direction the team takes as the feature is implemented. Then poke your head up every once in a while to give us an update, maybe something a little more substantial than “Yes, we’re still working on it.”

That said, I appreciate the content in this post. It clears up some of the concerns I had about the current implementation that I emailed you about a few days ago. I’m really looking forward to how this shapes up as it moves toward RTM.

Well – I’m obviously in the minority – but we NEED the SQL Code for the DB Migrations. We work in a large company – and we will need to hand SQL Scripts off to our DBA’s to actually apply against the DB. They will NOT allow us to hand them executable code to just run against the DB to do the changes. I’m pretty sure that this is pretty standard for a lot of large organizations.

We’ve summarized your feedback and what changes we are making in this post; http://blogs.msdn.com/b/adonet/archive/2011/08/04/code-first-migrations-your-feedback.aspx

[...] Automatic migrations is just too much magic: The ‘August 2011 CTP’ actually included the ability to do automatic migrations, imperative migrations (similar workflow to existing solutions), or a combination of both. However, the lack of a provider model and a code based way of expressing scripts meant the imperative experience was below par compared to existing solutions. We also focused on the automatic and mixed-mode approach in our walkthrough. For the next release we will start with the imperative approach and then delve into how automatic migrations can optionally be used to avoid having large amounts of code in your project for simple changes. The existing imperative experience is described in this post. [...]

sweet the Ruby on Rails way..nice to see

[...] Automatic migrations is just too much magic: The ‘August 2011 CTP’ actually included the ability to do automatic migrations, imperative migrations (similar workflow to existing solutions), or a combination of both. However, the lack of a provider model and a code based way of expressing scripts meant the imperative experience was below par compared to existing solutions. We also focused on the automatic and mixed-mode approach in our walkthrough. For the next release we will start with the imperative approach and then delve into how automatic migrations can optionally be used to avoid having large amounts of code in your project for simple changes. The existing imperative experience is described in this post. [...]


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 159 other followers

%d bloggers like this: