Photo by Lindsay Henwood on Unsplash

Quick and Easy Migration To Room

Migrating Android apps to Room is just a couple of clicks away

Room is a new library that provides an abstraction layer on top of SQLite. It makes interactions with databases simpler, less error-prone by reducing the amount of code we write, in addition to checking our SQL statements at compile-time. Room was first introduced at Google I/O 2017. It reached production-ready status in November 2017.

Migrating our existing Android apps to Room is possible with a few manual steps:

1. Add Room libraries as project dependencies
2. Create an Entity class for each table in the SQLite database
3. Create Data Access Objects (DAOs) for each Entity class
4. Create a Migration class
5. Create the Room Database class
6. (Optional) Create necessary Type Converters

(For more details about manual migration to Room, please read “7 Steps to Room”, by Florina Muntenescu).

Wouldn’t it be convenient and efficient to generate Room classes from our existing database schemas automatically? …

Enter SQLScout

SQLScout is a plugin for Android Studio and IntelliJ IDEA that provides support for SQLite databases, including:

• The ability to connect to databases in Android devices and the file system
• A database schema explorer that displays the structure of databases
• A SQL editor that provides all the features you’d expect from an IDE (syntax highlighting, code completion, reference navigation and refactoring) and the ability to execute SQL statements
• A database console to view query results, edit table data, and export data to different formats (including Excel)
• Database diagrams

Figure 1. SQLScout’s seamless integration with Android Studio

Introducing SQLScout 3.2.0!

This version of SQLScout is dedicated to provide support for generating Room Entity, DAO and Database classes from existing database schemas.

Automating Migration to Room — With SQLScout

With 3 clicks as shown in figure 2, SQLScout’s “Room Migration” wizard will handle the heavy-lifting, manual work for you.

Figure 2. SQLScout’s “Room Migration” wizard

After clicking the “Finish” button in the wizard, SQLScout will automatically:

1. Add the Google Maven repository to your project, if needed
2. Add the Room libraries as dependencies, if needed
3. Generate Entity, DAO, Migration and Database classes from your existing database schema (the generated DAOs contain basic CRUD operations, which is a pretty good start)
4. Generate a TypeConverter for java.util.Date, if needed
5. Generate a detailed report explaining the changes made to your project and the classes that were generated

SQLScout generates 100% Kotlin code if your app is already using Kotlin.

Figure 3. SQLScout’s code generation in action

A Closer Look at the Generated Code

SQLScout generates code using the IDE’s own mechanism for generating Java and Kotlin code: code templates (for new classes and interfaces), and PSI (for fields, methods and functions), ensuring that the generated code follows your own coding style and preferences.

Figure 4. Room code generated by SQLScout

The followings are some examples of SQLScout’s generated code:

import android.arch.persistence.room.ColumnInfo
import android.arch.persistence.room.Entity
import android.arch.persistence.room.ForeignKey
import android.arch.persistence.room.Index

@Entity(tableName = "InvoiceLine",
        primaryKeys = arrayOf("InvoiceLineId"),
        foreignKeys = arrayOf(
                ForeignKey(entity = Invoice::class, 
                           childColumns = arrayOf("InvoiceId"), 
                           parentColumns = arrayOf("InvoiceId")),
                ForeignKey(entity = Track::class, 
                           childColumns = arrayOf("TrackId"), 
                           parentColumns = arrayOf("TrackId"))),
        indices = arrayOf(Index("InvoiceId"), Index("TrackId")))
data class InvoiceLine(
        @ColumnInfo(name = "InvoiceLineId") val invoiceLineId: Long,
        @ColumnInfo(name = "InvoiceId") val invoiceId: Long,
        @ColumnInfo(name = "TrackId") val trackId: Long,
        @ColumnInfo(name = "UnitPrice") val unitPrice: Double,
        @ColumnInfo(name = "Quantity") val quantity: Long)

import android.arch.persistence.room.*
import com.example.yvonne.myapplication.room.entity.Invoice

@Dao
interface InvoiceDao {
    @Query("SELECT * FROM Invoice")
    fun getInvoices(): List<Invoice>

    @Insert
    fun insertInvoices(vararg invoices: Invoice)

    @Update
    fun updateInvoice(invoice: Invoice)

    @Delete
    fun deleteInvoice(invoice: Invoice)
}

import android.arch.persistence.room.Database
import android.arch.persistence.room.RoomDatabase
import android.arch.persistence.room.TypeConverters
import com.example.yvonne.myapplication.room.converter.DateConverter
import com.example.yvonne.myapplication.room.dao.*
import com.example.yvonne.myapplication.room.entity.*

@TypeConverters(DateConverter::class)
@Database(entities = arrayOf(Album::class, Artist::class,    
                             Customer::class, Employee::class, 
                             Genre::class, Invoice::class, 
                             InvoiceLine::class, MediaType::class, 
                             Playlist::class, PlaylistTrack::class, 
                             Track::class),
           version = 2)
abstract class AppDatabase : RoomDatabase() {
    companion object {
        @JvmField
        val MIGRATION_1_2 = Migration1To2()
    }
    abstract fun albumDao(): AlbumDao
    abstract fun artistDao(): ArtistDao
    abstract fun customerDao(): CustomerDao
    abstract fun employeeDao(): EmployeeDao
    abstract fun genreDao(): GenreDao
    abstract fun invoiceDao(): InvoiceDao
    abstract fun invoiceLineDao(): InvoiceLineDao
    abstract fun mediaTypeDao(): MediaTypeDao
    abstract fun playlistDao(): PlaylistDao
    abstract fun playlistTrackDao(): PlaylistTrackDao
    abstract fun trackDao(): TrackDao
}

More Room to Go

SQLScout’s Room Migration feature is just the beginning. We are adding more support for Room-centric features.

For example, in the next version of SQLScout (3.2.1) you will be able to execute the SQL statements inside @Query annotations in any DAO class, just by clicking an action in the editor gutter (both Java and Kotlin are supported!)

Update (12/12/2017): SQLScout 3.3.0 includes this feature (and more!)

Figure 5. Executing a SQL statement inside a @Query annotation

By now, I hope this article has got you excited about SQLScout’s Room Migration feature.

Please share your experience with us at support at idescout.com. We’d like to hear from you.

Because migration to Room is a one-time deal, this feature is not available in SQLScout’s trail version. The rest of the functionalities are identical in both trail and paid versions.

Happy coding!

(Follow us on Google+ and Twitter)