Photo by Mikito Tateisi on Unsplash

Incrementally migrate from SQLite to Room

Migrate your complex database to Room with manageable PRs.

You’ve heard about Room — perhaps you checked out the documentation, watched a video or two, and decided to start integrating Room into your project. If your database has only a few tables and simple queries, you can easily migrate with a relatively small pull request by following these 7 steps to Room.

7 Steps To Room

However, if your database is larger or has complex queries, implementing all the entities, DAOs, tests for the DAOs, and replacing usages of SQLiteOpenHelper can take a long time; you’ll end up with a big pull request that will take time to implement and review. Let’s see how you can gradually migrate from SQLite to Room, with manageable PRs.

TL;DR:

First PR: Create your entity classes, the RoomDatabase, and update from your custom SQLiteOpenHelper to SupportSQLiteOpenHelper.

Following PRs: Gradually create DAOs to replace Cursor and ContentValue code.

Project setup

Let’s consider the following:

• Our database has 10 tables, each with a corresponding model object. E.g. for the users table, we have a corresponding User object.
• A CustomDbHelper class extends SQLiteOpenHelper
• The class that works with the CustomDbHelper to access the database is the LocalDataSource.
• We have tests for LocalDataSource.

First PR

Your initial PR will contain the minimum amount of changes that are needed to setup the Room database.

Create the entity classes

If you already have data model objects for every table, just add the @Entity, @PrimaryKey and @ColumnInfo annotations.

+ @Entity(tableName = "users")
  public class User {

    + @PrimaryKey
    + @ColumnInfo(name = "userid")
      private int mId;

    + @ColumnInfo(name = "username")
      private String mUserName;

      public User(int id, String userName) {
          this.mId = id;
          this.mUserName = userName;
      }

      public int getId() { return mId; }

      public String getUserName() { return mUserName; }
}

Create the Room database

Create an abstract class extending RoomDatabase. In the @Database annotation, list all the entity classes you’ve created. For now, we don’t need to create DAO classes.

Increment your database version number and implement a migration. If you didn’t change the database schema, you’ll still need to implement an empty migration to tell Room to keep the existing data.

@Database(entities = {<all entity classes>}, 
          version = <incremented_sqlite_version>)
public abstract class AppDatabase extends RoomDatabase {
    private static UsersDatabase INSTANCE;
    static final Migration      MIGRATION_<sqlite_version>_<incremented_sqlite_version> 
= new Migration(<sqlite_version>, <incremented_sqlite_version>) {
         @Override public void migrate(
                    SupportSQLiteDatabase database) {
           // Since we didn’t alter the table, there’s nothing else 
           // to do here.
         }
    };

Update the class that works with SQLiteOpenHelper

Initially, our LocalDataSource worked with a CustomOpenHelper class. We’ll update this to use the SupportSQLiteOpenHelper from the RoomDatabase.getOpenHelper().

public class LocalUserDataSource {
    private SupportSQLiteOpenHelper mDbHelper;
    LocalUserDataSource(@NonNull SupportSQLiteOpenHelper helper) {
       mDbHelper = helper;
    }

Because SupportSQLiteOpenHelper doesn’t directly extend SQLiteOpenHelper but instead is a wrapper over it, we need to update calls to get the writable and readable database and use SupportSQLiteDatabase instead of SQLiteDatabase.

SupportSQLiteDatabase db = mDbHelper.getWritableDatabase();

SupportSQLiteDatabase is a database abstraction that provides similar methods to the SQLiteDatabase. Because it provides a cleaner API for inserting and querying the database, some changes in the code will be required.

For insert, Room removes the optional nullColumnHack parameter. Instead of SQLiteDatabase.insertWithOnConflict, use SupportSQLiteDatabase.insert.

@Override
public void insertOrUpdateUser(User user) {
    SupportSQLiteDatabase db = mDbHelper.getWritableDatabase();

    ContentValues values = new ContentValues();
    values.put(COLUMN_NAME_ENTRY_ID, user.getId());
    values.put(COLUMN_NAME_USERNAME, user.getUserName());

    - db.insertWithOnConflict(TABLE_NAME, null, values,
    -        SQLiteDatabase.CONFLICT_REPLACE);
    + db.insert(TABLE_NAME, SQLiteDatabase.CONFLICT_REPLACE,
    + values);
    db.close();
}

For queries, SupportSQLiteDatabase provides 4 methods:

Cursor query(String query);
Cursor query(String query, Object[] bindArgs);
Cursor query(SupportSQLiteQuery query);
Cursor query(SupportSQLiteQuery query, CancellationSignal cancellationSignal);

If you’re using raw queries, then no changes will be required. If your queries are more complex, you’ll have to create a SupportSQLiteQuery via SupportSQLiteQueryBuilder.

For example, we have a users table and we want to get only the first user in the table, ordered by name. Here’s how the method would look like with SQLiteDatabase vs SupportSQLiteDatabase.

public User getFirstUserAlphabetically() {
        User user = null;
        SupportSQLiteDatabase db = mDbHelper.getReadableDatabase();
        String[] projection = {
                COLUMN_NAME_ENTRY_ID,
                COLUMN_NAME_USERNAME
        };
    
        // Get the first user from the table ordered alphabetically
        - Cursor cursor = db.query(TABLE_NAME, projection, null,
        - null, null, null, COLUMN_NAME_USERNAME + “ ASC “, “1”);
        
        + SupportSQLiteQuery query =
        +  SupportSQLiteQueryBuilder.builder(TABLE_NAME)
        +                           .columns(projection)
        +                           .orderBy(COLUMN_NAME_USERNAME)
        +                           .limit(“1”)
        +                           .create();
        
        + Cursor cursor = db.query(query);
        
        if (c !=null && c.getCount() > 0){
            // read data from cursor
              ...
        }
        if (c !=null){
            cursor.close();
        }
        db.close();
        return user;
    }

If you don’t have tests for the usages of your SQLiteOpenHelper implementation, I strongly recommend writing tests first then working on the migration to Room, to decrease the risks of regression issues.

Following PRs

Now that your data layer is using Room, you can start to gradually create DAOs (with tests) and replace the Cursor and ContentValue code with DAO calls.

The query that gets the first user from the users table ordered by name would be defined in the UserDao class.

@Dao
public interface UserDao {
    @Query(“SELECT * FROM Users ORDERED BY name ASC LIMIT 1”)
    User getFirstUserAlphabetically();
}

The method will then be used in the LocalDataSource.

public class LocalDataSource {
     private UserDao mUserDao;
     public User getFirstUserAlphabetically() {
        return mUserDao.getFirstUserAlphabetically();
     }
}

Moving a large database from SQLite to Room in a single PR will contain a lot of new and updated files; it can take quite some time to implement and consequently make the PR harder to review. Use the OpenHelper exposed by RoomDatabase to make minimal changes to your code for the initial PR and then gradually add DAOs to replace the Cursor and ContentValue code in follow up PRs.

For more info on Room, check out these articles:

7 Pro-tips for Room

Understanding migrations with Room

Testing Room migrations

Room 🔗 RxJava