Icons by Font Awesome

Ship an Android app with a pre-populated database

Johann Pardanaud
Jun 26, 2018 · 5 min read

I recently had to create an Android app with a pre-populated database containing containing more than 8,000 rows. Unfortunately, Android doesn't provide any API to do this easily. After searching for a few minutes, I found a blog post explaining how to install a SQLite database from your assets to your app directory (by Juan-Manuel Fluxà) , which helped me to create my own database helper.

However, this article is a bit outdated (it was written in 2009), the code could benefit from some optimizations, it doesn't handle database updates and it is written in Java (while I'm in love with Kotlin ❤️).

Choosing between Room or pure SQLite

Android highly suggests to use their Room library, an abstraction tool running over SQLite. It's a great API, however, it manages your data with an obscure structure. Shipping a compatible database is really complicated (and honestly, I did not manage to do it), and the Android team has been asked to provide a solution. Because of this complexity, we will stick to a basic SQLite database.

If using Room is a requirement for you and you don't have that much data, you can try what is explained in this article (by Gonzalo Martin). But you should probably avoid this if you have hundreds of rows to store.

Building your database file

Databases in Android are using the SQLite3 file format, the extension changes nothing.

Supporting the right version

Be careful to use features supported by old SQLite versions, because Android runs different ones depending on the API level. You can check which versions are available on the android.database.sqlite package reference.

For example, if you want to support Android API 21+, you shouldn't use SQLite features added after the 3.8 version.

Metadata is optional

While reading or writing your database, Android will use the android_metadata table to know the locale. It will be automatically added if it doesn't already exist, however you can add this table by yourself if you want to set a specific locale for your content:

Cursor adapters need a special field

If you want to support cursor adapters, you must add a primary key field named _ID to your tables. Here's a quote from the documentation:

Note: By implementing the BaseColumns interface, your inner class can inherit a primary key field called _ID that some Android classes such as CursorAdapter expect it to have. It's not required, but this can help your database work harmoniously with the Android framework.

Add the database to your project

To ship your database with your app, you will have to add it to your assets. If you don't already have one, create the assets directory: right click on your project in the sidebar, then click on New > Folder > Assets Folder.

Now right click on the new assets directory, then click on New > Directory, and name it “databases”.

Once the directory is created, copy your database file in it and name it “mydb.sqlite3” (feel free to change this).

Writing the database helper

Now that your database is created and stored in the assets, we can write the logic to copy it from the assets directory to the final directory where databases are stored to be used by your app.

The basic structure of our helper

First, create a simple database helper just like explained in Android's documentation:

Since the database structure is already defined by the SQLite file stored in the assets, there is no need to define any logic in the onCreate and onUpgrade methods, but we must override them because they are defined as abstract.

Installing the database from the assets

Now we need to copy our database from the assets to the final directory, here's how:

The installDatabaseFromAssets method:

  • opens an input stream for the database file in the assets (note the usage of the ASSETS_PATH constant);
  • opens an output stream to a file where the active databases of your app are located;
  • copies the data form the input stream to the output one;
  • closes the streams.

Note: For readability, I'm commenting out the sections we have already treated and will provide the full code at the end of the article.

Checking the database status

Before installing/updating our database, we must check if it is already installed and up to date. This can be done through SharedPreferences:

On initialization, we create/open a new shared preference file to store the installed database version. We use the package name to generate a unique name, which is recommended by Android's documentation:

When naming your shared preference files, you should use a name that’s uniquely identifiable to your app. An easy way to do this is prefix the file name with your application ID. For example: "com.example.myapp.PREFERENCE_FILE_KEY"

We have created two methods to check or update the database version:

  • installedDatabaseIsOutdated compares the version stored in the shared preferences and the version written in the companion object.
  • writeDatabaseVersionInPreferences saves the version written in the companion object to the shared preferences.

Installing the database automatically

You may have noticed that all our custom methods are private. This is because we don't wan't to mess with them when instanciating our database helper, we just want them to be executed when necessary.

The perfect moment to run our install/update workflow is when you try to read your database. If you worry about performance, remember this from Android's documentation:

Because they can be long-running, be sure that you call getWritableDatabase() or getReadableDatabase() in a background thread, such as with AsyncTask or IntentService.

Since you will have to use these two methods in a background thread anyway, adding a bit of workload to the getReadableDatabase method shouldn't be an issue:

To install/update our database on read, we must override getReadableDatabase method to run installOrUpdateIfNecessary before returning the database. If our database is outdated (or not installed) we:

  • remove the current database with the same name;
  • install the latest database version;
  • write the new version in the shared preferences.

Note: The installOrUpdateIfNecessary method has the Synchronized annotation to avoid potential issues if you call getReadableDatabase simultaneously in two different threads.

And what about the getWritableDatabase method? Well, if you're planning to update your database sometimes like me, you cannot allow the database to be writable because it would be extremely complicated to merge user modifications to your own database.

My suggestion here is to store the data generated by the user in a new database created programmatically on the device. Actually, it's great because you will be able to use Room for this database, since you're not importing it from the assets.

If you really need to write data in your imported database, use the installOrUpdateIfNecessary method in getWritableDatabase, just like in getReadableDatabase. But remember to never increase the database version in the future or the user modifications will be overriden.

Getting everything together

Finally, here's the whole code in one piece with a (very) simple use case:

Johann Pardanaud

Written by

Web developer working at batch.com - Passionate climber

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch
Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore
Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade