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.