So, you've mastered the basics of Room. You can create an entity, a DAO, and a database, and you can perform simple CRUD operations. That's a great start, but real-world apps are rarely that simple. Your app's data models will evolve, and your data will have complex relationships.

This guide will take you beyond the basics and into the two most critical advanced topics: handling database schema changes with migrations and modeling complex data with relations. Mastering these will elevate your local data persistence game.

The Inevitable Change: Painless Database Migrations

Imagine you've shipped version 1 of your app. Your User entity has a name and email. In version 2, you need to add a creation_date column. What happens to the existing users' data when they update the app? If you just change the entity and increment the database version, your app will crash with an IllegalStateException because Room doesn't know how to get from version 1 to version 2.

The solution is a Migration. You provide Room with a set of SQL commands to execute to safely transform the database from one version to the next, preserving all existing data.

Step 1: Bump the Version Number

First, in your @Database class, increment the version number.

@Database(entities = [User::class], version = 2) // Was version = 1
abstract class AppDatabase : RoomDatabase() { /* ... */ }

Step 2: Write the Migration Logic

Next, you create a Migration object. This object defines the start version, the end version, and a migrate function containing the necessary SQL.

Creating a Migration from Version 1 to 2 
val MIGRATION_1_2 = object : Migration(1, 2) {
    override fun migrate(db: SupportSQLiteDatabase) {
        // Add the new 'creation_date' column to the 'user' table
        // We give it a default value of the current time for existing rows.
        db.execSQL("ALTER TABLE user ADD COLUMN creation_date INTEGER NOT NULL DEFAULT CURRENT_TIMESTAMP")
    }
}
Pro Tip: Always test your migrations thoroughly. Room has a testing artifact (`androidx.room:room-testing`) that lets you verify your migrations in an isolated environment to ensure they work as expected.

Step 3: Add the Migration to the Database Builder

Finally, tell your database builder about the new migration.

Room.databaseBuilder(context, AppDatabase::class.java, "app-db")
    .addMigrations(MIGRATION_1_2)
    .build()

Now, when a user on version 1 updates to version 2, Room will execute your migration, and the app will work perfectly without any data loss.

Modeling Reality: Handling Database Relations

Your data is rarely flat. Users have playlists, playlists have songs, and songs can be in multiple playlists. Room allows you to model these relationships efficiently.

One-to-Many Relationships

This is the most common relationship. For example, one User can have many Playlists. To model this, you first need a "parent" entity (User) and a "child" entity (Playlist), with the child holding a foreign key to the parent.

@Entity
data class User(@PrimaryKey val userId: Long, val name: String)

@Entity(foreignKeys = [ForeignKey(
    entity = User::class,
    parentColumns = ["userId"],
    childColumns = ["userOwnerId"],
    onDelete = ForeignKey.CASCADE
)])
data class Playlist(@PrimaryKey val playlistId: Long, val userOwnerId: Long, val title: String)

To query this data together, you create a POJO that contains the parent and a list of its children, using the @Relation annotation.

// Data class to hold the result
data class UserWithPlaylists(
    @Embedded val user: User,
    @Relation(
        parentColumn = "userId",
        entityColumn = "userOwnerId"
    )
    val playlists: List<Playlist>
)

// In your DAO
@Transaction // Important! Ensures the query is atomic.
@Query("SELECT * FROM User")
fun getUsersWithPlaylists(): Flow<List<UserWithPlaylists>>

Many-to-Many Relationships

What if a Song can be in many Playlists, and a Playlist can have many Songs? This requires a third table, called a "junction table" or "cross-reference table," that links the two.

@Entity
data class Song(@PrimaryKey val songId: Long, val title: String)

// The junction table
@Entity(primaryKeys = ["playlistId", "songId"])
data class PlaylistSongCrossRef(
    val playlistId: Long,
    val songId: Long
)

You then create a POJO that uses @Relation with the associateBy property pointing to this junction table.

data class PlaylistWithSongs(
    @Embedded val playlist: Playlist,
    @Relation(
        parentColumn = "playlistId",
        entityColumn = "songId",
        associateBy = Junction(PlaylistSongCrossRef::class)
    )
    val songs: List<Song>
)

By understanding and correctly implementing migrations and relations, you unlock the full power of Room. You can build complex, robust, and maintainable data layers that can evolve with your app and accurately reflect the real-world data you need to store.