Android Room Database migration helper for ALTER TABLE

DbMigrationsHelper.kt

object Example {
    
    fun alterTableUsage(database: SupportSQLiteDatabase) {
        DbMigrationsHelper.alterTable(
            db = database,
            tableName = "Reservations",
            columns = mapOf(
                "id INTEGER".toExisting(), // Retains without changes
                "title TEXT".toExisting("name"), // Renames column "name" to "title"
                "description TEXT".toNothing(), // Adds a new column
                "creatorId INTEGER NOT NULL".toExisting() // Change scheme from "creatorId INTEGER" and add "NON NULL"
                // Any columns that existed in the "Reservations" table
                // and aren't specified in this map will be removed
            ),
            primaryKeys = listOf("id") // Add more than one key to create a compound primary key
        )
    }
}

object DbMigrationsHelper {

    /**
     * Helper for altering tables. Handles creating a temporary table with the desired fields,
     * and filling it with values from the old table and then dropping the old table and renaming the new one.
     *
     *  Supported operations: Add, Delete, Rename or Change Scheme of a column.
     *
     *  To Retain an existing column, it has to be defined in [columns] otherwise it will be Deleted.
     *
     * To Add a new column or Retain an existing column, use one of:
     *  - `"foo INTEGER".toNothing() // Add/Retain column with null value (or default value if defined in schema).`
     *  - `"foo INTEGER".toExisting() // Retain column with its existing value from previous version of this table.`
     *  - `"foo INTEGER".toExisting("bar") // Add/Retain column with a value from another existing column.`
     *  - `"foo INTEGER".toExisting("COALESCE(bar, baz)") // Add/Retain column with a value from the first non-null column in the COALESCE statement.`
     *
     * To Delete a column, omit it from the [columns] map.
     *
     * To Rename a column, use: `"foo INTEGER".toExisting("bar")`, which will map the value of `bar` column to the `foo` column.
     *
     * To Change Scheme of a column, specify the new scheme in the [columns]' key, e.g:
     *  - `"foo TEXT NOT NULL".toExisting("bar")
     */
    private fun alterTable(db: SupportSQLiteDatabase, tableName: String, columns: Map<String, String?>, primaryKeys: List<String>) {
        db.execSQL(
            "CREATE TABLE ${tableName}_temp (" + columns.map { it.key }.joinToString() + ", PRIMARY KEY(${primaryKeys.joinToString()}))"
        )

        /** Filters only columns that want to mapped to another column. */
        val columnsWithMapping = columns.filterValues { it != null }

        db.execSQL(
            "INSERT INTO ${tableName}_temp (" + columnsWithMapping.map { it.key.substringBefore(' ') }.joinToString() + ") " +
                    "SELECT " + columnsWithMapping.map { it.value }.joinToString() + " FROM $tableName"
        )

        db.execSQL("DROP TABLE $tableName")
        db.execSQL("ALTER TABLE ${tableName}_temp RENAME TO $tableName")
    }
    
    /**
     * Indicates that this column should copy the value from an existing column of this table.
     * If [column] is null, the value will be copied from [this] column.
     * If a [column] is specified, value will be copied from it.
     */
    private fun String.toExisting(column: String? = null): Pair<String, String> =
        Pair(this, column ?: this.substringBefore(' '))

    /**
     * Creates a pairing to a null value, indicating there is no previous column to copy a value from.
     * Used when adding new columns.
     * */
    private fun String.toNothing(): Pair<String, Nothing?> = Pair(this, null)
}

For future readers: Please note that this won't work if you have any foreign keys and/or indices on the table. You will get errors like "Migration didn't properly handle: [...] during the migration.

One possible solution for this is simply passing some additional arguments to the alterTable function. For me, I solved it like this:

Call:

 DbMigrationsHelper.alterTable(
            db = database,
            tableName = "items",
            columns = mapOf(
                "id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT".toExisting(),
                "title TEXT NOT NULL".toExisting(),
                "added_at INTEGER NOT NULL".toExisting("created_at"),
                "author_id INTEGER".toExisting()
            ),
            foreignKeyQuery = "FOREIGN KEY (author_id) REFERENCES authors(id) ON DELETE SET NULL ON UPDATE NO ACTION",
            indicesQuery = "CREATE INDEX index_author_id ON items(author_id);"
        )

 fun alterTable(db: SupportSQLiteDatabase, tableName: String, columns: Map<String, String?>, foreignKeyQuery: String? = null, indicesQuery: String? = null) {
    
        var createQuery = "CREATE TABLE ${tableName}_temp (" + columns.map { it.key }.joinToString()
        foreignKeyQuery?.let {
            createQuery += ", $foreignKeyQuery"
        }
        createQuery += ")"
        db.execSQL(createQuery)

        /** Filters only columns that want to mapped to another column. */
        val columnsWithMapping = columns.filterValues { it != null }

        db.execSQL(
            "INSERT INTO ${tableName}_temp (" + columnsWithMapping.map { it.key.substringBefore(' ') }.joinToString() + ") " +
                    "SELECT " + columnsWithMapping.map { it.value }.joinToString() + " FROM $tableName"
        )

        db.execSQL("DROP TABLE $tableName")
        db.execSQL("ALTER TABLE ${tableName}_temp RENAME TO $tableName")
        indicesQuery?.let {
            db.execSQL(indicesQuery)
        }
}