How do you add a non-null column with a default in Room?

WHAT THIS TESTS: This question probes whether you know the boundary between SQLite's native ALTER TABLE capabilities and Room's Migration abstraction. The interviewer wants to see that you understand SQLite can add a non-null column only when a default is supplied, that you know how to express this in Room's Migration class, and that you treat user data as immutable during upgrades.

A GOOD ANSWER COVERS: Four things in order. First, increment the database version number in your RoomDatabase annotation. Second, create a Migration instance for the version pair, override migrate, and inside it call db.execSQL with ALTER TABLE my_table ADD COLUMN new_column INTEGER NOT NULL DEFAULT 0. Third, register that migration by passing it to Room.databaseBuilder via addMigrations. Fourth, mention that because SQLite assigns the default value to every existing row automatically, no manual backfill is required and zero data is lost.

COMMON WRONG ANSWERS: A major red flag is suggesting fallbackToDestructiveMigration or dropping the table and recreating it, because both erase user data. Another is proposing to add a NOT NULL column without a DEFAULT clause, which SQLite rejects when rows already exist. Some candidates also forget to register the Migration object in the builder, which causes Room to throw an IllegalStateException at runtime. Finally, describing a complex table-rebuild workflow for this simple additive change reveals unfamiliarity with SQLite ALTER TABLE semantics.

LIKELY FOLLOW-UPS: The interviewer may ask what happens if you need to add a non-null column without any default value; the correct response is that SQLite forbids it, so you must perform a table rebuild by creating a new table, copying existing data with an inserted placeholder value, dropping the old table, and renaming the new one. They may also ask how to test migrations, in which case you should mention MigrationTestHelper and writing an instrumentation test that validates the schema and data after the migration runs. Another follow-up is Room's auto-migration feature; note that Room can generate simple migrations automatically if you export schemas and use AutoMigration, but manual Migrations are still required for changes that Room cannot infer.

ONE CONCRETE EXAMPLE: Suppose your app has a User table with id and name, and you need to add age INTEGER NOT NULL DEFAULT 0 in version 2. You would annotate the entity field with ColumnInfo and set its defaultValue attribute, bump the database version to 2, then write val MIGRATION_1_2 = object : Migration(1, 2) { override fun migrate(db: SupportSQLiteDatabase) { db.execSQL("ALTER TABLE User ADD COLUMN age INTEGER NOT NULL DEFAULT 0") } } and pass it to addMigrations(MIGRATION_1_2). Existing users keep their rows and age is backfilled to zero automatically.

Source: developer.android.com