Database migrations in Android Kotlin - Deep Dive

Overview - Database migrations

What is it?

Database migrations are the process of changing a database's structure over time without losing existing data. In mobile apps, this means updating tables, columns, or indexes when the app updates. Migrations help keep the app's data organized and compatible with new features. They ensure users don't lose their data when the app changes its database.

Why it matters

Without migrations, updating an app's database could erase or corrupt user data, causing frustration and loss of trust. Migrations solve this by smoothly transforming the database structure while keeping data safe. This allows apps to evolve and add features without breaking existing data or user experience.

Where it fits

Before learning migrations, you should understand basic database concepts like tables and queries, and how to use SQLite or Room in Android. After migrations, you can explore advanced database optimization, version control for databases, and multi-user data syncing.

Mental Model

Core Idea

Database migrations are like carefully remodeling a house while its residents still live inside, changing the structure without losing anything important.

Think of it like...

Imagine you live in a house and want to add a new room or change the kitchen layout. You can't just tear down everything and start fresh because your belongings and daily life depend on the house. Instead, you plan and do the changes step-by-step, making sure nothing gets lost or broken. Database migrations work the same way for app data.

┌───────────────┐      ┌───────────────┐      ┌───────────────┐
│ Old Database  │─────▶│ Migration     │─────▶│ New Database  │
│ Structure    │      │ Steps         │      │ Structure    │
│ (Version N)  │      │ (Version N+1) │      │ (Version N+1)│
└───────────────┘      └───────────────┘      └───────────────┘

Build-Up - 6 Steps

1

Foundation

Understanding database versions

Concept: Databases have versions to track their structure changes over time.

In Android apps, each database has a version number. When you change the database schema, you increase this version. The app uses this number to know if it needs to update the database structure when it starts.

Result

The app can detect when the database schema has changed and prepare to update it.

Knowing that the database version controls when migrations run helps you manage schema changes safely.

2

Foundation

Basic migration steps in Room

Concept: Room library uses Migration objects to define how to change the database from one version to another.

You create a Migration class specifying the start and end versions and write SQL commands to alter tables or add columns. Room runs these commands automatically when the app detects a version change.

Result

The database schema updates without losing existing data.

Understanding how to write Migration objects is the foundation for safe database updates.

3

Intermediate

Handling column additions safely

🤔Before reading on: do you think adding a new column requires deleting the table or just altering it? Commit to your answer.

Concept: Adding columns can be done with simple SQL ALTER commands without losing data.

To add a new column, you use SQL like: ALTER TABLE table_name ADD COLUMN new_column TYPE DEFAULT value. This keeps existing rows intact and sets a default for the new column.

Result

The database now has the new column, and old data remains safe.

Knowing that ALTER TABLE can add columns without data loss prevents destructive migrations.

4

Intermediate

Complex migrations with data transformation

🤔Before reading on: do you think you can rename a column directly in SQLite? Commit to your answer.

Concept: Some schema changes require creating new tables and copying data to transform it safely.

SQLite does not support renaming columns directly. To rename, you create a new table with the desired schema, copy data from the old table, drop the old table, and rename the new one. This process preserves data while changing structure.

Result

The database schema changes as needed, and data is preserved through careful copying.

Understanding when to copy data between tables helps handle complex migrations safely.

5

Advanced

Testing migrations before release

🤔Before reading on: do you think migrations always work perfectly on the first try? Commit to your answer.

Concept: Testing migrations ensures they work correctly on real user data before app release.

Android provides tools to test migrations by creating databases at old versions, applying migrations, and verifying the new schema and data integrity. This prevents crashes and data loss in production.

Result

You catch migration bugs early and deliver stable app updates.

Knowing how to test migrations protects users from data loss and app crashes.

6

Expert

Handling multi-step and conditional migrations

🤔Before reading on: do you think migrations must always be linear and simple? Commit to your answer.

Concept: Real apps may need multiple migrations and conditional logic depending on the current database state.

You can chain multiple Migration objects for each version step. Sometimes, migrations check existing data or schema details to decide how to proceed. This flexibility handles complex update paths and backward compatibility.

Result

The app can upgrade databases from many old versions safely and correctly.

Understanding multi-step and conditional migrations is key to maintaining apps with long histories and many users.

Under the Hood

When the app starts, Room compares the current database version with the version coded in the app. If the app version is higher, Room runs the Migration objects in order. Each Migration executes SQL commands on the SQLite database to alter tables, add columns, or copy data. This happens inside a transaction to ensure all changes succeed or none do, preventing partial updates.

Why designed this way?

Room uses version numbers and Migration objects to give developers control over database changes while protecting user data. SQLite's limited ALTER TABLE support means Room must sometimes recreate tables. The design balances safety, flexibility, and simplicity for mobile apps with evolving data needs.

App Start
  │
  ▼
Check DB Version
  │
  ▼
Is app DB version > current DB version?
  │ Yes
  ▼
Run Migrations in order
  │
  ▼
Execute SQL commands inside transaction
  │
  ▼
Update DB version
  │
  ▼
App uses updated DB

Myth Busters - 4 Common Misconceptions

Quick: Do you think you can rename a column in SQLite with a simple ALTER command? Commit to yes or no.

Common Belief: You can rename columns directly using ALTER TABLE commands.

Reality: SQLite does not support renaming columns directly; you must create a new table and copy data.

Why it matters: Trying to rename columns directly causes migration failures and data loss.

Quick: Do you think migrations automatically run without developer code? Commit to yes or no.

Common Belief: Migrations happen automatically without writing migration code.

Reality: Developers must write Migration objects to define how to update the database schema.

Why it matters: Without migration code, the app crashes or loses data when the schema changes.

Quick: Do you think increasing the database version number alone updates the schema? Commit to yes or no.

Common Belief: Just increasing the database version number updates the schema automatically.

Reality: Increasing the version number only triggers migrations; actual schema changes require migration code.

Why it matters: Without migration code, the database structure stays old, causing app errors.

Quick: Do you think testing migrations is optional and safe to skip? Commit to yes or no.

Common Belief: Migrations are simple and don't need testing before release.

Reality: Migrations can fail or corrupt data if not tested carefully.

Why it matters: Skipping migration tests risks data loss and app crashes in production.

Expert Zone

1

Migration order matters: migrations must be applied in the correct sequence to avoid schema conflicts.

2

Transactions protect migrations: running migrations inside transactions ensures partial failures don't corrupt data.

3

Handling user data variations: migrations sometimes need conditional logic to handle unexpected or corrupted data states.

When NOT to use

For very simple apps with no schema changes, migrations may be unnecessary. For complex data syncing or multi-user databases, consider server-side migrations or cloud databases instead.

Production Patterns

In production, developers maintain a migration history, write tests for each migration, and use feature flags to roll out database changes gradually. They also monitor crash reports to catch migration issues early.

Connections

Version Control Systems

Both track changes over time and allow safe updates.

Understanding how migrations track database versions is similar to how Git tracks code changes, helping manage evolution safely.

Software Deployment Pipelines

Migrations are part of the deployment process ensuring data compatibility.

Knowing migrations helps understand how apps update smoothly without breaking user experience during releases.

Building Renovation

Both involve changing structures while preserving existing use.

Recognizing that database migrations are like renovating a building helps appreciate the care needed to avoid disruption.

Common Pitfalls

#1 Forgetting to write migration code after increasing database version.

Wrong approach:

val db = Room.databaseBuilder(context, AppDatabase::class.java, "app.db")
.fallbackToDestructiveMigration()
.build()

Correct approach:

val migration1to2 = object : Migration(1, 2) {
override fun migrate(database: SupportSQLiteDatabase) {
database.execSQL("ALTER TABLE users ADD COLUMN age INTEGER DEFAULT 0 NOT NULL")
}
}
val db = Room.databaseBuilder(context, AppDatabase::class.java, "app.db")
.addMigrations(migration1to2)
.build()

Root cause:

Assuming Room updates schema automatically without explicit migration code.

#2 Trying to rename a column with ALTER TABLE RENAME COLUMN (unsupported in SQLite).

Wrong approach:

database.execSQL("ALTER TABLE users RENAME COLUMN username TO user_name")

Correct approach:

database.execSQL("CREATE TABLE users_new (user_name TEXT, age INTEGER)")
database.execSQL("INSERT INTO users_new (user_name, age) SELECT username, age FROM users")
database.execSQL("DROP TABLE users")
database.execSQL("ALTER TABLE users_new RENAME TO users")

Root cause:

Not knowing SQLite's limitations on ALTER TABLE commands.

#3 Not testing migrations before releasing the app.

Wrong approach:

// No migration tests written or run

Correct approach:

Use MigrationTestHelper to create old version DB, run migration, and verify schema and data.

Root cause:

Underestimating the complexity and risk of migrations.

Key Takeaways

Database migrations let you change app data structures safely without losing user data.

You must write migration code to tell the app how to update the database schema between versions.

Some schema changes need complex steps like copying data to new tables because SQLite has limited ALTER commands.

Testing migrations before releasing prevents data loss and app crashes.

Understanding migrations is essential for maintaining and evolving mobile apps with persistent data.

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