Upgrade the database table without losing the existing data of the app in Android
Upgrading an Android SQLite database without losing data is a fundamental task. By default when we do the upgrade it generally drop the table and recreate the table again. In this the existing user data will be lost and create a major issue with existing user. Here’s a clear, step-by-step guide on how to do it correctly using the SQLiteOpenHelper.onUpgrade() method.
Database Migration
A migration is the process of moving your database from one version to another. You write SQL commands to alter the database structure (e.g., ALTER TABLE, CREATE TABLE) instead of just dropping and recreating it.
1. Define Your Database Contract
First, always define your table structure in a contract class. This makes managing columns much easier.
// Contract for the "notes" table
object NoteEntry : BaseColumns {
const val TABLE_NAME = "notes"
const val COLUMN_NAME_TITLE = "title"
const val COLUMN_NAME_BODY = "body"
const val COLUMN_NAME_TIMESTAMP = "timestamp"
// New column we want to add in version 2
const val COLUMN_NAME_IS_FAVORITE = "is_favorite" // INTEGER (0 for false, 1 for true)
}
2. Implement SQLiteOpenHelper
Your DatabaseHelper class extends SQLiteOpenHelper. The key is the onUpgrade() method.
import android.content.Context
import android.database.sqlite.SQLiteDatabase
import android.database.sqlite.SQLiteOpenHelper
class DatabaseHelper(context: Context) : SQLiteOpenHelper(
context,
DATABASE_NAME,
null,
DATABASE_VERSION // <- Increment this to trigger onUpgrade
) {
companion object {
const val DATABASE_NAME = "myapp.db"
const val DATABASE_VERSION = 2 // <- INCREMENT THIS NUMBER
}
// SQL string to create the initial table
private val SQL_CREATE_ENTRIES = """
CREATE TABLE ${NoteEntry.TABLE_NAME} (
${BaseColumns._ID} INTEGER PRIMARY KEY,
${NoteEntry.COLUMN_NAME_TITLE} TEXT,
${NoteEntry.COLUMN_NAME_BODY} TEXT,
${NoteEntry.COLUMN_NAME_TIMESTAMP} INTEGER
)
""".trimIndent()
// SQL string to DROP the table (for downgrade or full reset - use cautiously!)
private val SQL_DELETE_ENTRIES = "DROP TABLE IF EXISTS ${NoteEntry.TABLE_NAME}"
override fun onCreate(db: SQLiteDatabase) {
// This is called the FIRST time the database is created.
db.execSQL(SQL_CREATE_ENTRIES)
}
override fun onUpgrade(db: SQLiteDatabase, oldVersion: Int, newVersion: Int) {
// This is called when DATABASE_VERSION is increased.
// We use a sequential "path" of upgrades for users skipping multiple versions.
if (oldVersion < 2) {
// Upgrade from v1 to v2: Add the 'is_favorite' column
db.execSQL("ALTER TABLE ${NoteEntry.TABLE_NAME} ADD COLUMN ${NoteEntry.COLUMN_NAME_IS_FAVORITE} INTEGER DEFAULT 0")
}
if (oldVersion < 3) {
// If we had a version 3, we would put the upgrade logic here.
// Example: Create a new table 'users'
// db.execSQL("CREATE TABLE users (...));")
}
// ... and so on for each subsequent version.
}
override fun onDowngrade(db: SQLiteDatabase, oldVersion: Int, newVersion: Int) {
// This is not always implemented, but it's good practice.
// Handle downgrade by resetting the database. WARNING: This deletes data!
onUpgrade(db, oldVersion, newVersion) // Not always the right choice.
// A better approach is often to throw an exception to prevent accidental data loss.
// throw SQLiteException("Can't downgrade database from version $oldVersion to $newVersion.")
}
}
3. Update Your App Code
After changing the DATABASE_VERSION constant, the next time your app runs, onUpgrade() will be called automatically, and the new column will be added. Your existing data will remain intact.
Best Practices & Important Considerations
-
Never Modify
onUpgrade()Logic for a Version After Release:-
Once a version of your app is in users’ hands, the
onUpgrade()path for that version is set in stone. You cannot change it. If you need to change the upgrade logic for v1->v2, you must introduce a new version (e.g., v4) and handle it there.
-
-
Use a Migration Path:
-
The
if (oldVersion < X)pattern is crucial. A user might skip an update (e.g., from v1 directly to v3). Your code must apply all necessary intermediate upgrades sequentially.
-
-
Test Migrations Thoroughly:
-
Export your database from an old version of your app (e.g., using Device File Explorer in Android Studio).
-
Install the new version of your app with the increased database version.
-
The app should run without crashes, and the new column should be present with the correct default values.
-
Verify your existing data is still there.
-
-
Complex Schema Changes:
-
ALTER TABLEhas limitations. You cannot rename a column or remove a column directly. For complex changes, you must create a new table, copy the data, drop the old table, and rename the new one.
-
Example: Renaming a Column (a common complex task)
if (oldVersion < 2) {
// We want to rename 'body' to 'content'
db.execSQL("ALTER TABLE ${NoteEntry.TABLE_NAME} RENAME TO notes_old;")
val SQL_CREATE_NEW_ENTRIES = """
CREATE TABLE ${NoteEntry.TABLE_NAME} (
${BaseColumns._ID} INTEGER PRIMARY KEY,
${NoteEntry.COLUMN_NAME_TITLE} TEXT,
content TEXT, // <- New column name
${NoteEntry.COLUMN_NAME_TIMESTAMP} INTEGER
)
""".trimIndent()
db.execSQL(SQL_CREATE_NEW_ENTRIES)
// Copy data from old table to new table
db.execSQL("""
INSERT INTO ${NoteEntry.TABLE_NAME}
(${BaseColumns._ID}, ${NoteEntry.COLUMN_NAME_TITLE}, content, ${NoteEntry.COLUMN_NAME_TIMESTAMP})
SELECT ${BaseColumns._ID}, ${NoteEntry.COLUMN_NAME_TITLE}, body, ${NoteEntry.COLUMN_NAME_TIMESTAMP}
FROM notes_old;
""".trimIndent())
// Drop the old table
db.execSQL("DROP TABLE notes_old;")
}
Use Room Persistence Library
While the above method works, for any non-trivial app, you should strongly consider using Room. Room handles migrations automatically for simple changes and provides a much safer, easier abstraction for complex ones.
With Room, adding a column is almost automatic:
- Update your Entity class:
@Entity(tableName = "notes")
data class Note(
@PrimaryKey(autoGenerate = true) val id: Long = 0,
val title: String,
val body: String,
val timestamp: Long,
val isFavorite: Boolean = false // <- Simply add the new field
)
- Increase the database version:
@Database(entities = [Note::class], version = 2) // <- Change from 1 to 2
abstract class AppDatabase : RoomDatabase() {
...
}
- Provide a Migration object (if needed): For simple additions, Room can often figure it out. For complex changes, you provide a
Migrationobject.
val MIGRATION_1_2: Migration = object : Migration(1, 2) {
override fun migrate(database: SupportSQLiteDatabase) {
// Room can sometimes handle this itself, but you can be explicit.
database.execSQL("ALTER TABLE notes ADD COLUMN is_favorite INTEGER NOT NULL DEFAULT 0")
}
}
- Build the database with the migration:
Room.databaseBuilder(context, AppDatabase::class.java, "myapp.db")
.addMigrations(MIGRATION_1_2) // Add the migration to the builder
.build()
Room will automatically run the necessary migrations, and its compile-time checks prevent many common SQL errors.
Handle multiple sequentially migration with Room
Room calls the migrate() method of a Migration object to move from one specific version to the next. You must provide a Migration for every possible version jump that might exist in the wild.
-
If a user updates from version 1 to version 4, Room will execute:
-
Migration(1, 2) -
Migration(2, 3) -
Migration(3, 4)
-
If any of these migrations are missing, Room will throw an IllegalStateException.
Step-by-Step Implementation
Let’s walk through a scenario where we need to handle migrations from version 1 to version 4.
1. Define Your Entities and Database (Version 4)
// Entity at Version 1 (Initial)
// @Entity(tableName = "user")
// data class User(
// @PrimaryKey val id: Long,
// val name: String
// )
// Entity at Version 4 (Current)
@Entity(tableName = "user")
data class User(
@PrimaryKey val id: Long,
val name: String,
val age: Int, // Added in Version 2
val email: String? // Added in Version 3, can be null
)
@Database(
entities = [User::class],
version = 4, // The current version of the database
exportSchema = true // IMPORTANT: This allows you to see the schema history
)
abstract class AppDatabase : RoomDatabase() {
abstract fun userDao(): UserDao
}
2. Create Individual Migration Objects
Create a separate Migration object for each version step. This is the most maintainable approach.
import androidx.room.migration.Migration
import androidx.sqlite.db.SupportSQLiteDatabase
// Migration from version 1 to 2: Add 'age' column (INTEGER, NOT NULL)
val MIGRATION_1_2: Migration = object : Migration(1, 2) {
override fun migrate(database: SupportSQLiteDatabase) {
// Since 'age' is not nullable, we must provide a default value for existing rows.
database.execSQL("ALTER TABLE user ADD COLUMN age INTEGER NOT NULL DEFAULT 0")
}
}
// Migration from version 2 to 3: Add 'email' column (TEXT, NULLABLE)
val MIGRATION_2_3: Migration = object : Migration(2, 3) {
override fun migrate(database: SupportSQLiteDatabase) {
// Adding a nullable column is simple, no default needed.
database.execSQL("ALTER TABLE user ADD COLUMN email TEXT")
}
}
// Migration from version 3 to 4: Create a new table 'book'
val MIGRATION_3_4: Migration = object : Migration(3, 4) {
override fun migrate(database: SupportSQLiteDatabase) {
database.execSQL("""
CREATE TABLE IF NOT EXISTS `book` (
`id` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
`title` TEXT NOT NULL,
`userId` INTEGER NOT NULL,
FOREIGN KEY(`userId`) REFERENCES `user`(`id`) ON UPDATE NO ACTION ON DELETE CASCADE
)
""".trimIndent())
}
}
3. Add All Migrations to the Database Builder
When building your Room database, you must add every migration you’ve defined to the builder.
// In your database module or where you provide the Database
fun provideDatabase(applicationContext: Context): AppDatabase {
return Room.databaseBuilder(
applicationContext,
AppDatabase::class.java,
"app_database.db"
)
.addMigrations(
MIGRATION_1_2, // Handles 1->2
MIGRATION_2_3, // Handles 2->3
MIGRATION_3_4 // Handles 3->4
)
// .fallbackToDestructiveMigration() // Use with CAUTION (see below)
.build()
}
How it works: If a user’s database is at version 1, Room will find and apply MIGRATION_1_2, MIGRATION_2_3, and MIGRATION_3_4 in sequence. If the database is already at version 3, it will only apply MIGRATION_3_4.
Best Practices and Important Considerations
1. Enable Schema Export (CRITICAL)
In your build.gradle file, enable schema export. This tells Room to generate a JSON file of your database schema for each version, which is invaluable for testing and writing correct migrations.
android {
...
defaultConfig {
...
javaCompileOptions {
annotationProcessorOptions {
arguments += ["room.schemaLocation": "$projectDir/schemas".toString()]
}
}
}
}
After building your project, you can find the schema files in app/schemas/your.package.name.AppDatabase/. Use these files to see the exact table structure at each version.
2. Testing Migrations
Never deploy a migration without testing it. Room provides a powerful testing utility.
-
Export a database: Run your app at version 1. Use Device File Explorer to pull the database file from
/data/data/your.package.name/databases/. -
Place it in assets: Create an
assetsfolder (if you don’t have one) and place the database file inside, renaming it toapp_database.db(matching your database name). -
Write a test: Use the
createFromAssetmethod to load this pre-populated database and run the migration.
@RunWith(AndroidJUnit4::class)
class MigrationTest {
private lateinit var database: AppDatabase
@Test
fun migrate1To4() {
val helper = MigrationTestHelper(
InstrumentationRegistry.getInstrumentation(),
AppDatabase::class.java.canonicalName,
FrameworkSQLiteOpenHelperFactory()
)
// Open the version 1 database from assets
helper.createDatabase(TEST_DB_NAME, 1).apply {
close()
}
// Run all migrations up to version 4
database = Room.databaseBuilder(
InstrumentationRegistry.getInstrumentation().targetContext,
AppDatabase::class.java,
TEST_DB_NAME
)
.addMigrations(MIGRATION_1_2, MIGRATION_2_3, MIGRATION_3_4)
.createFromAsset("databases/app_database.db") // Load your v1 database from assets
.build()
// If it opens without throwing an exception, the migration is successful.
// You can also write queries here to verify data integrity.
database.query("SELECT * FROM user", null).use { cursor ->
// Assert that the data is correct after migration
}
}
companion object {
private const val TEST_DB_NAME = "test_database"
}
}
3. Fallback Strategies (Use with Extreme Caution)
-
.fallbackToDestructiveMigration(): DANGER – If Room doesn’t find a required migration, it will crash and recreate the database, deleting all user data. Only use this in development. -
.fallbackToDestructiveMigrationOnDowngrade(): Allows destructive migrations only if the version number goes down (a rare downgrade scenario). -
.fallbackToDestructiveMigrationFrom(x, y, z...): Allows destructive migrations only when migrating from the specific versions you list. Slightly safer but still risky.
Best Practice: Avoid destructive fallbacks in production. Instead, ensure you have provided a migration path for all possible version jumps.
4. Handling Complex Changes
For changes that ALTER TABLE can’t handle (e.g., dropping/renaming a column, changing a column type), you must follow a multi-step process:
-
Create a new table with the desired schema.
-
Copy the data from the old table to the new table.
-
Drop the old table.
-
Rename the new table to the original name.
Room’s AutoMigration can handle some simple cases (like adding a column), but for full control, manual Migration objects are necessary.
I am a very enthusiastic Android developer to build solid Android apps. I have a keen interest in developing for Android and have published apps to the Google Play Store. I always open to learning new technologies. For any help drop us a line anytime at contact@mobologicplus.com
