Loading...
Android

Migrating existing SQLite implementation to Room

Room is a persistence library introduce by Google in this year I/0. Room makes it extremely easy to work with SQLite in your android application and worth giving a shot if you are using SQLiteOpenHelper as of now for handling data in your app. Migrating your current implementation to Room is, surprisingly, very easy. Google has provided samples for this here, but I personally think it’s too much code for such simple task and it can be explained easily. I explain below few simple steps for migrating your current implementation to Room DB and I hope you will find it helpful, if you do, share it and let other people know about it.

 


  • SQLiteOpenHelper implementation :
    SQLiteOpenHelper
    is a helper class which manages database creation and version management. All the CRUD operations of database we have to implement by sub classing SQLiteOpenHelper. You can find more info about how it is done here. We will migrate user database currently implemented using SQLiteOpenHelper. Current implementation looks like this.

    public class User  {
    
        private int uId;
        private String uName;
        private String uContact;
    
        public User() {
        }
    
        public User(int id, String name, String number){
            this.uId = id;
            this.uName = name;
            this.uContact= number;
        };
    
        //getters setters left out for brevity
    }
    

    All the users we have to store in SQLite db and for that we will use helper class UserDbHelper sub classed from SQLiteOpenHelper.

    public class UserDbHelper extends SQLiteOpenHelper {
        private static final int DATABASE_VERSION = 1;
    
        // Database Name
        private static final String DATABASE_NAME = "userDB";
    
        // user table name
        private static final String TABLE_USERS = "users";
    
        // user Table Columns names
        private static final String USER_ID = "user_id";
        private static final String USER_NAME = "user_name";
        private static final String USER_PH_NO = "user_contact";
    
        public UserDbHelper(Context context) {
            super(context, DATABASE_NAME, null, DATABASE_VERSION);
        }
    
        @Override
        public void onCreate(SQLiteDatabase db) {
            //create the table if not yet created
            String CREATE_USER_TABLE = "CREATE TABLE " + TABLE_USERS + "("
                    + USER_ID + " INTEGER PRIMARY KEY," + USER_NAME + " TEXT,"
                    + USER_PH_NO + " TEXT" + ")";
            db.execSQL(CREATE_USER_TABLE);
        }
    
        @Override
        public void onUpgrade(SQLiteDatabase db, int i, int i1) {
            // Drop older table if existed
            db.execSQL("DROP TABLE IF EXISTS " + TABLE_USERS);
            // Create tables again
            onCreate(db);
        }
    
        public void addUser(User user){
            //Add new user to database
            SQLiteDatabase db = this.getWritableDatabase();
            ContentValues values = new ContentValues();
            values.put(USER_NAME, user.getUName()); // Contact Name
            values.put(USER_PH_NO, user.getUContact()); // Contact Phone
            values.put(USER_ID, user.getUId());
            // Inserting Row
            db.insert(TABLE_USERS, null, values);
            db.close(); // Closing database connection
        }
    
        public User getUser(int id){
            SQLiteDatabase db = this.getReadableDatabase();
            Cursor cursor = db.query(TABLE_USERS, new String[] { USER_ID,
                            USER_NAME, USER_PH_NO }, USER_ID + "=?",
                    new String[] { String.valueOf(id) }, null, null, null, null);
            if (cursor != null)
                cursor.moveToFirst();
    
            User contact = new User(Integer.parseInt(cursor.getString(0)),
                    cursor.getString(1), cursor.getString(2));
            // return contact
            return contact;
        }
    }
    

    Now we can add users to database like this.

    //get database instance
    UserDbHelper userDbHelper = new UserDbHelper(this);
    
    //add couple of users
    userDbHelper.addUser(new User(1,"Hulk","11-445-9999"));
    userDbHelper.addUser(new User(2,"Dominic","11-445-9999"));
    

    And fetch the users using this.

    User extractedUser = userDbHelper.getUser(1);
    

    As you can see, we have to write queries to put and fetch data in table ourselves using ContentValues and cursors. This kind of code is highly prone to errors and errors can be difficult to find as well. Also there is not compile time check involved in above implementation which can produce potential runtime errors and termination.

    Room helps you achieve database abstraction through Data Access Objects and also provides compile time query checks so as to minimize potential runtime failures. Win win for developers!


  • Migrating to Room:
    I assume that at this point, you know the basics of how Room works and have tried it out. If not, I would recommend you go through this tutorial to get started. For migrating our current implementation of users database, we will update our POJO class first. Lets add @Entity annotations to User class as this class will be mapped to table in our database. It is mandatory to define at least one primary field in Room.

    @Entity
    public class User  {
    
        @PrimaryKey
        private int uId;
        private String uName;
        private String uNumber;
    
        public User() {
        }
    
        @Ignore
        public User(int id, String name, String number){
            this.uId = id;
            this.uName = name;
            this.uNumber= number;
        };
    
        //add all the getters and setters here
         
    }
    

    Let’s create Data Access Object (DAO) for exposing methods which will be used by application for accessing the data back and forth from database. Our DAO implementation will be very easy, it will only be 2 method, one to add user to database and one to read all users from database.

    @Dao
    public interface UserDAO {
    
        @Insert(onConflict = REPLACE)
        void insertUser(User user);
    
        @Query("SELECT * FROM User")
        List<User> getUsers();
    
       // @Query("SELECT * FROM User")
    
    }
    

    As you can see above, how simple implementation we provided for Data Access Object. Compare it with the methods we used in SQLiteOpenHelper class for carrying out same operations. For adding, updating or deleting row(s) in table, we don’t even have to form queries, Room will do it for us. Only for reading data, we need to use @Query annotation and provide it with query unlike SQLiteHelper where we have to deal with cursors and all of boiler plate code. Remember, more the code, more the bugs!


    We will get DAO object instance from RoomDatabase class. RoomDatabase class will be the abstract class with only one method as shown below. We have to mention version number for DB in this class. As we are migrating Database from SQLiteHelper to Room, version number needs to be more than SQLiteHelper db version number. We have used version 1 for our SQLiteHelper Database, so we will user version 2 for Room.

    @Database (entities = {User.class},version = 2)
    public abstract class UserDB extends RoomDatabase {
    
        public abstract UserDAO userDAO();
    
    }
    

    And we are done with our Room Database implementation and its time to see it in action. While creating RoomDatabase, we have to provide migration information which we will see in next part.


    We need Migration class instance while building Room Database from database builder.Migration class constructor takes 2 versions as parameter, one is the version from which it needs to migrate (version 1 in our case) and second is the version to which it is going to migrate (version 2 in our case). It provides one method migrate(SupportSQLiteDatabase db) SupportSQLiteDatabase which is called by Room while migrating the Database with db instance as parameter. Here is the place where we can make changes (if any) to db schema or db data. If we do not want to make any changes, and just want to migrate, we leave the implementation empty.
    This is how we create Room Database.

    final Migration MIGRATION_1_2 = new Migration(1, 2) {
        @Override
        public void migrate(SupportSQLiteDatabase database) {
            // Since we didn't alter the table, there's nothing else to do here.
        }
    };
    
    //create db
    final UserDB userDB = Room.databaseBuilder(this
            , UserDB.class
            , "userDB")
            .addMigrations(MIGRATION_1_2)
            .build();
    
    //inserting and accessing data using DAO
    //this operations needs to be performed on thread other than main thread
    userDB.userDAO().insertUser(new User123(5,"Wowman", "888888888"));
    userDB.userDAO().insertUser(new User123(6,"Captain", "888888888"));
    
    User user = userDB.userDAO().getUsers().get(0);
    

    And that is pretty much it. We are done with our changes and executing this will migrate our db successfully to room.

    You can get the source code mentioned here on my Git Hub here.


But while migrating db, you need to make sure of couple things, otherwise you may face issues.

  1. You have to make sure column names mentioned in SQLiteHelper class and your POJO class should match, otherwise migration will not be successful. For e.g. suppose column name for id field of user is ‘user_id’ in SQLite open helper class, either you have to name your id variable in POJO class ‘user_id’ or explicitly mention column name of id field by using @ColumnInfo annotation like this
    @ColumnInfoname(name="user_id")
    
    private int uId;
    
  2. You have to make sure that table names also matches. By default, room will assign table name as name of entity class (‘user’ in our case), but we can change this by mentioning table name while annotating entity class like this @Entity( tableName = “users” )

  • Migrating with change in DB schema:

In above example, we migrated the database by keeping its schema and data same as previous implementation. Now we will try to spice things up by adding one column in table while migrating. Let’s add new column for storing user address in table by writing SQL query inside migrate() method.

final Migration MIGRATION_1_2 = new Migration(1, 2) {
    @Override
    public void migrate(SupportSQLiteDatabase database) {
        //needs to be matched to old table name
        String TABLE_NAME = "user";

        //needs to match with whatever column name you are providing in POJO class
        String COLUMN_NAME = "uAddress";

        String ADD_COLUMN = "alter table " + TABLE_NAME + " add column " + COLUMN_NAME + " TEXT";
        
        //Execute the query
        database.execSQL(ADD_COLUMN);
    }
};

Code is pretty much self explanatory, still if you face any issues, feel free to comment it down. This is how our Database schema will look before and after migration.

SQLite schema
Database schema in SQLiteOpenHelper class

 

SQLite schema
Database schema in Room Database

I will be writing more articles about tips and tricks for making most of your android application in future, subscribe our newsletter for getting the articles directly in your inbox. Subscription box can be found on top right side of this article of you are on PC and at the bottom of this article if you are on mobile device.

5 comments
  1. Anushka Khare

    I am not getting the concept of migration, If we will have to all the corresponding tables as entities manually then what’s the use of migration, we can create a fresh Roomdatabase then . And how can we move data from our SQLite Database to RoomDatabase .

    1. amit.bhandari.hulk

      Hey Anushka, I think you have not quite understood the concept of Room Database. Room database is not actually a database, but SQLite wrapper (just like SQLite Helper). So when we are migrating from SQLitehelper to Room, we are not really moving data from one db to another, but just creating new way to access our current data (via Room). If you still have any question, feel free to write me at amit.bhandari.c@gmail.com and we can discuss more about it. Thanks.

  2. Layla

    I do not even know how I ended up here, but I thought this
    post was good. I do not know who you are but certainly you’re going to a famous blogger if you aren’t
    already 😉 Cheers!

Leave a Reply

Your email address will not be published. Required fields are marked *