SPEAKER NOTES:
Monday established the foundation: Entity, DAO, Database, Repository. Today we go deeper. Real apps need more than simple SELECT * queries — they need filtering, aggregation, relationships between tables, and tools to debug when things go wrong. All of that is on the agenda today.
SPEAKER NOTES:
Simple queries don't scale. Fetching all scores and computing in Kotlin is fine with 10 rows, catastrophic with 17,000. The answer: push the computation into SQL, where the database engine can do it efficiently with indexes. Today we learn how to write those queries in Room.
SPEAKER NOTES:
Frame the session. Monday's content covered 90% of what you need for the lab. Today covers the remaining 10% — but that 10% is what separates a demo app from a real app. Aggregation queries, relationships, and Database Inspector are all directly applicable to the team project.
SPEAKER NOTES:
Room passes your @Query string directly to SQLite. If it's valid SQL, Room will execute it. That means every SQL feature you can use in SQLite is available: WHERE, ORDER BY, GROUP BY, HAVING, JOIN, subqueries, aggregate functions.
SPEAKER NOTES:
Three important patterns. First: standard parameter binding with :paramName. Second: IN clause with a List — Room expands the list into the IN (...) syntax automatically. Third: LIKE search — the || operator is SQLite's string concatenation. The '%' || :query || '%' pattern wraps the search term in wildcards for a contains search. All of these are compile-time verified by Room.
SPEAKER NOTES:
SQL aggregate functions work exactly as you'd expect. SUM and AVG return nullable types — they return NULL for zero matching rows in SQL, which Room maps to Kotlin nullable. COUNT always returns an integer (0 for no rows). These queries run inside SQLite's engine — efficient even over thousands of rows because SQLite can use indexes to avoid reading every row.
SPEAKER NOTES:
This is a real leaderboard query: JOIN players to scores, GROUP BY player, SUM the strokes, ORDER BY lowest strokes, LIMIT to top N. This runs entirely in SQLite — no Kotlin processing of 17,000 rows. The return type is LeaderboardEntry — a data class we'll define next that maps to these specific column aliases (AS playerId, AS firstName, etc.).
SPEAKER NOTES:
Room is flexible about return types. @Query doesn't have to return an entity — it can map to any data class whose property names match the column aliases. The AS clause in the SQL creates the alias. Room verifies the column-to-property mapping at compile time — if the alias doesn't match the property name, the build fails. This is more powerful than raw Cursor mapping and more efficient than loading full entities just to display a summary.
SPEAKER NOTES:
A transaction groups multiple database operations into an atomic unit — either all succeed or none do. Room makes this easy with the @Transaction annotation.
SPEAKER NOTES:
Atomicity matters for data integrity. If the process is killed between inserting the player and inserting the scores, you'd have scores in the database with a player_id that doesn't exist — a foreign key violation. With @Transaction, if anything fails after the first insert, the entire transaction rolls back to the state before the function ran. No half-writes. @Transaction can be placed on any DAO function or on a non-DAO suspend function in the Repository.
SPEAKER NOTES:
A common sync pattern: you get fresh data from the server, want to replace everything in the local cache atomically. Without a transaction, a user might see an empty list between the delete and the re-insert. With @Transaction, the UI never observes the empty state — the delete and insert are invisible to collectors until both complete. This is how Room handles the "replace on sync" pattern.
SPEAKER NOTES:
Relational databases have — relationships. SQLite supports foreign keys; Room provides two mechanisms to work with related data in Kotlin: @Embedded for simple nesting, and @Relation for proper one-to-many associations.
SPEAKER NOTES:
@Embedded is not a join — it's column flattening. The Address fields (street, city, state) become columns in the players table directly. No second table, no join needed. Good for value objects that naturally belong to a single entity. Not good for shared or reusable data — if two players live at the same address, they each store a copy.
SPEAKER NOTES:
@Relation is the real many-to-one join tool. TournamentWithPlayers is not an entity — it's a POJO that Room populates by running two queries: one for the tournament row, one for all players where tournament_id matches. @Transaction is required on @Relation queries — without it, Room runs the two queries separately and there's a window where the data could be inconsistent. Always pair @Relation with @Transaction.
SPEAKER NOTES:
This gives you all players for a tournament, each with their complete list of Score objects attached. Room runs one query for the players list, then one batch query for all scores belonging to those players — it does NOT run N+1 queries (one per player). This is efficient. The result is a List<PlayerScoreSummary> you can render directly without any in-memory joining.
SPEAKER NOTES:
Database Inspector is an Android Studio tool that lets you view, query, and modify your Room database while your app is running. It's invaluable for debugging schema issues, verifying that data was written correctly, and testing queries interactively.
SPEAKER NOTES:
Live demo this. Open the golf app on the emulator, then show Database Inspector. Students are often surprised that they can see the database contents in real time. Emphasize: this works on an attached device too, not just the emulator. The database must be open (app running and has used the database at least once) for Inspector to connect.
SPEAKER NOTES:
Click through the tables live. Insert a tournament from the app UI and watch the row appear in Inspector in real time. This is what "reactive" means — the database is live. Inspector also lets you click individual cells and edit values directly — useful for testing edge cases without writing UI for them. Show a live edit: change a player's name directly in Inspector, then observe the UI update via the Flow.
SPEAKER NOTES:
Run these live in Inspector. The query console is powerful for debugging: you can prototype the exact SQL before writing it in a @Query annotation. If the SQL works in Inspector, it'll work in Room. This is also how to debug migration issues — run the query that the migration was supposed to enable and verify the schema is correct.
SPEAKER NOTES:
The "Keep database connections open" toggle is easy to miss. Without it, Inspector periodically disconnects as Room manages connection lifecycle — frustrating when you're watching data change. With it enabled, Inspector stays connected as long as the app is running. Use Inspector to verify that: inserts produce the row you expect, updates target the right row, deletes cascade correctly via ForeignKey.CASCADE, and nullable columns contain the right null vs non-null values.
SPEAKER NOTES:
Let's build the complete Room layer for the Hope Foundation scoring app. We'll go from empty project to a working database with transactions and live debugging in Inspector.
SPEAKER NOTES:
Draw this on the board. Three entities, three DAOs, two ViewModels. This is the full database layer for the golf app — everything the project team might need. We won't build every piece live (too much), but we'll hit the most important: the Score DAO and leaderboard query, because those involve real SQL craft.
SPEAKER NOTES:
Type this live. Point to each element: the @Relation query uses @Transaction (always), the leaderboard is the full JOIN/GROUP BY/ORDER BY query from earlier, and replaceScoresForPlayer is an atomic score replacement for when a scorer needs to correct an entry. After typing, run the app and open Database Inspector to verify the queries work.
SPEAKER NOTES:
Seed data is essential for development — you can't test the leaderboard query without scores in the database. This seeds one tournament, one player, and 18 scores with random strokes. Add a "Seed" button in your debug UI or call this from a ViewModel init block guarded by BuildConfig.DEBUG. After seeding, open Database Inspector and verify all three tables have the expected rows. Then run the leaderboard query in the query console.
SPEAKER NOTES:
Run each of these in Inspector live. Step through them in order: first verify the tournament exists (basic sanity), then the player count, then the individual scores, then the full leaderboard query. This is the debugging workflow students should adopt: write data, inspect it, verify your queries are correct before integrating into the ViewModel.
SPEAKER NOTES:
Before closing, let's cover the mistakes that trip up students most often. These will save hours of debugging.
SPEAKER NOTES:
Room issues a compile warning if you use @Relation without @Transaction. The warning is easy to miss and hard to reproduce as a bug — the inconsistency only occurs in a narrow race window. Add @Transaction as a rule: every DAO function that uses @Relation, always.
SPEAKER NOTES:
Students often try to call suspend functions directly from composables. onClick lambdas run on the main thread — suspend functions called on the main thread without a coroutine scope throw IllegalStateException. The fix: always call through the ViewModel, which wraps the suspend call in viewModelScope.launch. The composable's onClick calls a regular ViewModel function, not a suspend function.
SPEAKER NOTES:
This is the most common Room crash in student projects. During development, you can use fallbackToDestructiveMigration() to just drop and recreate the database — fine when there's no real user data. But in production, you must provide a real migration. The crash message "Room cannot verify the data integrity" means the expected schema doesn't match the actual schema — the version wasn't bumped or the migration wasn't registered.
SPEAKER NOTES:
Flow.collect() is a suspend function — it can only be called from a coroutine. If you call it outside a scope, it'll block. If you call it in a CoroutineScope that outlives the screen, it'll leak. stateIn(viewModelScope) is the correct pattern: the collection is scoped to the ViewModel lifecycle, so it stops when the ViewModel is cleared. WhileSubscribed(5_000) adds an extra optimization: collection pauses when no UI is collecting, saving resources in the background.
SPEAKER NOTES:
Two-column summary captures the two halves of today's session. The left column covers the SQL craft — these are skills that transfer to any relational database. The right column covers Room-specific patterns and tooling. Database Inspector alone is worth remembering: every time you're unsure what Room is actually writing to disk, open Inspector and look.
SPEAKER NOTES:
The Lab 8 CodeLab is the priority this week — it's a structured walk through everything from Monday and today, with a pre-built project and step-by-step instructions. The project integration is the most valuable: take what you built in Lab 8 and apply it to your actual team project. Next week we add the network layer — fetch data from a server and cache it in Room. That's the full offline-first architecture.