The Android Content Resolver doesn’t have the nicest API to work with, so I recently wrote a tiny helper library that allows writing pure SQL instead of using queries.

The underlying Content Provider is not guaranteed to be a SQL-complaint database, and some specific SQL expressions may not work. However, the library can be still useful to write prettier queries because SELECT , FROM , WHERE (with simple condition) and ORDER BY should work with any content provider.

The problem

When working on the third release of my app Canaree (an Android music player), I decided to move all the mapping and filtering outside of code and put them directly in SQL queries, but I immediately ran into a wall. MediaStore allows only very simple queries without losing your mind.

Take this example:

If you’ll ever want to query the first 20 artists that wrote at least 10 songs and recorded 2 albums, sorted by the artist name, you’ll have to write something like this.

The main problem is that Android adds automatically some parentheses in the selection to complete the query.

So now, aware of that, if you really want to make that query, you have to be very careful where you are putting the parentheses in the selection part depending on whether you use or not GROUP BY and HAVING , with a high risk of a runtime exception, and some not clear stack trace.

You can notice a parenthesis before GROUP BY and one after HAVING . The position of the parenthesis changes depending on what keyword are you using.

A better way

Then, after hours of runtime exceptions, I came up with this idea of writing a SQL parser that translates the SQL query to the corresponding ContentResolver query.

The final result translates to something like this. Sounds more familiar?

The library offers 2 Kotlin extension methods, contentResolver.querySql and contentResolver.querySql2 (not the best name but used for better discoverability, or maybe I’m just lazy).

The first one just return a simple android.database.Cursor , while the latter returns a Query object, that contains all the params needed to feed the old contentResolver.query (useful for external libraries like SqlBrite).

Limitations

Unfortunately, this library has some limitations due to MediaStore API itself:

When using GROUP BY or HAVING , WHERE is mandatory

or , is You can use WHERE 1 GROUP BY if you don’t need a WHERE condition

if you don’t need a condition When using LIMIT or OFFSET , ORDER BY is mandatory

or , is Obviously JOIN is not supported

On Android Q there are some more limitations. Renaming columns or using aggregate funcions is not permitted anymore, the SDK will throw a runtime exception. Same for GROUP BY and HAVING .

Where’s the library

You can find the library at the following link.

Please feel free to open an issue on the Github repo if you encounter any problems.