Drop column Room migration android

Question

I have a table Server

@Entity(tableName = "servers")
data class ServerDto(

    @PrimaryKey
    @ColumnInfo(name = "server_id")
    var serverId: Long,

    @ColumnInfo(name = "address", defaultValue = "")
    var serverAddress: String,

    @ColumnInfo(name = "description", defaultValue = "")
    var serverDescription: String,

    @ColumnInfo(name = "file_segment")
    var fileSegment: Int

) : Serializable

and a table accounts

@Entity(tableName = "accounts", primaryKeys = ["server_id", "account_id"])
data class AccountDto(

    @ColumnInfo(name = "server_id")
    val serverId: Long,

    @ColumnInfo(name = "account_id")
    val accountId: Int,

  @ColumnInfo(name = "username", defaultValue = "")
    val username: String,

    @ColumnInfo(name = "password", defaultValue = "")
    val password: String,

    @ColumnInfo(name = "first_name", defaultValue = "")
    var firstname: String,

    @ColumnInfo(name = "last_name", defaultValue = "")
    var lastname: String,

    @ColumnInfo(name = "email", defaultValue = "")
    var email: String,

    @ColumnInfo(name = "active")
    var active: Int

) : Serializable

I want to transfer 2 columns (username, password) from accounts to server and then remove them from accounts. I wrote the Migrations but i see that Android Studio does not allow to write DROP COLUMN since it underlines it with red. What is wrong with that??

override fun migrate(database: SupportSQLiteDatabase) {
        database.execSQL("ALTER TABLE servers ADD COLUMN username VARCHAR")
        database.execSQL("ALTER TABLE servers ADD COLUMN password VARCHAR")
        database.execSQL("UPDATE servers SET username = (SELECT a.username FROM accounts a where a.server_id = servers.server_id and a.active = 1)")
        database.execSQL("UPDATE servers SET password = (SELECT a.password FROM accounts a where a.server_id = servers.server_id and a.active = 1)")
        database.execSQL("ALTER TABLE accounts ***DROP*** COLUMN username")
        database.execSQL("ALTER TABLE accounts ***DROP*** COLUMN password")
    }

I'll not answer your question because I'm not experienced with SQLite enough to answer it with confidence but this [old question](https://stackoverflow.com/questions/8442147/how-to-delete-or-add-column-in-sqlite) seems to indicate that SQLite does not support deleting columns. This [other one](https://stackoverflow.com/questions/5938048/delete-column-from-sqlite-table/5987838#5987838) addresses how to hack it. But since the question are old, maybe SQLite added support for it in the meantime. — Eduardo macedo, Feb 01 '20 at 03:18

score 15 · Accepted Answer · answered Feb 03 '20 at 15:09

SQLite doesn't support column deletion straight away. You will have to do migration steps as follows: Source: https://www.sqlite.org/faq.html#q11

CREATE TABLE accounts_backup(serverId VARCHAR, accountId VARCHAR, firstname VARCHAR, lastname VARCHAR, email VARCHAR, active VARCHAR);

INSERT INTO accounts_backup SELECT serverId, accountId, firstname, lastname, email, active FROM accounts;

DROP TABLE accounts;

CREATE TABLE accounts(serverId VARCHAR, accountId VARCHAR, firstname VARCHAR, lastname VARCHAR, email VARCHAR, active VARCHAR);

INSERT INTO accounts SELECT serverId, accountId, firstname, lastname, email, active FROM accounts_backup;

DROP TABLE accounts_backup;

I think the last 3 statements can be replaced by ALTER TABLE accounts_backup RENAME TO accounts — Anders Ullnæss, Jul 30 '21 at 08:53

score 13 · Answer 2 · answered Aug 02 '21 at 12:55

Here's a combination of the previous two answers, written in Kotlin for Android:

private val MIGRATION_3_2 = object : Migration(3,2) {
        override fun migrate(database: SupportSQLiteDatabase) {
            //Drop column isn't supported by SQLite, so the data must manually be moved
            with(database) {
                execSQL("CREATE TABLE Users_Backup (id INTEGER, name TEXT, PRIMARY KEY (id))")
                execSQL("INSERT INTO Users_Backup SELECT id, name FROM Users")
                execSQL("DROP TABLE Users")
                execSQL("ALTER TABLE Users_Backup RENAME to Users")
            }
        }
    }

I couldn't get this to work without my app freezing indefinitely, so I ended up just updating my unwanted columns to empty values. ie.) `execSQL("UPDATE users_table SET middleName = ''")`, not the cleanest solution but it just works. — lasec0203, Nov 23 '21 at 03:29

score 0 · Answer 3 · answered Aug 21 '23 at 20:16

Since this post is still coming at the top of the search result, I think it would be better to update it to the easiest solution:

Create a class that implements AutoMigrationSpec:

@DeleteColumn(
     tableName = "concerned_table_name", 
     columnName = "column_to_delete"
)
class RemoveSomeColumnMigration: AutoMigrationSpec

Then add it to your DB class migrations:

@Database(
    entities = [MyEntity::class],
    version = 10,
    autoMigrations = [
        //.....
        AutoMigration(from = 9, to = 10, spec = RemoveSomeColumnMigration::class)
    ]
)
abstract class AppDB : RoomDatabase() {
    //.....
}

Drop column Room migration android

3 Answers3