DB Upgrade Scenarios


  1. Introduction
  2. How Db Upgrade works
  3. Enabling/Disabling the DB upgrade process
  4. DbInfoModule and tables
  5. Data Migrations
  6. Using vdbtool to generate DDL scripts


One very cool feature of VITA application framework is automatic database schema (model) update. You define your data model in c# code - and VITA takes care of creating/updating matching database objects - tables, keys, indexes, stored procedures. This is a great convenience for team development - database model 'travels' with .NET code, and local databases of individual developers are automatically brought to date as soon as they sync the code with central source code repository.
For shared staging/test and production databases the desired scenario may be different - production databases should probably be updated under careful watch of db admin and deployment engineer. So VITA provides several options to control the process and execute the database update differently depending on the environment. For production databases, you can generate the update script using the vdbtool application. This script can be tweaked and then either applied manually or as a part of automated deployment process.
Sometimes upgrading the database requires to run some data operations - initialize some columns in existing rows, insert initial data into new or existing tables, do some cleanup, etc. You can define such actions - data migrations - and link to them to particular version of an entity module.
In this document we discuss how the automatic schema update process works, what options you can use to alter it, and how to use this facility in different environments.

How DB Upgrade works

Assuming the automatic DB model update is enabled (more on this below), here is how it works. At application startup, VITA builds the database model as it 'should-be' according to your entity model in c# code. It then loads the database model as it is now in the database. Then VITA compares both models, and builds a list of SQL DDL scripts that should be applied to bring the database up-to-date with the entity model. Unlike other ORM frameworks, you do not need to build up/down migration scripts, VITA builds them automatically - as soon as the delta is detected, the database can be brought up-to-date with c# code. Also, if schema update fails for any reason, it might be restarted from any middle point - VITA will figure out the difference again and try to continue where it stopped previously.
Automatic DB schema update capabilities are very useful for team development when each member is using his/her local database - this is arguably the best arrangement for teams, large and small. What usually becomes a challenge is propagating schema updates across local machines and synchronizing them with code. With VITA, things are really easy. Team members modify c# code, merge and push it to the central source repository. To get database schema synced up, you just sync and rebuild the .NET code in your application. Once you start the app, all changes from all developers will appear in your local database. The DB schema effectively 'travels' with code.
VITA defines a number of attributes that let you describe additional database artifacts like indexes, keys, exact data types. This information is not used by your .NET code directly, but is used exclusively to build proper database objects. This allows you to build a database schema that is realistically close to the database model that will be deployed in production, without the need for extra SQL scripts to setup these objects.

Enabling/Disabling the DB upgrade process

As we already stated, the automatic DB update should be enabled only in certain environments (development). You enable/disable the facility by setting the DbSettings.DbUpgradeMode property that you use when connecting to the database. You set the property using a parameter in constructor:

  var dbSettings = new DbSettings(Driver, dbOptions, connectionString, upgradeMode: DbUpgradeMode.Always);

DbUpgradeMode is an enumeration with the following values:
  • Always - always check the differences and update the database schema if any found.
  • Never - never update the database; it is assumed that the database is up-to-date.
  • NonProductionOnly - update only if the database is not a production database. This is a third 'flexible' setting (it is default value for parameter in constructor). In this case VITA will try to find a table DbInfo in the database and read a value in the column Instance type. If the table is not found in the database, VITA assumes that the database is a dev-type installation. More on this table and module in the section below.
We recommend to turn the automatic update off for production databases (value Never). You can use either of the following two approcaches for setting ModelUpdateMode:
  • Use DbUpgradeMode.NonProductionOnly and add DbInfoModule to the entity application. Adjust the value of DbInfo.InstanceType in production databases by running Update SQL.
  • Use the configuration file to store the mode value, read it at application startup and use the value as parameter to DbSettings constructor. For production databases use DbUpgradeMode.Never, for local databases use DbUpgradeMode.Always. For shared test/staging installations you can use either of the modes, depending on your needs.
UpgradeMode setting is basically on/off flag for model updates. You can use another property DbSettings.UpgradeOptions - this is a flag set specifying what kind of objects you want to update - tables, indexes and/or stored procedures (all three are ON by default). There is also a flag DropUnknownObjects (off by default) - it specifies if VITA should drop the database objects that have no matching object in the entity model. By default, VITA leaves such objects untouched, but you can use it to drop tables that were previously used but no longer needed.
The fact that VITA does not touch unknown tables is important for apps running against a sub-set of a bigger database used by another application.

DbInfoModule and tables.

This module is part of Vita.Modules assembly, available in Vita.Modules nuget package. The modules defines entities/tables that store information about application version, instance type, and versions of containing modules. .
If you add DbInfoModule to your application, it will create the table and a single record in DbInfo talbe. The instance type will be set to Development (integer 0). You cannot change this value from application code - this is intentional. For a production database, you need to change the value manually to 2 (Production) by running UPDATE SQL script using some data management tool like Management Studio.
After you change it, the VITA-based entity application will no longer try to update this database at startup (if you use NonProductionOnly setting). You (or database administrators and deployment engineers) will be updating the schema using the update scripts during deployment. The update scripts will be generated by the vdbtool (described below) by comparing current 'Release-state' of entity model and current (older-version) state of the production database.
The other useful column in the DbInfo table is Version. It contains a copy of EntityApp.Version string copied from the EntityApp version last time the db schema was updated. It is then necessary to uptick the EntityApp.Version each time the developer makes changes to entity model (just increment the build number in the version: "" -> ""). Other than keeping the information about the current version, this column helps to prevent erroneous rollback of the database to older version by accidental connection of an older version of the app.
This might be useful in protecting the shared (staging) environments - when somebody with older version of the app tries to connect to the shared database. In this case the application will detect the newer version of the database and will abort the start of the older app.

Data Migrations

Often upgrading the database requires executing some data operations - to init some values, insert initial records into a new table, or to adjust the existing data to new application version. VITA provides a convenient facility to do this - data migrations. Note that it's not the same as migrations in EF - it is not schema changes, but pure data edits on top of automatic schema upgrades.
Data migration is an action (custom SQL or .NET code) linked to certain version. Note that migrations are defined at entity module level, and therefore are linked to the version of the module. Entity modules are primary components of application construction - they define a set of related entities and accompanying code. Each module has a version property - you specify the current version value (ex: "") as a parameter to base module constructor. VITA saves these current module versions in the database (using DbInfo module) after upgrade completes. At application startup VITA reads module version (old) from the database and compares it with the (new) module version provided in entity module definition in code. It then selects the migration actions that are linked to any versions in between (higher than old version and lower or equal to new). Those selected migrations are executed during/after the db upgrade process.
You define migration actions in the override of entity module's method RegisterMigrations:

public override void RegisterMigrations(DbMigrationSet migrations) {
  // register module migrations here

There are two methods on DbMigrationSet class you can use to add migrations:
// DbMigrationSet class
public DbMigration AddSql(string version, string name, string description, 
          string sql, DbMigrationTiming timing = DbMigrationTiming.End);
public DbMigration AddPostUpgradeAction(string version, string name, string description, 
     Action<IEntitySession> action);    
Both methods have 3 common parameters: version, name and description. Version (ex: "") is very important, it identifies to which module version this migration is linked to. Name and Description provide descriptive information about the action. They will appear in logs.
The first method adds plain SQL to run during DB upgrade. You can also specify timing - when to run it in the upgrade process. Possible values are Start, Middle, End. Start and End are self-explanatory. Middle is the intermediate point, right after all new tables/columns are created, but not finalized.
There is one situation when this is particularly useful. All new non-nullable columns are first added as Nullable. They are converted to non-nullable at the end of upgrade process. Right before that all nulls are set to default value for the column data type. This works for simple columns, but would fail for newly added foreign keys. So if you add a non-nullable foreign key column, you can execute a script with 'timing=Middle' that sets all values in existing rows to some initial value.
The second method AddPostUpgradeAction allows you to add a code-based delegate using an entity session provided as a parameter. This action is executed after the DB model is updated, so normal data operations with entity session can be executed.
The migrations parameter (DbMigrationSet class) provides some extra properties and methods that might be usefull. For SQL scripts, you do not need to hard-code the table name, you can call .GetFullTableName<TEntity>() method - it would return full db name (properly quoted schema.table) for a given entity type.
var tableName = migrations.GetFullTableName<IChildEntityRenamed>();
var sql = string.Format(
  @"UPDATE {0} SET ""OtherParent_Id"" = ""Parent_Id"" WHERE ""OtherParent_Id"" IS NULL;", tableName); 
migrations.AddSql("", "InitOtherParentColumn", 
   "Initialize values of IChildEntityRenamed.OtherParent column.", 
   timing: DbMigrationTiming.Middlle);
There is also ServerType property - you can alter script based on the server type. DbVersion property returns the 'old' version of the module, as it was saved in the database, so you know exactly from which version you are upgrading.
Look at TestSchemaUpdate test method (in Basic unit test project) for an example of migrations definitions. Also Login module defines a migration action for version 1.1 of the module - it renames existing text templates for login-related emails to a new naming convention.

Using vdbtool to generate DDL scripts

The vdbtool command line utility is available in source distribution on codeplex site. It allows you to generate the DDL SQL scripts by comparing the current state of your entity app (in .NET assembly) and current (older) version of the database. The generated DDL scripts can be used during deployment to upgrade the production databases.
Before you can run the utility, you must prepare the settings XML file that contains all the parameters it will use. Start with the dbupdate.mssql.books.cfg file in ConfigSamples folder in Vita.Tools.VdbTool project sources - copy and rename it.
Here is a sample file:

<?xml version="1.0" encoding="utf-8" ?>
  <ConnectionString>Data Source=.;Initial Catalog=MyAppDb;Integrated Security=True</ConnectionString>
  <!--Comma-delimited list of DbOptions enum values -->
  <!--Comma-delimited list of DbModelUpdateOptions enum values -->

You need to provide driver/provider type. It can be any of the servers supported by Vita: MsSql,SqlCe,MySql,Postgres,SQLite. Next you provide the connection string to the database - this is a production database which needs to be upgraded. Do not worry - the utility never tries to modify the database, it only reads objects definitions (tables, indexes, etc) from it.
Next you need to specify where your compiled entity app resides. You provide a full path to the assembly and the name of the entity app class.
IMPORTANT: The entity app class must have a parameterless constructor. It might be made internal so it is not available to regular code.
Finally, 2 options values. These values are needed because your entity app object does not 'contain' any db-specific information that you specify in DbSettings instance in app setup code, when you are connecting to database using app.ConnectTo(dbSettings); method call. So we need to provide this missing pieces here: DbOptions value specifying things like whether you use or not referential integrity; DbModelUpdate options that specify what kind of objects to update - tables, indexes and/or stored procedures. Both values are comma-delimited lists of corresponding enumeration values.
Once you finished editing the configuration file, run the vdbtool.exe from command line like the following:

  C:>vdbtool.exe dbupdate /cfg:myfile.cfg

You can run it directly from Visual Studio, just set command line arguments in project property pages (everything starting with 'dbupdate...'). The utility will report the results in the console (# of scripts generated), or will show an error message if it fails.
In some cases you'll need to tweak the generated script. A common case is initialization of data in newly created columns. One helpful thing VITA does is the way it adds new non-nullable columns: it does this in 3 steps. First it adds columns as nullable, then it runs an update script to set the column to some default (zero, empty) value. Finally, it changes the column definition to be non-nullable. So you have a chance to inject some SQL in the middle, after the column is created as Nullable, to initialize the values.
Here is an example, suppose we are adding non-nullable column Price to Book entity:

ALTER TABLE [books].[Book] ADD  "Price" decimal(18,4)   NULL;
UPDATE [books].[Book] SET "Price" = 0 WHERE "Price" IS NULL;
ALTER TABLE [books].[Book] ALTER COLUMN  "Price" decimal(18,4)   NOT NULL;

After the update script is generated, you can modify the UPDATE statement and assign some sensible values, based on some other data in the database.
Note if you try to add a non-nullable foreign key and the table is not empty, the unmodified script would fail. The UPDATE statement in the middle sets to 0 (Guid.Empty) all values in the column, and then the attempt to enforce the referential constraint fails. So you have to provide some custom code to set the key values properly.

Last edited Feb 23, 2016 at 11:53 PM by rivantsov, version 11


No comments yet.