Spilling the beans on Room migrations
In this post we're going to get familiar with data migrations and migration testing in android using the Room library. I want to focus specifically on the manual migration features of Room so that we can get a better understanding of what's happening under the hood in our database whenever we do one. Many of these examples can actually be handled by Room's auto migration feature set, but I think it's important to understand what happens when we change our entities and how those changes reflect in our tables lest we become useless when we run into something we can't auto migrate.
Our example will be a simple single table db where we can imagine we are recording our favorite coffee flavored drinks from our favorite coffee shops. Each time we try something new we will imagine we are adding a new entry to our db. We might model something like this with a simple kotlin room entity class:
@Entity(tableName = "coffee_table")
data class Coffee(
@PrimaryKey(autoGenerate = true)
val id: Int = 0,
val name: String,
val shopName: String
)
First Migration: Rating
Let's imagine this initial schema serves us well for a while, but we find ourselves trying to remember how much we liked a particular drink at a particular shop, and how it compares to what we're drinking today. We wish we had included a way to rate these drinks. Well thankfully Room with its migration features makes it easy for us to evolve our schema over time and so for our first migration, we'll add a column that lets us give a rating to a drink when we save it to our database.
Let's dive into the changes we'll need to make to our entity, then how we create a migration that will support inserting a default value for the existing entries, and finally a test to verify it all works before we roll out the update.
First the Coffee Entity change
@Entity(tableName = "coffee_table")
data class Coffee(
@PrimaryKey(autoGenerate = true) val id: Int = 0,
val name: String,
val shopName: String,
@ColumnInfo(defaultValue = "3")
val rating: Int = 3
)
We add a rating property and annotate it with a default value of '3'. This tells Room to expect a default value of 3 at the database level if no value is provided. To make our lives easier and avoid updating every existing instantiation of Coffee in our code, I'm also giving it a Kotlin default value (= 3). It's important to note that this Kotlin default is purely for our application code and is distinct from the schema change that Room actually needs.
Now we need to go modify our database version, add a migration, and then use it when we build the db.
@Database(
entities = [
Coffee::class
],
version = 2,
exportSchema = true
)
abstract class CoffeeDatabase : RoomDatabase() {
abstract fun coffeeDao(): CoffeeDao
companion object{
val MIGRATION_1_to_2 = Migration(1,2){ database ->
database.execSQL("ALTER TABLE coffee_table ADD COLUMN rating INTEGER NOT NULL DEFAULT 3")
}
}
}
Our migration will execute the schema change in SQL ensuring that existing rows are given a default value of 3 for our new rating property.
@Provides
@Singleton
fun provideDb(@ApplicationContext context: Context): CoffeeDatabase {
val db = Room.databaseBuilder(
context.applicationContext,
CoffeeDatabase::class.java,
"coffee_database"
)
.addMigrations(CoffeeDatabase.MIGRATION_1_to_2)
.build()
return db
}
We need to make sure that we use this migration when we build the database in our module and then finally we can write a test to verify the schema changes work as expected.
@Test
@Throws(IOException::class)
fun migrate1To2() {
// Create version 1 of the database
helper.createDatabase(TEST_DB, 1).apply {
// Insert some data using v1 schema (no rating column)
execSQL("INSERT INTO coffee_table (id, name, shopName) VALUES (1, 'Espresso', 'Local Cafe')")
close()
}
// Run migration and validate schema
val db = helper.runMigrationsAndValidate(TEST_DB, 2, true, CoffeeDatabase.MIGRATION_1_to_2)
// Verify data was migrated correctly with default rating
val cursor = db.query("SELECT id, name, shopName, rating FROM coffee_table WHERE id = 1")
cursor.moveToFirst()
assert(cursor.getInt(cursor.getColumnIndex("rating")) == 3)
cursor.close()
}
Second Migration: Drink Name
Our first migration was a success and now we can rate our coffee drinks, but now we've decided that naming the column simply "name" wasn't descriptive enough. We really should have named it "drinkName" so nobody gets confused about whether we're talking about the bean roast used in the drink vs the actual drink. This migration will be a little more involved than our last one because of some limitations with the ALTER TABLE statement in sqlite.
Until version 3.25.0, SQLite didn't support the RENAME COLUMN command. Because Android bundles its own version of SQLite into the OS, devices running Android 10 or older are stuck on these legacy versions. If we try to run a simple rename command, those older devices will crash. To safely support our entire user base, we have to use a classic database workaround: the 4 step table rebuild.
It's worth noting that there is a modern trick we could use here. If your project is using Room version 2.7.0 or higher along with the new bundled SQLite driver, you actually can bypass this limitation entirely. We will discuss exactly how to set that up later in the post.
However, for the sake of this post and the exercises in it we are going to pretend that trick doesn't exist. Learning the 4 step table rebuild is a critical skill because it is a pattern you are virtually guaranteed to encounter when working in existing Android codebases.
What is this "4 step table rebuild"? Well I'm so glad you asked because we're about to show you.
Step 0:
First the simple part that doesn't really count as a step in the 4 part migration ;)
Rename the property in our kotlin entity class
@Entity(tableName = "coffee_table")
data class Coffee(
@PrimaryKey(autoGenerate = true)
val id: Int = 0,
val drinkName: String,
val shopName: String,
@ColumnInfo(defaultValue = "3")
val rating: Int = 3
)
Step 1:
In our actual migration code, rename our existing table
database.execSQL("ALTER TABLE coffee_table RENAME TO coffee_table_old")
Step 2:
Create our new table matching our existing table with the new and better named column
database.execSQL(
"""
CREATE TABLE IF NOT EXISTS `coffee_table` (
`id` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
`drinkName` TEXT NOT NULL,
`shopName` TEXT NOT NULL,
`rating` INTEGER NOT NULL DEFAULT 3
)
""".trimIndent()
)
Step 3:
Copy the data from the old table to the new table
database.execSQL("""
INSERT INTO coffee_table (id, drinkName, shopName, rating)
SELECT id, name, shopName, rating FROM coffee_table_old
""".trimIndent())
Step 4:
Drop the old table
database.execSQL("DROP TABLE coffee_table_old")
All Together:
val MIGRATION_2_to_3 = Migration(2, 3) { database ->
database.execSQL("ALTER TABLE coffee_table RENAME TO coffee_table_old")
database.execSQL(
"""
CREATE TABLE IF NOT EXISTS `coffee_table` (
`id` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
`drinkName` TEXT NOT NULL,
`shopName` TEXT NOT NULL,
`rating` INTEGER NOT NULL DEFAULT 3
)
""".trimIndent()
)
database.execSQL("""
INSERT INTO coffee_table (id, drinkName, shopName, rating)
SELECT id, name, shopName, rating FROM coffee_table_old
""".trimIndent())
database.execSQL("DROP TABLE coffee_table_old")
}
At the end of this migration we'll have a new table that matches our modified kotlin entity with the column renamed to what we wanted as well as everything else exactly matching what was already there. We've essentially switched out the table from underneath Room.
Finally, here's how we can test this change
@Test
fun migration2to3(){
// Create version 2 of the database
helper.createDatabase(TEST_DB, 2).apply {
execSQL("""INSERT INTO coffee_table (id, name, shopName, rating) VALUES (1, 'Espresso', 'Local Cafe', 3)""".trimMargin())
close()
}
val db = helper.runMigrationsAndValidate(TEST_DB, 3, true, CoffeeDatabase.MIGRATION_2_to_3)
val cursor = db.query("SELECT drinkName FROM coffee_table WHERE id = 1")
cursor.moveToFirst()
assert(cursor.getString(cursor.getColumnIndex("drinkName")) == "Espresso")
cursor.close()
}
We need to create the DB in the before state, run the migration, and then for validating this migration we will look for a column matching "drinkName" rather than "name"
Third Migration: dateAdded
Our third migration will be another relatively simple one where we are adding another default value to a column. We'll just be capturing time at the moment of migration and using that as a good enough default value for keeping track of when a review was added. This will mean that when a user updates and this migration runs all of their past reviews will appear to have happened at the same moment in time, but this is a decision we are ok living with for our simple coffee reviewing app.
So without further ado let's look at the entity and the migration for this round.
@Entity(tableName = "coffee_table")
data class Coffee(
@PrimaryKey(autoGenerate = true)
val id: Int = 0,
val drinkName: String,
val shopName: String,
@ColumnInfo(defaultValue = "3")
val rating: Int = 3,
val dateAdded: Long = System.currentTimeMillis()
)
val MIGRATION_3_to_4 = Migration(3, 4) { database ->
val now = System.currentTimeMillis()
database.execSQL("""
ALTER TABLE coffee_table ADD COLUMN dateAdded INTEGER NOT NULL DEFAULT $now
""".trimIndent())
}
You can see that we've added a new property to the entity class that automatically gets set to the system's current time, and then when we perform the migration to make sure all existing entities have a non null value for this property we just take the time of the migration and insert it. We capture the time in kotlin and interpolate it into the sql because of a limitation of sqlite you can find documented here:
4. ALTER TABLE ADD COLUMN
The ADD COLUMN syntax is used to add a new column to an existing table. The new column is always appended to the end of the list of existing columns. The column-def rule defines the characteristics of the new column. The new column may take any of the forms permissible in a CREATE TABLE statement, with the following restrictions:The column may not have a PRIMARY KEY or UNIQUE constraint.The column may not have a default value of CURRENT_TIME, CURRENT_DATE, CURRENT_TIMESTAMP, or an expression in parentheses.If a NOT NULL constraint is specified, then the column must have a default value other than NULL.If foreign key constraints are enabled and a column with a REFERENCES clause is added, the column must have a default value of NULL.The column may not be GENERATED ALWAYS ... STORED, though VIRTUAL columns are allowed.
Fourth Migration: Shop Table
So far we've just been keeping track of what shop a drink comes from via a column on the Coffee table itself but this presents some problems.
- If two shops have the same name, how do we differentiate them later when we query for drinks from them?
- We're not being as efficient with our storage space as we could be, if we add 10 drinks from the same shop, we're storing the shop's name 10 times
- When we add new drinks we dont have an easy way to enforce consistency, so maybe we log some drinks as from "Downtown Cafe" and some others from "The Downtown Cafe".
- If a shop changes their name and we want to update our database to reflect that the update query is much more expensive with our current schema than it could be if we properly normalized
- Finally and perhaps most importantly, we've kind of boxed ourselves in on future extensibility. If we wanted to store additional information about a shop what do we do with our current schema?
So let's build our a shop table and entity and then migrate to using a foreign key in our coffee table to point to it so that we can solve these issues and move to a better way of representing this data in sqlite.
First, the shop entity:
@Entity(tableName = "shops")
data class Shop(
@PrimaryKey(autoGenerate = true)
val shopId: Int = 0,
val name: String
)
Then we'll update the coffee entity
@Entity(tableName = "coffee_table",
foreignKeys = [
ForeignKey(
entity = Shop::class,
parentColumns = ["shopId"],
childColumns = ["shopId"]
)
]
)
data class Coffee(
@PrimaryKey(autoGenerate = true)
val id: Int = 0,
val drinkName: String,
val shopId: Int,
@ColumnInfo(defaultValue = "3")
val rating: Int = 3,
val dateAdded: Long = System.currentTimeMillis()
)
Now let's take a look at what we need to do in order to handle this migration in sql. This will be our most complex migration because we need to introduce a brand new table, copy data into it, then recreate our existing table with our updated schema and then copy the data in, making sure that we copy it in correctly linking to the new shop table, and then finally delete our old coffee table.
// 1. Create the new shops table
database.execSQL("""
CREATE TABLE IF NOT EXISTS `shops` (
`shopId` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
`name` TEXT NOT NULL
)
""".trimIndent())
// 2. Populate shops with the distinct shop names from existing coffee data
database.execSQL("""
INSERT INTO shops (name)
SELECT DISTINCT shopName FROM coffee_table
""".trimIndent())
// 3. Rename old coffee table so we can recreate it with the new schema
database.execSQL("ALTER TABLE coffee_table RENAME TO coffee_table_old")
// 4. Create new coffee table with shopId foreign key instead of shopName
database.execSQL("""
CREATE TABLE IF NOT EXISTS `coffee_table` (
`id` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
`drinkName` TEXT NOT NULL,
`shopId` INTEGER NOT NULL,
`rating` INTEGER NOT NULL DEFAULT 3,
`dateAdded` INTEGER NOT NULL,
FOREIGN KEY(`shopId`) REFERENCES `shops`(`shopId`) ON UPDATE NO ACTION ON DELETE NO ACTION
)
""".trimIndent())
// 5. Copy data over, using a JOIN to resolve shopName -> shopId
database.execSQL("""
INSERT INTO coffee_table (id, drinkName, shopId, rating, dateAdded)
SELECT c.id, c.drinkName, s.shopId, c.rating, c.dateAdded
FROM coffee_table_old c
JOIN shops s ON c.shopName = s.name
""".trimIndent())
// 6. Drop the old table now that data has been migrated
database.execSQL("DROP TABLE coffee_table_old")
Once we've done this we've got a properly normalized db schema and we are free to potentially begin to map out more attributes related to the coffee shops we visit in addition to just the drinks.
Fifth Migration: Column Removal
Now let's imagine as time goes on we see that ratings are being abused and used in a toxic way so we've decided we're going to eliminate them from our functionality. How do we do this? Can we simply drop a column from a table full of data?
The answer sadly isn't perfectly straight forward. Support for DROP COLUMN within an ALTER TABLE statement came with sqlite 3.35.0. Android started shipping with that version in android 12 but prior to that you would be out of luck.
However we're using room so doesn't it bundle its own version of sqlite and therefore we'd be able to use that command? Yes but only if you have taken some extra set up steps for your project and room DB
- Add
implementation "androidx.sqlite:sqlite-bundled:$sqlite_version"to your dependency list - Use
.setDriver(BundledSQLiteDriver())when building your Room database instance - Ensure your Room library dependencies are updated to version 2.7.0 or higher.
For our example though we'll assume you weren't aware of this and still want to support older versions of android. So what do we do? Well we have to create a temporary table, copy data into it from our existing table, drop our old table, and then rename the temporary table. It's important to keep in mind that when we do this we also need to recreate any indices, or foreign keys.
With that lengthy explanation out of the way let's look at how we do this
First, let's alter the kotlin entity code by removing the rating property
Once we've done that let's update our DB version and write our migration:
val MIGRATION_5_to_6 = Migration(5, 6) { database ->
database.execSQL("""
CREATE TABLE IF NOT EXISTS `coffee_table_new` (
`id` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
`drinkName` TEXT NOT NULL,
`shopId` INTEGER NOT NULL,
`dateAdded` INTEGER NOT NULL,
FOREIGN KEY(`shopId`) REFERENCES `shops`(`shopId`) ON UPDATE NO ACTION ON DELETE NO ACTION
)
""".trimIndent())
database.execSQL("""
INSERT INTO coffee_table_new (id, drinkName, shopId, dateAdded)
SELECT id, drinkName, shopId, dateAdded FROM coffee_table
""".trimIndent())
database.execSQL("DROP TABLE coffee_table")
database.execSQL("ALTER TABLE coffee_table_new RENAME TO coffee_table")
}
Then we'll need to just write some tests, and update our application code to account for the fact that we no longer allow ratings.
Sixth Migration: Index Creation
As time goes by and we log more and more coffees, we might begin to notice our app slowing down. Why is this? What's going on? Well, whenever we try to get all the coffees associated with a particular shop, we run a query like this:
SELECT * FROM coffee_table WHERE shopId = 3
With our current schema, this forces SQLite to perform a table scan, meaning it has to read every single row in the coffee table to find the matches. Once you have thousands of coffees logged, this can cause latency. Fortunately, there's a simple solution: an index. By adding an index on the shopId, our queries can jump straight to the relevant rows, skipping the table scan entirely. Here's how to implement an index in Room and write the migration for it:
First, we add it to the entity annotation:
indices = [Index(value =["shopId"], name = "idx_coffee_table_shopId")]
We're naming our index and a common naming pattern is to follow something like "index" or "idx", and then
table name and columns in the index. If you don't provide a name room has an auto naming scheme that follows that pattern, specifically room will do "index_{tableName}{columnName}{otherColumnName}_{anotherColumnName}"
Room recommends you name your indices rather than relying on its auto generation scheme and I do as well.
Once we've done that we need to go update the database version, add our migration, and then tell the database constructor to use our migration when it's being built in our hilt module. This migration is a simple one, we just use the CREATE INDEX statement and ensure we use the same name that we used in our annotation, as well of course as the same table and column.
val MIGRATION_6_to_7 = Migration(6, 7){ database ->
database.execSQL("""
CREATE INDEX IF NOT EXISTS `idx_coffee_table_shopId` ON `coffee_table` (`shopId`)
""".trimIndent())
}
Now the next time we run
SELECT * FROM coffee_table WHERE shopId = 3
We will get a very efficient read from our db. Instead of doing a full table scan trying to find which results match, the index will point us right at just the rows we need to read. Keep in mind though that each index we add
makes every write a little bit slower since the indices must be updated, so you don't want to go crazy and add indices everywhere for everything. Use them strategically when they are needed.
And with that final migration we've come to the end of everything I wanted to talk about regarding manual migrations in room. I've included some other helpful links on the topic below and you can also find the full project here.