Skip to content
Tutorials Center
My learning

Topics

Lesson 68 of 104 ยท Local Data

Room Relations and Migrations

Model one-to-many and many-to-many data in Room, add indices and TypeConverters, export schemas and migrate databases safely between app versions.

A single table is rarely enough. An author has many books, a book has many tags, and an order contains many line items. In Room Database Basics you stored flat entities. This lesson shows how Room models relationships, how to store types SQLite does not know with TypeConverters, and how to change your tables in a later app version without throwing away the user's data, which is what migrations are for.

Room, SQLite and KSP need the Android build, so every Room snippet below is read-only. The runnable TryIt blocks are plain Kotlin stand-ins: a tiny in-memory "database" that behaves like the relational ideas behind Room, and a migration runner that walks versioned schemas. Each one says what it models and what is the stand-in.

One-to-many with a foreign key

Room does not store object graphs. A Book does not contain an Author object inside it; the book row holds the author's id, a foreign key. You declare that link on the child entity:

Kotlin
// read-only example: needs Room
@Entity(tableName = "authors")
data class Author(
    @PrimaryKey(autoGenerate = true) val id: Long = 0,
    val name: String
)

@Entity(
    tableName = "books",
    foreignKeys = [ForeignKey(
        entity = Author::class,
        parentColumns = ["id"],
        childColumns = ["authorId"],
        onDelete = ForeignKey.CASCADE
    )],
    indices = [Index("authorId")]
)
data class Book(
    @PrimaryKey(autoGenerate = true) val id: Long = 0,
    val authorId: Long,
    val title: String
)

onDelete = CASCADE tells SQLite to delete a person's books when the person is deleted. The Index("authorId") matters: without it, every "books of this author" lookup scans the whole table, and deleting a parent also scans the child table to enforce the foreign key. Room even warns at compile time when a foreign key column is not indexed.

To read an author together with the books, you describe the shape of the result in a plain class and let Room run both queries:

Kotlin
// read-only example: needs Room
data class AuthorWithBooks(
    @Embedded val author: Author,
    @Relation(parentColumn = "id", entityColumn = "authorId")
    val books: List<Book>
)

@Dao
interface LibraryDao {
    @Transaction
    @Query("SELECT * FROM authors")
    fun observeAuthorsWithBooks(): Flow<List<AuthorWithBooks>>
}

@Embedded flattens the parent's columns into the result. @Relation says how to find the children: the parent's id equals the child's authorId. Room performs two queries, so @Transaction makes them see one consistent snapshot.

Many-to-many with a junction table

A book can have many tags and a tag many books. Neither table can hold the link, so a third table, the junction table (or cross-reference table), holds pairs of ids:

Kotlin
// read-only example: needs Room
@Entity(primaryKeys = ["bookId", "tagId"], indices = [Index("tagId")])
data class BookTagCrossRef(val bookId: Long, val tagId: Long)

data class BookWithTags(
    @Embedded val book: Book,
    @Relation(
        parentColumn = "id",
        entityColumn = "id",
        associateBy = Junction(
            value = BookTagCrossRef::class,
            parentColumn = "bookId",
            entityColumn = "tagId"
        )
    )
    val tags: List<Tag>
)

The composite primary key stops the same pair from being inserted twice. The extra index on tagId serves the reverse lookup, "all books with this tag", because the primary key index only helps queries that start with bookId.

The first TryIt is a stand-in. Plain lists play the role of tables, and the functions do what @Relation and @Junction do: match ids. The delete function imitates CASCADE. Real Room does this in SQLite, not in your code.

Kotlin Relations by id (stand-in for Room)

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with readln()), it gets the next line from this box.

Only the shape of the data is real here. In Room the matching happens through SQL and indices, and loading is asynchronous.

TypeConverters and embedded objects

SQLite has integers, floats, text and blobs. Anything else, like Instant, an enum or a list, needs a TypeConverter that turns it into one of those and back:

Kotlin
// read-only example: needs Room
class Converters {
    @TypeConverter fun fromInstant(value: Instant?): Long? = value?.toEpochMilli()
    @TypeConverter fun toInstant(value: Long?): Instant? = value?.let(Instant::ofEpochMilli)

    @TypeConverter fun fromStatus(value: Status): String = value.name
    @TypeConverter fun toStatus(value: String): Status = Status.valueOf(value)
}

@Database(entities = [Book::class], version = 1)
@TypeConverters(Converters::class)
abstract class AppDatabase : RoomDatabase()

Store enums by name rather than by ordinal. If you later reorder or insert a constant, ordinals silently change meaning. Avoid squeezing a whole list into one text column unless you will never query it; a separate table with a foreign key is the relational answer.

For a small group of fields that always travel together, such as an address, @Embedded copies the fields into the parent table's own columns. Use prefix = "home_" when you embed the same class twice so the column names do not clash.

Schema export and migrations

When you ship version 1 and later ship version 2 with a new column, every installed phone still has the version 1 file. Room compares version in the code with the version stored in the file and, if they differ, needs a migration: instructions to go from the old schema to the new one. With no migration, Room throws an IllegalStateException at startup, which means crash on launch for every user.

Set exportSchema = true and tell the build where to write the JSON files (with the Room Gradle plugin that is room { schemaDirectory("$projectDir/schemas") }). Room then writes 1.json, 2.json and so on, one per version. Commit these files. They are the written record of what each shipped version looks like, and they let Room generate auto migrations and let your tests check that migrating really produces the schema the code expects.

For simple changes, Room can generate the migration itself:

Kotlin
// read-only example: needs Room
@Database(
    entities = [Book::class],
    version = 3,
    autoMigrations = [
        AutoMigration(from = 1, to = 2),
        AutoMigration(from = 2, to = 3, spec = Migration2To3::class)
    ]
)
abstract class AppDatabase : RoomDatabase()

@RenameColumn(tableName = "books", fromColumnName = "title", toColumnName = "name")
class Migration2To3 : AutoMigrationSpec

Adding a table is automatic. Adding a column works when the column is nullable or has a default (@ColumnInfo(defaultValue = "0")). Renames and deletions are ambiguous (did you rename it or drop one and add another?), so you must say which with an annotation on a spec class. Anything more creative, such as splitting a table or moving data, needs a handwritten migration:

Kotlin
// read-only example: needs Room
val MIGRATION_3_4 = object : Migration(3, 4) {
    override fun migrate(db: SupportSQLiteDatabase) {
        db.execSQL("ALTER TABLE books ADD COLUMN rating INTEGER NOT NULL DEFAULT 0")
        db.execSQL("CREATE INDEX IF NOT EXISTS index_books_rating ON books(rating)")
    }
}

Room.databaseBuilder(context, AppDatabase::class.java, "library.db")
    .addMigrations(MIGRATION_3_4)
    .build()

fallbackToDestructiveMigration() is the escape hatch that deletes all data when no migration exists. That is fine for a cache you can refetch and a disaster for notes a person wrote, so choose it on purpose.

flowchart LR
    V1["v1 schema"] -->|"auto migration"| V2["v2: new column"]
    V2 -->|"auto + spec (rename)"| V3["v3: renamed column"]
    V3 -->|"manual Migration(3, 4)"| V4["v4: backfilled data"]

An installed database climbs the chain one step at a time until it reaches the version the app expects.

The second TryIt is a stand-in migration runner. It keeps a schema (table to columns) plus rows, finds the migration that starts at the current version and applies steps until it reaches the target. It also shows the two failure modes: a missing step crashes, and the destructive option wipes the data.

Kotlin Migration runner over versioned schemas (stand-in)

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with readln()), it gets the next line from this box.

The real runner also runs inside a transaction, so a failed step leaves the file unchanged instead of halfway; the stand-in's "stopped halfway" line is exactly what Room protects you from.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. How does a Room Book entity usually refer to its Author?

    Room maps rows, not object graphs. The child holds the parent's id as a foreign key, and a class with @Embedded plus @Relation describes how to load the two together.
  2. 2. Why should a foreign key column such as authorId have an index?

    An index lets SQLite find the matching rows directly. Without it, queries for one parent's children and cascade deletes have to scan the table. Room warns about missing foreign key indices.
  3. 3. Which situation needs a handwritten Migration or an AutoMigration spec instead of a plain AutoMigration?

    A rename looks the same as a delete plus an add, so Room needs you to say which one you mean with @RenameColumn in a spec. New tables, nullable columns and indices are detected from the exported schemas.
  4. 4. What happens if the version number increases and no migration path exists, and you did not opt into destructive fallback?

    Room refuses to open a database it cannot bring to the expected schema. Shipping that is a crash on launch, which is why exported schemas and migration tests exist.
  5. 5. What is the safer way to store an enum in a Room column?

    Names survive reordering and inserting constants. Ordinals change meaning silently when someone edits the enum.

Summary

  • Relationships are modeled with foreign keys: the child row holds the parent's id, and @Embedded plus @Relation load both together inside a @Transaction.
  • Index every foreign key column; use a junction entity with a composite primary key for many-to-many (@Junction).
  • TypeConverters map unsupported types to SQLite types; store enums by name and prefer tables over packed lists.
  • Export schemas (exportSchema = true) and commit the JSON files; they power auto migrations and migration tests.
  • Auto migrations cover additive changes; renames and deletes need a spec, and data moves need a handwritten Migration.
  • fallbackToDestructiveMigration() deletes data, so use it only for rebuildable caches.
  • The Room code is read-only here; the TryIt blocks are stand-ins for relational lookups and the migration chain.

Next: DataStore and Settings