Handling database changes when app version number changes

Question

I'm trying to figure out the best way to handle database upgrades and versioning.

At the moment I delete the database and log users out when I do a point release, which isn't a great experience.

Can anyone recommend any tips for doing this?

Answer 1

Your database version is independent of your app version. If your database schema doesn't change at all, you shouldn't need to do anything to your database during an update.

When your database schema changes, you should handle database updates in onUpgrade() of your SQLiteOpenHelper. This method is called when you try to access your database and you have updated your database version, as described in the Data Storage Options documentation.

If you are using a third party library to handle your databases, it should either handle the upgrade for you or provide similar functionality.

There is no universal strategy for upgrading your database here. What you do depends completely on what your schema looked like before the upgrade and what the new schema looks like. Depending on what changed, you might create new tables or columns, delete tables or columns, update rows in the database, or move data between tables. If you have a specific question about how to migrate your data, create a new question describing the new and old schemas.

Answer 2

The way we do it is that we run a routine every time the app starts that calls a stored proc on the server to get SQL that upgrades the database if it is necessary. (The sql can be quite involved: dropping tables and recreates them with new structures and inserting new values). We store the version of the database in the database itself and upgrades to the new version.

We don't use the onUpgrade() call.