I cannot comment on other ORMs, but I have been using DevExpress XPO for a corporate treasury application since 2007. The layout changes slightly with each version, but over the years there have also been some changes to the layout. The somewhat expanded version of the XPO update mechanism is by default convenient for all changes.
There is good basic information here about updating XPO applications.
DevExpress provides a DBUpdater tool to help you upgrade your production environments. You can expand this tool to meet additional requirements. In my application, we added some options for logging, rollback previews, etc.
Each module has the virtual methods UpdateDatabaseBeforeSchemaUpdate() and UpdateDatabaseAfterSchemaUpdate() . You can significantly control the update process in them.
As you noticed, some updates will be handled automatically by XPO (for example, by adding a new column), but some things need additional control, such as initializing a new column with a default value for existing records.
For example, let's say MyNewField was added to the MyEntity XPO class in version 2.0 of your application. Suppose that existing records should be set to 3. XPO will process the creation of a new column, but existing records will be NULL. (If you specify a default value in the XPO class, it will only apply to new entries). To correct the value for existing records, you would add something like the following to the overridden UpdateDatabaseAfterSchemaUpdate() module:
public override void UpdateDatabaseAfterUpdateSchema() { base.UpdateDatabaseAfterUpdateSchema(); if (CurrentDBVersion < new Version(2, 0, 0, 0)) ObjectSpace.GetSession().ExecuteNonQuery( "UPDATE [MyEntity] SET [MyNewField] = 3 WHERE [MyNewField] IS NULL"); }
(You can also use ObjectSpace.GetObjects<MyEntity>() and foreach if you want to avoid direct SQL.)
In your more extreme example of splitting a table into two parts, you can use the same method, but instead you override UpdateDatabaseBeforeUpdateSchema() , run SQL to split the table, let XPO do any other schema updates, and enter default values if necessary UpdateDatabaseAfterUpdateSchema() .
You will find that you are experiencing restriction problems, such as foreign key violations, so you may need to write some common routines such as DropAllForeignKeyConstraints() as part of UpdateDatabaseBeforeUpdateSchema() . Sometimes you find that XPO already provides something, sometimes not. Missing restrictions and indexes will be restored in updating the schema. (In my experience, switching the primary key of the master data table has proven to be the most difficult upgrade procedure to qualify.)
By default, all calls occur in an SQL transaction, so if something fails, everything should be rolled back.
Developers should know when changing a domain model can cause a problem with the underlying schema.
For testing purposes, we save several old client databases and run tests before and after testing as part of the build process to ensure that existing clients can correctly update any version from which they are updated. During production, whenever we encounter an update problem, these problems are added to this test library to prevent similar problems in the future.
We deal with large international companies and banks. The result is quite satisfied. In situations where the corporate database administrator needs to subscribe to the changes, they don't seem to need a command-line tool for updating, not a script.