SQLDelight in Android: Getting Started
We have covered the creation of tables and columns, but so far we haven’t actually used the database for something substantial - you know, like inserting or fetching data. Well, no more I say! Let’s add some functionality to the sample app.
There are three Activities in the app: An overview screen of bug collections, the detail page of a collection where all bugs in that collection are displayed, and finally a dedicated screen for a bug itself. We will start by connecting the functionalities for the collection overview screen together, step by step.
On this screen, there are two contact points with the database that we can be see: First, this RecyclerView shows a list of all collections in the database, in the order that they were created. Furthermore, the floating action button will open a dialog from which a new collection can be created.
Open the SQLDelight script file for the collection table. As a rule of thumb, the creation statement of the table should always be at the top of a file and all functions follow underneath.
The syntax for adding a new function is very simple: write a name for the function and a colon. In the next line, write the SQL statement for this query. It’s that easy!
Getting the list of collections is a basic SELECT query - let’s call the corresponding function “all”. In the next line, write “SELECT * FROM collection” to retrieve all rows from the table.
all:
SELECT * FROM collection;
The return type of these query statements is determined by whatever is selected in the SQL itself - in this case, the return value will be of type Collection since we use a star projection. When you select only certain columns, SQLDelight will create a special class for these values - we’ll see that later.
Let’s use this function in the code. As always, the IDE plugin has automatically generated it. Functions are stored in a dedicated Queries class for each table. Remember when we discovered how each table generated two Kotlin classes?
Well, here is the reason for that second class. Press the Shift key twice to bring up the “Search Anywhere” menu, then type in the word “CollectionQueries” and open the class.
In here, we will find two few methods called ‘all()’! One of them has a parameter called ‘mapper’: This can be used to transform the database type into something more suitable for your purpose. The variant without parameters simply yields the generated class directly, which is fine in this particular case.
Let’s connect the query to the DatabaseRepository and then use that connection from the ViewModel of the first screen.
In the repository, create a new method called ‘listCollections’ and make it return a Query of Collection. ‘Query’ is a SQLDelight class that encapsulates one specific statement and provides some cool listeners that we will use in a moment.
In the method body, use the private database field to access the ‘collectionQueries’, and from there, call the generated ‘all()’ method. That’s all for the DatabaseRepository for now.
fun listCollections(): Query<Collection> {
return database.collectionQueries.all()
}
Next, open the CollectionListViewModel - this is the ViewModel for that first screen in the app. After opening it, create a private field for the query and initialize it by calling the repository’s listCollections() method.
The reason for making a private field for this is the fact that the UI should automatically refresh itself whenever the list of collections to show on screen changes. Later in this course, we will see how we can use other frameworks for this, like RxJava and Flow, but for now we stick to the basics.
private val collectionQuery = repository.listCollections()
First, fill in the ‘refreshState()’ method, replacing the TODO comment with some logic. We can execute a query in a number of different ways, but here we want to get all results, not just the first one. Therefore, choose ‘executeAsList’!
private fun refreshState() {
_state.value = State.Result(
collections = collectionQuery.executeAsList()
)
}
Next, go to the constructor and register a listener to the query field. This listener will be called whenever there is new data to be fetched for the query. We create a private field for the listener as well, because we need access to it from multiple methods.
The Listener interface has a single method, and whenever it is called, we simply invoke ‘refreshState()’ again to re-run the query and update the UI. Finally, override the ViewModel’s onCleared() function and remove the listener from the query again.
private val collectionQuery = repository.listCollections()
+private val collectionQueryListener = object : Query.Listener {
+ override fun queryResultsChanged() {
+ refreshState()
+ }
+}
init {
refreshState()
+ collectionQuery.addListener(collectionQueryListener)
}
+override fun onCleared() {
+ collectionQuery.removeListener(collectionQueryListener)
+}
We now have an auto-updating connection to the database via the repository, and the UI will receive new data whenever it changes behind the scenes. Let’s build the ‘Add Collection’ feature next so we can complete this screen!
Back in the collection script file, append a new function to the end of it and call it ‘insert’. In SQL, this is how to write an insertion statement - if needed, please consult the internet for some guides on how the syntax works here!
insert:
INSERT INTO collection(creationTime, name)
VALUES (:creationTime, :name);
Note how the variables in the first row can be referenced by adding a colon in front. SQLDelight also understands question marks for placeholders, which is quite common in SQL as well.
Also note that the IDE shows parameter names here, but there seems to be a bug with it in the current version of SQLDelight where the naming doesn’t match completely - oops.
With the SQL out of the way, move back to the DatabaseRepository and add another method to it: ‘addCollection()’.
It receives the name of the new collection as a parameter and uses the new method from the SQL file to insert it into the database. The generated method has two parameters, just like the INSERT INTO statement from SQL.
For the timestamp, we use whatever the current UTC timestamp is.
fun addCollection(name: String) {
database.collectionQueries.insert(
creationTime = ZonedDateTime.now().withZoneSameInstant(UTC),
name = name
)
}
The final step: Open the ViewModel again and fill out the existing addCollection() method in there. Call through to the DatabaseRepository and hand over the parameter to it.
fun addCollection(name: String) {
repository.addCollection(name)
}
Okay, I know it’s taken a long time to get to this point, but we can finally observe some results from the app. Hit the Run button to deploy it to your device and click the action button on the bottom.
Add a name, hit OK and BOOM! There is the collection at the top. We can see that the listener for the selection query automatically updates the data. Very, very nice.
The same basic procedure repeats itself for any type of SQL query. The general gist is always the same, no matter what kind of statement you want to create: and a method to the script file, connect this method to the DatabaseRepository, then consume that method from a ViewModel.
Profit! Let’s briefly review a couple of statements for the second screen and implement them off-camera.
Alright, so we can see that there are a looot of contact points to the database here.
I want to build the last two functions with some additional features of SQLDelight, so let’s not deal with those for now.
Starting from the top, the Edit function will become an UPDATE function called ‘rename’. It has two parameters: The collection’s new name and its ID. We will add this to the script file of the collection table.
Similarly, the Delete button is connected to a DELETE FROM function - a good name for this might be ‘deleteById’.
It only needs the item’s ID and SQLDelight will do the job. Again, if you’re unsure about how to write database queries like this, feel free to pause the lesson here and look up an SQL handbook online.
Let’s go to the next part of the screen. Down here, we need to get the details of single collection by ID and also the list of bugs that it contains. Getting a single item by its ID is another SELECT function.
Here you can see that SQLDelight understands the question mark as a placeholder, so you don’t have to use the named parameter approach from before.
Getting the list of bugs for a specific collection requires a slightly more advanced query. This will be added to the inCollection script file, the relational table between collections and bugs. We will select a row from this relation and join it with the associated data from the bug table.
Also, instead of the usual asterisk projection, let’s select the interesting columns manually. As I mentioned before, when this happens, SQLDelight will generate a class with just these fields for you. The class is named after the function, so here it would be a class called ‘ListBugsInCollection’.
Alright, that was a lot of code to process. Before moving on, feel free to review the sample app and this lesson in case anything went a bit too fast.
Starting from the next lesson, most of the remaining queries will be prepared already, so make sure to check out the SQLDelight script files, as well as the DatabaseRepository class for more info. We are close to finishing the logic for all queries, so let’s continue with a few more advanced features to get this done.