Room Database Skill

Overview

This skill provides expert capabilities for Android Room persistence library. It enables designing database schemas, implementing DAOs, configuring migrations, and integrating with modern Android architecture components.

Allowed Tools

• bash - Execute Gradle commands and Android build tools
• read - Analyze Room entities and DAO files
• write - Generate Room database components
• edit - Update existing Room configurations
• glob - Search for database-related files
• grep - Search for patterns in database code

Capabilities

Entity Design

1. Entity Definition

   • Define @Entity classes with proper annotations
   • Configure primary keys (single and composite)
   • Set up foreign key relationships
   • Configure indices for query optimization
   • Implement embedded objects

2. Type Converters

   • Create @TypeConverter for custom types
   • Handle Date/Time conversions
   • Convert enums to database types
   • Serialize complex objects to JSON
   • Configure global type converters

DAO Implementation

1. Query Methods

   • Write @Query annotations with SQL
   • Implement @Insert, @Update, @Delete
   • Configure conflict strategies
   • Create complex JOIN queries
   • Implement pagination queries

2. Reactive Queries

   • Return Flow for reactive updates
   • Configure LiveData return types
   • Implement one-shot suspend functions
   • Handle nullable results
   • Create parameterized queries

Database Configuration

1. Database Setup

   • Configure @Database annotation
   • Set up database builder
   • Configure pre-populated databases
   • Implement multiple databases
   • Configure in-memory databases for testing

2. Migrations

   • Implement Migration objects
   • Configure auto-migrations
   • Handle destructive migrations
   • Test migrations with MigrationTestHelper
   • Design fallback strategies

Integration

1. Hilt Integration

   • Provide database with @Singleton
   • Inject DAOs into repositories
   • Configure database scopes
   • Handle multi-module setups

2. Repository Pattern

   • Implement repository interfaces
   • Handle offline-first logic
   • Configure caching strategies
   • Implement sync mechanisms

Target Processes

This skill integrates with the following processes:

• android-room-database.js - Room implementation
• offline-first-architecture.js - Offline data strategies
• mobile-security-implementation.js - Secure data storage

Dependencies

Required

• Android Studio
• Room 2.6+
• Kotlin 1.9+
• KSP or KAPT

Optional

• Hilt for dependency injection
• Kotlin Coroutines
• Paging 3 library

Configuration

Gradle Setup

// build.gradle.kts (app)
plugins {
    id("com.google.devtools.ksp")
}

dependencies {
    implementation(libs.room.runtime)
    implementation(libs.room.ktx)
    ksp(libs.room.compiler)

    // Optional - Paging 3 Integration
    implementation(libs.room.paging)

    // Testing
    testImplementation(libs.room.testing)
}

Version Catalog

# gradle/libs.versions.toml
[versions]
room = "2.6.1"

[libraries]
room-runtime = { group = "androidx.room", name = "room-runtime", version.ref = "room" }
room-ktx = { group = "androidx.room", name = "room-ktx", version.ref = "room" }
room-compiler = { group = "androidx.room", name = "room-compiler", version.ref = "room" }
room-paging = { group = "androidx.room", name = "room-paging", version.ref = "room" }
room-testing = { group = "androidx.room", name = "room-testing", version.ref = "room" }

Usage Examples

Entity Definition

// data/local/entity/UserEntity.kt
package com.example.app.data.local.entity

import androidx.room.*

@Entity(
    tableName = "users",
    indices = [
        Index(value = ["email"], unique = true),
        Index(value = ["created_at"])
    ]
)
data class UserEntity(
    @PrimaryKey
    @ColumnInfo(name = "id")
    val id: String,

    @ColumnInfo(name = "email")
    val email: String,

    @ColumnInfo(name = "display_name")
    val displayName: String,

    @ColumnInfo(name = "avatar_url")
    val avatarUrl: String?,

    @ColumnInfo(name = "created_at")
    val createdAt: Long,

    @ColumnInfo(name = "updated_at")
    val updatedAt: Long,

    @Embedded(prefix = "settings_")
    val settings: UserSettings
)

data class UserSettings(
    @ColumnInfo(name = "notifications_enabled")
    val notificationsEnabled: Boolean = true,

    @ColumnInfo(name = "theme")
    val theme: String = "system"
)

Entity with Relations

// data/local/entity/PostEntity.kt
package com.example.app.data.local.entity

import androidx.room.*

@Entity(
    tableName = "posts",
    foreignKeys = [
        ForeignKey(
            entity = UserEntity::class,
            parentColumns = ["id"],
            childColumns = ["author_id"],
            onDelete = ForeignKey.CASCADE
        )
    ],
    indices = [Index(value = ["author_id"])]
)
data class PostEntity(
    @PrimaryKey
    @ColumnInfo(name = "id")
    val id: String,

    @ColumnInfo(name = "author_id")
    val authorId: String,

    @ColumnInfo(name = "title")
    val title: String,

    @ColumnInfo(name = "content")
    val content: String,

    @ColumnInfo(name = "published_at")
    val publishedAt: Long?,

    @ColumnInfo(name = "is_draft")
    val isDraft: Boolean = true
)

// Relation class for queries
data class PostWithAuthor(
    @Embedded val post: PostEntity,
    @Relation(
        parentColumn = "author_id",
        entityColumn = "id"
    )
    val author: UserEntity
)

Type Converters

// data/local/converter/Converters.kt
package com.example.app.data.local.converter

import androidx.room.TypeConverter
import java.time.Instant
import java.time.LocalDateTime
import java.time.ZoneId

class Converters {
    @TypeConverter
    fun fromTimestamp(value: Long?): LocalDateTime? {
        return value?.let {
            LocalDateTime.ofInstant(Instant.ofEpochMilli(it), ZoneId.systemDefault())
        }
    }

    @TypeConverter
    fun toTimestamp(date: LocalDateTime?): Long? {
        return date?.atZone(ZoneId.systemDefault())?.toInstant()?.toEpochMilli()
    }

    @TypeConverter
    fun fromStringList(value: List<String>?): String? {
        return value?.joinToString(",")
    }

    @TypeConverter
    fun toStringList(value: String?): List<String>? {
        return value?.split(",")?.map { it.trim() }
    }
}

DAO Implementation

// data/local/dao/UserDao.kt
package com.example.app.data.local.dao

import androidx.room.*
import kotlinx.coroutines.flow.Flow

@Dao
interface UserDao {
    @Query("SELECT * FROM users ORDER BY display_name ASC")
    fun observeAllUsers(): Flow<List<UserEntity>>

    @Query("SELECT * FROM users WHERE id = :userId")
    fun observeUserById(userId: String): Flow<UserEntity?>

    @Query("SELECT * FROM users WHERE id = :userId")
    suspend fun getUserById(userId: String): UserEntity?

    @Query("SELECT * FROM users WHERE email = :email LIMIT 1")
    suspend fun getUserByEmail(email: String): UserEntity?

    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun insertUser(user: UserEntity)

    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun insertUsers(users: List<UserEntity>)

    @Update
    suspend fun updateUser(user: UserEntity)

    @Delete
    suspend fun deleteUser(user: UserEntity)

    @Query("DELETE FROM users WHERE id = :userId")
    suspend fun deleteUserById(userId: String)

    @Query("DELETE FROM users")
    suspend fun deleteAllUsers()

    @Transaction
    suspend fun replaceAllUsers(users: List<UserEntity>) {
        deleteAllUsers()
        insertUsers(users)
    }
}

DAO with Relations

// data/local/dao/PostDao.kt
package com.example.app.data.local.dao

import androidx.room.*
import androidx.paging.PagingSource
import kotlinx.coroutines.flow.Flow

@Dao
interface PostDao {
    @Transaction
    @Query("SELECT * FROM posts WHERE is_draft = 0 ORDER BY published_at DESC")
    fun observePublishedPostsWithAuthor(): Flow<List<PostWithAuthor>>

    @Transaction
    @Query("SELECT * FROM posts WHERE is_draft = 0 ORDER BY published_at DESC")
    fun getPublishedPostsPagingSource(): PagingSource<Int, PostWithAuthor>

    @Transaction
    @Query("SELECT * FROM posts WHERE id = :postId")
    suspend fun getPostWithAuthor(postId: String): PostWithAuthor?

    @Query("SELECT * FROM posts WHERE author_id = :authorId ORDER BY published_at DESC")
    fun observePostsByAuthor(authorId: String): Flow<List<PostEntity>>

    @Query("""
        SELECT * FROM posts
        WHERE title LIKE '%' || :query || '%'
           OR content LIKE '%' || :query || '%'
        ORDER BY published_at DESC
    """)
    suspend fun searchPosts(query: String): List<PostEntity>

    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun insertPost(post: PostEntity)

    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun insertPosts(posts: List<PostEntity>)

    @Update
    suspend fun updatePost(post: PostEntity)

    @Query("UPDATE posts SET is_draft = :isDraft WHERE id = :postId")
    suspend fun updateDraftStatus(postId: String, isDraft: Boolean)

    @Delete
    suspend fun deletePost(post: PostEntity)
}

Database Definition

// data/local/AppDatabase.kt
package com.example.app.data.local

import androidx.room.*
import com.example.app.data.local.converter.Converters
import com.example.app.data.local.dao.PostDao
import com.example.app.data.local.dao.UserDao
import com.example.app.data.local.entity.PostEntity
import com.example.app.data.local.entity.UserEntity

@Database(
    entities = [
        UserEntity::class,
        PostEntity::class
    ],
    version = 2,
    autoMigrations = [
        AutoMigration(from = 1, to = 2)
    ],
    exportSchema = true
)
@TypeConverters(Converters::class)
abstract class AppDatabase : RoomDatabase() {
    abstract fun userDao(): UserDao
    abstract fun postDao(): PostDao
}

Hilt Module

// di/DatabaseModule.kt
package com.example.app.di

import android.content.Context
import androidx.room.Room
import com.example.app.data.local.AppDatabase
import com.example.app.data.local.dao.PostDao
import com.example.app.data.local.dao.UserDao
import dagger.Module
import dagger.Provides
import dagger.hilt.InstallIn
import dagger.hilt.android.qualifiers.ApplicationContext
import dagger.hilt.components.SingletonComponent
import javax.inject.Singleton

@Module
@InstallIn(SingletonComponent::class)
object DatabaseModule {

    @Provides
    @Singleton
    fun provideAppDatabase(
        @ApplicationContext context: Context
    ): AppDatabase {
        return Room.databaseBuilder(
            context,
            AppDatabase::class.java,
            "app_database"
        )
            .fallbackToDestructiveMigration()
            .build()
    }

    @Provides
    fun provideUserDao(database: AppDatabase): UserDao = database.userDao()

    @Provides
    fun providePostDao(database: AppDatabase): PostDao = database.postDao()
}

Repository Implementation

// data/repository/UserRepositoryImpl.kt
package com.example.app.data.repository

import com.example.app.data.local.dao.UserDao
import com.example.app.data.local.entity.UserEntity
import com.example.app.data.remote.api.UserApi
import com.example.app.domain.model.User
import com.example.app.domain.repository.UserRepository
import kotlinx.coroutines.flow.Flow
import kotlinx.coroutines.flow.map
import javax.inject.Inject

class UserRepositoryImpl @Inject constructor(
    private val userDao: UserDao,
    private val userApi: UserApi
) : UserRepository {

    override fun observeUsers(): Flow<List<User>> {
        return userDao.observeAllUsers().map { entities ->
            entities.map { it.toDomain() }
        }
    }

    override fun observeUser(userId: String): Flow<User?> {
        return userDao.observeUserById(userId).map { it?.toDomain() }
    }

    override suspend fun refreshUsers() {
        val remoteUsers = userApi.getUsers()
        val entities = remoteUsers.map { it.toEntity() }
        userDao.replaceAllUsers(entities)
    }

    override suspend fun getUser(userId: String): User? {
        return userDao.getUserById(userId)?.toDomain()
    }
}

// Extension functions for mapping
private fun UserEntity.toDomain() = User(
    id = id,
    email = email,
    displayName = displayName,
    avatarUrl = avatarUrl
)

Quality Gates

Data Integrity

• Foreign key constraints properly configured
• Indices on frequently queried columns
• Unique constraints where appropriate
• Proper cascade delete behavior

Performance

• Queries optimized with EXPLAIN
• Pagination for large datasets
• Background thread execution
• Proper indexing strategy

Testing

• DAO tests with in-memory database
• Migration tests
• Repository integration tests

Related Skills

• kotlin-compose - Android UI development
• offline-storage - Cross-platform patterns
• mobile-security - Encrypted databases

Version History

• 1.0.0 - Initial release with Room 2.6 support