Select, Insert, Indexes and Foreign Keys on Room Migrations
A practical overview of Migration from SQLite to Room
In the new project, we are working on, we are making a huge migration from SQLite to Room, We usually always use Realm, but we decide to give it a try to Coroutines and Room, to handle this huge migration from our side, but I found that sometimes you need a little more information about how to handle some parts of the code. So let's start with migrations:
Migrations are simpler than in pure SQLite, imagine we have this Data class entity, for our new Room implementation, with a twin but, in SQLite,
Migration and RoomDatabase
All classes that are annotated with Database must extend this class. RoomDatabase provides direct access to the underlying database implementation. Any query apart for the migration should never happen in this layer of the implementation, for that, you can use Dao. You need a migration if there was any kind of database you want to conserve for your users, this is handled with Room, Rooms offers a migration wrapper like the next one:
Migration: SELECT and INSERT
All the entities that your database will need, should be added to the superior part of the abstract class.
We can make a step by step guide for the migration:
1. You need to create a table with your Room entity with a different name
2. Copy all the information, from the SQLite Table to the Room table
3. Drop the table in SQLite
4. Rename the table to have a final entity with the same name that the table on SQLite
Foreign Keys
On the last step, we handle the copy and creation of the table, we use INSERT or REPLACE into the table selected. But this is not good enough yet, for our selected Entity, we need to create the Foreign keys over the UpdatedTableProduct, if we don’t do it, we will face this exception: Foreign key constraint failed, so when we create our table we will add this:
Indexes
The indexes in your table are important for multi-relational tables, for more efficient queries, remember a key to make this really efficient, is not to have many indexes, and this indexes should not change over time, because this will affect the efficiency, to create indexes for our entity:
Add a new column
Imagine your structure of columns change, maybe you add a new column to tell you if the user has viewed the detail of the product, so you need to alter your table first on SQLite:
And this will be our final result, with all these changes, you will have a final migration that is successful, without exceptions, remember if you have more that one table, you probably are going to write the 4 steps to handle the migration a lot.
If you need help, I’m always happy to help, you can find me here:
Medium: https://medium.com/@dinorahto
StackOverflow: https://stackoverflow.com/users/4613259/dinorah-tovar
Happy Coding! 👩🏻💻
