Redeem Now
Read More
Read now

SQLITE query on upgrade database


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-

. . .


Add Your Comment

Be the first to comment.

Hire Us!
Brief us about your requirements and we'll get back to you.
Woo! Hooy!
We have just recieved your project brief and our expert will contact you shortly.
Send Again