SQLDelight in Android: Getting Started
Aside from the other wonderful features that we have explored so far, SQLDelight also supports one of the most fundamental concepts of working with SQL databases: Transactions.
A transaction is a unit of work that may consist of multiple smaller parts, and that is committed to the database as a single block: It’s kind of similar to how Android’s SharedPreferences work.
Say that we wanted to update data in two different tables. The first statement works fine and the data is inserted, but something goes wrong with the second update.
If the two queries were executed without a transaction, then the first change would stay in the database if the second one fails. If the two statements depend on each other, that could lead to an inconsistent state.
If the two changes are executed in the context of a transaction, all changes would be discarded when the second one fails, including that first change which worked successfully.
With a transaction, we can ensure that the database is always in an expected state, without the chance of any half-committed changes. The process of reverting a transaction is called a rollback, and SQLDelight provides access to both of these concepts with an easy-to-use API.
In our case, when a new bug should be inserted into the database, two different tables are being affected: of course, first a new row is added to the Bug table. On top of that however, remember that the stats of a bug have their own table: The BugAttributes!
And let’s also not forget that the new bug should be added to a specific collection, so we need to store a new reference between it and the collection in the inCollection table, too.
In total, there are three parts to pulling off this operation. We built the first two of these statements with a grouping statement earlier, so those are already good to go.
You can think of a grouping statement like an “inline transaction” because it has similar properties to a full-fledged transaction.
For the sake of showing off the API in SQLDelight, we wil add the third query as a separate function and execute everything together inside a transaction.
So let’s remember the three-step process of adding functions to the sample app and get to work. We are going to create the query for the inCollection table first, extend the DatabaseRepository afterwards and finally use it from the ViewModel.
In order to add a bug to a collection, we will create a new function for the inCollection table. Open its script file and append it to the end. It’s a straightforward INSERT INTO statement, just like the ones we added previously for other tables. I will call it addBugToCollection.
addBugToCollection:
INSERT INTO inCollection(collectionId, bugId, quantity)
VALUES (:collectionId, :bugId, :quantity);
Next, open the DatabaseRepository class and find the addBug() method from earlier. In the sample app, a bug is always associated with a collection, so we might as well update this method to automatically attach it to a collection with a specific ID. And you guessed it - we will do this with a transaction!
Add a parameter for the collection ID to the beginning of the parameter list.
fun addBug(
+ collectionId: Long,
name: String,
description: String?,
size: String,
weight: String,
attack: Int,
defense: Int,
quantity: Int
) {
database.bugQueries.insert(name, description, size, weight, attack, defense)
}
Next, add a new line to the start of the method body and access the BugQueries field of our database.
From here, type in “t r a” to see the IDE suggestions. We can see that SQLDelight generated a few methods for transactions. All of them receive a lambda block, which makes up the contents of the transaction.
If you need to return a value from this block, use one of the variants called ‘transactionWithResult’. Otherwise, use the simple ‘transaction’ method.
fun addBug(
collectionId: Long,
name: String,
description: String?,
size: String,
weight: String,
attack: Int,
defense: Int
) {
+ database.bugQueries.transaction {
+
+ }
database.bugQueries.insert(name, description, size, weight, attack, defense)
}
Inside this block, we have access to a Transaction object, which allows users to perform a rollback whenever they want with this method. Aside from that, there are a few listener methods that can be attached to the transaction. “afterRollback()” is called whenever a rollback actually happens.
Similarly, “afterCommit()” is called after the block was successfully applied to the database. Use these when you need to be notified of these events - in our case, it’s not necessary.
database.bugQueries.transaction {
+ afterRollback {
+ }
+ afterCommit {
+ }
- afterRollback {
- }
- afterCommit {
- }
}
At the start of the block, cut and paste the insertion statement for the Bug table that we already defined before. Remember that this is actually the grouping statement under the hood and performs two insertions in different tables.
database.bugQueries.transaction {
+ database.bugQueries.insert(name, description, size, weight, attack, defense)
}
-database.bugQueries.insert(name, description, size, weight, attack, defense)
Afterwards, we want to obtain the ID of the newly inserted bug and use its ID for the relationship to its collection. Since we added this function to the inCollection table, access the inCollectionQueries here and select ‘addBugToCollection’.
The collectionId is given to the DatabaseRepository up here, so we can pop that into the method right away, but we’re lacking the bugId and quantity parameters. The latter is a value representing how many units of the bug are part of the collection.
Let’s assume that the ViewModel can decide the amount, so add another parameter to ‘addBug()’ and use it in the query.
fun addBug(
collectionId: Long,
name: String,
description: String?,
size: String,
weight: String,
attack: Int,
defense: Int,
+ quantity: Int
) {
database.bugQueries.transaction {
database.bugQueries.insert(name, description, size, weight, attack, defense)
+ database.inCollectionQueries.addBugToCollection(
+ collectionId = collectionId,
+ bugId = ...,
+ quantity = quantity
)
}
}
But for the bugId, we will need another function. Remember that only SELECT statements return a value in SQLDelight, so this insertion call up here actually just returns Unit.
Previously, we used a special SQL function to get the ID of the most recently inserted item and we will need to make use of this feature again now.
Open the script for the bug table and add another new function to it, called ‘getLastInsertedId’. It’s a SELECT query that grabs the last_insert_rowid() and returns it - that’s it.
getLastInsertedId:
SELECT last_insert_rowid();
Back in the DatabaseRepository, call that method in-between the two lines of the transaction block store the value in a variable called bugId. This is the last piece of the puzzle.
Since getLastInsertedId() returns a Query object again, execute it immediately to get the underlying Long.
fun addBug(
collectionId: Long,
name: String,
description: String?,
size: String,
weight: String,
attack: Int,
defense: Int,
quantity: Int
) {
database.bugQueries.transaction {
database.bugQueries.insert(name, description, size, weight, attack, defense)
+ val bugId = database.bugQueries.getLastInsertedId().executeAsOne()
database.inCollectionQueries.addBugToCollection(
collectionId = collectionId,
+ bugId = bugId,
quantity = quantity
)
}
}
With the repository prepared, let’s go back one more time to the ViewModel of the collection detail screen and update its use of the method for adding a bug. It’s already prepared down here next to the TODO comment.
The method creates a bug object with random values and we simply want to add it to the database. Remove the comment and call the repository’s addBug() function.
There’s a bunch of typing here, mapping all the values of the bug to the correct parameter. For the last parameter, let’s make the initial quantity of a new bug equal to 1. There’s some other UI which can update this quantity in the sample app.
fun addBug() {
// Generate some random data for the bug, then add it
val bug = Random.nextBug()
repository.addBug(
collectionId = collectionId,
name = bug.name,
description = bug.description,
size = bug.size,
weight = bug.weight,
attack = bug.atk,
defense = bug.def,
quantity = 1
)
}
The feature is complete! Hit the Run button to deploy the app and select a collection from the list or create a new one. Then, hit the Plus button at the bottom to create random bugs! A bunch of data is inserted every time this happens, and it’s all thanks to SQLDelight and our DatabaseRepository.
We have learnt how transactions can group together multiple SQL statements and the app is pretty much done at this point.
The only mising feature are these smaller buttons for adjusting the quantity. It should also use transactions, but the implementation is left as an exercise for you to complete on your own.
For reference, the sample project for the next lesson will have everything completed already. So as a small challenge in-between lessons, you can test your scripting skills with SQLDelight and I’ll catch up with you in the next lesson.