PRAGMA: Migration Helper

LDR: Migration made simple. Check out the code to know how !!

#TLDR: Migration made simple. Check out 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

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

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

I then use totalColumns to create a new table

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

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 its name and it’s unique or not.

Here’s how I did it

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

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 its 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.

Originally published at https://engineering.apna.co on March 30, 2021.