Pre-populating your Room I

Building your Room

Photo by Shelly Busby on Unsplash

Shipping pre-built databases along with your application (or downloadable module) may have benefits both for user experience and your backend infrastructure: less server queries, less data transfer, faster response. This article describes an easy way to populate your database with data which plays nice with your Room database definition and allows an easy integration with your CI service.

This is a first part of two-part series that introduces the Room-friendly way to pre-package a database. Continue to Part 2 to see the approach in action.

Pre-populating your Room II

Warning! TypeScript and Node.js😨

Although this article describes an Android Room library and is mainly written for Android developers it uses TypeScript and Node.js to populate databases. This approach was chosen because the database in our service gets pre-populated where the original data resides (backend). Anyway, I think the basic code structure will be well-readable even if you are not familiar with Node.js ecosystem while the detailed instructions and references will walk you through :)

Introduction

There are times you have a large amount of frequently used and well structured data which is not updated frequently. Take a list of train stations in a root planning application for example. The station record may have such properties as ID, name and geographical coordinates that are almost constant. Also the rate of opening new train stations in your region may be far slower than your app update cycle :) In that case you may decide to ship the bulk of constant data along with your application or module while leaving only a station ID as a reference in your backend API.

The approach is not anything new but you had to deal with your own implementation or some third-party library to copy your pre-built database to an application data folder.

Things have changed however since Room 2.2.0 was released in October 2019. The new version introduced two new APIs in RoomDatabase.Builder:

• createFromAsset() is for when the pre-populated database file is in the assets folder of the APK and is shipped along. The database open helper will copy the file from assets behind the scenes on first call to database methods.
• createFromFile() is for when the file is in an arbitrary location — say you download (or update it) at the time of first usage.

Sample project

The story is based on a sample project available on GitHub:

motorro/room-populate-demo

The project has two folders:

• android — a handy Android application that searches for a city in pre-populated database and displays it in your favorite map application 😜
  The application gets a new database each time import is done.
• script — a database population script which imports sample data from OpenWeather and pre-populates android database

The sample uses a city list of around 200000 following objects as a source of data:

Source city data from OpenWeather

A Room database structure

Here is our sample entity definition:

City entity definition

We just copy the original structure here. An index for city name is defined for illustrative purposes (it is not used in our “real world” application).

Creating pre-packaged database schema

A quote from Room JavaDoc of createFromAsset():

The pre-packaged database schema will be validated.

The following means that Room database open helper checks the supplied file each time it is opened.

• The database version is checked against current version. If version is different, the migration or fallback is performed (more about it Part 2).
• The database structure may also be checked to match a generated schema if master table is not present in file. See the onValidateSchema override in our generated CitiesDb_Impl.java to get the idea if you like.

So if you intend to create your schema manually you should be careful to produce definitions compatible with what Room expects from you. Also you will have to maintain the same structure twice — at Room entity definitions and at your populating script.

How should we create correct database schema then and pre-populate it to be compatible with Room engine?

Getting Room-exported schema

Just like it is said in Room JavaDoc:

It might be best to create your pre-packaged database schema utilizing the exported schema files generated when exportSchema() is enabled.

To get schema exported do the following:

1. Enable schema export in module build.gradle :

Gradle config for schema export

2. Build your application or module

3. Get your schema saved in:
app/schemas/your_app_package/db_package/DbClass/DB_VERSION.json

Database schema description generated by Room processor

What’s inside?

Now when we have a schema file let’s take a look what’s inside focusing on what may be exploited 😉

Truncated schema definition as exported by Room

Here we have a JSON file with a structure I have not found official documentation for. However since Room is an open source project we can do some reverse engineering of migration bundle package.

Here we have definitions for:

• entities — we use them to create tables and corresponding indices
• views — we use them to create views
• setupQueries — an array of SQL statements used to create Room master table and otherwise initialize the database

The most useful for our purpose are createSql properties of these objects — we run them almost as-is to create our tables.

The Plan

As we have schema definitions the way Room likes it let’s plan some things to do to get our database created and filled with data.

1. Import Room schema and create fresh database.
2. Run all the setupQueries as is to prepare database the way Room likes it.
3. Create tables using createSql of each table definition.
4. Populate tables with data. We are on our own here importing and inserting data.
5. Create database indexes. Note that we are doing it after bulk data insert that is a general recommendation in terms of speed and balance. However you loose unique checks for each record inserted — so if your data is not reliable you may want to create indexes beforehand.
6. Create database views

Can I skip to number four at once, please?

It is obvious that the above plan is mostly (always) the same for any database pre-packaging you may need. So to make things easier I’ve created a simple library which imports Room schema and full-fills the above plan letting you to focus on data import only.

motorro/room-populate

It has a single template function exported in a library root:

A template function to full-fill The Plan

And here is how you call it:

A simple example of pre-population assembly

If you need to alter The Plan take a look at the RoomDbCreator class of the library. You may instance it yourself and call it’s creation methods the way it fits your scenario.

This ends the part one of the series which describes the approach of database pre-packaging. Take a look at the next part to see it in action — the building of our city-finder application.

Pre-populating your Room II