In the documentation says, we should use singleton for RoomDatabase instance. Is it per database or per application process?

Question

In official documentation, it's mentioned that

Note: If your app runs in a single process, you should follow the singleton design pattern when instantiating an AppDatabase object. Each RoomDatabase instance is fairly expensive, and you rarely need access to multiple instances within a single process.

Assuming we have multiple databases, with different tables, shall we considate all of them under one database and then create a single instance of the consolidated database? Does this approach have any impact on migration? If yes how to do the migration, as migration from one version to another db version is support but consolidation of multiple db to a single one I am not aware.

Answer 1

Assuming we have multiple databases, with different tables, shall we considate all of them under one database and then create a single instance of the consolidated database?

If the migration is from multiple databases to a single database, then the migration will very likely require accessing all the databases for that one migration.

You would still get the singleton for the single resultant (consolidated) database. You would not attempt to get the singleton(s) of the other databases.

Does this approach have any impact on migration?

Rather, you would open (and then close them when done with) or attach (and then detach them when done with) the other databases to perform the consolidation to extract and copy the existing data when performing the migration.

---

---

Example

---

---

Here's a relatively simple working example. This actually consolidates two, schema-wise, identical databases with just a single table each. The consolidation is just copying the data from the second database to the main database (as such there is no actual change the the schema).

Rather than attaching the second database for the migration, the other database is opened as an SQLiteDatabase, the data extracted into a Cursor and then inserted using the SupportSQLiteDatabase's insert method.

The code caters for running the exact same code, it is the VERSION NUMBER of the main database that determines whether both (version 1) or only the main (version 2) database is accessed. Only if upgrading from 1 - 2 does the migration run.

The code

MainTable - the single @Entity annotated class used for both databases.

@Entity
class MainTable {
    @PrimaryKey
    @ColumnInfo(name = "main_id")
    Long id=null;
    @ColumnInfo(name = "main_name")
    String name;
}

MainTableDao - The Dao for the main database

@Dao
abstract class MainTableDao {
   @Insert(onConflict = OnConflictStrategy.IGNORE)
   abstract long insert(MainTable mainTable);
   @Query("SELECT * FROM maintable")
   abstract List<MainTable> getAllFromMainTable();
}

SecondTableDao for the second database (can't appear to use the one for both) :-

@Dao
abstract class SecondTableDao {
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    abstract long insert(MainTable mainTable);
    @Query("SELECT * FROM maintable")
    abstract List<MainTable> getAllFromSecondTable();
}

MainDatabase

@Database(entities = {MainTable.class, },exportSchema = false,version = MainActivity.DATABASE_VERSION_MAIN)
abstract class MainDatabase extends RoomDatabase {
    abstract MainTableDao getMainTableDao();

    private static Context currentContext;

    private static volatile MainDatabase main_instance;
    public static MainDatabase getMainDatabaseInstance(Context context) {
        currentContext = context;
        if (main_instance == null) {
            main_instance = Room.databaseBuilder(context,MainDatabase.class,MainActivity.DATABASE_NAME_MAIN)
                    .allowMainThreadQueries()
                    .addMigrations(migrate1_to_2)
                    .build();
        }
        return main_instance;
    }

    static Migration migrate1_to_2 = new Migration(1,2) {
        @SuppressLint("Range")
        @Override
        public void migrate(@NonNull SupportSQLiteDatabase maindb) {

            SQLiteDatabase seconddb = SQLiteDatabase.openDatabase(
                    currentContext.getDatabasePath(MainActivity.DATABASE_NAME_SECOND).getPath(),
                    null,
                    SQLiteDatabase.OPEN_READONLY
            );
            Cursor csr = seconddb.query("maintable",null,null,null,null,null,null);
            ContentValues cv = new ContentValues();
            while (csr.moveToNext()) {
                cv.clear();
                cv.put("main_id",csr.getLong(csr.getColumnIndex("main_id")));
                cv.put("main_name",csr.getString(csr.getColumnIndex("main_name")));
                maindb.insert("maintable",SQLiteDatabase.CONFLICT_IGNORE,cv);
            }
            csr.close();
            seconddb.close();
        }
    };
}

SecondDatabase

@Database(entities = {MainTable.class},exportSchema = false,version = MainActivity.DATABASE_VERSION_SECOND)
abstract class SecondDatabase extends RoomDatabase {
    abstract SecondTableDao getSecondTableDao();

    private static volatile SecondDatabase second_instance;

    public static SecondDatabase getSecondDatabaseInstance(Context context) {
        if (second_instance == null) {
            second_instance = Room.databaseBuilder(context, SecondDatabase.class, MainActivity.DATABASE_NAME_SECOND)
                    .allowMainThreadQueries()
                    .build();
        }
        return second_instance;
    }
}

• obviously no migration required

And putting it all together MainActivity :-

public class MainActivity extends AppCompatActivity {

    public static final String DATABASE_NAME_MAIN = "main.db";
    public static final String DATABASE_NAME_SECOND = "second.db";
    public static final int DATABASE_VERSION_MAIN = 1;
    public static final int DATABASE_VERSION_SECOND = 1;

    MainDatabase mainDB;
    SecondDatabase secondDB;
    MainTableDao mainDao;
    SecondTableDao secondDao;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        /* Always get the main DB */
        mainDB = MainDatabase.getMainDatabaseInstance(this);
        mainDao = mainDB.getMainTableDao();

        /* Add data >>>>IF AT VERSION 1<<<< to both databases */
        /* at VERSION 2 NO DATA IS ADDED */
        if (DATABASE_VERSION_MAIN == 1) {
            addData(true,null);

            secondDB = SecondDatabase.getSecondDatabaseInstance(this);
            secondDao = secondDB.getSecondTableDao();
            addData(false,1000L);
        }
         /* ALWAYS WRITE DATA To THE LOG */
        logAll(true);
        if (secondDao != null) {
            logAll(false);
        }
    }

    /* Add Data to either database - noting that if the second db (mainDBFlag is false) then the first row
        is inserted with an ID of 1000 (to differentiate the data)
     */
    void addData(boolean mainDBFlag,Long initialId) {
        for (int i=0; i < 10; i++) {
            MainTable mt = new MainTable();
            if (initialId != null && i == 0) {
                mt.id = initialId;
                mt.name = "NAME" + (i + initialId);
            } else {
                mt.id = null;
                mt.name = "NAME" + i;
            }
            if (mainDBFlag) {
                mainDao.insert(mt);
            } else {
                secondDao.insert(mt);
            }
        }
    }
    
    /* Extract the data, writing it to the log, from either database (mainDBFlag true then main) */
    void logAll(boolean mainDBFlag) {
        String suffix = "_MAIN";
        List<MainTable> mainTableList;
        if (mainDBFlag) {
            mainTableList = mainDao.getAllFromMainTable();
        } else {
            mainTableList = secondDao.getAllFromSecondTable();
            suffix = "_SECOND";
        }
        if (mainTableList == null) return;
        for (MainTable mt: mainTableList) {
            Log.d("DBINFO" + suffix,"ID is " + mt.id + " NAME is " + mt.name);
        }
    }
}

So when DATABASE_VERSION_MAIN is 1 then the log includes :-

2022-07-19 08:01:22.133 D/DBINFO_MAIN: ID is 1 NAME is NAME0
2022-07-19 08:01:22.133 D/DBINFO_MAIN: ID is 2 NAME is NAME1
2022-07-19 08:01:22.134 D/DBINFO_MAIN: ID is 3 NAME is NAME2
2022-07-19 08:01:22.134 D/DBINFO_MAIN: ID is 4 NAME is NAME3
2022-07-19 08:01:22.134 D/DBINFO_MAIN: ID is 5 NAME is NAME4
2022-07-19 08:01:22.134 D/DBINFO_MAIN: ID is 6 NAME is NAME5
2022-07-19 08:01:22.134 D/DBINFO_MAIN: ID is 7 NAME is NAME6
2022-07-19 08:01:22.134 D/DBINFO_MAIN: ID is 8 NAME is NAME7
2022-07-19 08:01:22.134 D/DBINFO_MAIN: ID is 9 NAME is NAME8
2022-07-19 08:01:22.134 D/DBINFO_MAIN: ID is 10 NAME is NAME9


2022-07-19 08:01:22.137 D/DBINFO_SECOND: ID is 1000 NAME is NAME1000
2022-07-19 08:01:22.137 D/DBINFO_SECOND: ID is 1001 NAME is NAME1
2022-07-19 08:01:22.137 D/DBINFO_SECOND: ID is 1002 NAME is NAME2
2022-07-19 08:01:22.137 D/DBINFO_SECOND: ID is 1003 NAME is NAME3
2022-07-19 08:01:22.138 D/DBINFO_SECOND: ID is 1004 NAME is NAME4
2022-07-19 08:01:22.138 D/DBINFO_SECOND: ID is 1005 NAME is NAME5
2022-07-19 08:01:22.138 D/DBINFO_SECOND: ID is 1006 NAME is NAME6
2022-07-19 08:01:22.138 D/DBINFO_SECOND: ID is 1007 NAME is NAME7
2022-07-19 08:01:22.138 D/DBINFO_SECOND: ID is 1008 NAME is NAME8
2022-07-19 08:01:22.138 D/DBINFO_SECOND: ID is 1009 NAME is NAME9

and App Inspection shows :-

and also (the second database) :-

If then changing DATABASE_VERSION_MAIN to 2 then the log includes :-

2022-07-19 08:06:48.628 D/DBINFO_MAIN: ID is 1 NAME is NAME0
2022-07-19 08:06:48.628 D/DBINFO_MAIN: ID is 2 NAME is NAME1
2022-07-19 08:06:48.628 D/DBINFO_MAIN: ID is 3 NAME is NAME2
2022-07-19 08:06:48.629 D/DBINFO_MAIN: ID is 4 NAME is NAME3
2022-07-19 08:06:48.629 D/DBINFO_MAIN: ID is 5 NAME is NAME4
2022-07-19 08:06:48.629 D/DBINFO_MAIN: ID is 6 NAME is NAME5
2022-07-19 08:06:48.629 D/DBINFO_MAIN: ID is 7 NAME is NAME6
2022-07-19 08:06:48.629 D/DBINFO_MAIN: ID is 8 NAME is NAME7
2022-07-19 08:06:48.629 D/DBINFO_MAIN: ID is 9 NAME is NAME8
2022-07-19 08:06:48.629 D/DBINFO_MAIN: ID is 10 NAME is NAME9
2022-07-19 08:06:48.630 D/DBINFO_MAIN: ID is 1000 NAME is NAME1000
2022-07-19 08:06:48.630 D/DBINFO_MAIN: ID is 1001 NAME is NAME1
2022-07-19 08:06:48.630 D/DBINFO_MAIN: ID is 1002 NAME is NAME2
2022-07-19 08:06:48.630 D/DBINFO_MAIN: ID is 1003 NAME is NAME3
2022-07-19 08:06:48.631 D/DBINFO_MAIN: ID is 1004 NAME is NAME4
2022-07-19 08:06:48.631 D/DBINFO_MAIN: ID is 1005 NAME is NAME5
2022-07-19 08:06:48.631 D/DBINFO_MAIN: ID is 1006 NAME is NAME6
2022-07-19 08:06:48.631 D/DBINFO_MAIN: ID is 1007 NAME is NAME7
2022-07-19 08:06:48.631 D/DBINFO_MAIN: ID is 1008 NAME is NAME8
2022-07-19 08:06:48.631 D/DBINFO_MAIN: ID is 1009 NAME is NAME9

and App Inspection :-

• Note that second.db is closed and thus data cannot be extracted from it via App Inspection (i.e. the log shows the copied data).