Automatically migrate an application's database when the application is updated

I wonder if anyone knows of a good framework or advice of how to automatically do house keeping and updating a database depending on the version.

For example, imagine we are on version 1.1 and the software is upgraded to version 2.0. There are certain things that need to happen between 1.1 and 2.0 for things to be compatible.

• 1.2 adds a new table
• 1.3 removes an old table and changes some field names
• 1.4 Adds a relationship between table a and b.

So hence for version 2.0 to work on a version 1.1 database it needs to execute the above procedures in order before allowing the application to run.

How we did it before was we had a version number stored in a field in the database. A class would inherit for a base class and call itself the Update + version number, e.g., Update1.2. Each class would do its required tasks and update the version number and continue with the process.

There must be some kind of framework for this or at least a rec开发者_如何学Goommended way of doing things?

Use a migration framework, e.g. MigratorDotNet, which we use at our company and are quite pleased with. As your application evolves, you add more migrations (in the form of classes that inherit from a migration base class and override two methods for migrating up and migrating down; inside these methods, you use a simple API to issue SQL statements). Each migration has a sequence number or timestamp, and MigratorDotNet maintains a table in the database that keeps track of which migrations have been applied. On application startup, the necessary migrations are applied, so that the database always matches the requirements of the application code.

In other words, you were doing it the right way, but indeed, there are frameworks for it. :-)