May 8, 20264 min read

Advanced Room Database Patterns: Relations, FTS, and Type Converters

Basic Room is straightforward. Relations between entities, full-text search, complex queries, and custom type converters are where most developers hit walls. Here's how to handle the advanced cases correctly.

AndroidDataKotlinArchitecture

On this page

Type Converters for Custom Types
One-to-Many Relations
Many-to-Many Relations
Full-Text Search (FTS)
Complex Queries With @RawQuery
Database Migrations
Takeaways

You've set up Room, you have CRUD working, your basic queries run. Now you need foreign keys, one-to-many relations, full-text search, or a column type Room doesn't understand natively. Here's how to handle them.

Type Converters for Custom Types

Room stores primitive types. For anything else, you need a

code

TypeConverter

kotlin

// Store LocalDate as String
class DateConverters {
    @TypeConverter
    fun fromLocalDate(date: LocalDate?): String? = date?.toString()
    
    @TypeConverter
    fun toLocalDate(dateString: String?): LocalDate? = 
        dateString?.let { LocalDate.parse(it) }
}

// Store List<String> as JSON string
class ListConverters {
    private val json = Json { ignoreUnknownKeys = true }
    
    @TypeConverter
    fun fromList(list: List<String>?): String? = 
        list?.let { json.encodeToString(it) }
    
    @TypeConverter
    fun toList(jsonString: String?): List<String>? = 
        jsonString?.let { json.decodeFromString(it) }
}

// Register with the database
@Database(entities = [TaskEntity::class], version = 1)
@TypeConverters(DateConverters::class, ListConverters::class)
abstract class AppDatabase : RoomDatabase() {
    abstract fun taskDao(): TaskDao
}

[!WARNING] Avoid storing complex objects as JSON blobs in Room columns. It defeats the purpose of a relational database and makes querying difficult. Store related data as separate entities with foreign keys.

One-to-Many Relations

code

Project

has many

code

Tasks

kotlin

@Entity(tableName = "projects")
data class ProjectEntity(
    @PrimaryKey val id: String,
    val name: String
)

@Entity(
    tableName = "tasks",
    foreignKeys = [ForeignKey(
        entity = ProjectEntity::class,
        parentColumns = ["id"],
        childColumns = ["projectId"],
        onDelete = ForeignKey.CASCADE // Delete tasks when project is deleted
    )],
    indices = [Index("projectId")] // Required for foreign key performance
)
data class TaskEntity(
    @PrimaryKey val id: String,
    val title: String,
    val projectId: String
)

// Data class for the join result
data class ProjectWithTasks(
    @Embedded val project: ProjectEntity,
    @Relation(
        parentColumn = "id",
        entityColumn = "projectId"
    )
    val tasks: List<TaskEntity>
)

// DAO
@Dao
interface ProjectDao {
    @Transaction // Required for @Relation queries
    @Query("SELECT * FROM projects WHERE id = :projectId")
    fun getProjectWithTasks(projectId: String): Flow<ProjectWithTasks>
    
    @Transaction
    @Query("SELECT * FROM projects")
    fun getAllProjectsWithTasks(): Flow<List<ProjectWithTasks>>
}

code

@Transaction

is required when using

code

@Relation

to ensure consistency when reading parent and child rows.

Many-to-Many Relations

code

Task

can have many

code

Tags

, and a

code

Tag

can belong to many

code

Tasks

kotlin

@Entity(tableName = "tags")
data class TagEntity(
    @PrimaryKey val id: String,
    val name: String
)

// Junction table
@Entity(
    tableName = "task_tags",
    primaryKeys = ["taskId", "tagId"],
    foreignKeys = [
        ForeignKey(entity = TaskEntity::class, parentColumns = ["id"], childColumns = ["taskId"]),
        ForeignKey(entity = TagEntity::class, parentColumns = ["id"], childColumns = ["tagId"])
    ]
)
data class TaskTagCrossRef(
    val taskId: String,
    val tagId: String
)

data class TaskWithTags(
    @Embedded val task: TaskEntity,
    @Relation(
        parentColumn = "id",
        entityColumn = "id",
        associateBy = Junction(TaskTagCrossRef::class)
    )
    val tags: List<TagEntity>
)

Full-Text Search (FTS)

For search functionality, FTS4/FTS5 is dramatically faster than

code

LIKE '%query%'

kotlin

@Fts4(contentEntity = TaskEntity::class)
@Entity(tableName = "tasks_fts")
data class TaskFtsEntity(
    @PrimaryKey @ColumnInfo(name = "rowid") val rowId: Int,
    val title: String,
    val description: String
)

@Dao
interface TaskSearchDao {
    @Query("""
        SELECT tasks.* FROM tasks
        INNER JOIN tasks_fts ON tasks.id = tasks_fts.rowid
        WHERE tasks_fts MATCH :query
        ORDER BY rank
    """)
    fun searchTasks(query: String): Flow<List<TaskEntity>>
}

// Usage — note the * wildcard for prefix matching
val results = searchDao.searchTasks("buy*") // Matches "buy", "buying", "buyer"

FTS search is indexed and significantly faster than

code

LIKE

on large datasets.

Complex Queries With @RawQuery

For dynamic queries where the structure isn't known at compile time:

kotlin

@Dao
interface TaskDao {
    @RawQuery(observedEntities = [TaskEntity::class])
    fun getTasksByRawQuery(query: SupportSQLiteQuery): Flow<List<TaskEntity>>
}

// Build the query dynamically
fun buildFilterQuery(
    completed: Boolean?,
    priority: Priority?,
    tagId: String?
): SupportSQLiteQuery {
    val queryBuilder = StringBuilder("SELECT * FROM tasks WHERE 1=1")
    val args = mutableListOf<Any>()
    
    completed?.let {
        queryBuilder.append(" AND completed = ?")
        args.add(if (it) 1 else 0)
    }
    priority?.let {
        queryBuilder.append(" AND priority = ?")
        args.add(it.name)
    }
    tagId?.let {
        queryBuilder.append(" AND id IN (SELECT taskId FROM task_tags WHERE tagId = ?)")
        args.add(it)
    }
    
    queryBuilder.append(" ORDER BY createdAt DESC")
    
    return SimpleSQLiteQuery(queryBuilder.toString(), args.toTypedArray())
}

Database Migrations

Every schema change requires a migration. Don't use

code

fallbackToDestructiveMigration()

in production:

kotlin

val MIGRATION_1_2 = object : Migration(1, 2) {
    override fun migrate(db: SupportSQLiteDatabase) {
        // Add a new column
        db.execSQL("ALTER TABLE tasks ADD COLUMN priority TEXT NOT NULL DEFAULT 'MEDIUM'")
    }
}

val MIGRATION_2_3 = object : Migration(2, 3) {
    override fun migrate(db: SupportSQLiteDatabase) {
        // Create a new table
        db.execSQL("""
            CREATE TABLE IF NOT EXISTS tags (
                id TEXT NOT NULL PRIMARY KEY,
                name TEXT NOT NULL
            )
        """)
    }
}

// In your database builder
Room.databaseBuilder(context, AppDatabase::class.java, "app_database")
    .addMigrations(MIGRATION_1_2, MIGRATION_2_3)
    .build()

Always test migrations with

code

MigrationTestHelper

before releasing.

Takeaways

code
```
TypeConverter
```
handles custom types — avoid JSON blobs for queryable data
code
```
@Relation
```
with
code
```
@Transaction
```
is the correct pattern for entity relationships
FTS4/FTS5 is the right tool for search — not
code
```
LIKE '%query%'
```
on large datasets
code
```
@RawQuery
```
handles dynamic filter queries that can't be expressed at compile time
Never skip migrations —
code
```
fallbackToDestructiveMigration()
```
deletes all user data

Sudarshan Chaudhari

AI Systems Builder / Product Engineer

Bangkok, Thailand

Solo Android developer with 13+ years in QA, building Android apps, AI automation systems, and developer tools at SudarshanTechLabs.

GitHub Play Store

Stay updated

Get new posts on Android, Kotlin, and solo dev straight to your inbox.

RSS Feed Telegram

Mastering Room Database in Production: 7 Battle-Tested Practices for Android Apps

3 min read

AndroidKotlin

Building Offline-First Android Apps: Architecture and Sync Strategies

4 min read

AndroidArchitecture

Hilt Dependency Injection: Everything You Need to Know

4 min read

HiltDependency Injection

Related Apps

MyFamilyTracker

Real-time family location sharing — Firebase Realtime DB for sub-second propagation, WorkManager + ForegroundService for OS-compliant background collection, geofencing via Google Maps API.

Get it onGoogle Play Details

Building something? Available for Android dev and QA consulting.

Work with me

Comments — powered by Giscus

Advanced Room Database Patterns: Relations, FTS, and Type Converters

Type Converters for Custom Types

One-to-Many Relations

Many-to-Many Relations

Full-Text Search (FTS)

Complex Queries With @RawQuery

Database Migrations

Takeaways

MyFamilyTracker

Apps tagged with this

MyFamilyTracker

DreamWeave

FitQuick