How to merge multiple Room databases during migration
Problem
I've two databases, mainDb and secondDb, which I need to merge into a single database during a database upgrade (migration).
Attempts
I've looked into createFromAsset() which allows to import data from a database, but this only seems to work with fallbackToDestructiveMigration() enabled which would clear my mainDb when loading createFromAsset(secondDb).
Solution
Perhaps consider this example, noting that the answer you have given has some issues:-
- You are not creating the table(s) from the old (if they have different tables). The answer assumes different tables, if not then you simply need to change the table names accessed and also remove the code that creates the new table.
- You MUST have Room code to access the second database and thus would leave a mess unless another version of the App is rolled out to clean up the code. In the example bar the DATABASE2_NAME the old Room code has been done away with (commented out). It would be easy to also remove DATABSE2_NAME and hard code it in the Migration.
The Database code (@Daos,@Databases,@Entities) for the 2 databases
- In this case they are very similar for brevity
- Note comments included to see what will change for the migration/merge
- Main Thread is used for brevity
:-
const val DATABASE1_NAME = "db1.db"
const val DATABASE2_NAME = "db2.db" /* Left in to make migration simpler (no hard coding BUT!!!!) */
const val DATABASE1_VERSION = 1 /*<<<<<<<<<< CHANGED fro migration(merge) >>>>>>>>>>*/
const val DATABASE2_VERSION = 1 /* NOT NEEDED FOR Conversion */
/* Unchanged for migration (merge into 1 DB) */
@Entity
data class Table1(
@PrimaryKey
val table1Id: Long?=null,
val table1Name: String
// etc
)
/* Unchanged for migration (merge into 1 DB) */
@Dao
interface DB1DAOs {
@Insert(onConflict = OnConflictStrategy.IGNORE)
fun insert(table1: Table1): Long
@Query("SELECT * FROM table1")
fun getAllFromTable1(): List<Table1>
@Query("SELECT count(*) FROM table1")
fun getDB1Table1RecordCount(): Long
}
/* Unchanged for migration (merge into 1 DB) */
@Entity
data class Table2(
@PrimaryKey
val table2Id: Long?=null,
val table2Name: String
// etc
)
/* Unchanged for migration (merge into 1 DB) */
@Dao
interface DB2DAOs {
@Insert(onConflict = OnConflictStrategy.IGNORE)
fun insert(table2: Table2): Long
@Query("SELECT * FROM table2")
fun getAllFromTable2(): List<Table2>
}
@Database(entities = [Table1::class/*>>>>>>>>>> CHANGED TO ADD >>>>>>>>>>*//*,Table2::class*/], exportSchema = false, version = DATABASE1_VERSION)
abstract class Database1: RoomDatabase() {
abstract fun getDB1DAOs(): DB1DAOs
/*<<<<<<<<<< ADDED although code from DB2DAOs could be moved into DB2DAOs >>>>>>>>>>*/
//abstract fun getDB2DAOs(): DB2DAOs
companion object {
private var instance: Database1?=null
fun getInstance(context: Context): Database1 {
if (instance==null) {
val migration_From_V1_TO_V2 = object : Migration(1,2) {
override fun migrate(database: SupportSQLiteDatabase) {
doMigration1To2(context,database)
}
}
instance=Room.databaseBuilder(context,Database1::class.java, DATABASE1_NAME)
.allowMainThreadQueries()
.addMigrations(migration_From_V1_TO_V2) /* SEE BELOW for function invoked */
.build()
}
return instance as Database1
}
/* The migration - Frees up having the Old database Room code and
would be a little more efficient due to additional overheads of opening Database via Room
(no schema checking etc etc that Room undertakes )
*/
@SuppressLint("Range")
fun doMigration1To2(context: Context, database: SupportSQLiteDatabase) {
val otherDBFile = context.getDatabasePath(DATABASE2_NAME)
var otherDB: SQLiteDatabase? = null
if (!otherDBFile.exists()) throw RuntimeException("Other Database not found at ${otherDBFile.path}.")
try {
otherDB = SQLiteDatabase.openDatabase(
otherDBFile.path,
null,
SQLiteDatabase.OPEN_READWRITE
)
} catch (e: Exception) {
e.printStackTrace()
throw RuntimeException("Unable to Open the Other Database ${otherDBFile}. See stack trace immediately prior to this.")
}
/* Need to create the table to be added to DB1 from DB2 */
/* SQL copied from java(generated) */
database.execSQL("CREATE TABLE IF NOT EXISTS `Table2` (`table2Id` INTEGER, `table2Name` TEXT NOT NULL, PRIMARY KEY(`table2Id`))")
val csr = otherDB.query("Table2",null,null,null,null,null,null)
val cv = ContentValues()
while (csr.moveToNext()) {
cv.clear()
for (c in csr.columnNames) {
when (csr.getType(csr.getColumnIndex(c))) {
Cursor.FIELD_TYPE_BLOB -> cv.put(c,csr.getBlob(csr.getColumnIndex(c)))
Cursor.FIELD_TYPE_FLOAT -> cv.put(c,csr.getDouble(csr.getColumnIndex(c)))
Cursor.FIELD_TYPE_INTEGER -> cv.put(c,csr.getLong(csr.getColumnIndex(c)))
Cursor.FIELD_TYPE_STRING -> cv.put(c,csr.getString(csr.getColumnIndex(c)))
Cursor.FIELD_TYPE_NULL -> {} /* no-op */
}
}
database.insert("Table2",OnConflictStrategy.IGNORE,cv)
}
csr.close()
otherDB.close()
val renameOldFile = context.getDatabasePath("renamed_${DATABASE2_NAME}")
if (renameOldFile.exists()) renameOldFile.delete()
otherDBFile.renameTo(renameOldFile)
//otherDBFile.delete() when tested
}
}
}
/* COMMENTED OUT for Migration(merge) as no longer needed */
@Database(entities = [Table2::class], exportSchema = false,version = DATABASE2_VERSION)
abstract class Database2: RoomDatabase() {
abstract fun getDB2DAOs(): DB2DAOs
companion object {
private var instance: Database2?=null
fun getInstance(context: Context): Database2 {
if (instance==null) {
instance = Room.databaseBuilder(context,Database2::class.java, DATABASE2_NAME)
.allowMainThreadQueries()
.build()
}
return instance as Database2
}
}
}
/**/
Some Activity Code to load some data into both databases for retention :-
class MainActivity : AppCompatActivity() {
lateinit var db1: Database1
lateinit var dao1: DB1DAOs
lateinit var db2: Database2 /* COMMENTED OUT for Migration(merge) as no longer needed */
lateinit var dao2: DB2DAOs
override fun onCreate(savedInstanceState: Bundle?) {
super.onCreate(savedInstanceState)
setContentView(R.layout.activity_main)
db1 = Database1.getInstance(this)
dao1 = db1.getDB1DAOs()
db2 = Database2.getInstance(this)
/* COMMENTED OUT for Migration(merge) as no longer needed */
dao2 = db2.getDB2DAOs()
//dao2 = db1.getDB2DAOs() /*<<<<<<<<<< changed from above commented out line >>>>>>>>>>*/
if (DATABASE1_VERSION == 1 && dao1.getDB1Table1RecordCount() < 1) {
dao1.insert(Table1(table1Name = "DB1Name11"))
dao1.insert(Table1(table1Name = "DB1Name12"))
dao1.insert(Table1(table1Name = "DB1Name13"))
dao2.insert(Table2(table2Name = "DB2Name21"))
dao2.insert(Table2(table2Name = "DB2Name22"))
}
for(t1 in dao1.getAllFromTable1()) {
Log.d("DBINFO_V${DATABASE1_VERSION}_T1","ID=${t1.table1Id} NAME=${t1.table1Name}")
}
for (t2 in dao2.getAllFromTable2()) {
Log.d("DBINFO_${DATABASE1_VERSION}_T2","ID=${t2.table2Id} NAME=${t2.table2Name}")
}
}
}
When run (1 or more times due the the version/row count test):-
The Log shows:-
2023-02-24 00:23:17.916 D/DBINFO_V2_T1: ID=1 NAME=DB1Name11
2023-02-24 00:23:17.916 D/DBINFO_V2_T1: ID=2 NAME=DB1Name12
2023-02-24 00:23:17.916 D/DBINFO_V2_T1: ID=3 NAME=DB1Name13
2023-02-24 00:23:17.918 D/DBINFO_2_T2: ID=1 NAME=DB2Name21
2023-02-24 00:23:17.918 D/DBINFO_2_T2: ID=2 NAME=DB2Name22
App Inspection Shows:-
and also :-
The Merge
The Database Code becomes :-
const val DATABASE1_NAME = "db1.db"
const val DATABASE2_NAME = "db2.db" /* Left in to make migration simpler (no hard coding BUT!!!!) */
const val DATABASE1_VERSION = 2 /*<<<<<<<<<< CHANGED fro migration(merge) >>>>>>>>>>*/
//const val DATABASE2_VERSION = 1 /* NOT NEEDED FOR Conversion */
/* Unchanged for migration (merge into 1 DB) */
@Entity
data class Table1(
@PrimaryKey
val table1Id: Long?=null,
val table1Name: String
// etc
)
/* Unchanged for migration (merge into 1 DB) */
@Dao
interface DB1DAOs {
@Insert(onConflict = OnConflictStrategy.IGNORE)
fun insert(table1: Table1): Long
@Query("SELECT * FROM table1")
fun getAllFromTable1(): List<Table1>
@Query("SELECT count(*) FROM table1")
fun getDB1Table1RecordCount(): Long
}
/* Unchanged for migration (merge into 1 DB) */
@Entity
data class Table2(
@PrimaryKey
val table2Id: Long?=null,
val table2Name: String
// etc
)
/* Unchanged for migration (merge into 1 DB) */
@Dao
interface DB2DAOs {
@Insert(onConflict = OnConflictStrategy.IGNORE)
fun insert(table2: Table2): Long
@Query("SELECT * FROM table2")
fun getAllFromTable2(): List<Table2>
}
@Database(entities = [Table1::class/*>>>>>>>>>> CHANGED TO ADD >>>>>>>>>>*/,Table2::class], exportSchema = false, version = DATABASE1_VERSION)
abstract class Database1: RoomDatabase() {
abstract fun getDB1DAOs(): DB1DAOs
/*<<<<<<<<<< ADDED although code from DB2DAOs could be moved into DB2DAOs >>>>>>>>>>*/
abstract fun getDB2DAOs(): DB2DAOs
companion object {
private var instance: Database1?=null
fun getInstance(context: Context): Database1 {
if (instance==null) {
val migration_From_V1_TO_V2 = object : Migration(1,2) {
override fun migrate(database: SupportSQLiteDatabase) {
doMigration1To2(context,database)
}
}
instance=Room.databaseBuilder(context,Database1::class.java, DATABASE1_NAME)
.allowMainThreadQueries()
.addMigrations(migration_From_V1_TO_V2) /* SEE BELOW for function invoked */
.build()
}
return instance as Database1
}
/* The migration - Frees up having the Old database Room code and
would be a little more efficient due to additional overheads of opening Database via Room
(no schema checking etc etc that Room undertakes )
*/
@SuppressLint("Range")
fun doMigration1To2(context: Context, database: SupportSQLiteDatabase) {
val otherDBFile = context.getDatabasePath(DATABASE2_NAME)
var otherDB: SQLiteDatabase? = null
if (!otherDBFile.exists()) throw RuntimeException("Other Database not found at ${otherDBFile.path}.")
try {
otherDB = SQLiteDatabase.openDatabase(
otherDBFile.path,
null,
SQLiteDatabase.OPEN_READWRITE
)
} catch (e: Exception) {
e.printStackTrace()
throw RuntimeException("Unable to Open the Other Database ${otherDBFile}. See stack trace immediately prior to this.")
}
/* Need to create the table to be added to DB1 from DB2 */
/* SQL copied from java(generated) */
database.execSQL("CREATE TABLE IF NOT EXISTS `Table2` (`table2Id` INTEGER, `table2Name` TEXT NOT NULL, PRIMARY KEY(`table2Id`))")
val csr = otherDB.query("Table2",null,null,null,null,null,null)
val cv = ContentValues()
while (csr.moveToNext()) {
cv.clear()
for (c in csr.columnNames) {
when (csr.getType(csr.getColumnIndex(c))) {
Cursor.FIELD_TYPE_BLOB -> cv.put(c,csr.getBlob(csr.getColumnIndex(c)))
Cursor.FIELD_TYPE_FLOAT -> cv.put(c,csr.getDouble(csr.getColumnIndex(c)))
Cursor.FIELD_TYPE_INTEGER -> cv.put(c,csr.getLong(csr.getColumnIndex(c)))
Cursor.FIELD_TYPE_STRING -> cv.put(c,csr.getString(csr.getColumnIndex(c)))
Cursor.FIELD_TYPE_NULL -> {} /* no-op */
}
}
database.insert("Table2",OnConflictStrategy.IGNORE,cv)
}
csr.close()
otherDB.close()
val renameOldFile = context.getDatabasePath("renamed_${DATABASE2_NAME}")
if (renameOldFile.exists()) renameOldFile.delete()
otherDBFile.renameTo(renameOldFile)
//otherDBFile.delete() when tested
}
}
}
/* COMMENTED OUT for Migration(merge) as no longer needed
@Database(entities = [Table2::class], exportSchema = false,version = DATABASE2_VERSION)
abstract class Database2: RoomDatabase() {
abstract fun getDB2DAOs(): DB2DAOs
companion object {
private var instance: Database2?=null
fun getInstance(context: Context): Database2 {
if (instance==null) {
instance = Room.databaseBuilder(context,Database2::class.java, DATABASE2_NAME)
.allowMainThreadQueries()
.build()
}
return instance as Database2
}
}
}
*/
The Activity Code becomes:-
class MainActivity : AppCompatActivity() {
lateinit var db1: Database1
lateinit var dao1: DB1DAOs
//lateinit var db2: Database2 /* COMMENTED OUT for Migration(merge) as no longer needed */
lateinit var dao2: DB2DAOs
override fun onCreate(savedInstanceState: Bundle?) {
super.onCreate(savedInstanceState)
setContentView(R.layout.activity_main)
db1 = Database1.getInstance(this)
dao1 = db1.getDB1DAOs()
//db2 = Database2.getInstance(this)
/* COMMENTED OUT for Migration(merge) as no longer needed */
//dao2 = db2.getDB2DAOs()
dao2 = db1.getDB2DAOs() /*<<<<<<<<<< changed from above commented out line >>>>>>>>>>*/
if (DATABASE1_VERSION == 1 && dao1.getDB1Table1RecordCount() < 1) {
dao1.insert(Table1(table1Name = "DB1Name11"))
dao1.insert(Table1(table1Name = "DB1Name12"))
dao1.insert(Table1(table1Name = "DB1Name13"))
dao2.insert(Table2(table2Name = "DB2Name21"))
dao2.insert(Table2(table2Name = "DB2Name22"))
}
for(t1 in dao1.getAllFromTable1()) {
Log.d("DBINFO_V${DATABASE1_VERSION}_T1","ID=${t1.table1Id} NAME=${t1.table1Name}")
}
for (t2 in dao2.getAllFromTable2()) {
Log.d("DBINFO_${DATABASE1_VERSION}_T2","ID=${t2.table2Id} NAME=${t2.table2Name}")
}
}
}
- Note rather then cleaned up the old code has been commented out
Merged Results*
The Log :-
2023-02-24 00:52:05.617 D/DBINFO_V2_T1: ID=1 NAME=DB1Name11
2023-02-24 00:52:05.617 D/DBINFO_V2_T1: ID=2 NAME=DB1Name12
2023-02-24 00:52:05.617 D/DBINFO_V2_T1: ID=3 NAME=DB1Name13
2023-02-24 00:52:05.621 D/DBINFO_2_T2: ID=1 NAME=DB2Name21
2023-02-24 00:52:05.621 D/DBINFO_2_T2: ID=2 NAME=DB2Name22
i.e. ALL data retained
And via App Inspection:-
and
- note sure why App Inspection is showing db2.db. However Device Explorer shows, that it isn't there as a file (might be some sort of caching in App Inspection) :-
- Plugin [id: 'kotlin-kapt'] was not found in any of the following sources: Gradle Core Plugins & Plugin Repositories
- Android Studio Database Inspector always showing database as "closed"
- [Android][Room] Storing Sealed classes into Room Database
- Room: @Embedded vs @TypeConverters
- Is using a local database as the Single Source of Truth (SSOT) ideal for real-time collaboration and offline sync in mobile apps?
- Should I use single Room Database instance in case of multithreading?
- Extend existing Hibernate Entity as Android Room Entity with Kotlin Serialization annotation
- App crash after delete item in detail screen and then navigate back to list screen
- Room cannot find implementation
- Getting Execution failed for task ':app:kaptDebugKotlin'. when trying to implement Room component
- Android Room @Delete with parameters
- Trying to concatenate two flows from Room DAO query without success in kotlin
- Unresolved reference kapt and ksp in Android Studio when trying to do the setup for the use of ROOM local database
- Android Room "One-to-Many" relationships leading to entity-related errors in Gradle/KSP
- Cannot update Boolean Array in Room database
- Room - Not sure how to convert a Cursor to this method's return type (java.lang.Object)
- Showing data stored in room database when offline
- AutoIncrement of Room Entity Id is not working
- Room DB is Destroyed When App Restarts in Android 15 Pixel 9
- How to make Room work with "pre-packaged" databases with different schemas?
- How to TypeConverter List<Object> Room
- How to resolve "Entities and POJOs must have a usable public constructor"
- Room database not returning prepopulated data on first app run with Flow and LiveData in Jetpack Compose
- How to save enum field in the database room?
- Android Studio how to store API data in room database and retrieve by city name?
- Issue: java.lang.IllegalStateException: Storage for [...] is already registered When Adding Type Converters with KSP
- Room database relational
- How to use Secondary Value in a Spinner
- Cannot find setter for field - using Kotlin with Room database
- Android Room - Query to only insert new entry if combination of two fileds do not exist