SQLITE query on upgrade database

Updated 28 November 2020


We are going to learn how to upgrade our Android SQLite table and handle un-synced data without losing anything. Whenever any column related changes in our table we need to upgrade our table. There are cases we need to handle while we update our database version.

These are the common cases we face when we update the Android SQLite database version.

In this blog, we will learn how can we handle these cases. No matter which type you have implemented your database like Room or SQLite because room also working on SQLite table queries. We write some database queries which can be used in a Room or SQLite database.

What is the SQLite database version? and why we need to update?

Whenever you create your database by Room or SQLite in your Android app then you have to set a database version which is used to handle database upgrade. When you change any column in your database for example you want to add a new column in your database after 1st release of your app. then you have to update your database version.

Database upgrade callback you receive on onUpgrade in SQLiteOpenHelper and Migration function in your Room.

Let’s do it practically by take some examples. suppose we have created a table mytable (table name) and the data schema is like below.

mytable (database version 1)

id name phone
1 Webkul 00000000
2 Software 123456789
3 Ecommerce 123432122
4 Mobikul Webkul 123432122


Suppose we have to change the “name” column and create “first_name” and “last_name” two new columns and old “name” column data should be moved into the “first_name” column.

So, in this case, we need to update our database version and increment the database version.

What happened if we change the table schema and update our app upon the old app without increment SQLite database version?

Your app will crash on opening with some errors like a new column(created now) not exists in the xyz table etc. That’s why we have to update the database version.

Handle the above cases we will increment our database version now database version 2 and write down some queries in onUpgrade or Migration function to persist old data and update new table schema changes.

onUpgrade() function will call after database version incremented with oldVersion 1 and new version 2, Here we have created a clone table with the new schema, and fetch data from mytable and save into new clone table. for the “last_name” column here we set the default value as default. You can ignore it for blank or set anything which you want to set the default value of the last_name column.

The new database schema will look like it.

mytable (database version 2)

id first_name last_name phone
1 Webkul deault 00000000
2 Software deault 123456789
3 Ecommerce deault 123432122
4 Mobikul Webkul deault 123432122


If you have any query comment below.

Thanks for Reading

Helpful blogs. find out more- https://mobikul.com/blog/

. . .

Leave a Comment

Your email address will not be published. Required fields are marked*

Be the first to comment.

Start a Project

    Message Sent!

    If you have more details or questions, you can reply to the received confirmation email.

    Back to Home