Room database migration if only new table is added

Question

Let't assume, I have a simple Room database:

@Database(entities = {User.class}, version = 1)
abstract class AppDatabase extends RoomDatabase {
    public abstract Dao getDao();
}

Now, I'm adding a new entity: Pet and bumping version to 2:

@Database(entities = {User.class, Pet.class}, version = 2)
abstract class AppDatabase extends RoomDatabase {
    public abstract Dao getDao();
}

Of course, Room throws an exception: java.lang.IllegalStateException: A migration from 1 to 2 is necessary.

Assuming, I haven't changed User class (so all data is safe), I have to provide migration which just creates a new table. So, I'm looking into classes generated by Room, searching for generated query to create my new table, copying it and pasting into migration:

final Migration MIGRATION_1_2 =
        new Migration(1, 2) {
            @Override
            public void migrate(@NonNull final SupportSQLiteDatabase database) {
                database.execSQL("CREATE TABLE IF NOT EXISTS `Pet` (`name` TEXT NOT NULL, PRIMARY KEY(`name`))");
            }
        };

However I find it inconvenient to do it manually. Is there a way to tell Room: I'm not touching any of the existing table, so data is safe. Please create migration for me?

Answer 1

Room does NOT have a good Migration System, at least not until 2.1.0-alpha03.

So, until we have better Migration System, there are some workarounds to have easy Migrations in the Room.

As there is no such method as @Database(createNewTables = true) or MigrationSystem.createTable(User::class), which there should be one or other, the only possible way is running

CREATE TABLE IF NOT EXISTS `User` (`id` INTEGER, PRIMARY KEY(`id`))

inside your migrate method.

val MIGRATION_1_2 = object : Migration(1, 2){
    override fun migrate(database: SupportSQLiteDatabase) {
        database.execSQL("CREATE TABLE IF NOT EXISTS `User` (`id` INTEGER, PRIMARY KEY(`id`))")
    }
}

In order to get above SQL script, you have 4 ways

1. Write by yourself

Basically, you have to write the above script that will match the script that Room generates. This way is possible, not feasible. (Consider you have 50 fields)

2. Export Schema

If you include exportSchema = true inside your @Database annotation, Room will generate database schema within /schemas of your project folder. The usage is

@Database(entities = [User::class], version = 2, exportSchema = true)
abstract class AppDatabase : RoomDatabase {
   //...
}

Make sure that you have included below lines in build.grade of your app module

kapt {
    arguments {
        arg("room.schemaLocation", "$projectDir/schemas".toString())
    }
} 

When you run or build the project you will get a JSON file 2.json, which has all the queries within your Room database.

  "formatVersion": 1,
  "database": {
    "version": 2,
    "identityHash": "325bd539353db508c5248423a1c88c03",
    "entities": [
      {
        "tableName": "User",
        "createSql": "CREATE TABLE IF NOT EXISTS `${TABLE_NAME}` (`id` INTEGER NOT NULL, PRIMARY KEY(`id`))",
        "fields": [
          {
            "fieldPath": "id",
            "columnName": "id",
            "affinity": "INTEGER",
            "notNull": true
          },

So, you can include the above createSql within you migrate method.

3. Get query from AppDatabase_Impl

If you don't want to export schema you can still get the query by running or building the project which will generate AppDatabase_Impl.java file. and within the specified file you can have.

@Override
public void createAllTables(SupportSQLiteDatabase _db) {
  _db.execSQL("CREATE TABLE IF NOT EXISTS `User` (`id` INTEGER, PRIMARY KEY(`id`))");

Within createAllTables method, there will be the create scripts of all the entities. You can get it and include in within you migrate method.

4. Annotation Processing.

As you might guess, Room generates all of the above mentioned schema, and AppDatabase_Impl files within compilation time and with Annotation Processing which you add with

kapt "androidx.room:room-compiler:$room_version"

That means you can also do the same and make your own annotation processing library that generates all the necessary create queries for you.

The idea is to make an annotation processing library for Room annotations of @Entity and @Database. Take a class that is annotated with @Entity for example. These are the steps you will have to follow

1. Make a new StringBuilder and append "CREATE TABLE IF NOT EXISTS "
2. Get the table name either from class.simplename or by tableName field of @Entity. Add it to your StringBuilder
3. Then for each field of your class create columns of SQL. Take the name, type, nullability of the field either by the field itself or by @ColumnInfo annotation. For every field, you have to add id INTEGER NOT NULL style of a column to your StringBuilder.
4. Add primary keys by @PrimaryKey
5. Add ForeignKey and Indices if exists.
6. After finishing convert it to string and save it in some new class that you want to use. For example, save it like below

public final class UserSqlUtils {
  public String createTable = "CREATE TABLE IF NOT EXISTS User (id INTEGER, PRIMARY KEY(id))";
}

Then, you can use it as

val MIGRATION_1_2 = object : Migration(1, 2){
    override fun migrate(database: SupportSQLiteDatabase) {
        database.execSQL(UserSqlUtils().createTable)
    }
}

I made such a library for myself which you can check out, and even use it in your project. Note that the library that I made is not full and it just fulfills my requirements for table creation.

RoomExtension for better Migration

Application that uses RoomExtension

Hope it was useful.

UPDATE

By the time of writing this answer, room version was 2.1.0-alpha03 and when I emailed developers I got a response of

It is expected to have better Migration System in 2.2.0

Unfortunately, we still lack better Migration System.

Answer 2

Sorry, Room doesn't support auto-creation of tables without data loss.

It is mandatory to write the migration. Otherwise, it'll erase all the data and create the new table structure.

Answer 3

For anybody out there still looking for solutions to this issue, I have some good news. Room is starting to support Auto-migrations with version 2.4.0

https://developer.android.com/jetpack/androidx/releases/room#2.4.0-alpha01

Answer 4

You can add the following gradle command to your defaultConfig in your app.gradle:

javaCompileOptions {
        annotationProcessorOptions {
            arguments = ["room.schemaLocation":
                                 "$projectDir/schemas".toString()]
        }
    }

When you run this it will compile a list of table names with their relevant CREATE TABLE statements from which you can just copy and paste into your migration objects. You might have to change the table names.

For example this is from my generated schema:

"tableName": "assets",
"createSql": "CREATE TABLE IF NOT EXISTS `${TABLE_NAME}` (`asset_id` INTEGER NOT NULL, `type` INTEGER NOT NULL, `base` TEXT NOT NULL, `name` TEXT NOT NULL, PRIMARY KEY(`asset_id`))"

And so I copy paste the createSql statement and change the '${TABLE_NAME}' to 'assets' the table name, and voila auto generated Room create statements.

Answer 5

Maybe in this case(if you've only created new table without changing others) you can do this not creating any migrations at all?

Answer 6

You can do this way-

@Database(entities = {User.class, Pet.class}, version = 2)

abstract class AppDatabase extends RoomDatabase {
public abstract Dao getDao();
public abstract Dao getPetDao();
}

Remaining will be same as you have mentioned above-

 db = Room.databaseBuilder(this, AppDatabase::class.java, "your_db")
        .addMigrations(MIGRATION_1_2).build()

Reference - For more

Answer 7

In this case, you don't need to do a migration, you can call .fallbackToDestructiveMigration() when you are creating database instance.

Example:

    instance = Room.databaseBuilder(context, AppDatabase.class, "database name").fallbackToDestructiveMigration().build();

And don't forget to change database version.