Since I released the Database Migrator for .NET, a lot of people have asked me if I’ve used it on real/large/live projects and how was my experience. Like any tools, it can be good and … it can be bad. Here’s a couple of guidelines or what some would call “Good Practices” (note my voluntary abstinence over the incorrect “Best Practices” term).
Coming from hand-written SQL script to beautifully manage database migrations is a big step towards programming nirvana. But like the use of any revision control tools you ought to follow some guidelines.
Tip #1 : Test your migrations
Like any code in your application you must test your migrations. Ups and downs code. Do it part of your continuous build process and test it on as many different databases and environment as you can.
Tip #2 : Never edit a released migration
Once your application is released you should never alter it. Doing so would permit that two different database schema have the same version number, which is purely evil and can lead to programming darkness.
A good way to enforce this practice is to allow modifications to the migrations as long as they are on a feature branch or on the trunk and not on a release branch.
I guess one could code a Subversion pre-commit hook for this.
Tip #3 : Keep them short and sweet
Don’t create one migration for version 1.0 of your app. Split it in as many migrations as possible (be logical here). Adding a table ? 001_CreateUserTable, Removing a column ? 002_RemoveSomeColumn. This is simple SoC.
It also ease-up debugging when a migration fails and you only have 3 lines of code in there.
Tip #4 : Be migration-driven
Stop using any GUI to alter the database. Code it!
As a coder you should find that writing:
generate migration AddPostNoteColumn
edit db/migrations/004_AddPostNoteColumn.cs
...
Database.AddColumn("Post", "Note", typeof(string));
CTRL+S
nant migrate
is a lot faster then:
- Fire your database management app
- Connect to the database server
- Browse to the Post table
- Click in the menu to edit table schema
- Type : Note
- Select : varchar
- CTRL+S
.
Plus you have to write the migration code after anyway.
If you tough of some other tips let us know!
Marc-André Cournoyer's blog 
I was able to use a migration to add users to the ASP.NET Membership infrastructure by including the Membership configuration in a file called Migrator.Console.exe.config in the lib folder.
I guess this could be used for any configuration element you need in a migration.
Now I would like to use the Membership API in the Console (from script\console), but the boo interpreter is a dynamic in memory assembly and I don’t know how to add configuration elements to it to initialize the Membership API.
Is this possible?
I haven’t used the Membership infrastructure, but you’ll need to show me some code. What code have you ran in the console ?
>>> import System.Web.Security from System.Web
>>> user = Membership.CreateUser(‘myusername’, ‘mypassword’, ‘m@mydomain.com’)
System.Web.Security.MembershipCreateUserException: La réponse-mot de passe fourn
ie n’est pas valide.
à System.Web.Security.Membership.CreateUser(String username, String password,
String email)
à Input12Module.Main(String[] argv)
This comes from the fact that the Membership configuration comes from the default web.config or machine.config which requires 1 special character in the password (Mine does not); in any case it would save the user in the wrong database (The default one).
What I would like is the console to load its configuration from the web.config of my application (in the public folder)
The configuration is something like:
Sorry the XML got cut out, this should work:
<configuration>
<connectionStrings>
<add name=”WKSConnectionString” connectionString=”Data Source=(local);Initial Catalog=WKS;Integrated Security=True” />
</connectionStrings>
<system.web>
<machineKey validationKey=”validation key here” decryptionKey=”decription key here” validation=”SHA1″/>
<membership defaultProvider=”WKSSqlMembershipProvider”>
<providers>
<add connectionStringName=”WKSConnectionString” applicationName=”/” enablePasswordRetrieval=”true” enablePasswordReset=”true” requiresQuestionAndAnswer=”false” requiresUniqueEmail=”false” passwordFormat=”Encrypted” minRequiredNonalphanumericCharacters=”0″ minRequiredPasswordLength=”6″ maxInvalidPasswordAttempts=”5″ name=”WKSSqlMembershipProvider” type=”Wks.Helpers.MySqlMembershipProvider, Wks”/>
</providers>
</membership>
<roleManager enabled=”true” defaultProvider=”WKSSqlRoleProvider”>
<providers>
<add connectionStringName=”WKSConnectionString” applicationName=”/” name=”WKSSqlRoleProvider” type=”System.Web.Security.SqlRoleProvider”/>
</providers>
</roleManager>
</system.web>
</configuration
I don’t think you can load AppDomain config into the console script unless you write it in your machine.config.
Maybe their’s a way to load Membership config at runtime ?
I’ve had problems with config in the console too with Castle Windor container, the solution was to use a plain XML config file and load it at runtime to configure the container.
Sorry I’m not of any help here… Maybe you could try asking your question in the boo mailing list.
Thanks,
I could not find a way to load the Membership config at runtime after going through the code in Reflector.
It is not that big of a deal as VS has a membership built-in and I will later build a web interface or it would not be that big of a deal to modify the default configuration for a development machine.
Hello,
I just happened to find a solution to load AppDomain config into the console script
I included the membership configuration in booi.exe.config and it works to access the membership API from the console.
Nice!
Thanks for noting it back here JP!
First off, thank you very much for developing this tool and making it available. This is exactly the tool we need to help us work with an evolving database.
However, I am unable to successfully rollback a migration. I get reader errors. (a reader is already open. Close it before opening another) I got the code from the svn link from your original post. Is a more recent version available? Any pointers will be useful.
Thanks again
Hey Rams,
Glad you like my project, the svn is at http://code.macournoyer.com/svn/migrator/trunk/
Make sure you dispose the reader:
using (IDataReader r = ExecuteQuery(“…”) {
…
}
or put r.Dispose in s catch block.
Let me know if this helps!
DOH!!
My mistake. I just stepped thru my code and realized what I was doing wrong. I was assuming the reader would be “magically” closed for me.
With this tool you saved me a couple of day’s work.
Thank you for your quick response and pointer. It helped.
thx
how does one create a column of mssql type uniqueidentifier? I see the code for it, but when I pass in System.Guid I get
“The System.Guid” type is not supported.”
This is really cool… Be good to my robust exception Nice joke! Who was Snow White’s brother? Egg White. Get the yolk?
Probably a simple answer, but your #1 guideline is to test your migrations, but I can’t for the life of me find examples of unit tests for migrator.net, and I am not so far able to figure out how to do it myself, either.
Any tutorials out there that are evading my google searches somehow?
Thanks.
Pingback: Database Versioning «
There are some interesting points on this article however I don’t know if I see all of them center to heart. There may be some validity however I’ll take hold an opinion till I look into it further. Good article , thanks and we would like more! Added to FeedBurner as well.
you are actually a good webmaster. The website loading pace is incredible. It seems that you’re doing any distinctive trick. In addition, The contents are masterpiece. you’ve done a excellent process in this topic!
I was very encouraged to find this site. I wanted to thank you for this special read. I definitely savored every little bit of it and I have you bookmarked to check out new stuff you post.
Pingback: Nail Designs
Pingback: How do I test database migrations? | Question and answer
each time i used to read smaller articles that also clear their motive, and that is also happening with this article which I am reading at this time.
Pingback: How do I test database migrations? | Ask Programming & Technology