Thursday, April 2, 2015

SQLITE Database Migration

Today one of my colleague shared a problem with me and he asked for the solution if I have any. I thought to share that here.

He has an iOS application which is already distributed to users. He now need to release new version of the app. The app have local database. He used SQLite. In the new version of the app he have updated the database schema. He don't want the users to loose their data when update the application but want the database to get updated.

What to do?

I know such situation can be achieved using database migration. I also know that it's bit easy to use migration in Core Data (I am not covering Core Data migration today). So I decided to write this post. It will help a lot of people to migrate the database while upgrading the application.


Here is the obvious question why we need database migration. Why can't we just replace the old database with new one?
The answer is you don't want your users to update the application to a new version and loose all his/her data. That's why database migration is necessary.

How to do this?

We need to set a version number to the database we are creating. So next time when the app start, we can compare the version with our new version. While creating the database we can set the version of database using below query:

"PRAGMA user_version = 1"

The default version will be Zero (0), so in case we are trying to migrate to a database, which is already distributed, then the version of that database will be 0. Next time when you run the app you can compare the database version. You must have your current database version, so you need to compare it with the version of the database, which is already in use with the app. For that you can use following query:

"PRAGMA user_version"

This will give you the version of your database. Now if your database version is equals to your current version you don’t need to migrate the database but if this doesn’t match you need to migrate the database.

The Possible changes in database:

 New table is added to database

 Table is Renamed

 New Column is added to table

 Column’s datatype is updated

 Column is deleted

New table is added to database

      Here we need to execute Create Table query:

      “CREAT TABLE IF NOT EXISTS TABLE_NAME (column column-def)”

Table is Renamed: 

  Here we need to execute ALTER Table query here.


New Column is added to table: 

Here the ALTER Table query will add new column in the table. Using this all existing data will not be affected and new column will be added with the default value.


Column’s data type is updated:

In case of same table with same column name but the data type is updated e.g.. column1 is updated from TEXT to INTEGER, we need to create a new table and drop existing one. Here is the sequence.

1. Create new table OLD_TABLE_TEMP with same column name but different datatype as required.

2. Copy data from OLD_TABLE to OLD_TABLE_TEMP using below query:

3. Drop OLD_TABLE using below query: “DROP TABLE OLD_TABLE"

4. Rename OLD_TABLE_TEMP to OLD_TABLE using the ALTER TABLE query.

Column is deleted: 

In case of a table has column(s) deleted then we need to follow the same process as we followed above. The same 4 steps will do the magic.


There may be possibilities that a user is not upgrade to the current version. e.g.. We released the app with DB version 1. Then we released the version 2. There may be some users who are not upgrade from version 1 to 2 and directly upgrade to version 3.


Now when we release version 3 may be those users who are still on version 1 tries to upgrade directly to version 3. In such cases we need to have all implementation of upgrading from 1 to 2 and then 2 to 3. So the database will be migrated sequentially from the existing db version to current version.

Post your comments if you need any help.

No comments:

Post a Comment