Migration Helper

TLDR: Migration made simple. Checkout the code at MigrationHelper

We at Apna use Room for sql operations. A few days back I had to drop a column from one of the tables. Dropping a column is a painful ordeal.

Steps to drop a column in sqlite in android are as followed :

• Create a temp table identical to the original table without the column in question
• Copy the data from the first table and add it to the second one.
• Drop the first table
• Rename the temp table to the original table name and viola we have a brand new table with the column dropped and data intact.

I have attached a code snippet from the project with table name and field names changed which should give you a picture of what is to be done in the code.

The problem with writing this is we don’t exactly have the definition of the table since we left that to Room. Sure there are ways to find that out and we would test it again and again to make sure everything works well. But it adds to the development time.

Is this the best we can do?

I found one solution while I was looking for this. But I thought it will make my life only slightly easier. I still have to do write everything.

I started digging more and I came across PRAGMA. What PRAGMA does is that it will give you information about the table. The data type, default value, if it is a primary key or not and name of the field of course.

Can we put PRAGMA to use and write a reusable method that does all this heavy lifting for us? Glad you asked.

I created a helper method for the same.

First I asked for all the fields and their information using PRAGMA

val existingCols = db.query("PRAGMA table_info(\"${tableName}\")")
        .use { cursor ->
            cursor.moveToFirst()
            val mutableSetOf = mutableSetOf<ColumnData>()
            do {
                val name = cursor.getString(1)
                val type = cursor.getString(2)
                val isNotNull = cursor.getInt(3) == 1
                val defaultValue = cursor.getString(4)
                val isPrimaryKey = cursor.getInt(5) == 1
                val element = ColumnData(name, type, isNotNull, defaultValue, isPrimaryKey)
                mutableSetOf.add(element)
            } while (cursor.moveToNext())
            return@use mutableSetOf.toSet()
        }

This will give me the field names and it’s type, if it’s nullable, it’s default value and if it’s a primary key.

I simply remove the fields I don’t want from existingCols.

val totalColumns = existingCols subtract dropColumns

I then use totalColumns to create a new table

val tempDbName = "${tableName}_temp"
val tableCreateScript = "CREATE TABLE $tempDbName (" +
        totalColumns.joinToString { it.toScript } +
        ", PRIMARY KEY(${primaryKeys.joinToString { it.key }}))"


db.execSQL(tableCreateScript)

I then copy data from the original table to the temp table. Drop the original table and rename the temp table to the original table.

val existingNotDropped = existingColumns subtract dropColumns

val insertionScript = "INSERT INTO $tempDbName (" +
        existingNotDropped.joinToString { "`${it.key}`" } + ") " +
        "SELECT " + existingNotDropped.joinToString { "`${it.key}`" } + " FROM $tableName"

db.execSQL(insertionScript)
db.execSQL("DROP TABLE $tableName")
db.execSQL("ALTER TABLE $tempDbName RENAME TO $tableName")

And that’s it.

But this alone did not solve my problem since in my case indices were included as well. So I had to find a solution to that problem too.

PRAGMA will help you with that as well. It’s index_list will give you information about the indices on that table. But it wasn’t giving me the field name on which it was indexed. PRAGMA’s index_info method will give you that information. index_info will give you information about it’s name and it’s unique or not.

Here’s how I did it

val indices = query("PRAGMA index_list(\"${tableName}\")").use { cursor ->
    cursor.moveToFirst()
    val indexes = mutableSetOf<IndexData>()
    do {
        val indexName = cursor.getString(cursor.getColumnIndex("name"))
        val unique = cursor.getInt(cursor.getColumnIndex("unique")) == 1
        val indexOn = query("PRAGMA index_info(\"${indexName}\")")
                .use {
                    it.moveToFirst()
                    it.getString(it.getColumnIndex("name"))
                }
        indexes.add(IndexData(indexName, indexOn, unique))
    } while (cursor.moveToNext())
    return@use indexes.toList()
}

You can then add this index data on the temp table or after the migration is complete.

if (indices.isNotEmpty()) {
    for (index in indices) {
        val script = index.getScript(tableName)
        db.execSQL(script)
    }
}

The method getScript is a helper method that will give the script for creating an index.

The complete code can be found at https://github.com/ninad458/MigrationHelper

What it can do

• Add or remove columns from a table
• Handle the indices as is

What it can’t do

• Create/delete/edit indices
• Foreign key mapping

I will be updating the code to accommodate it’s shortcomings. But for general use case it should suffice. Feel free to use the code. PRs are welcome.

Oh, also, Apna is currently hiring rockstars across all verticals. If you want to be part of a mission that endeavours to create livelihood opportunities for Bharat, please write to us at careers@apna.co with a short line or two about why you would be a perfect fit.