Database migrations in Room require developers to write SQL statements manually that are executed in the database. This can be tricky, since Room abstracts most of the SQL statements away from developers and we don't have to write them all the time. This cheat sheet serves as a reminder of the SQL required for various migrations.
There are a number of changes to your database that could require a migration between versions. We can have one or more of these changes in a given migration:
- Adding a field to an existing table.
- Removing a field from an existing table.
- Changing the data type or column name of a field from an existing table.
- Adding a new table to the database.
- Removing a table from the database.
If you have found yourself writing a database migration that's not in the above list, create an Issue and/or Pull Request so we can get it added to the list. :)
It's important to remember how Room entities map to the table name and column names used in the database.
If we have an entity like this:
@Entity
data class Student(
val id: Long = 0L,
val firstName: String = ""
)Then we have a table named Student, with two columns named id and firstName. It maps directly from class and property names. If you want granular control over this, you can add those properties inside the annotations:
@Entity(tableName = "student_table")
data class Student(
val id: Long = 0L,
@ColumnInfo(name = "first_name")
val firstName: String = ""
)To add a new column, we can use the ALTER TABLE command:
database.execSQL("ALTER TABLE table_name ADD COLUMN columnName TYPE [NULL|NOT NULL] [DEFAULT default]")A default is required if your new type is not nullable:
database.execSQL("ALTER TABLE Student ADD COLUMN age INTEGER NOT NULL DEFAULT 0")If your new type is nullable, a default is not required:
database.execSQL("ALTER TABLE Student ADD COLUMN nickName TEXT NULL")You can see a pull request diff of this type of migration here.
SQLite does not support removing a column from a table directly. Instead we need to do the following:
- Create a backup table that has the schema we actually want.
- Copy everything from the original table into the backup.
- Delete the original table.
- Rename the backup table to the same name as the original.
Here is an example of this type of change:
database.execSQL("CREATE TABLE Student_backup (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, firstName TEXT NOT NULL, age INTEGER NOT NULL)")
database.execSQL("INSERT INTO Student_backup (id, firstName, age) SELECT id, firstName, age FROM Student")
database.execSQL("DROP TABLE Student")
database.execSQL("ALTER TABLE Student_backup RENAME TO Student")You can see a pull request implementing and testing this type of migration here.
Similar to the removing a field example, SQLite doesn't support the action of changing a data type. Instead, we need to create a back up table, copy everything over, and rename it.
Keep in mind the type of data is important in this change. In the sample pull request we are moving from an integer to a floating point number. Moving from a number to a string type may be more complicated.
Here is an example of this change:
database.execSQL("CREATE TABLE Student_backup (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, firstName TEXT NOT NULL, age REAL NOT NULL)")
database.execSQL("INSERT INTO Student_backup (id, firstName, age) SELECT id, firstName, age FROM Student")
database.execSQL("DROP TABLE Student")
database.execSQL("ALTER TABLE Student_backup RENAME TO Student")When adding a new entity to your project, all you need is the relevant create table statement:
database.execSQL("CREATE TABLE University (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, schoolName TEXT NOT NULL)")If you have trouble determining what the right syntax is, you can always look at the json file that room generates.
You can find the pull request demonstrating that here.
When removing an entity from your project, the only SQL needed is a drop table statement:
database.execSQL("DROP TABLE University")You can find a pull request for that here.