Database Migration

27 Nov 2013

In this article we review the main problems that can occur when making modifications to the structure of database. Also, we’ve tried to identify general approaches to database migration process so the structure of database can be stored and maintained in the development process.

What is database migration?

Database migration is a process of updating one database structure to another.

One of the most challenging tasks in development process or in support of finished products is product database. As database is an integral part of your software program, it should be thoroughly elaborated, so during maintenance process there will be no critical issues. Project requirements may be changed right after the first release, so you may need to change the structure of the database or add new content to it.

Challenges

While upgrading to new application version, changing the database scheme can lead to user’s data loss,. This is good example of bad user experience. To avoid such situations, we use “version migration” in our software products.

Let’s consider live example. In the first version of the application developer (during his work on the database), creates a table called “Person”, with the following structure:

Database version 1.0
id (integer) - unique key
firstname (string)
lastname (string)
email (string)
phone_number (integer)
company (string)
note (string)

But a month later, when second version of the application is released, two new columns where added to Person table:

Database version 2.0
+ occupation (string)
+ birth_date (integer)

And after a while it appears, that in the next application release “Person” data should have an additional email and phone number fields. Also column “Note” is no longer required, so  we need to remove it.

Database version 3.0
+ email_2
+ phone_number_2
- note

Now lets think about the following situation. User has the first version of an application. After some time two new versions of application are released, but user missed second update, and he is going straightly from first version to the third. And if developer didn’t predict such situation, this may lead to data loss, and in worth case your queries will fail during execution which causes crash.

This way, we can make a conclusion: migration must be performed sequentially from version to version, in correct sequence, because one version can affect the others. And all database queries must be performed once.

General principles of version migration:

  • Single execution of each shift (SQL-query).
  • Strict order set for all changes.
  • Any version of database should be upgradable to any (usually, the most recent) version.
  • A set of SQL-queries that implement migration between any two versions should be available to retrieve quickly and easily.
  • You can always create a database from scratch with the structure of the latest version.

Our Approach

Now we can move on to practical skills. First, let's take a look at the UML diagram. It describes the main classes and interfaces that we use in our products when working with database.

SQLiteOpenHelper has several helper-methods:

  • OnCreate - called when new database is created. This method execute database creation sql statement
  • onUpgrade - called when database must be upgraded. This is where database migration occurs.

First, we need to create a class that extends SQLiteOpenHelper.

public class DatabaseHelper extends SQLiteOpenHelper {
 private static final String DATABASE_NAME = "applicationdata";
 private static final int DATABASE_VERSION = 1;
DatabaseHelper(Context theContext) {
     super(theContext, DATABASE_NAME , null, DATABASE_VERSION);
    }
 …
}

Then, create an interface called MigrationTask:

public interface MigrationTask {
   public void onUpgrade(SQLiteDatabase theDb);
   public void onDowngrade (SQLiteDatabase theDb);
}

Next, we need to create a class MigrationTask1To2, which will modify the table Person, namely add two new columns to it: occupation and birth_date.

public class MigrationTask1To2 implements MigrationTask {
…
   @Override
   public void onUpgrade(SQLiteDatabase theDb) {
        String query =
“ALTER TABLE Person ADD occupation TEXT, ADD birth_date TEXT”;
    theDb.execSQL(query);
   }
…
      }

Once we have created a migration task we need to add it to the list of migrations that can be retrieved in the method getMigrationTasks of DatabaseHelper class. Migration is added to the Map, in which key is the version of the database.

@Override
public MigrationTask  getMigrationTasks(int theCurrentDatabaseVersion) {
switch (theCurrentDatabaseVersion) {    
             case 1: // The first version of the database
return new MigrationTask1To2();                    
        }
     };

Now let's get back to onUpgrade method which is inside DatabaseHelper class and see what happens here.

@Override
public void onUpgrade(SQLiteDatabase theDb, int oldVersion, int newVersion)    {
    for(int currentVersion = oldVersion + 1;
currentVersion <= newVersion; currentVersion++){
// We pull the migration TASK out of the Map. It includes a
// request to update the table.
        MigrationTask task = getMigrationTasks(currentVersion);
// Updates the “Person” table structure
        task.onUpgrade(theDb);
    }
}

When method onUpgrade finishes its work, our “Person” table will be modified. Currently we implemented database migration from version 1 to version 2. Let’s move on.

Create a new migration task and call it MigrationTask2To3. It will handle database migration from version 2 to version 3, in which we need to remove “Note” field and add two new fields email_2 and  phone_number_2.

public class MigrationTask2To3 implements MigrationTask {
…
   @Override
   public void onUpgrade(SQLiteDatabase theDb) {
        String query = “ALTER TABLE Person ADD email_2 TEXT,
ADD phone_number_2 TEXT, DROP COLUMN* note”;
    theDb.execSQL(query);
   }
…
      }

Note: SQLite does not support altering table with drop column command, to make it work you should check official FAQ here.

Next step is to modify the getMigrationTasks method which should create and return correct migration task. The onUpgrade method stays unchanged.

 @Override
    public MigrationTask getMigrationTasks(int theCurrentDatabaseVersion)  {
        switch (theCurrentDatabaseVersion) {
case 2: //The second version of the database
         return  new MigrationTask2To3();        
         case 1: //The first version of the database
return new MigrationTask1To2();                    
        }        
           return map;
      }

Further Changes

Whenever your database structure is changed, you need to follow two simple steps to make database migration works correctly:

  1. Add a new migration task
  2. Update getMigrationTasks method

Don’t forget about the main rule of database migration:

“Any previous version of database should be upgradable to any new version"