---
name: room-database
description: Expert skill for Android Room persistence library
version: 1.0.0
category: Android Data Storage
slug: android-room
status: active
graph:
  domains: [domain:mobile]
  specializations: [specialization:mobile-development]
  skillAreas: [skill-area:android-native, skill-area:mobile-local-databases]
  roles: [role:mobile-engineer]
  workflows: [workflow:feature-development, workflow:release-management]
  topics: [topic:accessibility]
---

# 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

3. **Query Methods**
   - Write @Query annotations with SQL
   - Implement @Insert, @Update, @Delete
   - Configure conflict strategies
   - Create complex JOIN queries
   - Implement pagination queries

4. **Reactive Queries**
   - Return Flow for reactive updates
   - Configure LiveData return types
   - Implement one-shot suspend functions
   - Handle nullable results
   - Create parameterized queries

### Database Configuration

5. **Database Setup**
   - Configure @Database annotation
   - Set up database builder
   - Configure pre-populated databases
   - Implement multiple databases
   - Configure in-memory databases for testing

6. **Migrations**
   - Implement Migration objects
   - Configure auto-migrations
   - Handle destructive migrations
   - Test migrations with MigrationTestHelper
   - Design fallback strategies

### Integration

7. **Hilt Integration**
   - Provide database with @Singleton
   - Inject DAOs into repositories
   - Configure database scopes
   - Handle multi-module setups

8. **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

```kotlin
// 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

```toml
# 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

```kotlin
// 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

```kotlin
// 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

```kotlin
// 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

```kotlin
// 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

```kotlin
// 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

```kotlin
// 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

```kotlin
// 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

```kotlin
// 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


---
Source attribution: a5c-ai
Original project: https://github.com/a5c-ai/babysitter
License: MIT
License information: https://opensource.org/license/mit
Original upstream instructions; downloaded from Mega AI Hub.

## Upstream license notice

MIT License

Copyright (c) 2026 Gabriel Moreira

Permission is hereby granted, free of charge, to any person obtaining a copy
of this software and associated documentation files (the "Software"), to deal
in the Software without restriction, including without limitation the rights
to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
copies of the Software, and to permit persons to whom the Software is
furnished to do so, subject to the following conditions:

The above copyright notice and this permission notice shall be included in all
copies or substantial portions of the Software.

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
SOFTWARE.



# Mega AI Hub resource metadata

Creator / publisher: a5c-ai
Original project: https://github.com/a5c-ai/babysitter
Attribution basis: Upstream repository explicitly recorded in package mirror path

Fields of work: Development & Engineering
Roles: Backend Developer, Data Engineer, Mobile Developer
Specialties: Database, Mobile
Tools: Android Studio, Kotlin
Country applicability: Country not specified
Classification basis: Source catalog labels
Instruction SHA-256: 2bab416c9623fa011288b043904d7257e9237aa818c4beae5c6b7a93a59d8f4a