android-roomandroid-room-migration
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) :-
- 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 Studio Database Inspector always showing database as "closed"
- Android Room - Query to only insert new entry if combination of two fileds do not exist
- How to annotate Column as NOT NULL using Android Room Persistence Library
- Many to Many Relationship in Room: Cannot find the child entity column
- Android Room: How to extract data from relational tables?
- Android Room Embedded
- What is the right way to insert records with 1:N and N:M relationships in a Room database using MVVM?
- Room cannot verify the data integrity
- How to get the sum of a column grouped by dates in a Room database?
- Error with Room dao class when using Kotlin coroutines
- [Android][Room- database] error: [SQLITE_ERROR] SQL error or missing database (near "missing": syntax error)
- LaunchedEffect not triggering when adding a new profile in Jetpack Compose
- Android Room crash cannot start a transaction within a transaction
- How can I make the parameters of a uiState persist when turning the screen in Kotlin?
- Using Room query to filter out multiple items?
- How to propagate the changes from Room when I perform an update?
- Empty SQLiteException when using Room for certain api levels
- How to get context for Room database and initialize it correctly in Android Compose?
- Android Jetpack Compose Room query depending on Flow