SQLDelight in Android: Getting Started

Let’s explore how the database of our bug collector app is supposed to work. Afterwards, we will create some tables for it!

A bug is represented by this data structure: It has a unique identifier, a name and description text. Since we want to keep track of its statistics as well, we introduce another table to store these so-called ‘bug attributes’.

There is a composition between attributes and bug: The attributes cannot live on their own and are strictly tied to a bug object; when it is deleted, the attributes will be as well. We define some attributes for a bug’s appearance - size and weight - as well as its prowess in battle, using the attack and defense stats here.

Since bugs can be grouped together, let’s add another table to express the data structure for this group. I will call it ‘collection’ - it keeps track of a creation timestamp and has a name of its own.

The relationship between collection and bug is a many-to-many relationship: Aacollection shall contain an arbitrary amount of bugs, and a bug object shall be allowed to be part of multiple collections.

Traditionally, this type of relationship is modeled with a join table, which is the mediator between the two original tables. Ours is called ‘in collection’, contains two ID columns and a quantity property. In summary, there will be four tables in our database.

There is a composition on the left side here, and a many-to-many relationship between bug and collection over here. Let’s move over to Android Studio and write some code for this!

SQLDelight uses special script files to describe its functionality, such as tables and queries. These files use the file extension ‘.sq’ and are placed in one of the source folders we have provided to the Gradle plugin.

First, right-click on the ‘main’ source set of the app module and create a new directory called ‘sqldelight’. Like the ‘java’ folder, this is the default home directory for all script files recognized by SQLDelight, and also just like the ‘java’ folder, these files are supposed to be organized in a hierarchical order.

When the tool generates the code for you in a second, it will use the folder structure inside the source directory as a guideline for the package names it uses.

Because of this, we first have to create a bunch of empty nested folders inside the ‘sqldelight’ directory. Right-click it, select New > Directory and name the first folder ‘com’.

Afterwards, right-click that new folder, create another one and name it the next part of your desired package name. I’m going to mimick the package name of the actual Kotlin code up here, so I will add the folder ‘raywenderlich’ next, then ‘android’ and ‘sqldelight’.

Finally, I would like the database models to have their own sub-package inside here, so let’s add two more folders here; ‘models’ and ‘db’.

Wwe have finally arrived at the destination folder for our script files! Remember, any SQLDelight script that we add to this folder will get its Kotlin class generated in the package ‘com.raywenderlich.android.sqldelight.models.db’.

Let’s write the script for the bug table first: Right-click the ‘db’ folder, select New > SQLDelight File. This option is added by the IDE plugin for SQLDelight and makes it super easy to create new tables, so let’s make use of it!

Call this file ‘bug’ and select Table from the list of options below. Here is the skeleton for an SQL table!

CREATE TABLE bug (

);

Please note that this course will not go into the intricacies of how SQL syntax works beyond the capabilities of SQLDelight, so feel free to look up external resources to familiarize yourself with it as we continue on!

The database schema requires three columns for a bug, so let’s add them one by one. Starting with the ID, which will be the autogenerated primary key for this table, then a required field for the name, and lastly an optional field for its description.

Save the file and believe it or not, you just created a Kotlin class!

CREATE TABLE bug (
+   bugId INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
+   name TEXT NOT NULL,
+   description TEXT
);

Don’t believe me? Let’s check out what happened behind the scenes! Open the build directory inside the app module and navigate to the ‘generated’ folder. See how there is a sqldelight folder in here?

Click through all the way down to reveal two new classes: Bug and BugQueries! If this doesn’t appear for you, please make sure you have the IDE plugin installed and enabled correctly. Just for fun, let’s open the Bug class to see what the tool generated. We can see that it’s a data class with the three properties given in the SQL statement.

Also, check out the nullability on the description field here, because we didn’t make that column NOT NULL. A verbose toString method is added as well and that’s it. Let’s repeat the process for the remaining tables so that we have four .sq files inside the model directory.

Note how SQLDelight’s script files understand SQL syntax to a tee, so it’s possible to create things like foreign keys in the creation statement for the attributes table here. Very cool!

CREATE TABLE bugAttributes (
  attributesId INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  bugId INTEGER NOT NULL,
  size TEXT NOT NULL,
  weight TEXT NOT NULL,
  attack INTEGER NOT NULL,
  defense INTEGER NOT NULL,

  FOREIGN KEY(bugId)
    REFERENCES bug(bugId)
    ON DELETE CASCADE
);

CREATE TABLE collection (
  collectionId INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  creationTime INTEGER NOT NULL,
  name TEXT NOT NULL
);

CREATE TABLE inCollection (
  collectionId INTEGER NOT NULL,
  bugId INTEGER NOT NULL,
  quantity INTEGER NOT NULL,

  FOREIGN KEY(collectionId)
    REFERENCES collection(collectionId)
    ON DELETE CASCADE,

  FOREIGN KEY(bugId)
    REFERENCES bug(bugId)
    ON DELETE CASCADE
);

Alright, we have now completed four script files. This will cause SQLDelight to generate two Kotlin classes each, giving us a total of 8 classes in the package. We’re going to refine the column types a little more before actually connecting the database code to the existing app, but this is some great progress already!

Before we continue, we need to remove the existing Collection class from the Kotlin source code. I added this in as a temporary stub so that the code would compile in the beginning, but now that we have the actual SQLDelight integration in place, we can remove it.

Delete com.raywenderlich.android.sqldelight.models.db.PlaceholderModels.kt

Open the models.db package in the app’s code and simply delete the file PlaceholderModels.kt. This will get rid of the placeholder for the Collection class and make way for the auto-generated class created by SQLDelight!