SQLDelight in Android: Getting Started

We have learnt that for any SELECT query added to a script file, SQLDelight will generate not only a method,

but also a dedicated class if multiple columns are selected from a table. This is very convenient, but sometimes the library cannot distinguish between equivalent data polled from different functions and that can create some awkward situations.

Assume new feature wherein the list of bugs in a collection can be sorted by name or by quantity. This can be mapped easily to a pair of SELECT functions inside the inCollection table.

Let’s create two copies of the existing function ‘listBugsInCollection’ and update their SQL to include an ORDER BY clause for name and quantity, respectively.

listBugsInCollectionByName:
SELECT
  bug.bugId,
  name,
  imageUrl,
  quantity
FROM inCollection
NATURAL JOIN bug
WHERE collectionId = :collectionId
ORDER BY name;

listBugsInCollectionByQuantity:
SELECT
  bug.bugId,
  name,
  imageUrl,
  quantity
FROM inCollection
NATURAL JOIN bug
WHERE collectionId = :collectionId
ORDER BY quantity;

This will, of course, create new methods for the InCollectionQueries class. Let’s open that class and check it out.

Here it is, and here we can see the problem rifht away: If we compare the return types of these new methods, we can see that they are different, even though the query methods under the hood pull the exact same columns.

In fact, if we open up the generated classes for ListBugsInCollectionByName and ByQuantity and put them side by side, we can spot the similarities immediately: they are one and the same, but SQLDelight doesn’t know that.

It can become tedious to deal with these equal-but-different model types so it would be nice to tell the library that these two actually contain the same information. SQL Views are here to help us in this regard.

In case you’re not familiar with Views in SQL, you can think of them as essentially “virtual tables”. They don’t produce a full-fledged table and store data themselves, like ‘bug’ or ‘inCollection’, but instead they aggregate the data in a preprocessed manner and allow you to - well - view it!

SQLDelight understands Views and we can create sub-queries on this virtual table instead of the real ones, reusing the same model type across different functions.

This may sound a bit abstract but I’m sure it’ll become clearer when we apply this knowledge to our sample app.

Adding a View to the database is an action that requires a migration. Let’s add a file named 2.sqm to the migration folder and open it. In here, declare the new View using the special SQL syntax CREATE VIEW AS. The view’s name is assigned as well and finally, write a SELECT statement that describes the data to address under this name.

Let’s call our view ‘bugsInCollection’ and append one of the SELECT statements from the inCollection script to the end of this line. The only change we need to make is also pulling the collectionId column from the table, since View creation statements can’t have a WHERE clause. This will need to be added to all of the query functions that access the View down the line.

CREATE VIEW bugsInCollection AS
SELECT
  collectionId,
  bug.bugId,
  name,
  imageUrl,
  quantity
FROM inCollection
NATURAL JOIN bug;

Okay, now that this view is created, make sure to create this entire statement into the inCollection script itself. Remember that the script files always reflect the latest state of the database, so the view needs to exist here as well.

I will paste the creation statement of the view underneath the CREATE TABLE statement of the inCollection script.

With this step completed, it will make SQLDelight create a class called ‘BugsInCollection’. At this point, it’s possible to refactor these queries down here so that they pull all of the columns from this View, instead of making a selection on the actual table instead.

By using the star projection here, the return type of the queries will be the same - our new BugsInCollection class! The only thing that needs to be retained from the original functions is the WHERE clause. Other than that this looks pretty much done.

listBugsInCollectionByName:
-SELECT
-  bug.bugId,
-  name,
-  imageUrl,
-  quantity
-FROM inCollection
-NATURAL JOIN bug
-WHERE collectionId = :collectionId
+SELECT *
+FROM bugsInCollection
+WHERE collectionId = :collectionId
ORDER BY name;

listBugsInCollectionByQuantity:
-SELECT
-  bug.bugId,
-  name,
-  imageUrl,
-  quantity
-FROM inCollection
-NATURAL JOIN bug
-WHERE collectionId = :collectionId
+SELECT *
+FROM bugsInCollection
+WHERE collectionId = :collectionId
ORDER BY quantity;

In fact, we might as well refactor this original statement from earlier, too! Replace the projection of the original with a similar refactoring to what we just did to the others, and we will get a third method using the same return type.

listBugsInCollection:
-SELECT
-  bug.bugId,
-  name,
-  imageUrl,
-  quantity
-FROM inCollection
-NATURAL JOIN bug
-WHERE collectionId = :collectionId;
+SELECT *
+FROM bugsInCollection
+WHERE collectionId = :collectionId;

Let’s open the InCollectionQueries class one more time to verify this change and indeed, the three methods share the same return type! This makes it easier to consume the data, so in general you may want to consider using Views for queries that pull similar data out of your database.

In order to make the app compile again, we have to add one change to the DatabaseRepository, as the listBugs function now pulls one additional column.

Inside the method ‘listBugsInCollection’, add one parameter to the start of this mapper. Use an underscore for the parameter name as the Kotlin side doesn’t need this value.

fun listBugsInCollection(collectionId: Long): Query<BugWithQuantity> {
    return database.inCollectionQueries
-        .listBugsInCollection(collectionId) { bugId, name, imageUrl, quantity ->
+        .listBugsInCollection(collectionId) { _, bugId, name, imageUrl, quantity ->
            BugWithQuantity(bugId, name, imageUrl, quantity)
        }
}

Click the Run button to launch the app and everything should still work as before. As the last step, execute the schema generation task from Gradle to create a new file, which will upgrade the database to version 3, and complete the change.