Room best ways to create backups for offline application?

Question

So I have let's say pretty complex database which is using many to many database design with foreign keys and join tables and it's Room database. I want to create backup system for it because it's offline application I would need to export database and store it to google drive's app folder. I have read quite a lot about it in recent day's but still a bit confused I saw that there is ways to export database as CSV, JSON, excel files or just as .db file, but what are the best practices for doing so and how professionals dealing with it?

Answer 1

There is very little need to do anything complex, rather simply save the SQLiteDatabase file.

Basically close Room db then save the file.

e.g. the following is a very rudimentary example that saves to the downloads directory in a sub-directory called DBsaves :-

@Override
protected void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.activity_main);
    resetSequenceAction();
    if(Build.VERSION.SDK_INT >= Build.VERSION_CODES.M) {
        verifyStoragePermissions(this);
    }
}

@Override
protected void onStart() {
    super.onStart();
    mTestDB = Room.databaseBuilder(this,TestDatabase.class,TestDatabase.DBNAME).build();
    addSomeData();
    addSomeData();
    addSomeData();
    addSomeData();
    mTestDB.close();
    File dbfile = this.getDatabasePath(TestDatabase.DBNAME);
    File sdir = new File(Environment.getExternalStoragePublicDirectory(Environment.DIRECTORY_DOWNLOADS),"DBsaves");
    String sfpath = sdir.getPath() + File.separator + "DBsave" + String.valueOf(System.currentTimeMillis());
    if (!sdir.exists()) {
        sdir.mkdirs();
    }
    File savefile = new File(sfpath);
    try {
        savefile.createNewFile();
        int buffersize = 8 * 1024;
        byte[] buffer = new byte[buffersize];
        int bytes_read = buffersize;
        OutputStream savedb = new FileOutputStream(sfpath);
        InputStream indb = new FileInputStream(dbfile);
        while ((bytes_read = indb.read(buffer,0,buffersize)) > 0) {
            savedb.write(buffer,0,bytes_read);
        }
        savedb.flush();
        indb.close();
        savedb.close();

    } catch (Exception e) {
        e.printStackTrace();
    }
}


public void verifyStoragePermissions(Activity activity) {

    final int REQUEST_EXTERNAL_STORAGE = 1;
    String[] PERMISSIONS_STORAGE = {

            //Manifest.permission.READ_EXTERNAL_STORAGE,
            Manifest.permission.WRITE_EXTERNAL_STORAGE
    };

    int permission = ActivityCompat.checkSelfPermission(
            activity,
            Manifest.permission.WRITE_EXTERNAL_STORAGE);

    if(permission != PackageManager.PERMISSION_GRANTED) {
        ActivityCompat.requestPermissions(
                activity,
                PERMISSIONS_STORAGE,
                REQUEST_EXTERNAL_STORAGE
        );
    }
}

• Note the the onCreate and verifyStoragePermissions methods only included to get the permission to write to external storage (note user permissions also set in manifest).

  • The import thing is to do this outside of Room (likewise if you were to restore from a backup).

After running :-

And then copying the file to a PC and opening with SQLite Manager :-

This being entirely as expected and as shown highly portable i.e. you can drop it into any SQLite tool (SQLite version used by such a tool may be a restrictive factor)