Updating new version to app store with different sqlite db structure

Question

I have uploaded an app on app store (version 1.0). My app is using a sqlite database for storing some data. Now, I have made some changes in the database (I have added 2 or 3 new columns in one of the tables in my db). I want to update the previous version of my app with the new version 1.1 (which is having different db structure). Now, when the users which are already using the version 1.0 upgrade the app to version 1.1, the db is already present in app sandbox and hence, the app is pointing to version 1.1, but my db is still the old one. I want to have the new db with the old data, if any. Please help me out. Thanks.

Answer 1

sqlite supports something called as user_version property, you can execute PRAGMA user_version to query for current schema version of your app db. This query can happen write at the beginning when your app starts.

to update this user_version execute following update query PRAGMA user_version = version_num;

Whenever you create sqlite db, it is best practice to put this property user_version, so that when you are upgrading in future you can query current value. Check what it's needs to be and execute remaining alter or create tables to upgrade your schema version.

For example:

In first release I create table1 with col1, col2

I execute sql to create table1 and once it is successfully done, i execute pragma user_version = 1. so this will indicate my current schema version is 1

In future release i add new col3, i need to change my schema version to 2

I first query user_version, check it's value and if it is 1, then you need to run alter script to add new column and set user version to 2.

In your case, since you haven't set the user_version before, it would be difficult to differentiate new install vs an upgrade scenario. So for now may be you assume if db is present it is upgrade scenario and execute alter scripts and if not present assume it is a new install scenario and run create scripts. But see if you can use above pragma to solve your problem in future atleast.