SQLDelight in Android: Getting Started

If you have followed along with the sample app until this point, there are only two functions missing before the UI is completely functional, and we can find them in the CollectionDetailViewModel.

On this screen, there is a button on the bottom to create a bug with random properties and add it to the current collection. This function is more tricky than it first seems, since multiple tables have to be updated for a single bug.

After all, remember that the stats of a bug have their own table: The BugAttributes! This single function of adding a bug is actually a combination of two SQL statements.

As we have seen previously, SQLDelight has an amazing understanding of how SQL works, but it also builds on top of this knowledge to create some new features as well.

If you ever find yourself in a situation where you need to execute multiple SQL statements for a single task, consider using a grouping statement.

In a nutshell, a grouping statement is a function inside a script file with more than one SQL statement inside of it.

These three functions can be written individually, but by using a grouping statement, they are merged into a single function that executes multiple SQL queries under the hood.

Open the bug.sq file and add a new function to it. I’m adding it in front of the existing one, but you can add it to the end if you like.

The function name will be ‘insert’ and it will become a grouping statement. Normally a SQLDelight function is declared with a colon, but for grouping statements, you use curly braces to open a new block.

insert {
}

Inside this block, you can execute as many SQL statements as necessary for the task at hand. First, I will write the insertion statement into the Bug table itself, declaring two parameters for name and description along the way.

insert {
+    INSERT INTO bug(name, description)
+    VALUES (:name, :description);
}

This covers the bug table, but now we will declare a second statement below for the insertion of the attributes, associating that object with the newly created bug from the first line.

Write out the columns of the bugAttributes table like so and provide the values via parameters.

insert {
    INSERT INTO bug(name, description)
    VALUES (:name, :description);

+    INSERT INTO bugAttributes(bugId, size, weight, attack, defense)
+    VALUES (..., :size, :weight, :attack, :defense);
}

There is a problem now: We need to provide the bugId of the bug from the first line, but we can’t get this information from it directly as there is no return value for an INSERT INTO query.

Turns out, there are many convenient functions hidden in the SQL documentation that can be leveraged. Since queries can contain other queries, we will use the special function ‘last_insert_rowid()’ to grab the ID of the most recently added bug, and give that as the bugId. Mission accomplished!

insert {
    INSERT INTO bug(name, description)
    VALUES (:name, :description);

    INSERT INTO bugAttributes(bugId, size, weight, attack, defense)
-    VALUES (...), :size, :weight, :attack, :defense);
+    VALUES ((SELECT last_insert_rowid()), :size, :weight, :attack, :defense);
}

The remaining parameters will get placeholders again as the data will be provided from the Kotlin side, and this is the final result! SQLDelight will generate a single function called ‘insert’ for the BugQueries class, and when it’s called it will execute both of these statements in sequence. Let’s use it!

Open the DatabaseRepository class and add a new method called ‘addBug()’ to it. Provide a truckload of parameters for each of the bug’s properties to it.

Inside the method body, you already know the drill: Using the database field, access the query class for bugs and call the new method!

fun addBug(
    name: String,
    description: String?,
    size: String,
    weight: String,
    attack: Int,
    defense: Int
) {
    database.bugQueries.insert(name, description, size, weight, attack, defense)
}

We are halfway through the last big piece of logic in our sample app now, but let’s stop here and complete this process in the next lesson, after learning about another useful tool for dealing with SQLDelight.

In summary, grouping statements are very useful for semantic groupings of queries that don’t have any conditional logic or complicated inter-dependencies.

They are declared in a similar fashion to ordinary script functions, but where those use a colon, the grouping statements expect a pair of curly braces.

Lastly, it’s important to note that grouping statements cannot return any value, it’s always Unit! They are useful for anything other than SELECT statements. You will want to use other structures for complex functionalities, such as transactions, which are covered in the next lesson.