Backup Room database
Solution 1
How can I properly re-open room db after I close it?
I am sorry that this doesn't answer that question.
But if moving everything to the original database file is what you want to do, then you don't have to close the database in the first place. You can instead force a checkpoint using the wal_checkpoint
pragma.
Query the following statement against the database. We use raw queries here as pragma
is not yet supported by Room (it will trigger a UNKNOWN query type
error). Have this query inside of your DAO:
@RawQuery
int checkpoint(SupportSQLiteQuery supportSQLiteQuery);
And then when you call the checkpoint method, use the query then:
myDAO.checkpoint(new SimpleSQLiteQuery("pragma wal_checkpoint(full)"));
This link may shed some light on what wal_checkpoint
does.
Solution 2
To more specifically answer your question, this is how I backup the room database in one of my Apps.
- Check for permission to read from / write to the external storage. You can ignore this step if you write to your App files directory.
- Close your
RoomDatabase
. In my caseAppDatabase
refers to a singleton that contains logic for building the room database initially.AppDatabase.getInstance(this).getDatabase()
gets the current instance of the singleton, and its current database class, that extends fromRoomDatabase
. This essentially callsRoomDatabase.close()
. - Define the source and destination files, depending on backing up or restoring. I include shm and wal files, even though they are temporary files.
- Copy the files with your method of choice.
FileUtils
in this case, refers tocommons-io
.
The code
if(id == R.id.action_save_db) {
int permission = ActivityCompat.checkSelfPermission(this, Manifest.permission.WRITE_EXTERNAL_STORAGE);
if(permission == PackageManager.PERMISSION_GRANTED) {
AppDatabase.getInstance(this).getDatabase().close();
File db = getDatabasePath("my-db");
File dbShm = new File(db.getParent(), "my-db-shm");
File dbWal = new File(db.getParent(), "my-db-wal");
File db2 = new File("/sdcard/", "my-db");
File dbShm2 = new File(db2.getParent(), "my-db-shm");
File dbWal2 = new File(db2.getParent(), "my-db-wal");
try {
FileUtils.copyFile(db, db2);
FileUtils.copyFile(dbShm, dbShm2);
FileUtils.copyFile(dbWal, dbWal2);
} catch (Exception e) {
Log.e("SAVEDB", e.toString());
}
} else {
Snackbar.make(mDrawer, "Please allow access to your storage", Snackbar.LENGTH_LONG)
.setAction("Allow", view -> ActivityCompat.requestPermissions(this, new String[] {
Manifest.permission.WRITE_EXTERNAL_STORAGE
}, 0)).show();
}
} else if(id == R.id.action_load_db) {
int permission = ActivityCompat.checkSelfPermission(this, Manifest.permission.READ_EXTERNAL_STORAGE);
if(permission == PackageManager.PERMISSION_GRANTED) {
AppDatabase.getInstance(this).getDatabase().close();
File db = new File("/sdcard/", "my-db");
File dbShm = new File(db.getParent(), "my-db-shm");
File dbWal = new File(db.getParent(), "my-db-wal");
File db2 = getDatabasePath("my-db");
File dbShm2 = new File(db2.getParent(), "my-db-shm");
File dbWal2 = new File(db2.getParent(), "my-db-wal");
try {
FileUtils.copyFile(db, db2);
FileUtils.copyFile(dbShm, dbShm2);
FileUtils.copyFile(dbWal, dbWal2);
} catch (Exception e) {
Loge("RESTOREDB", e.toString());
}
} else {
Snackbar.make(mDrawer, "Please allow access to your storage", Snackbar.LENGTH_LONG)
.setAction("Allow", view -> ActivityCompat.requestPermissions(this, new String[] {
Manifest.permission.READ_EXTERNAL_STORAGE
}, 0)).show();
}
}
Solution 3
As an alternative, you can always create your Room database while forcing it not to use write ahead logging:
Room.databaseBuilder(context, db.class, dbName)
.setJournalMode(JournalMode.TRUNCATE)
.build();
Solution 4
To more specifically answer your question, this is how I backup the room database in one of my Apps.
1-Check for permission to read from / write to the external storage. 2-Close your RoomDatabase. In my case AppDatabase refers to a singleton that contains logic for building the room database initially. AppDatabase.getInstance(this@MainActivity) gets the current instance of the singleton, and its current database class, that extends from RoomDatabase. 3-Then essentially call dbInstance.close().
private fun createBackup() {
val db = AppDatabase.getInstance(this@MainActivity)
db.close()
val dbFile: File = getDatabasePath(DATABASE_NAME)
val sDir = File(Environment.getExternalStorageDirectory(), "Backup")
val fileName = "Backup (${getDateTimeFromMillis(System.currentTimeMillis(), "dd-MM-yyyy-hh:mm")})"
val sfPath = sDir.path + File.separator + fileName
if (!sDir.exists()) {
sDir.mkdirs()
}
val saveFile = File(sfPath)
if (saveFile.exists()) {
Log.d("LOGGER ", "File exists. Deleting it and then creating new file.")
saveFile.delete()
}
try {
if (saveFile.createNewFile()) {
val bufferSize = 8 * 1024
val buffer = ByteArray(bufferSize)
var bytesRead: Int
val saveDb: OutputStream = FileOutputStream(sfPath)
val indDb: InputStream = FileInputStream(dbFile)
do {
bytesRead = indDb.read(buffer, 0, bufferSize)
if (bytesRead < 0)
break
saveDb.write(buffer, 0, bytesRead)
} while (true)
saveDb.flush()
indDb.close()
saveDb.close()
}
} catch (e: Exception) {
e.printStackTrace()
}
}
You have to include save file in
try {
//backup process
}
} catch (e: Exception) {
e.printStackTrace()
}
in order of any error occur and to avoid app crashes.
And to get date from currentTimeMillis use this function
fun getDateTimeFromMillis(millis: Long, pattern: String): String {
val simpleDateFormat = SimpleDateFormat(pattern, Locale.getDefault()).format(Date())
return simpleDateFormat.format(millis)
}
The Code For Resoting Db passing file object to Uri.fromFile
try {
val fileUri: Uri = Uri.fromFile(file)
val inputStream = contentResolver.openInputStream(fileUri)
println("restoring ")
restoreDatabase(inputStream);
inputStream?.close()
} catch (e: IOException) {
println( e.message)
e.printStackTrace()
}
**Or returning result with start activity for result **
protected void onActivityResult(int requestCode, int resultCode, Intent data) {
super.onActivityResult(requestCode, resultCode, data);
if (requestCode == 12 && resultCode == RESULT_OK && data != null) {
Uri fileUri = data.getData();
try {
assert fileUri != null;
InputStream inputStream = getContentResolver().openInputStream(fileUri);
restoreDatabase(inputStream);
inputStream.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
restoreDatabase function
private fun restoreDatabase(inputStreamNewDB: InputStream?) {
val db = AppDatabase.getInstance(this@MainActivity)
db.close()
val oldDB = getDatabasePath(DATABASE_NAME)
if (inputStreamNewDB != null) {
try {
copyFile(inputStreamNewDB as FileInputStream?, FileOutputStream(oldDB))
println("restore success")
} catch (e: IOException) {
Log.d("BindingContextFactory ", "ex for is of restore: $e")
e.printStackTrace()
}
} else {
Log.d("BindingContextFactory ", "Restore - file does not exists")
}
}
now you need to copy file from backup into real db file use copyFile
@Throws(IOException::class)
fun copyFile(fromFile: FileInputStream?, toFile: FileOutputStream) {
var fromChannel: FileChannel? = null
var toChannel: FileChannel? = null
try {
fromChannel = fromFile?.channel
toChannel = toFile.channel
fromChannel?.transferTo(0, fromChannel.size(), toChannel)
} finally {
try {
fromChannel?.close()
} finally {
toChannel?.close()
}
}
}
Solution 5
First thing that needs to be done is to create the database with appropriate journal mode.
Room.databaseBuilder(context, AppDatabase::class.java, name)
.setJournalMode(RoomDatabase.JournalMode.TRUNCATE)
.build()
After that the following checkpoint query needs to be executed to ensure all of the pending transactions are applied.
For this a following method needs to be added to the database Dao interface
interface UserDao {
@RawQuery
fun checkpoint(supportSQLiteQuery: SupportSQLiteQuery?): Single<Int>
}
Then the method needs to be called with the following SQL query
userDao.checkpoint((SimpleSQLiteQuery("pragma wal_checkpoint(full)")))
Once the checkpoint method has succeeded the database backup file can finally be saved.
Finally the database backup file can be retrieved using the following code
File(database.openHelper.writableDatabase.path)
The file then needs to be copied into the backup file location.
To restore the file the onky thing that needs to be done is to overwrite the database file (this can be retrieved using above snippet) with the backup file.
You can read about this in more detail on my blog
https://androidexplained.github.io/android/room/2020/10/03/room-backup-restore.html
Alex Busuioc
I have been strongly focusing on native Android development for the past six years. My Android skill set includes: RxJava2, Room SQL, MVVM architecture, retrofit2 Firebase In-app purchases, SQLite, Google Cloud Messaging, Analytics, NFC, AdMob Third-party libraries: Apache POI, Zxing Material design Android Studio, Gradle Frontend: React, Javascript, Datatables WebSockets Backend: node.js, PHP APIs: Maps, Facebook, Dropbox, Slack, Stripe, MsTeams, AWS
Updated on June 07, 2022Comments
-
Alex Busuioc about 2 years
I'm trying to backup a room database programmatically.
For that, I'm simply copying the
.sqlite
file that contains the whole databaseBut, before copying, due to the fact that room has write ahead logging enabled, we must close the database so that
-shm
file and-wal
file merge into a single.sqlite
file. As pointed out hereI run
.close()
onRoomDatabase
object:Everything works fine with the backup, BUT, later on, when I try to execute an
INSERT
query, I get this error:android.database.sqlite.SQLiteException: no such table: room_table_modification_log (code 1)
How can I properly re-open room db after I close it?
PS:
.isOpen()
onRoomDatabase
object returnstrue
beforeINSERT
Room version:
1.1.1-rc1