Android Room Persistence Library: Queries and Migration Support

The Room persistence library is part of the Architecture Components and since Google I/O 2018 it is also part of Android Jetpack.

1️⃣ Queries

By using Room we are having access to write all the queries types:

  1. @Insert
  2. @Update
  3. @Delete
  4. @Query

🔵 @Insert, @Update and @Delete

@Insert, @Update and @Delete have similar behaviour since these methods could be applied to an object, a list of objects or to a varargs of objects.

@Insert(onConflict = OnConflictStrategy.REPLACE)
void insertCompany(Company company);

@Insert(onConflict = OnConflictStrategy.REPLACE)
void insertAll(List<Company> companies);

@Insert(onConflict = OnConflictStrategy.REPLACE)
void insertAll(Company… companies);

void updateCompany(Company company);

void updateCompanies(Company… company);

void deleteCompany(Company company);

void deleteCompanies(Company… company);

In the above code examples the return type for all the methods is “void”, but these methods could also return a number. For insert that number will represent actually the id of the new inserted row in the database table, and for update and delete that number will represent the number of the rows affected.

🔵 @Query

@Query could be used with INSERT, UPDATE, DELETE and SELECT.

@Query("SELECT * FROM Company WHERE name = :companyName")
List<Company> getCompanies(String companyName);

@Query("SELECT * FROM Company")
LiveData<List<Company>> getAllCompanies();

@Query("SELECT * FROM Company")
Flowable<List<Company>> getAllCompanies();

@RawQuery(observedEntities = Employee.class)
LiveData<List<Employee>> getAllEmployeesWithLimit(SupportSQLiteQuery query);

🚀 Observable queries

A very helpful thing is the fact that Room offers support for RxJava and LiveData. And by combining Room with these two libraries we get observable queries.

These observable queries are actually a good way to automatically update the UI when something is changed in the database.

Both LiveData and Flowable queries rely on the InvalidationTracker.Observer#onInvalidated notification. When this is received, it triggers a re-query because InvalidationTracker keeps a list of tables modified by queries and notifies its callbacks about these tables.

🚀 @RawQuery

Also for the case when we don’t know from the beginning how the SQL query will look and we just want to build it dynamically, then a good solution for this scenario is to use @RawQuery.

This annotation could be also combined with LiveData but if we want to be notified about specific entities updates, then we need to define them as values for observedEntities attribute.

Warning: If we will try to run database operations on the main thread, an IllegalStateException will be raised.

⛔️java.lang.IllegalStateException: Cannot access database on the main thread since it may potentially lock the UI for a long period of time.

🔵 @Transaction

When we want to run multiple queries(operations) in the same block, a good solution is to use @Transaction.

public abstract class DepartmentDao {
    public abstract void insert(Department product);

    public abstract void delete(Department product);

    public void insertAndDeleteInTransaction(Department newDepartment, Department oldDepartment) {
        // Anything inside this method runs in a single transaction.

This approach could be also used with SELECT and there are 2 scenarios:

  1. If the result of the query is fairly big and we are not sure if the query result will fit into a single CursorWindow, which is about 2mb. In this case the query result may be corrupted due to changes in the database in between cursor window swaps.
  2. If the result of the query is a Pojo with Relation fields, these fields are queried separately. To receive consistent results between these queries, we probably want to run them in a single transaction.

🔵 Threading

Thanks to WAL in the latest versions of Room we are able to do two operations in the same time.

Room 1.0
Room 1.1 — WAL

The traditional rollback journal works by writing a copy of the original unchanged database content into a separate rollback journal file and then writing changes directly into the database file.

The WAL approach inverts this. The original content is preserved in the database file and the changes are appended into a separate WAL file.

Another way to think about the difference between rollback and write-ahead log is that in the rollback-journal approach, there are two primitive operations, reading and writing, whereas with a write-ahead log there are now three primitive operations: reading, writing, and checkpointing. (SQLite docs)

2️⃣ Migration

The Room persistence library allows you to write Migration classes to preserve user data from a version of the database to another one.

Version 1 of the Company table
Version 2 of the Company table
static final Migration MIGRATION_1_2 = new Migration(1, 2) {
    public void migrate(SupportSQLiteDatabase database) {
        database.execSQL("ALTER TABLE Company "
                + " ADD COLUMN ref_no TEXT");

✅ Migrate from v1 to v2

So in our case we need to migrate the data from version 1 to version 2 by altering the Company table and adding a new column inside of it. Behind the scenes in order to help Room to understand when a migration is necessary or not there is a room_master_table which contains an identity hash key. This key is updated each time when a change is added in the database schema.

To apply the migration we just need to use the “addMigrations” static method:

public static AppDatabase getAppDatabase(Context context) {
    if (INSTANCE == null) {
        INSTANCE = Room.databaseBuilder(context.getApplicationContext(),
    return INSTANCE;

✅ Export database schema

Other helpful thing that we could achieve by using Room is exporting the database schema (json file) directly in our project code. This file is helpful for us, the developers, to see all the changes added in the database but it is also used by the system in order to test the migrations.

 * You can set annotation processor argument (room.schemaLocation) to tell Room to
 * export the schema into a folder. Even though it is not mandatory, it is a good
 * practice to have version history in your codebase and you should commit that file * into your version control system (but don't ship it with your app!).
 * */
//export schema
javaCompileOptions {
    annotationProcessorOptions {
        arguments = ["room.schemaLocation": "$projectDir/schemas".toString()]

✅ Migrate only schema

When we don’t want to persist the user data we could use a different method “fallbackToDestructiveMigration()” which will clear the data from the tables and it will just migrate the database to the latest schema.

✅ Migrate schema and a part of the data

And for the case when we want to persist data only for a defined set of database versions a good approach is to use “.fallbackToDestructiveMigrationFrom(int… startVersions)” method.

Enjoy! Happy coding and feel free to leave a comment if something is not clear or if you have questions. 🙂

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s