Gradually Migrate to Room While
So you decided to migrate your data access layer to use Room, but the number of tables in your database along with how deeply integrated your DAO classes are in your codebase (whether you’re using another ORM library or not) freaks you out. Based on all the guides you’ve read so far, you know that to get into Room’s world, it’s either all or nothing, right? Well, not really. There’s a way to do it gradually while still releasing your app to public and slowly migrating one table at a time — having your old database (using outdated ORM) and Room co-exist.
The Reality
The tutorials that you’ve read so far were more suitable for small scale apps (or to be more accurate, sample apps presented on blogposts, which are far away from real-world apps with real users).
The project that I’m currently working on is several years old and has more than 20 tables. Being offline is one of its key features. There’re users with potentially years of data (synced also to the server, of course). The project is currently using ORMLite, which is tightly integrated across the codebase.
As you may already know, changing your database’s ORM requires migrating all database tables along with their DAO classes at once, since you can only have one SQLiteOpenHelper talking to one database at a time. This can be a huge task when you have more than 20 tables and DAO classes used all over the codebase. Not to mention that you also need to convince your business department, product owner and whatnot that this is a necessary thing to do in order to ease the development and maintenance in the long run.
Some of the reasons why you can’t have more than one
SQLiteOpenHelperfor the same database include: concurrency — there can only be one connection to the database at given time, migrations and versioning — database migrations and versioning will only be executed on the firstly created SQLiteOpenHelper instance.
The Clone — Creating a new Room database
If you’re in a big team, you’re most likely to continue releasing updates while this refactoring is on going. To keep up with this requirement, you’ll have to create a separate new database specifically for Room. You will then gradually create clone tables in this database, i.e. copy the data from the old tables to the new ones, and at the same time refactor your codebase to make use of the newly introduced Room DAO classes.
Identify the Table to Migrate
Your first step is to identify the table (or set of associated tables) that you’d like to migrate to Room. I suggest starting with one table, normally one that is utilized by one feature. Once you identified it, proceed to integrating Room by creating the necessary class for the database, the table and its DAO class. Room has a very good training guide and there’s also another guide by Florina Muntenescu that explains how to get started so I won’t repeat the steps to integrate Room here.
Pro-tip: make sure to configure Room to generate the JSON schema file (enabled by default). It will help you later unit testing your Room migrations when new tables are introduced in later versions. The generated JSON files contain the SQL statements that Room executes to create the tables. You will have to configure the location for the schema files and set the location as a source set for
testconfiguration. It’s all explained here.
Migrating the Data to Room
Now that you have Room set up and your first Room Entity class (a.k.a. table), we will need to copy the data from the old database table to the new one. To do that, we will utilize migrations on the old database via SQLiteOpenHelper.onUpgrade() or the respective upgrade mechanism in the ORM library you’re currently using.
Let’s assume we have a pre-filled database called FeedDatabase that contains 2 tables, feed and comments with the following SQLiteOpenHelper implementation:
class FeedDatabase(
context: Context
) : SQLiteOpenHelper(context, DB_NAME, null, DB_VER) {
override fun onCreate(db: SQLiteDatabase?) {
db?.apply {
execSQL(SQL_CREATE_FEED)
execSQL(SQL_BOOTSTRAP_FEED)
execSQL(SQL_CREATE_COMMENTS)
execSQL(SQL_BOOTSTRAP_COMMENTS)
}
}
override fun onUpgrade(
db: SQLiteDatabase?,
oldVersion: Int,
newVersion: Int) {
}
}Assuming we want to migrate comments table to Room, here are the steps we need to follow:
- We need to increment the old database’s version so that we can write a migration. Let’s assume the old database’s version is 2 after incrementing it.
- Remove the SQL statements that creates
commentstable fromFeedDatabase.onCreate(). - Next, we’ll write the migration from the previous version (1) to the new version (2). What we need to do is query the old table and map each row in the
Cursorto the Room Entity. In my case, this entity is calledComment. Each of these objects will then be added to a list, which will then be passed to the Room DAO class to be inserted into the new table.
Here’s how this migration function looks like:
fun upgrade(
dbExecutor: Executor,
db: SQLiteDatabase?,
feedRoomDatabase: FeedRoomDatabase,
oldVersion: Int,
newVersion: Int
) {
if (oldVersion == 1) {
db?.apply {
// Query old table
query(Comment.TABLE_NAME).use { cursor ->
if (cursor.moveToFirst()) {
val comments = mutableListOf<Comment>()
do {
// Map to entity and add to list
comments.add(Comment.fromCursor(cursor))
} while (cursor.moveToNext())
// Insert the list to the new table
dbExecutor.execute {
feedRoomDatabase.commentDao()
.insert(comments)
}
}
}
// Finally, we drop the old table
execSQL(SQL_DROP_COMMENTS)
}
}
}Notice that there’s a caveat here. If the app crashes during the migrations, we might end up in a state where the data was already copied to the new table. When this migration runs for the 2nd time, attempting to insert existing rows to the new table might result in a crash unless we specify that our conflict resolution strategy is to replace existing rows.
Another issue might occur if the migrations completed successfully, yet inserting the data to the new table has failed, which will result in the data never getting copied to the new table. In this case, you should hopefully be able to recover the data from the server during your period sync.
CommentDao for reference:
@Dao
interface CommentDao {
@VisibleForTesting
@Query("SELECT * FROM ${Comment.TABLE_NAME}")
fun testFetchAll(): List<Comment>
@Insert(onConflict = OnConflictStrategy.REPLACE)
fun insert(comment: Comment)
@Insert(onConflict = OnConflictStrategy.REPLACE)
fun insert(comments: List<Comment>)
}And here is how it looks when it’s hooked up to our SQLiteOpenHelper implementation:
class FeedDatabase private constructor(
private val dbExecutor: Executor,
private val feedRoomDatabase: FeedRoomDatabase,
context: Context
) : SQLiteOpenHelper(context, OLD_DB_NAME, null, DB_VER) {
override fun onCreate(db: SQLiteDatabase?) {
create(db)
}
override fun onUpgrade(db: SQLiteDatabase?, oldVersion: Int, newVersion: Int) {
upgrade(dbExecutor, db, feedRoomDatabase, oldVersion, newVersion)
}
companion object {
@Volatile
private var INSTANCE: FeedDatabase? = null
fun getInstance(
dbExecutor: Executor,
feedRoomDatabase: FeedRoomDatabase,
applicationContext: Context
): FeedDatabase {
return INSTANCE ?: synchronized(this) {
FeedDatabase.INSTANCE ?: FeedDatabase(dbExecutor, feedRoomDatabase, applicationContext).also {
FeedDatabase.INSTANCE = it
}
}
}
} // create() and upgrade() were omitted for brevity
}We’re passing the Room database instance to the old database constructor so we can then use it when we do the migration. Both create() and upgrade() methods are static, i.e. companion object methods, so that we can later call them from a unit test. create() method takes an optional version that defaults to the new database version, which we’ll then use from a unit test to create the database in a specific version.
fun create(db: SQLiteDatabase?, version: Int = DB_VER) {
db?.apply {
// Left for testing
if (version == 1) {
execSQL(SQL_CREATE_COMMENTS)
execSQL(SQL_BOOTSTRAP_COMMENTS)
}
execSQL(SQL_CREATE_FEED)
execSQL(SQL_BOOTSTRAP_FEED)
}
}Assuring the Migration is Executed
The migration will be executed only if we access the old database, e.g. either by callingFeedDatabase.readableDatabase or FeedDatabase.writableDatabase in this case. This means we will get an empty result when we query the data from the new table if the migration hasn’t been executed.
In the example below, I chose to call it on Application.onCreate():
class App : Application() {
override fun onCreate() {
super.onCreate()
val dbExecutor = Executors.newSingleThreadExecutor() // By accessing the old database we make sure
// that the migrations are executed
FeedDatabase.getInstance(dbExecutor,
FeedRoomDatabase.getInstance(this), this).readableDatabase
}
}
We’re done with the migration. Our next step is to test that it actually works. Before we continue to testing, make sure to increment your app’s version code.
Testing the Migration
We need to test that the migration actually works. One way to test is to install a previous version of the app and then install the new version and see that the data is migrated to the new Room database. But, if you’re like me, you’d want to have a unit test to to cover it as well.
Room is very convenient to unit test, especially migrations. Here’s a very good guide on how to unit test Room migrations. Nevertheless, in our case the migration is not done on the Room database, but instead it’s done on the old database SQLiteOpenHelper implementation. Unlike Room, the traditional SQLiteOpenHelper doesn’t include built-in API for testing migrations, but we can still do something very similar.
@Test
fun migration1to2DataCopiedSuccessfullyToNewDatabase() {
val newDb = Room.inMemoryDatabaseBuilder(
appContext,
FeedRoomDatabase::class.java)
.build()
// Create old database in version 1 and then
// apply migration to version 2
val oldDb = SQLiteDatabase.create(null)
FeedDatabase.create(oldDb, 1)
FeedDatabase.upgrade(SynchronousExecutor(), oldDb, newDb, 1, 2)
// First indication that our migration
// was successful - comments table was dropped
assertFalse(oldDb.checkTableExists(Comment.TABLE_NAME))
oldDb.close()
// Second indication that our migration
// was successful - we have the data in the new database
newDb.apply {
val comments = commentDao().testFetchAll()
assertEquals(1, comments.size)
assertEquals(100, comments.first().id)
assertEquals("Foo", comments.first().user)
assertEquals("Bar", comments.first().message)
}
}- We first call
SQLiteDatabase.create()static method which creates an in-memory database, just like Room has. - Once we create the in-memory database, we create the schema in version 1. If you recall, we previously wrote a static
createmethod onFeedDatabasethat takes an optional version. - Next, we execute the migration itself and right after that we make our first check to see that the old database table was indeed deleted. This is our first indication that the migration was successful.
- Finally, we query our new Room database to see that the data was copied successfully.
Refactoring the Codebase to use Room DAO
Our last step, which is probably also the longest and hardest step (of course depending on how tightly coupled is your old DAO implementation in your codebase), is to refactor our codebase to use the new Room DAO, CommentDao in this case. There’s nothing special to say about this step. My only advice is that you test as much as possible.
That’s it! Once you’re done with this step, you can proceed with your app’s release. Repeat this flow until your old database is completely migrated to the new one. Remember that you will have to keep your old SQLiteOpenHelper implementation, FeedDatabase in this case, pretty much forever due to the migrations, but otherwise you should be able to delete any other classes from your old ORM implementation.
The code in this article is available on Github:
If you liked this article please remember to clap and follow me for more articles in the future.
