roomQL

Why roomQL

A query whose filter values vary at runtime already has a static answer in Room: WHERE (:minAge IS NULL OR age >= :minAge), checked at compile time. A query whose structure varies — which column to sort or group by, whether a join is present, which columns to return — is harder. SQL can bind a value as a parameter, never an identifier, so a static @Query reaches it only through a CASE ladder, or not at all for a dynamic SELECT list.

roomQL covers both with one typed DSL. A KSP processor generates a Column<T> for every column in your @Entity classes, so orderBy, groupBy, join, and select take real, rename-safe Kotlin symbols, and an optional null filter drops out of the generated SQL. You keep Room's own @RawQuery, your entities, your database, and your migrations.

• 1 · KSP processorReads your @Entity classes at build time and generates a UserEntityTable object with one typed column per field.
• 2 · query { } DSLBuilds a RoomQlQuery: plain SQL text plus positional ? arguments, on the pure JVM.
• 3 · .toQuery() bridgeTurns that result into the SupportSQLiteQuery a Room @RawQuery method accepts.
• Your Room setup staysNo new database, no new migrations, no runtime reflection. Delete roomQL and your schema is untouched.

Installation

roomQL is published on Maven Central under io.github.kotplat.roomql. Add the Android runtime and KSP processor to your version catalog.

[versions]
roomql = "2.0.0"

[libraries]
roomql-runtime-android = { module = "io.github.kotplat.roomql:runtime-android", version.ref = "roomql" }
roomql-ksp-processor = { module = "io.github.kotplat.roomql:ksp-processor", version.ref = "roomql" }

plugins {
    id("com.google.devtools.ksp")
}

dependencies {
    // .toQuery() bridge; pulls in :runtime
    implementation(libs.roomql.runtime.android)
    // generates the *Table objects
    ksp(libs.roomql.ksp.processor)
}

That is the whole dependency block for Android. runtime-android already depends on runtime (the query { } DSL), so it comes along automatically. The ksp(...) line stays separate because Gradle cannot pull a symbol processor in transitively. Add io.github.kotplat.roomql:runtime on its own only if you want the DSL without the Room bridge, for example to unit-test generated SQL on the plain JVM.

Requirements

Quick start

Define an entity and a @RawQuery DAO as usual. After one build, KSP generates UserEntityTable in the same package, and optional values drop out of the query when they are null.

import androidx.room.Dao
import androidx.room.Entity
import androidx.room.PrimaryKey
import androidx.room.RawQuery
import androidx.sqlite.db.SupportSQLiteQuery
import com.roomql.android.toQuery
import com.roomql.runtime.SortDirection
import com.roomql.runtime.query

@Entity(tableName = "users")
data class UserEntity(
    @PrimaryKey val id: Int,
    val name: String,
    val age: Int,
    val status: String,
)
// KSP generates UserEntityTable in the same package.

@Dao
interface UserDao {
    @RawQuery
    fun search(q: SupportSQLiteQuery): List<UserEntity>
}

fun searchUsers(dao: UserDao, minAge: Int?, status: String?): List<UserEntity> {
    val q = query {
        from(UserEntityTable)
        where {
            UserEntityTable.age gteIfNotNull minAge      // dropped when minAge is null
            UserEntityTable.status eqIfNotNull status    // dropped when status is null
        }
        orderBy(UserEntityTable.age, SortDirection.DESC)
        limit(20)
    }
    return dao.search(q.toQuery())
}

No if ladders, no IS NULL OR trick, and every value is bound as a positional parameter.

Filters: AND, OR, and optional values

Everything inside one where { } block is joined with AND. Use or { } when any one of several conditions is enough — roomQL wraps the group in parentheses so it combines correctly with the surrounding ANDs.

query {
    from(UserEntityTable)
    where {
        UserEntityTable.status eq "active"
        or {
            UserEntityTable.age lt 18
            UserEntityTable.age gt 65
        }
    }
}

SELECT * FROM users
WHERE status = ?
  AND (age < ? OR age > ?)

-- args: ["active", 18, 65]

You can call where { } more than once — each call adds to the same AND list, so plain Kotlin if statements work. If every condition inside an or { } is skipped, the group disappears too; you never get an empty ().

Two operators for two intents

Every value-taking operator comes in a required form that won't compile against a nullable value, and an optional form that skips the condition when the value is absent. The intent is visible at the call site, and a missing value can never silently bind NULL and match zero rows.

like takes your pattern verbatim; contains adds the % wildcards for you. Both only accept String columns. The list operators are named for emptiness: a multi-select with nothing chosen means “no filter”, while the required inList(emptyList()) renders IN (), which SQLite defines as matching nothing.

Sorting and paging

A runtime-chosen sort column has a static workaround — ORDER BY CASE WHEN :sortBy = 'age' THEN age … END bound to a string — but it becomes hard to maintain once a second axis needs to vary with it. roomQL takes a typed column instead. Call orderBy once per sort key to build a multi-column sort.

fun page(sort: Column<*>, dir: SortDirection, page: Int) =
    query {
        from(UserEntityTable)
        orderBy(sort, dir)
        orderBy(UserEntityTable.name, SortDirection.ASC)
        limit(20)
        offset(page * 20)
    }

page(UserEntityTable.age, SortDirection.DESC, 2)

SELECT * FROM users
ORDER BY age DESC, name ASC
LIMIT 20 OFFSET 40

For a single, fixed set of sort columns, a when dispatch is a good zero-dependency alternative. roomQL pays off when sorting has to compose with an optional join, a runtime groupBy, or a chosen select(...).

Grouping and aggregates

groupBy collapses rows that share a value; each call adds a column, so groupBy(a); groupBy(b) renders GROUP BY a, b. having { } filters the groups with the same operators as where { }. The aggregates count, countAll, sum, avg, min, and max are typed Expression<T> values usable in select(...), having { }, and orderBy(...).

query {
    from(OrderEntityTable)
    groupBy(OrderEntityTable.customerId)
    having { sum(OrderEntityTable.total) gt 100.0 }
    orderBy(countAll(), SortDirection.DESC)
    select(
        OrderEntityTable.customerId,
        count(OrderEntityTable.id) alias "order_count",
    )
}

SELECT customerId,
       COUNT(id) AS `order_count`
FROM orders
GROUP BY customerId
HAVING SUM(total) > ?
ORDER BY COUNT(*) DESC

A single value needs no name: select(countAll()) maps straight onto an Int or Long DAO return type. For several columns, alias names each one so Room can match it to a field. The name is backtick-quoted, so reserved words such as order work, and alias only compiles inside select(...).

Wrong answers become errors

SQLite quietly returns a value from an arbitrary row for some projections. roomQL's build() throws a RoomQlException instead when:

• a grouped query selects a column that is not in groupBy(...);
• an ungrouped query mixes an aggregate with a plain column, like select(name, countAll());
• two items share an output name, like select(UserEntityTable.id, OrderEntityTable.id).

Projections with @Projection

Hand-written aliases are strings, so they can drift: rename a field, forget the matching alias, and that field silently comes back empty. Annotate the result class with @Projection and the KSP processor generates a factory that writes the aliases for you.

@Projection
data class CustomerOrderCount(
    val customerId: Int,
    @ColumnInfo(name = "order_count") val orderCount: Long,
)
// generates CustomerOrderCountProjection(customerId: Expression<Int>, orderCount: Expression<Long>)

query {
    from(OrderEntityTable)
    groupBy(OrderEntityTable.customerId)
    select(*CustomerOrderCountProjection(OrderEntityTable.customerId, count(OrderEntityTable.id)))
}

A missing argument or a mismatched type is now a Kotlin compile error, just like a missing constructor argument. Each alias comes from @ColumnInfo(name = …), or the property name when there isn't one, and the factory matches the class's visibility.

Joins without column collisions

Use JoinType.INNER to keep only matching rows or JoinType.LEFT to keep every row from the first table; call join again for more tables. When a column name appears in more than one table, roomQL aliases it to table__column so a cursor never silently overwrites one id with another.

query {
    from(UserEntityTable)
    join(OrderEntityTable, JoinType.INNER) {
        on { UserEntityTable.id eq OrderEntityTable.userId }
    }
    where { OrderEntityTable.status eq "paid" }
}

SELECT users.id AS users__id, name, age,
  users.status AS users__status,
  orders.id AS orders__id, userId, total,
  orders.status AS orders__status
FROM users
INNER JOIN orders ON users.id = orders.userId
WHERE orders.status = ?

Clashing columns are also table-qualified inside where, having, groupBy, and orderBy. Map the result with your own data class: unique columns keep their bare names, collided ones use the alias.

data class UserOrder(
    @ColumnInfo(name = "name")       val userName: String,   // unique → bare
    @ColumnInfo(name = "total")      val orderTotal: Double, // unique → bare
    @ColumnInfo(name = "users__id")  val userId: Int,        // collided → aliased
    @ColumnInfo(name = "orders__id") val orderId: Int,       // collided → aliased
)

A join needs from(UserEntityTable), not the raw string from("users"): roomQL can only detect collisions from the generated column metadata, so mixing the two throws rather than returning broken rows.

Blocking, suspend, and Flow

You build the query the same way every time. The return style depends only on how the DAO method is declared, because .toQuery() produces an ordinary SupportSQLiteQuery.

@Dao
interface UserDao {
    @RawQuery
    fun search(q: SupportSQLiteQuery): List<UserEntity>

    @RawQuery
    suspend fun searchSuspend(q: SupportSQLiteQuery): List<UserEntity>

    @RawQuery(observedEntities = [UserEntity::class])
    fun observe(q: SupportSQLiteQuery): Flow<List<UserEntity>>
}

Room cannot infer which tables a raw query reads, so a Flow method must list them in observedEntities — every table a join touches, not just the primary one. Without it the Flow emits once and goes quiet.

Test the SQL without a device

query { } returns a plain RoomQlQuery from a pure-JVM module, so you can assert on the generated SQL and arguments in an ordinary JUnit test — no emulator, no Robolectric, no database.

@Test
fun `null status is omitted`() {
    val status: String? = null
    val q = query {
        from(UserEntityTable)
        where {
            UserEntityTable.age gte 18
            UserEntityTable.status eqIfNotNull status
        }
    }
    assertEquals("SELECT * FROM users WHERE age >= ?", q.sql)
    assertEquals(listOf(18), q.args)
}

The repository also ships a one-file minimal example, a full sample tested against in-memory Room, and a Compose demo that runs the same search four ways and shows the SQL each produces.

API at a glance

Full signatures and generated SQL are in the API reference.

What you get

• Typed dynamic structureSort, group, join, and select with real Column<T> references instead of a CASE ladder or a bare string.
• Compile-time column safetyRenames and typos fail during the build, not when the query runs.
• Optional filters that say soIfNotNull operators skip a missing value; required ones reject a nullable at compile time.
• Bound valuesValues are emitted as positional ? parameters instead of interpolated SQL.
• Aggregates and projectionsCounts, sums, and averages, with @Projection factories that keep result classes in sync.
• Safe joinsColliding column names are aliased automatically, so no column is silently overwritten.
• Blocking, suspend, and FlowWorks with every return type Room's @RawQuery supports.
• Plain JVM testingThe runtime produces SQL and arguments without an emulator, and needs no R8 keep rules.

How roomQL compares

Use roomQL when more than one axis of a query — sort, grouping, join, projection, filters — needs to vary together. Skip it for fully static queries, where a plain @Query gives full compile-time SQL verification, or for shapes it doesn't build yet: DISTINCT, subqueries, and UNION.

FAQ

More in the full FAQ and the troubleshooting guide.

Modules

Bug reports, feature requests, and questions go to GitHub Issues. Upgrading from 1.x? Read the changelog for the 2.0.0 breaking changes. Licensed under the Apache License 2.0.