Android RoomDB Migration: Add A New Table

In Android, the migration of Room Database is easy. If you want to make a change on your existing database and forget the migration attachment, the runtime monitor will give you an error like this:

java.lang.IllegalStateException: Room cannot verify the data integrity. Looks like you've changed schema but forgot to update the version number.

Here is my example of how to add a new table to your database.

— === MeNu === —

📃1. The Database Before Migration
📑2. Add New Entity
🔧3. Migration 123
🔨4. Fix SQL Issue

📃1. The Database Before Migration

< == Menu

I have two entities for my Paging 3 demo.

Entity: Food.kt

@Entity (tableName = "food")
data class Food (
    val foodname: String
) {
    @PrimaryKey(autoGenerate = true)
    var id: Long? = null
    companion object {
        // Diff.Util for Adapter
        val diffCallback = object: DiffUtil.ItemCallback<Food>() {
            override fun areItemsTheSame(
                oldItem: Food, newItem: Food
            ): Boolean = oldItem.id == newItem.id
            override fun areContentsTheSame(
                oldItem: Food, newItem: Food
            ): Boolean = oldItem == newItem
        }
    }
}

Entity: PageKey.kt

@Entity (
    tableName = "pagekey",
    indices = [Index(value = ["foodId"], unique = true)],
)
data class PageKey(
    val foodId: Long,
    val prevKey: Int?,
    val nextKey: Int?
) {
    @PrimaryKey(autoGenerate = true)
    var id: Long? = null
}

Database: FoodDatabase.kt

@Database(
    entities = [
        Food::class,
        PageKey::class
    ],
    version = 1,
    exportSchema = false
)
abstract class FoodDatabase: RoomDatabase() {
    abstract val foodDao: FoodDao
    abstract val keyDao: PageKeyDao
    companion object {
        private val TAG = "MYLOG FoodDb"
        fun lgd(s: String) = Log.d(TAG, s)
        const val DEFAULT_ROWS_PAGE = 10
        @Volatile
        private var INSTANCE: FoodDatabase? = null
        const val DB_NAME = "food_page_demo"
        fun getDatabase(context: Context): FoodDatabase {
            return INSTANCE ?: synchronized(this) {
                val instance = Room.databaseBuilder(
                    context.applicationContext,
                    FoodDatabase::class.java,
                    DB_NAME
                ).fallbackToDestructiveMigration().build()
                INSTANCE = instance
                instance
            }
        }
    }
}

I ran some test cases. This database is working fine.

📑2. Add New Entity

< == Menu

I want to add a new table for setting, called Setting.kt.

Entity: Setting.kt

@Entity (tableName = "setting")
data class Setting(
    @PrimaryKey
    val id: Long,
    val rows_page: Int?
)

Very simple, I want it to save the page size = rows_page.

So the new database will be:

@Database(
    entities = [
        ...
        Setting::class
    ],
    version = 1,
    exportSchema = false
)
abstract class FoodDatabase: RoomDatabase() {
    ...
    abstract val settingDao: SettingDao
    companion object {
        ...
        private fun DefaultSettingCallback(context: Context) =
            object : RoomDatabase.Callback() {
                override fun onCreate(db: SupportSQLiteDatabase) {
                    super.onCreate(db)
                    GlobalScope.launch(Dispatchers.IO) {
                        val settingDao = 
                                getDatabase(context).settingDao
                        settingDao.insertSetting(DEFAULT_ROWS_PAGE)
                    }
                }
            }
        fun getDatabase(context: Context): FoodDatabase {
            return INSTANCE ?: synchronized(this) {
                val instance = Room.databaseBuilder(
                    context.applicationContext,
                    FoodDatabase::class.java,
                    DB_NAME
                ).fallbackToDestructiveMigration()
                .addCallback(DefaultSettingCallback(context))
                .build()
INSTANCE = instance
                instance
            }
        }
    }
}

I have added a callback for default data. Run,

It’s the same message at the beginning.

🔧3. Migration 123

< == Menu

We need to attach the migration to fix the problem. The original version is 1. So the next version is 2. The Migration_1_2 will be,

fun getDatabase(context: Context): FoodDatabase {
    ...
}
// from version 1 to version 2
private val MIGRATION_1_2 = object : Migration(1, 2) {
    override fun migrate(db: SupportSQLiteDatabase) {
        db.execSQL("CREATE TABLE IF NOT EXISTS `setting` (`id` INTEGER, `rows_page` INTEGER, PRIMARY KEY(`id`))")
    }
}

The Bold-Italic portion is the only inconvenient part for us. Where is the drop and go? What are the Google programmers working on? Coding Card Machine? You have to type the SQLite script manually.

In getDatabase(),

fun getDatabase(context: Context): FoodDatabase {
    return INSTANCE ?: synchronized(this) {
        val instance = Room.databaseBuilder(
            context.applicationContext,
            FoodDatabase::class.java,
            DB_NAME
        ).fallbackToDestructiveMigration()
            .addCallback(DefaultSettingCallback(context))
            .addMigrations(MIGRATION_1_2)
            .build()
        INSTANCE = instance
        instance
    }
}

Fix the version on the top:

@Database(
    entities = [
        Food::class,
        PageKey::class,
        Setting::class
    ],
    version = 2,
    exportSchema = false
)

It’ll be good! The END.

🔨4. Fix SQL Issue

< == Menu

No, nothing is easy in the transition. An error pops up:

Expected:
    TableInfo{name='setting', columns={
rows_page=Column{
name='rows_page', 
type='INTEGER', 
affinity='3', 
notNull=false, 
primaryKeyPosition=0, 
defaultValue='null'
}, 
id=Column{
name='id', 
type='INTEGER', 
affinity='3', 
notNull=true, 
primaryKeyPosition=1, 
defaultValue='null'}}, 
foreignKeys=[], indices=[]}
     Found:
    TableInfo{name='setting', columns={rows_page=Column{
name='rows_page', 
type='INTEGER', 
affinity='3', 
notNull=true, 
primaryKeyPosition=0, 
defaultValue='null'}, 
id=Column{name='id', 
type='INTEGER', 
affinity='3', 
notNull=true, 
primaryKeyPosition=1, 
defaultValue='null'}}, 
foreignKeys=[], indices=[]}

You need to compare the Expected and Found. The difference is the notNull portion.

So I make rows_page be not null.

val rows_page: Int  // remove ?

It shall be fixed.
If you don’t want an empty value, you can set rows_page = 0.

This the fix on SQL script:

db.execSQL("CREATE TABLE IF NOT EXISTS `setting` (" +
        "`id` INTEGER, " +
        "`rows_page` INTEGER DEFAULT 0, " +
        "PRIMARY KEY(`id`)" +
        ")")

That’s for today.

Enjoy!