QueryBuilder: How I wrote a runtime SQL query generator for an Android app

I've been building an app called Transactions for some time. As the name suggests, Transactions offers you the capability to track your income and expenses made through cash or digital mediums. I mostly wrote it for my personal use, but it caught on with my friends and is now published on the Play Store.

Let's get some context about Transactions to understand the problem better

Transactions use Room ORM under the hood. It's an abstraction layer over the SQLite library and allows you to simplify your database operations on Android

In a typical addition flow in Transactions, the user adds a transaction through the add transaction fragment, which gets parsed and inserted into the database. Then, Room emits a new list of transactions through a Kotlin Flow, which is an observable data type that Room supports natively. So, a typical get query looks like this:

@Query("SELECT * FROM `transactions`")
fun getAllTransactions(): Flow<List<Transaction>>

This method emits a new list whenever there's a change in the database. This flow is collected in the ViewModel, which then dispatches the list to the UI controller, that is the fragment. The fragment then submits this list to a DiffUtil based RecyclerView adapter, which efficiently updates the list with the differences along with providing the default animations.

The challenge: Incremental Sorting and Filtering

I wanted to implement incremental sorting and filtering in the app. To understand it better, let's say that I only want to see transactions that are labeled as income. On top of that, I only want to see those transactions which carry an amount greater than 500, and then sort those by time. This gives us the pseudo-query

Show me all transactions that are income and which carry an amount greater than 500 ordered by time

Now, the problem with this incremental filtering approach is that each filter needs to stack on top of the other. How do we implement this?

Solution 1: Do filtering on Kotlin lists

We of course have the option to get a list of all transactions from the database, and then repeatedly apply the .filter { } or the .sort() method on the resultant lists, but that doesn't seem too efficient. After all, that's what SQL is for. Not using it would be a mistake in this scenario, so I moved to the other choice.

Solution 2: Write custom DAO methods for every combination

The problem with this approach is that this is extremely repetitive, error-prone and the number of queries that are needed to be saved grows exponentially with every new parameter, which is not scalable at all.

Solution 3: Find a way to feed the list back a custom SQL view, and then apply filtering on the view incrementally.

The problem with this approach is that you have to write a lot of SQLite code bypassing Room, which is incredibly complex for a small project like this.

My Solution: Build Query in Runtime

I found that SQL provides an annotation that allows you to execute a custom raw SQL query and observer the resultant. The syntax looks like this:

@RawQuery(observedEntities = [Transaction::class])
fun customTransactionQuery(query: SupportSQLiteQuery): Flow<List<Transaction>>

So, now our DAO method takes in a custom query, runs it, and provides us an observable Kotlin Flow (or LiveData) which will update every time there's a change in the queried view. So, now the challenge was to generate the SQL query dynamically based on the filtering parameters that the user has selected at any given instant. To do that, I wrote a QueryBuilder class.

The QueryBuilder

So, I first listed out all the possible choices for any particular filter through the use of enums. For example, a Sort Choices enum looked like this:

enum class SortByChoices(val readableString: String) {
    AMOUNT_HIGHEST_FIRST("Highest Amount First"),
    AMOUNT_LOWEST_FIRST("Lowest Amount First"),
    TIME_EARLIEST_FIRST("Earliest Transaction First"),
    TIME_NEWEST_FIRST("Latest Transaction First"),
    UNSPECIFIED("Default Order")
}

The enum takes in a constructor, which is the readable string that can be displayed to a user. To use the QueryBuilder, you have to create an object and call its builder methods that set the value of these parameters. A typical set method looks like this:

fun setFilterType(filterTypeChoice: FilterByTypeChoices): QueryBuilder {
        if (filterTypeChoice != FilterByTypeChoices.UNSPECIFIED) {
            this.filterTypeChoice = filterTypeChoice
            this.filterEnabled = true
        }
        return this
    }

So, you specified the values of these choices, and then called the build() method on the QueryBuilder instance. Now, the build method walks over each of the parameter values and generates the SQL query piece by piece. The query generator for Type Filter is shown below:

// Type filter
        if (filterTypeChoice != FilterByTypeChoices.UNSPECIFIED) {
            if (previousFilterExists) query.append(" AND")
            query.append(" isExpense")
            val typeIsExpense = when (filterTypeChoice) {
                FilterByTypeChoices.EXPENSE -> "= 1"
                FilterByTypeChoices.INCOME -> "= 0"
                else -> ""
            }
            query.append(" $typeIsExpense")
            previousFilterExists = true
        }

In the end, the builder returns a SimpleSQLiteQuery object, like this:

return SimpleSQLiteQuery(query.toString())

This query object is then received by the caller and sent to the DAO for execution. Since the DAO gives us an observable, any updates to the queried dataset are updated in the UI automatically.

Once I was done writing the Builder, I wrote a bunch of plain old JUnit4 tests to check if the generated queries were the expected ones. A sample test:

    @Test
    fun query_filter_amount_greater_and_type_income_and_medium_digital_and_sort_by_time_oldest() {
        val query = QueryBuilder()
            .setFilterAmount(FilterByAmountChoices.GREATER_THAN, 100)
            .setFilterType(FilterByTypeChoices.INCOME)
            .setFilterMedium(FilterByMediumChoices.DIGITAL)
            .setSortingChoice(SortByChoices.TIME_EARLIEST_FIRST)
            .build()
        assertEquals("SELECT * FROM `transaction` WHERE amount >= 100 AND isExpense = 0 AND isDigital = 1 ORDER BY timestamp ASC", query.sql)
    }

Now, in the UI, whenever any of the filter parameters are changed, a new Query is built and executed:

/**
     * Generates an SQL query from the current configuration and executes it through Room.
     * An observable flow is returned, updates whenever there's a change in the database
     */
    private fun executeConfig() {
        val query = QueryBuilder()
            .setFilterAmount(QueryConfig.filterAmountChoice, QueryConfig.filterAmountValue)
            .setFilterType(QueryConfig.filterTypeChoice)
            .setFilterMedium(QueryConfig.filterMediumChoice)
            .setSortingChoice(QueryConfig.sortChoice)
            .build()
        viewModelScope.launch {
            db.transactionDao().customTransactionQuery(query).collect {
                transactions.value = it
            }
        }
    }

This allows us to efficiently generate our SQL queries dynamically and handle any number of filter parameters in linear code complexity.

Transactions has a simple philosophy inspired by Alex Chiou and Rahul Pandey: In a side-project, build only a few features, but build them well. The entire source code is available on GitHub: https://github.com/sanskar10100/Transactions

This is what the app looks like now:

Transactions is available on the Play Store as well. Install it from: https://play.google.com/store/apps/details?id=dev.sanskar.transactions (At the time of writing the incremental filtering update is still in beta, so it may require some time to show up on your device).

QueryBuilder: How I wrote a runtime SQL query generator for an Android app

Comments

More from this blog

A note on Context Windows

Drawing Custom Alerts on Top of Bottom Sheets in Jetpack Compose

Automating Bluetooth Profile switching thanks to Copilot Chat

Context Receivers in Kotlin: An Example

Integrating Google Maps, Places API, and Reverse Geocoding with Jetpack Compose

Let's get some context about Transactions to understand the problem better

The challenge: Incremental Sorting and Filtering

Solution 1: Do filtering on Kotlin lists

Solution 2: Write custom DAO methods for every combination

Solution 3: Find a way to feed the list back a custom SQL view, and then apply filtering on the view incrementally.

My Solution: Build Query in Runtime

The QueryBuilder

Command Palette

Comments

More from this blog

Let's get some context about Transactions to understand the problem better

The challenge: Incremental Sorting and Filtering

Solution 1: Do filtering on Kotlin lists

Solution 2: Write custom DAO methods for every combination

Solution 3: Find a way to feed the list back a custom SQL view, and then apply filtering on the view incrementally.

My Solution: Build Query in Runtime

The QueryBuilder