Method to create tables in Room

Question

When using Room whenever new tables added to your database you have to create it in your migration. Unfortunately, Room doesn't have such method to create table by just giving class name. Something below is necessary to have

room.createTable(User::class)

The similar method exists for example in OrmLite

TableUtils.createTable(connectionSource, User::class.java)

The necessity comes from complexity of creating tables with just simple SQLite query. Currently, what you can do is within your migrate you write your create SQLite script

db.execSQL("CREATE TABLE IF NOT EXIST `User` (uid INTEGER NON NULL, PRYMARY KEY (`uid`))")

There is no problem in above method, but it gets complex and long SQLite script if your have for example 50 fields. Obviously, you don't write it by yourself, and there are two methods to get the Create Script automatically generated by Room for you so that you just copy past.

1. After building the app,AppDatabase_Impl will be generated and it will have all necessary table creations. You can get the query from there
2. You include exportSchema = true in your @Database annotation and it will create versionNumber.json schema of Room database within your schemas folder. You can get the create script from there.

But, both of above methods will require you to run the app without any proper migration (as you don't know correct query) and it will definitely crash. And after that, you have correct query which you can include in your migration method. I think this is not "professional" way of doing it. Plus even after you get the long SQLite query, it's not PR friendly and it's not just good practice to have long SQLite query which is can not be easily debugged.

So, I wanted to make object oriented way of creating tables while migrating. The only approach that I could think of was, obviously, using model data class and generate query according to each field of model. It will supposedly be like this

fun createTable(db: SupportSQLiteDatabase, clazz: KClass<*>) {
    val fields = extractColumns(clazz)
    val primaryKeys = fields
            .filter { it.primaryKey }
            .map { it.name }

    val createQuery = "CREATE TABLE IF NOT EXISTS `${clazz.simpleName}` (" +
            fields.joinToString(", ") { "`${it.name}` ${it.type} ${it.nonNull}" } +
            ", PRIMARY KEY (" + primaryKeys.joinToString(",") { "`$it`" } +
            "))"
    db.execSQL(createQuery)
}

fun extractColumns(clazz: KClass<*>): Array<Column>{
    val columns = ArrayList<Column>()
    for (field in clazz.declaredMemberProperties){
        val name = field.findAnnotation<ColumnInfo>()?.name ?: field.name
        val type = getSqlType(field.returnType)
        val nonNull = if (field.returnType.isMarkedNullable) "" else "NON NULL"
        val primaryKey = field.findAnnotation<PrimaryKey>() != null
        columns.add(Column(name, type, nonNull, primaryKey))
    }
    return columns.toTypedArray()
}

But the problem is Room Annotations are all annotated with @Retention(RetentionPolicy.CLASS) which is can only be accessed during compile time. They are not available during run time. So all of my findAnnotation methods will return null. I was thinking of doing creating during compile time, but couldn't think of how.

So, my question was is there any way to generate CREATE scripts during compile time and if so how to do it?

Apart from my mentioned way of solving, is there any other way of creating tables which doesn't involve first two methods of copy pasting?

And by the way, I am not considering fallbackToDestructiveMigration. I mean, who would want their users to lose all data?

Answer 1

As of current update of Room there is actually the way to create SQL queries using Annotation Processing. With Annotation processing you have to wrote small library that generate Room queries for you when you build it.

Creating Annotation Processing Library is not straightforward and here is related issue.

Room database migration if only new table is added