Persisting Room Database Between App Uninstall and Reinstall

I wrote an Android Native Jetpack Compose application, which uses a Room database. When I update the app with a new version, the database persists. However, when I had to uninstall the app because it somehow got corrupted, the database was also deleted.

Is there some way to persist the database even when the app is deleted?

Solution

When an App is uninstalled then the App's data is removed/cleared/deleted. The database, at least when using Room, forms part of this App owned data.

As such you would need to implement

a means of saving the data elsewhere (probably as a backup in shared storage) see https://developer.android.com/training/data-storage/, and
a means of determining whether or not there is data to be restored, and
1. e.g. if the actual database itself does not exist and there is a backup of the database in the other storage area
a means of restoring the data
1. probably simply a matter of copying the single file (as that is all that an SQLite database is (assuming that it has been check pointed))
  1. if, as is the default, WAL mode is used, then the database should be closed which fully checkpoints the database. If not closed, then two other files may exist. These file the same as the database filename but are suffixed with -wal and -shm. The -wal file, if not empty, is effectively part of the database (copying and restoring both should be undertaken if they exist).

Due to the complexities of the underlying classes, especially the SQLiteOpenHelper, it is probably best to undertake these tasks before building the Room database or restarting the App after restoring.

The following is a crude example using external storage but appears to be subject to the stricter storage policies (i.e. uninstalling the App appears to clear the App's external storage) so doesn't work when uninstalling the App. However, it does show the basics, and would need modifying to suit a suitable storage location for the backups.

The Database classes etc (using a very simple single table):-

const val DATABASE_VERSION =1
const val DATABASE_FILE_NAME = "the_database.db"
const val DATABASE_BACKUP_DIRECTORY = "database_backups"
const val DATABASE_BACKUP_EXTENSION = "_backup"
const val LOGTAG = "DBINFO"
@Entity
data class Table1(
    @PrimaryKey
    val id: Long?=null,
    val col1: String
)
@Dao
interface AllDAOs {
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    fun insert(table1: Table1): Long
    @Query("SELECT * FROM table1")
    fun getAllFromTable1(): List<Table1>
}
@Database(entities = [Table1::class], exportSchema = false, version = DATABASE_VERSION)
abstract class TheDatabase: RoomDatabase() {
    abstract fun getAllDAOs(): AllDAOs
    companion object {
        var instance: TheDatabase?=null
        fun getInstance(context: Context): TheDatabase {
            if (instance==null) {
                Log.d(LOGTAG,"INSTANTIATING TheDatabase so testing to see:-\n\tif the database exists ${doesDatabaseExist(context)}" +
                        " and," +
                        "\n\tif the backup exists ${doesDatabaseBackupExist(context)}")
                /* point 2 means of determining whether or not there is data to be restored */
                if (
                    !doesDatabaseExist(context) 
                    && doesDatabaseBackupExist(context)
                ) {
                    restoreDatabase(context)
                }
                instance = Room.databaseBuilder(context,TheDatabase::class.java, DATABASE_FILE_NAME)
                    .allowMainThreadQueries() /* for demo to allow use of the main thread */
                    .build()
            }
            return instance as TheDatabase
        }
        /* Check to see if the database exists or not */
        private fun doesDatabaseExist(
            context: Context,
            databaseFileName: String= DATABASE_FILE_NAME
        ): Boolean {
            Log.d(LOGTAG,"Invoking doesDatabaseExist function for database file ${databaseFileName}.")
            val dbFile = context.getDatabasePath(databaseFileName)
            Log.d(LOGTAG,"Processing doesDatabaseExist function looking to see if ${dbFile.path} exists.")
            if (dbFile != null && dbFile.exists()) return true
            if (!dbFile.parentFile.exists()) dbFile.parentFile.mkdirs()
            return false
        }
        /* Check to see if the backup exists or not */
        private fun doesDatabaseBackupExist(
            context: Context,
            databaseFileName: String = DATABASE_FILE_NAME + DATABASE_BACKUP_EXTENSION,
            backupsDirectory: String = DATABASE_BACKUP_DIRECTORY
        ): Boolean {
            Log.d(LOGTAG,"Invoking doesDatabaseBackupExist function for database file ${databaseFileName} in directory ${DATABASE_BACKUP_DIRECTORY}")
            val backupFile = File(context.getExternalFilesDir(backupsDirectory)!!.path + File.separator + databaseFileName)
            Log.d(LOGTAG,"Processing doesDatabaseBackupExist function for backup file ${backupFile.path}")
            return backupFile.exists()
        }

        /* Backup the database via Room SupportSQLiteDatabase*/
        fun backViaRoomSupportDB(
            context: Context,
            supportDb: SupportSQLiteDatabase,
            databaseFileName: String = DATABASE_FILE_NAME,
            backupExtension: String = DATABASE_BACKUP_EXTENSION,
            backupsDirectory: String = DATABASE_BACKUP_DIRECTORY
        ): Int {
            Log.d(LOGTAG,"Invoking backupViaRoomSupportDB function for database file ${databaseFileName} to be backed up to directory ${DATABASE_BACKUP_DIRECTORY} as file ${DATABASE_FILE_NAME+ DATABASE_BACKUP_EXTENSION}.")
            var rv = -1
            val dbFile = File(context.getDatabasePath(databaseFileName).path)
            val backupFile = File(context.getExternalFilesDir(DATABASE_BACKUP_DIRECTORY+File.separator+databaseFileName+backupExtension)!!.path)
            Log.d(LOGTAG,"Database location should be ${dbFile.path} Backup location will/should be ${backupFile.path}")
            supportDb.query("PRAGMA wal_checkpoint(FULL)")
            try {
                if (backupFile.exists()) {
                    backupFile.delete()
                }
                copy(
                    dbFile.inputStream().channel,
                    backupFile.outputStream().channel)
                rv = 0
            } catch (e: IOException) {
                e.printStackTrace()
            }
            return rv
        }
        /* Backup the database via SQLiteDatabase i.e. the underlying SQLite API */
        fun backupViaSQLiteDB(
            context: Context,
            databaseFileName: String = DATABASE_FILE_NAME,
            backupExtension: String = DATABASE_BACKUP_EXTENSION,
            backupsDirectory: String = DATABASE_BACKUP_DIRECTORY
        ): Int {
            Log.d(LOGTAG,"Invoking backupViaSQLIteDB function for database file ${databaseFileName} to be backed up to directory ${DATABASE_BACKUP_DIRECTORY} as file ${DATABASE_FILE_NAME+ DATABASE_BACKUP_EXTENSION}.")
            var rv = -1
            val dbFile = File(context.getDatabasePath(databaseFileName).path)
            val backupFile = File(context.getExternalFilesDir(DATABASE_BACKUP_DIRECTORY+File.separator+databaseFileName+backupExtension)!!.path)
            Log.d(LOGTAG,"Database location should be ${dbFile.path} Backup location will/should be ${backupFile.path}")
            val sqliteDB=SQLiteDatabase.openDatabase(dbFile.path,null,0)
            sqliteDB.close() /*<<<<< closing the database should checkpoint and empty the -wal file */
            try {
                if (backupFile.exists())
                    backupFile.delete()
                copy(
                    File(context.getDatabasePath(databaseFileName)!!.path).inputStream().channel,
                    File(context.getExternalFilesDir(backupsDirectory)!!.path+File.separator+databaseFileName+backupExtension).outputStream().channel
                )
                rv = 0
            } catch (e: IOException) {
                e.printStackTrace()
            }
            return rv
        }
        /* Restore the database from the backup */
        private fun restoreDatabase(
            context: Context,
            databaseFileName: String= DATABASE_FILE_NAME,
            backupExtension: String= DATABASE_BACKUP_EXTENSION, backupsDirectory: String = DATABASE_BACKUP_DIRECTORY
        ): Int {
            var rv = -1
            try {
                copy(
                    File(context.getExternalFilesDir(backupsDirectory)!!.path+File.separator+databaseFileName+backupExtension).inputStream().channel,
                    File(context.getDatabasePath(databaseFileName)!!.path).outputStream().channel
                )
                rv = 0
            } catch (e: IOException) {
                e.printStackTrace()
            }
            return rv
        }
    }
}

And some testing Activity code:-

class MainActivity : AppCompatActivity() {
    lateinit var db: TheDatabase
    lateinit var dao: AllDAOs
    override fun onCreate(savedInstanceState: Bundle?) {
        super.onCreate(savedInstanceState)
        setContentView(R.layout.activity_main)
        db = TheDatabase.getInstance(this)
        dao = db.getAllDAOs()
        for (i in 1..10) {
            dao.insert(Table1(col1 = System.currentTimeMillis().toString()))
        }
        for (t1 in dao.getAllFromTable1()) {
            Log.d("DBINFO","T1 row ID=${t1.id} COL1=${t1.col1}")
        }
        db.close()
        /* extraneous backups i.e. using OTHER backup file names */
        TheDatabase.backViaRoomSupportDB(this, backupExtension = "_VROOM${DATABASE_BACKUP_EXTENSION}",
            supportDb = db.openHelper.writableDatabase
        )
        TheDatabase.backupViaSQLiteDB(this, backupExtension = "_VSQLITE${DATABASE_BACKUP_EXTENSION}")
        /* THE REAL BACKUP */
        TheDatabase.backupViaSQLiteDB(this)
    }
}

So gets and or creates the database and then inserts 10 rows and then make 3 backups (first two overriding the backup name), the last being the one that would be tested for existence.

Run1

This first run is undertaken for a new install and the external files do not exist. The log includes:-

2024-02-26 13:03:50.867 D/DBINFO: Invoking doesDatabaseExist function for database file the_database.db.
2024-02-26 13:03:50.867 D/DBINFO: Processing doesDatabaseExist function looking to see if /data/user/0/a.a.so78054508kotlinroomnewinstallrestoreifbackedup/databases/the_database.db exists.
2024-02-26 13:03:50.868 D/DBINFO: Invoking doesDatabaseBackupExist function for database file the_database.db_backup in directory database_backups
2024-02-26 13:03:50.874 D/DBINFO: Processing doesDatabaseBackupExist function for backup file /storage/emulated/0/Android/data/a.a.so78054508kotlinroomnewinstallrestoreifbackedup/files/database_backups/the_database.db_backup
2024-02-26 13:03:50.874 D/DBINFO: INSTANTIATING TheDatabase so testing to see:-
        if the database exists false and,
        if the backup exists false
2024-02-26 13:03:50.874 D/DBINFO: Invoking doesDatabaseExist function for database file the_database.db.
2024-02-26 13:03:50.874 D/DBINFO: Processing doesDatabaseExist function looking to see if /data/user/0/a.a.so78054508kotlinroomnewinstallrestoreifbackedup/databases/the_database.db exists.
2024-02-26 13:03:50.874 D/DBINFO: Invoking doesDatabaseBackupExist function for database file the_database.db_backup in directory database_backups
2024-02-26 13:03:50.875 D/DBINFO: Processing doesDatabaseBackupExist function for backup file /storage/emulated/0/Android/data/a.a.so78054508kotlinroomnewinstallrestoreifbackedup/files/database_backups/the_database.db_backup
2024-02-26 13:03:50.987 D/DBINFO: T1 row ID=1 COL1=1708913030911
2024-02-26 13:03:50.987 D/DBINFO: T1 row ID=2 COL1=1708913030945
2024-02-26 13:03:50.987 D/DBINFO: T1 row ID=3 COL1=1708913030945
2024-02-26 13:03:50.987 D/DBINFO: T1 row ID=4 COL1=1708913030946
2024-02-26 13:03:50.987 D/DBINFO: T1 row ID=5 COL1=1708913030949
2024-02-26 13:03:50.987 D/DBINFO: T1 row ID=6 COL1=1708913030963
2024-02-26 13:03:50.987 D/DBINFO: T1 row ID=7 COL1=1708913030965
2024-02-26 13:03:50.987 D/DBINFO: T1 row ID=8 COL1=1708913030973
2024-02-26 13:03:50.988 D/DBINFO: T1 row ID=9 COL1=1708913030975
2024-02-26 13:03:50.989 D/DBINFO: T1 row ID=10 COL1=1708913030980
2024-02-26 13:03:51.008 E/ROOM: Invalidation tracker is initialized twice :/.
2024-02-26 13:03:51.008 D/DBINFO: Invoking backupViaRoomSupportDB function for database file the_database.db to be backed up to directory database_backups as file the_database.db_backup.
2024-02-26 13:03:51.012 D/DBINFO: Database location should be /data/user/0/a.a.so78054508kotlinroomnewinstallrestoreifbackedup/databases/the_database.db Backup location will/should be /storage/emulated/0/Android/data/a.a.so78054508kotlinroomnewinstallrestoreifbackedup/files/database_backups/the_database.db_VROOM_backup
2024-02-26 13:03:51.027 D/DBINFO: Invoking backupViaSQLIteDB function for database file the_database.db to be backed up to directory database_backups as file the_database.db_backup.
2024-02-26 13:03:51.031 D/DBINFO: Database location should be /data/user/0/a.a.so78054508kotlinroomnewinstallrestoreifbackedup/databases/the_database.db Backup location will/should be /storage/emulated/0/Android/data/a.a.so78054508kotlinroomnewinstallrestoreifbackedup/files/database_backups/the_database.db_VSQLITE_backup
2024-02-26 13:03:51.042 D/DBINFO: Invoking backupViaSQLIteDB function for database file the_database.db to be backed up to directory database_backups as file the_database.db_backup.
2024-02-26 13:03:51.045 D/DBINFO: Database location should be /data/user/0/a.a.so78054508kotlinroomnewinstallrestoreifbackedup/databases/the_database.db Backup location will/should be /storage/emulated/0/Android/data/a.a.so78054508kotlinroomnewinstallrestoreifbackedup/files/database_backups/the_database.db_backup
2024-02-26 13:03:51.101 D/OpenGLRenderer: Skia GL Pipeline
2024-02-26 13:03:51.113 I/Choreographer: Skipped 32 frames!  The application may be doing too much work on its main thread.

as can be seen the checking indicates that neither the database or the backup exist (as should be the case)
the 10 rows have been added
and the 3 backups have at least been attempted and as the log does not include any exception stack traces have likely made.

So using Device Explorer:-

3 backups exist and are all the same size

Then again Using Device Explorer:-

The database file is 20K (even though the -shm file is 32K, the -wal is 0k and thus empty and thus it has been fully checkpointed)

So all appears to be fine for the First run.

2nd Run

The App is just run again and the log includes:-

2024-02-26 13:13:58.848 D/DBINFO: Invoking doesDatabaseExist function for database file the_database.db.
2024-02-26 13:13:58.848 D/DBINFO: Processing doesDatabaseExist function looking to see if /data/user/0/a.a.so78054508kotlinroomnewinstallrestoreifbackedup/databases/the_database.db exists.
2024-02-26 13:13:58.848 D/DBINFO: Invoking doesDatabaseBackupExist function for database file the_database.db_backup in directory database_backups
2024-02-26 13:13:58.850 D/DBINFO: Processing doesDatabaseBackupExist function for backup file /storage/emulated/0/Android/data/a.a.so78054508kotlinroomnewinstallrestoreifbackedup/files/database_backups/the_database.db_backup
2024-02-26 13:13:58.850 D/DBINFO: INSTANTIATING TheDatabase so testing to see:-
        if the database exists true and,
        if the backup exists true
2024-02-26 13:13:58.850 D/DBINFO: Invoking doesDatabaseExist function for database file the_database.db.
2024-02-26 13:13:58.850 D/DBINFO: Processing doesDatabaseExist function looking to see if /data/user/0/a.a.so78054508kotlinroomnewinstallrestoreifbackedup/databases/the_database.db exists.
2024-02-26 13:13:58.959 D/DBINFO: T1 row ID=1 COL1=1708913030911
2024-02-26 13:13:58.960 D/DBINFO: T1 row ID=2 COL1=1708913030945
2024-02-26 13:13:58.960 D/DBINFO: T1 row ID=3 COL1=1708913030945
2024-02-26 13:13:58.960 D/DBINFO: T1 row ID=4 COL1=1708913030946
2024-02-26 13:13:58.960 D/DBINFO: T1 row ID=5 COL1=1708913030949
2024-02-26 13:13:58.960 D/DBINFO: T1 row ID=6 COL1=1708913030963
2024-02-26 13:13:58.960 D/DBINFO: T1 row ID=7 COL1=1708913030965
2024-02-26 13:13:58.960 D/DBINFO: T1 row ID=8 COL1=1708913030973
2024-02-26 13:13:58.960 D/DBINFO: T1 row ID=9 COL1=1708913030975
2024-02-26 13:13:58.960 D/DBINFO: T1 row ID=10 COL1=1708913030980
2024-02-26 13:13:58.960 D/DBINFO: T1 row ID=11 COL1=1708913638882
2024-02-26 13:13:58.960 D/DBINFO: T1 row ID=12 COL1=1708913638911
2024-02-26 13:13:58.960 D/DBINFO: T1 row ID=13 COL1=1708913638913
2024-02-26 13:13:58.960 D/DBINFO: T1 row ID=14 COL1=1708913638927
2024-02-26 13:13:58.960 D/DBINFO: T1 row ID=15 COL1=1708913638936
2024-02-26 13:13:58.960 D/DBINFO: T1 row ID=16 COL1=1708913638940
2024-02-26 13:13:58.960 D/DBINFO: T1 row ID=17 COL1=1708913638942
2024-02-26 13:13:58.960 D/DBINFO: T1 row ID=18 COL1=1708913638945
2024-02-26 13:13:58.960 D/DBINFO: T1 row ID=19 COL1=1708913638947
2024-02-26 13:13:58.960 D/DBINFO: T1 row ID=20 COL1=1708913638949
2024-02-26 13:13:58.974 E/ROOM: Invalidation tracker is initialized twice :/.
2024-02-26 13:13:58.975 D/DBINFO: Invoking backupViaRoomSupportDB function for database file the_database.db to be backed up to directory database_backups as file the_database.db_backup.
2024-02-26 13:13:58.977 D/DBINFO: Database location should be /data/user/0/a.a.so78054508kotlinroomnewinstallrestoreifbackedup/databases/the_database.db Backup location will/should be /storage/emulated/0/Android/data/a.a.so78054508kotlinroomnewinstallrestoreifbackedup/files/database_backups/the_database.db_VROOM_backup
2024-02-26 13:13:58.982 D/DBINFO: Invoking backupViaSQLIteDB function for database file the_database.db to be backed up to directory database_backups as file the_database.db_backup.
2024-02-26 13:13:58.984 D/DBINFO: Database location should be /data/user/0/a.a.so78054508kotlinroomnewinstallrestoreifbackedup/databases/the_database.db Backup location will/should be /storage/emulated/0/Android/data/a.a.so78054508kotlinroomnewinstallrestoreifbackedup/files/database_backups/the_database.db_VSQLITE_backup
2024-02-26 13:13:58.993 D/DBINFO: Invoking backupViaSQLIteDB function for database file the_database.db to be backed up to directory database_backups as file the_database.db_backup.
2024-02-26 13:13:58.994 D/DBINFO: Database location should be /data/user/0/a.a.so78054508kotlinroomnewinstallrestoreifbackedup/databases/the_database.db Backup location will/should be /storage/emulated/0/Android/data/a.a.so78054508kotlinroomnewinstallrestoreifbackedup/files/database_backups/the_database.db_backup

In this case the checks show that the database exists and that the backup exists and also that an additional 10 rows have been added.

Device Explorer shows that the 3 backups have been made due to the time stamp. The size is still 20K (the database stores data in blocks and thus the size will only increase when new blocks are required, i.e. the new 10 rows fit into already allocated storage):-

So in theory a 3rd run after uninstalling the App should restore the database. However after uninstalling the App then device explorer indicates an issue as per:-

i.e. the App's data in the emulated storage location has been deleted
BUT still exists in the equivalent non emulated storage

As such the the above will not restore the database as the backup will be deleted, other than that, in theory the database would be restored IF a less volatile location were obtained.

However, you may have luck using the - allowBackup option see https://developer.android.com/guide/topics/data/autobackup (but then you are a little at the mercy of the the cloud/google drive)