After reading
this blog post
(and a few others) on how to use the onUpgrade() method for your Android SQLite Database, I thought I should share my experience about how to correctly upgrade your database. It will also be beneficial to highlight why the final solution listed in that blog post would also fail at some point for some scenarios. The post explains quite well the drawbacks of some of the solutions that I also went through, but their final solution can also leave you in trouble.
When using your own SQLite database in Android, most people take for granted future releases of the application they are working on. Looking at the Android Developer documentation can also lead you down a rickety path.
Upgrading databases in Android is difficult. I’ve been there and battled the fires in production. It is not pretty.
These are the solutions that we went through:
Solution 1: Delete the tables that have changed and recreate them
Sure, when we started development this was obviously the easiest approach. We weren’t storing user data; our database was just a cache of things from the network. When the user upgraded, the “cache” would disappear and they would have to download all the data again.
It is also the solution that the
Android Developer Documentation
states. It’s worth noting that this solution
will not work
well for changing of table names.
You might have the following in your upgrade section of your app:
db.execSQL(drop + Books.TABLE_BOOKS);
db.execSQL(Books.CREATE_BOOKS);
This looks okay right? This upgrade could leave dangling tables (and might fail if you use “DROP TABLE”) if you change the name of the table in one upgrade to another and you call the same lines of code later on. It is not a good idea to reference variables in your upgrade scripts.
Solution 2: Drop tables that change and recreate them, with variables defined in code for SQL statements
The following example illustrates the second option that was taken.
In version 1 of the application, we defined a variable named TABLE_NAME and
SQL_CREATE_BOOK_ENTRY_TABLE
public static final String TABLE_NAME = "books";
public static final String COL_BOOKNAME = "book_name";
public static final String COL_NO_PAGES = "book_pages";
public static final String COL_DESCRIPTION = "book_description";
public static final String SQL_CREATE_BOOK_ENTRY_TABLE = "CREATE TABLE " + TABLE_NAME + " (" +
BookEntry._ID + " INTEGER PRIMARY KEY AUTOINCREMENT , " +
BookEntry.COL_BOOKNAME + " TEXT ," +
BookEntry.COL_DESCRIPTION + " TEXT, " +
BookEntry.COL_NO_PAGES + " INTEGER )";
In our onUpgrade, we defined the following:
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
Log.e(TAG, "Updating table from " + oldVersion + " to " + newVersion);
//Added new column to book table - book rating
if (oldVersion < 2){
db.execSQL(DROP + BookEntry.TABLE_NAME);
db.execSQL(BookEntry.SQL_CREATE_BOOK_ENTRY_TABLE);
//Rename table to book_information - this is where things will start failing.
if (oldVersion < 3){
db.execSQL(DROP + BookEntry.TABLE_NAME);
db.execSQL(BookEntry.SQL_CREATE_BOOK_ENTRY_TABLE);
// Add new column for a calculated value. By this time, if I am upgrading from version 2 to
// version 4, my table would already contain the new column I am trying to add below,
// which would result in a SQLException. These situations are sometimes difficult to spot,
// as you basically need to test from every different version of database to upgrade from.
// Some upgrades might work and some might fail with this method.
// It is best to follow the other method that is on the master branch of this repo.
if (oldVersion < 4){
db.execSQL("ALTER TABLE " + BookEntry.TABLE_NAME + " ADD COLUMN calculated_pages_times_rating INTEGER;");
//As you can probably imagine, this is a terrible way to do upgrades, Please DONT DO IT!!!!
There are a few problems with the above code sample. First of all, the change from version 2 to 3 would not drop the table, as we changed the table name to book_information from books. This would leave a dangling table in the users database.
The next issue is that the next upgrade from version 3 to 4, would fail completely if the user is upgrading from version 1 of the app to version 4 as the column calculated_pages_times_rating already would exist due to us using the same create variable defined previously.
As you can see, this method can get messy really quickly. Writing code in this way is not maintainable as it can be difficult to version our variables.
Solution 3: Final Result – Create separate .sql script files
The better solution that we found (somewhere on the internet – can’t find the link now) was to store versioned sql files in the assets/ folder in the application. This ensures that each step of the process of the upgrade is correct. If you need to drop and recreate a table, you don’t use the variables defined in the application as these might change and are not versioned.
Below is a sample of the DatabaseHelper which runs sql scripts that will run the correct upgrades for each version of the database that a user has.
public class DatabaseHelper extends SQLiteOpenHelper {
private static final int DATABASE_VERSION = 4;
private static final String DATABASE_NAME = "database.db";
private static final String TAG = DatabaseHelper.class.getName();
private static DatabaseHelper mInstance = null;
private final Context context;
private DatabaseHelper(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
this.context = context;
public static synchronized DatabaseHelper getInstance(Context ctx) {
if (mInstance == null) {
mInstance = new DatabaseHelper(ctx.getApplicationContext());
return mInstance;
@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL(BookEntry.SQL_CREATE_BOOK_ENTRY_TABLE);
// The rest of your create scripts go here.
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
Log.e(TAG, "Updating table from " + oldVersion + " to " + newVersion);
// You will not need to modify this unless you need to do some android specific things.
// When upgrading the database, all you need to do is add a file to the assets folder and name it:
// from_1_to_2.sql with the version that you are upgrading to as the last version.
try {
for (int i = oldVersion; i < newVersion; ++i) {
String migrationName = String.format("from_%d_to_%d.sql", i, (i + 1));
Log.d(TAG, "Looking for migration file: " + migrationName);
readAndExecuteSQLScript(db, context, migrationName);
} catch (Exception exception) {
Log.e(TAG, "Exception running upgrade script:", exception);
@Override
public void onDowngrade(SQLiteDatabase db, int oldVersion, int newVersion) {
private void readAndExecuteSQLScript(SQLiteDatabase db, Context ctx, String fileName) {
if (TextUtils.isEmpty(fileName)) {
Log.d(TAG, "SQL script file name is empty");
return;
Log.d(TAG, "Script found. Executing...");
AssetManager assetManager = ctx.getAssets();
BufferedReader reader = null;
try {
InputStream is = assetManager.open(fileName);
InputStreamReader isr = new InputStreamReader(is);
reader = new BufferedReader(isr);
executeSQLScript(db, reader);
} catch (IOException e) {
Log.e(TAG, "IOException:", e);
} finally {
if (reader != null) {
try {
reader.close();
} catch (IOException e) {
Log.e(TAG, "IOException:", e);
private void executeSQLScript(SQLiteDatabase db, BufferedReader reader) throws IOException {
String line;
StringBuilder statement = new StringBuilder();
while ((line = reader.readLine()) != null) {
statement.append(line);
statement.append("\n");
if (line.endsWith(";")) {
db.execSQL(statement.toString());
statement = new StringBuilder();
Now in order to make an upgrade to your database you can do the following:
Create a SQL file and name it from_2_to_3.sql according to the version of the database you are upgrading from and to.
In that file you can create all your SQL statements.
Up the DATABASE_VERSION in your code to the new version number.
Don’t forget to change your create script for the changes if a user is installing a fresh version of your app.
When a user opens your app and is upgrading, the upgrade process will run through all the scripts stored in the assets/ folder that are needed in order to upgrade them to the latest version.
The main benefit of doing it this way is that you don’t have upgrade scripts that depend on variables defined in code. It is also a really neat way to see what changed per version of your database.
This way of upgrading your database is the most future proof solution that I have found.
I have uploaded a sample project for this on Github, master branch contains the end solution. Checkout the different releases to see how the upgrade process works and the different databases associated with each release. There is also another branch that contains the broken example called – failing-upgrades-in-code.
Let me know your experiences with upgrading SQLite databases in Android in the comments below!
Upgrading the DB is always a pain the ass. Especially if one wants to keep the user logged in, his data up-to-date, and a slave-master sync with a server is used to populate the local DB. We tried different approaches here:
1) Logout user, delete db and do a complete initial sync again with the server (do not try this, deleting the DB is really hard, or let’s say it’s hard to RECREATE it.)
2) Make your DB changes(add column, remove rows etc.) and then force a sync (preferred, however sometimes it’s not that easy, what if the user is not online etc., then the app might crash when he tries to use it but some entities rely on a proper schema to be in place.)
3) ??
Hi Rebecca,
Great article! I wrote the article you linked to in your intro. I was happy to read that you found it better than several others out there. I can definitely see how your approach could be a step up from mine when working on more complicated database upgrades and for the longer term maintenance of an app. I will definitely share your article with my team. I look forward to trying out this approach on a future app.
Happy coding!
