SQLITE query on upgrade database

Neeraj Sharma

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.

Database schema changes
The app might be crash on database version update
If old data is important to you, Data should persist after a version update and etc.

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)

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.

@override
public void onUpgrade (SQLiteDatabase db, 
                int oldVersion, 
                int newVersion){
db.execSQL("CREATE TABLE mytableclone(id INTEGER PRIMARY KEY, firstname TEXT, last_name TEXT, phone TEXT)");
db.execSQL("INSERT INTO mytableclone(id,first_name,last_name,phone) SELECT id, name,'default', phone from mytable");
db.execSQL("DROP TABLE mytable")
db.execSQL("ALTER TABLE mytableclone RENAME mytable");
}

@override

public void onUpgrade (SQLiteDatabase db,

int oldVersion,

int newVersion){

db.execSQL("CREATE TABLE mytableclone(id INTEGER PRIMARY KEY, firstname TEXT, last_name TEXT, phone TEXT)");

db.execSQL("INSERT INTO mytableclone(id,first_name,last_name,phone) SELECT id, name,'default', phone from mytable");

db.execSQL("DROP TABLE mytable")

db.execSQL("ALTER TABLE mytableclone RENAME mytable");

}

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)

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/