Do I need to create a new SQLite database every time an application is updated?

Question

I have a Xamarin Forms application I would like to develop. It will have a SQLite database and I wish to make this available on iOS and Android. The database will be populated with data from a SQL Server database on the cloud with initial seed data. I'm thinking this will be about 500 rows of data with each row about 1Kb.

What I don't understand is when and how to populate this. Should I try to put the data into a CSV file and have this populate the database when the application is installed, or when it first starts? What's the normal way to populate seed data other than lines inside of the code with a huge number of insert statements.

Any help or advice on how this is normally done (I'm thinking most people do it the same way) would be much appreciated.

Thanks

Answer 1

You don't have to create your sqlite database every time the app is updated. Actually SQLiteOpenHelper provides the following two methods:

1. OnCreate() : you should implement this method and create your sqlite database with populated data from the server. It is called when you the app is started for the first time.
2. OnUpgrade(): you should implement this method if you want to modify the database (add a new table or column in a table) or populate additional data.

The database is preserved between app updates and you don't need to create it each time.

Check the following examples which explain how to use sqlite database with Xamarin:

Using Sqlite in a Xamarin.Android Application Developed using Visual Studio

and

An Introduction to Xamarin.Forms and SQLite

Answer 2

Lets break the problem down.

Is the initial data that you wish to use in your app going to change over time?

If you include any pre-populated data (a SQLite, Realm, or CSV-based file, ...) and the data that you are including goes stale and you have to update it on a routine basis, you will need to publish an application update (.apk/.ipa) so your new user installs receive the updated data (more on this below).

Note: This assumes that your current users get the updated data via actually running your app and it is handling the local data updates on routine basis (background service, push notifications, data polling, etc..)

Is this a Line of Business (LoB) application published via Ad-Hoc, private Store, and/or iOS Enterprise publishing?

If you control the user base, than having to force an update install so your users get your new/updated pre-populated data might be an acceptable approach, but not a great user experience if they forced to update the application all the time... but it works...

Is this application going to be distributed via the public Apple and Google App Stores?

This is where you need to be very careful on what pre-populated data you include within your application.

If the data goes stale and you need to push an updated app version to the Stores for your new install installs, beware that it could be days (or weeks or even month+) to get that new app into the store.

The Play Store usually is less then 24 hours on publishing app updates, and while the Apple Store can be the same, do not bet on it.

We routinely see 48-72 hour delays and randomly get rejected and thus it can take a week or more to get an update app into the Apple Store. We have had rejections delaying an app update for over a month and have gone into the appeal process and even removed already existing features to get re-published

Note: Every app update to the Apple store resets your user reviews... :-(

Bottom line: You want to want to publish to the Stores when you are bug fixing and/or adding features, not to update some "static" data that is stored within your app bundle...

What does this data cost your end-user and you?

Negative costs to you as an app developer are bad reviews and uninstalls. Look at how this "data" effects the end-users access to your application and how they react. Longer download time, usually acceptable. Longer initial app startup times, less acceptable... etc....

What markets will your app be used in? Network speeds and the cost of data transfer in many markets across the world are slow and costly...

What really is the true size of the data?

I "pre-populate" a Realm data instance with thousand of rows with 5MB of JSON data in under a second. SQLite takes longer, but it is still not bad. The data itself is stored in a zip and accessed as a static file (https-based get) and at a 80% compression factor, the 1MB of compressed data is pulled from a server (AWS S3) in under one second using LTE cellular data speeds and uncompressing it as stream while deserializing the JSON on-fly to update the Realm instance adds another second...

So, the user impact is very small and I "hide" this initial pre-populate update via a first-time welcome screen and some text that the user hopefully reads before getting to the first "real" app screen...

Note: This does assume that the user will have network data access the first time they open the app... In many markets around the world, this is not true, so factor this into your app design.

I also architect the app so its data can be update on background threads during its launch (the initial one or not) and thus the user does not stand there watching a spinning busy indictor, they can at least interact with the data that they do have.

So should you include any pre-populated data in your app bundle?

Sure, when that data is absolutely required to get the user up and running as fast as possible to enhance the user experience. Games are a great example of this in bundling 100s of megabytes or even gigabytes via .obb... with the various levels, media files, etc... into the app so the user does not experience a 10+ min. wait time upon opening the app the first time.

Now this does mean that their initial download time for the install was longer as that data was bundled within the app, the overall user experience was better as users accept the download/install times and view that as a carrier/phone/service plan issue vs. the time to open your app the first time to actually get to a functional screen.

So what do?

Personally I look at this issue on a case by case basis. I look at the data and if it is not going to change and only get added to and possibly pruned over time, include it as a pre-populated SQLite or Realm store or... Why cause the user to wait for the web requests, database updates and the additional network data usage and associated costs. If the data is going to go stale, do not bundle it in your app.

As for the mechanics of installing pre-populated data:

See my answer on this SO Question about "Bundle prebuilt Realm files"