SQLite Database Versioning in Android

Here is a sample application which demonstrates creating of a database in android and updating the table in later release of your application.

Consider an Employee table as given below:

public class EmployeeTable {
    public static final String TABLE_NAME = "employee";

    public static final String COLUMN_ID = "_id";
    public static final String COLUMN_NAME = "emp_name";
    public static final String COLUMN_ADDRESS = "address";
    public static final String COLUMN_CONTACT_NO = "contact_no";
    public static final String COLUMN_EMAIL_ID = "email_id";

    public static final String CREATE_TABLE = "create table " + TABLE_NAME
            + "("
            + COLUMN_ID + " integer primary key autoincrement"
            + "," + COLUMN_NAME + " text"
            + "," + COLUMN_ADDRESS + " text"
            + "," + COLUMN_CONTACT_NO + " text"
            + "," + COLUMN_EMAIL_ID + " text"
            + ")";  
}

Consider DatabaseHelper class as given below. This maintains the database name, database version and creates and updates the database.

public class DatabaseHelper extends SQLiteOpenHelper {
    public static final String DB_NAME = "sample_db.db";
    public static final int DB_VERSION = 1;

    public DatabaseHelper(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) {
        super(context, name, factory, version);
    }

    @Override
    public void onCreate(SQLiteDatabase sqLiteDatabase) {
        sqLiteDatabase.execSQL(EmployeeTable.CREATE_TABLE);
    }

    @Override
    public void onUpgrade(SQLiteDatabase sqLiteDatabase, int oldVersion, int newVersion) {
        }
    }
}

The below code can be used to insert the row into Employee table. The inserted row id is logged.

DatabaseHelper databaseHelper = new DatabaseHelper(this, DatabaseHelper.DB_NAME, null, DatabaseHelper.DB_VERSION);
        SQLiteDatabase sqLiteDatabase = databaseHelper.getWritableDatabase();
        if(sqLiteDatabase != null){
            ContentValues contentValues = new ContentValues();
            contentValues.put(EmployeeTable.COLUMN_NAME, "Abc");
            contentValues.put(EmployeeTable.COLUMN_ADDRESS, "Abc xyz");
            contentValues.put(EmployeeTable.COLUMN_CONTACT_NO, "1234567890");
            contentValues.put(EmployeeTable.COLUMN_EMAIL_ID, "abc@xyz.com");
         
            long id = sqLiteDatabase.insert(EmployeeTable.TABLE_NAME, null, contentValues);

            Log.v(TAG, "Row inserted id - " + id);
        }



Now, suppose the application is in released state i.e. users are using your app and you need to add 1 more column into Employee table, say Designation.
You will have to add column definition in your Create Table query and this will create your table with 1 more column but the application will crash on the devices where the users are already using the app and updated with new release if you have used this newly added column for any of the database operation i.e. CRUD.
To avoid this mess, you need to update onUpgrade() method in DatabaseHelper for proper update of your app on users' device which are already using your app.
Also, you need to update your Database Version and you must keep track of your database version to identify which changes are done in which version.

So, your updated Employee table with additional column will look like:

public class EmployeeTable {
    public static final String TABLE_NAME = "employee";

    public static final String COLUMN_ID = "_id";
    public static final String COLUMN_NAME = "emp_name";
    public static final String COLUMN_ADDRESS = "address";
    public static final String COLUMN_CONTACT_NO = "contact_no";
    public static final String COLUMN_EMAIL_ID = "email_id";
    public static final String COLUMN_DESIGNATION = "designation"; //added in db ver. 2

    public static final String CREATE_TABLE = "create table " + TABLE_NAME
            + "("
            + COLUMN_ID + " integer primary key autoincrement"
            + "," + COLUMN_NAME + " text"
            + "," + COLUMN_ADDRESS + " text"
            + "," + COLUMN_CONTACT_NO + " text"
            + "," + COLUMN_EMAIL_ID + " text"
            + "," + COLUMN_DESIGNATION + " text" //added in db ver. 2
            + ")";


    public static final String ALTER_TABLE_ADD_COLUMN_DESIGNATION = "Alter table " + TABLE_NAME + " add column " + COLUMN_DESIGNATION + " text";
}

Your DatabaseHelper class will look like:

public class DatabaseHelper extends SQLiteOpenHelper {
    public static final String DB_NAME = "sample_db.db";
//    public static final int DB_VERSION = 1;
    public static final int DB_VERSION = 2; //column designation added in employee table

    public DatabaseHelper(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) {
        super(context, name, factory, version);
    }

    @Override
    public void onCreate(SQLiteDatabase sqLiteDatabase) {
        sqLiteDatabase.execSQL(EmployeeTable.CREATE_TABLE);
    }

    @Override
    public void onUpgrade(SQLiteDatabase sqLiteDatabase, int oldVersion, int newVersion) {
        switch (oldVersion){
            case 1:            sqLiteDatabase.execSQL(EmployeeTable.ALTER_TABLE_ADD_COLUMN_DESIGNATION);  
        }
    }
}

and now the updated code snippet to insert Designation value:

DatabaseHelper databaseHelper = new DatabaseHelper(this, DatabaseHelper.DB_NAME, null, DatabaseHelper.DB_VERSION);
        SQLiteDatabase sqLiteDatabase = databaseHelper.getWritableDatabase();
        if(sqLiteDatabase != null){
            ContentValues contentValues = new ContentValues();
            contentValues.put(EmployeeTable.COLUMN_NAME, "Abc");
            contentValues.put(EmployeeTable.COLUMN_ADDRESS, "Abc xyz");
            contentValues.put(EmployeeTable.COLUMN_CONTACT_NO, "1234567890");
            contentValues.put(EmployeeTable.COLUMN_EMAIL_ID, "abc@xyz.com");
            contentValues.put(EmployeeTable.COLUMN_DESIGNATION, "pqr"); //can be used with db ver. 2 or above
         
            long id = sqLiteDatabase.insert(EmployeeTable.TABLE_NAME, null, contentValues);

            Log.v(TAG, "Row inserted id - " + id);
        }


Now, suppose in the next release, one more column is needed, say Department and you need to update the database again.
During this update of your app, you need to consider the users who haven't yet updated the build having database version 2 i.e. those are using first release of your app alongwith those who have updated the app i.e. those are using your app having database version 2 and also those users who will install your app for the first time.

So, your updated Employee Table with additional Department column will look like:

public class EmployeeTable {
    public static final String TABLE_NAME = "employee";

    public static final String COLUMN_ID = "_id";
    public static final String COLUMN_NAME = "emp_name";
    public static final String COLUMN_ADDRESS = "address";
    public static final String COLUMN_CONTACT_NO = "contact_no";
    public static final String COLUMN_EMAIL_ID = "email_id";
    public static final String COLUMN_DESIGNATION = "designation"; //added in db ver. 2
    public static final String COLUMN_DEPARTMENT = "department"; //added in db ver. 3

    public static final String CREATE_TABLE = "create table " + TABLE_NAME
            + "("
            + COLUMN_ID + " integer primary key autoincrement"
            + "," + COLUMN_NAME + " text"
            + "," + COLUMN_ADDRESS + " text"
            + "," + COLUMN_CONTACT_NO + " text"
            + "," + COLUMN_EMAIL_ID + " text"
            + "," + COLUMN_DESIGNATION + " text" //added in db ver. 2
            + "," + COLUMN_DEPARTMENT + " text" //added in db ver. 3
            + ")";


    public static final String ALTER_TABLE_ADD_COLUMN_DESIGNATION = "Alter table " + TABLE_NAME + " add column " + COLUMN_DESIGNATION + " text";
    public static final String ALTER_TABLE_ADD_COLUMN_DEPARTMENT = "Alter table " + TABLE_NAME + " add column " + COLUMN_DEPARTMENT + " text";
}

Now, your updated DatabaseHelper class will look like:

public class DatabaseHelper extends SQLiteOpenHelper {
    public static final String DB_NAME = "sample_db.db";
//    public static final int DB_VERSION = 1;
//    public static final int DB_VERSION = 2; //column designation added in employee table
    public static final int DB_VERSION = 3; //column department added in employee table

    public DatabaseHelper(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) {
        super(context, name, factory, version);
    }

    @Override
    public void onCreate(SQLiteDatabase sqLiteDatabase) {
        sqLiteDatabase.execSQL(EmployeeTable.CREATE_TABLE);
    }

    @Override
    public void onUpgrade(SQLiteDatabase sqLiteDatabase, int oldVersion, int newVersion) {
        //break statement not required
        switch (oldVersion){
            case 1:                sqLiteDatabase.execSQL(EmployeeTable.ALTER_TABLE_ADD_COLUMN_DESIGNATION);

            case 2:              sqLiteDatabase.execSQL(EmployeeTable.ALTER_TABLE_ADD_COLUMN_DEPARTMENT);
        }
    }
}

No break statement is required in onUpgrade() as if any user using your app released with database version 1 and haven't updated the app to database version 2 which is having designation column then that update is also done when updating to third release of your app with database version 3.
And if user has already updated the app having database version 2 then as its old version will be 2, it will directly fall to case 2, so no duplicate column issue and the database version 3 changes will get reflected.
If user installs your app for the first time and that too with database version 3 then call to onCreate() will happen and it will create Employee table with all changes made from database version 1 to database version 3 and onUpgrade() will definitely not get called.

The updated code snippet to insert Department value:

DatabaseHelper databaseHelper = new DatabaseHelper(this, DatabaseHelper.DB_NAME, null, DatabaseHelper.DB_VERSION);
        SQLiteDatabase sqLiteDatabase = databaseHelper.getWritableDatabase();
        if(sqLiteDatabase != null){
            ContentValues contentValues = new ContentValues();
            contentValues.put(EmployeeTable.COLUMN_NAME, "Abc");
            contentValues.put(EmployeeTable.COLUMN_ADDRESS, "Abc xyz");
            contentValues.put(EmployeeTable.COLUMN_CONTACT_NO, "1234567890");
            contentValues.put(EmployeeTable.COLUMN_EMAIL_ID, "abc@xyz.com");
            contentValues.put(EmployeeTable.COLUMN_DESIGNATION, "pqr"); //can be used with db ver. 2 or above
            contentValues.put(EmployeeTable.COLUMN_DEPARTMENT, "mno"); //can be used with db ver. 3 or above

            long id = sqLiteDatabase.insert(EmployeeTable.TABLE_NAME, null, contentValues);

            Log.v(TAG, "Row inserted id - " + id);
        }

This is the simple sample of adding columns into existing SQLite database of android application.

Updating datatype of existing column is usually ignored as it may lead to errors if the existing values are not casted properly to new datatype. To achieve this, you need to create new column with the desired datatype, copy all values from the column whose datatype is incorrect into this new column but the values from the old column must be cast properly while inserting into new column.