Database migrations in Android Kotlin - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to define a Room database version.

Android Kotlin

val db = Room.databaseBuilder(context, AppDatabase::class.java, "app_db")
  .version([1])
  .build()

Drag options to blanks, or click blank then click option'

A2

B1

C3

D0

Attempts:

3 left

💡 Hint

Common Mistakes

Using version 0 which is invalid

Skipping version number

The initial version of a Room database is usually set to 1.

2fill in blank

medium

Complete the code to add a migration from version 1 to 2.

Android Kotlin

val MIGRATION_1_2 = object : Migration(1, 2) {
  override fun migrate(database: SupportSQLiteDatabase) {
    database.execSQL([1])
  }
}

Drag options to blanks, or click blank then click option'

A"ALTER TABLE users ADD COLUMN age INTEGER DEFAULT 0"

B"DROP TABLE users"

C"CREATE TABLE users"

D"SELECT * FROM users"

Attempts:

3 left

💡 Hint

Common Mistakes

Dropping tables accidentally

Using SELECT instead of ALTER

To migrate from version 1 to 2, we add a new column 'age' with a default value.

3fill in blank

hard

Fix the error in the migration code to rename a table.

Android Kotlin

val MIGRATION_2_3 = object : Migration(2, 3) {
  override fun migrate(database: SupportSQLiteDatabase) {
    database.execSQL("ALTER TABLE users RENAME TO [1]")
  }
}

Drag options to blanks, or click blank then click option'

Ausers

Busers_new

Cusers_old

Duser

Attempts:

3 left

💡 Hint

Common Mistakes

Using the same name as original

Using invalid table names

To rename the table 'users' to 'users_old', the new name must be specified correctly.

4fill in blank

hard

Fill both blanks to create a migration that creates a new table.

Android Kotlin

val MIGRATION_3_4 = object : Migration(3, 4) {
  override fun migrate(database: SupportSQLiteDatabase) {
    database.execSQL("CREATE TABLE [1] (id INTEGER PRIMARY KEY, name TEXT)")
    database.execSQL("INSERT INTO [2] (id, name) VALUES (1, 'Alice')")
  }
}

Drag options to blanks, or click blank then click option'

Aemployees

Busers

Ccustomers

Dstaff

Attempts:

3 left

💡 Hint

Common Mistakes

Using different table names in CREATE and INSERT

Using reserved keywords as table names

Both blanks must use the same table name 'employees' to create and insert data correctly.

5fill in blank

hard

Fill all three blanks to define a migration that adds a new column and updates data.

Android Kotlin

val MIGRATION_4_5 = object : Migration(4, 5) {
  override fun migrate(database: SupportSQLiteDatabase) {
    database.execSQL("ALTER TABLE [1] ADD COLUMN [2] INTEGER DEFAULT 0")
    database.execSQL("UPDATE [3] SET [2] = 10 WHERE id = 1")
  }
}

Drag options to blanks, or click blank then click option'

Ausers

Bage

Dscore

Attempts:

3 left

💡 Hint

Common Mistakes

Using different table names in ALTER and UPDATE

Using invalid column names

The migration adds a new column 'score' to the 'users' table and updates it for a specific row.

Practice

1.

What is the main purpose of a database migration in Android apps?

easy

A. To update the database schema without losing existing data

B. To delete all data and recreate the database from scratch

C. To backup the database to external storage

D. To optimize database queries for faster performance

Solution

1. Step 1: Understand database migration purpose

   Database migrations are used to change the structure of the database safely.

2. Step 2: Identify data preservation goal

   Migrations update tables or columns without deleting existing user data.

3. Final Answer:

   To update the database schema without losing existing data -> Option A

4. Quick Check:

   Migration = Safe schema update [OK]

Hint: Migrations keep data safe while changing schema [OK]

Common Mistakes:

• Thinking migrations delete all data
• Confusing migrations with backups
• Believing migrations only optimize queries

2.

Which of the following is the correct way to define a migration from version 1 to 2 in Room database?

val MIGRATION_1_2 = object : Migration(1, 2) {
    override fun migrate(database: SupportSQLiteDatabase) {
        // ?
    }
}

easy

A. database.execSQL("ALTER TABLE users ADD COLUMN age INTEGER DEFAULT 0")

B. database.update("users", "age", 0)

C. database.insert("users", ContentValues())

D. database.delete("users")

Solution

1. Step 1: Understand Migration class usage

   Migration requires SQL commands inside migrate() to change schema.

2. Step 2: Identify correct SQL command

   ALTER TABLE adds a new column safely; execSQL runs raw SQL.

3. Final Answer:

   database.execSQL("ALTER TABLE users ADD COLUMN age INTEGER DEFAULT 0") -> Option A

4. Quick Check:

   Migration uses execSQL with ALTER TABLE [OK]

Hint: Use execSQL with ALTER TABLE to add columns [OK]

Common Mistakes:

• Using update() instead of execSQL
• Trying to insert or delete rows in migration
• Not providing default value for new column

3.

Given this migration code, what will be the result after migrating from version 1 to 2?

val MIGRATION_1_2 = object : Migration(1, 2) {
    override fun migrate(database: SupportSQLiteDatabase) {
        database.execSQL("CREATE TABLE IF NOT EXISTS books (id INTEGER PRIMARY KEY, title TEXT)")
    }
}

Assuming version 1 had no books table.

medium

A. No changes happen because CREATE TABLE is ignored

B. Migration fails because table 'books' already exists

C. A new table 'books' is created with columns 'id' and 'title'

D. Existing tables are deleted and only 'books' remains

Solution

1. Step 1: Analyze migration SQL command

   CREATE TABLE IF NOT EXISTS creates the table only if it doesn't exist.

2. Step 2: Check initial database state

   Version 1 has no 'books' table, so it will be created.

3. Final Answer:

   A new table 'books' is created with columns 'id' and 'title' -> Option C

4. Quick Check:

   CREATE TABLE IF NOT EXISTS creates missing table [OK]

Hint: IF NOT EXISTS avoids errors if table exists [OK]

Common Mistakes:

• Assuming migration fails if table exists
• Thinking CREATE TABLE is ignored
• Believing existing tables get deleted

4.

What is wrong with this migration code from version 2 to 3?

val MIGRATION_2_3 = object : Migration(2, 3) {
    override fun migrate(database: SupportSQLiteDatabase) {
        database.execSQL("ALTER TABLE users ADD COLUMN email TEXT")
        database.execSQL("ALTER TABLE users ADD COLUMN email TEXT")
    }
}

medium

A. The migration does not update the database version

B. The same column 'email' is added twice causing an error

C. The SQL syntax for ALTER TABLE is incorrect

D. The migration should delete the 'users' table first

Solution

1. Step 1: Review SQL commands in migration

   The code tries to add the 'email' column twice to the same table.

2. Step 2: Understand SQLite behavior

   Adding the same column twice causes a runtime error during migration.

3. Final Answer:

   The same column 'email' is added twice causing an error -> Option B

4. Quick Check:

   Duplicate column addition causes migration failure [OK]

Hint: Avoid adding same column twice in one migration [OK]

Common Mistakes:

• Ignoring duplicate column errors
• Thinking migration version is updated automatically
• Believing ALTER TABLE syntax is wrong here

5.

You want to rename a column username to user_name in your Room database from version 3 to 4. SQLite does not support renaming columns directly. Which migration strategy is correct?

hard

A. Add a new column and leave the old column as is

B. Use ALTER TABLE to rename the column directly

C. Delete the old table and create a new one with the new column name

D. Create a new table with the new column name, copy data, drop old table, rename new table

Solution

1. Step 1: Understand SQLite column rename limitation

   SQLite does not support direct column renaming with ALTER TABLE.

2. Step 2: Apply recommended migration pattern

   Create a new table with desired schema, copy data, drop old table, rename new table.

3. Final Answer:

   Create a new table with the new column name, copy data, drop old table, rename new table -> Option D

4. Quick Check:

   Rename column = new table + copy data [OK]

Hint: Rename columns by new table + copy data [OK]

Common Mistakes:

• Trying to rename column with ALTER TABLE
• Deleting old table without copying data
• Adding new column but not removing old one