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
BaseColumnsinterface, your inner class can inherit a primary key field called
_IDthat some Android classes such as
CursorAdapterexpect 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
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:
- opens an input stream for the database file in the assets (note the usage of the
- 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
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:
We have created two methods to check or update the database version:
installedDatabaseIsOutdatedcompares the version stored in the shared preferences and the version written in the companion object.
writeDatabaseVersionInPreferencessaves 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:
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.
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: