SQLDelight in Android: Getting Started

Let’s face it - the first version of your database will not be the last. Eventually, changes will need to be made to the data, or you want to add new tables or delete old ones.

Migrations are an essential part of dealing with SQL databases; here’s how they work with SQLDelight.

Basically, a table’s script file describes the latest revision of the table at all times. This means that, if you wanted to add a new column to a table, for instance, you’d simply add it to the CREATE TABLE declaration in that table’s file.

The versioning history of a database is kept in separate files, conveniently called Migration Files. These files have the file extension .sqm and can be located anywhere in the SQLDelight source set, preferably in their own folder.

The file name corresponds to the old version number. In the beginning, a database will have version 1. To upgrade to the next version, a file called 1.sqm is created. To upgrade from version 2 to 3, you would use the file 2.sqm - you get the idea.

SQM files are also just text files and contain the usual SQL syntax for modifying tables: ALTER TABLE, DROP TABLE and so on.

It’s important to note that migration files are only required for structural changes to the database! If you wanted to add or remove one of the functions from a script file, this is completely fine and you don’t need to bump the database version just for that.

However, if you modify a table structurally, by adding or removing columns from it, or if you create new tables, you do need them.

The detail screens for bugs look a little barren. I mean, this icon is pretty and all, but it would be nice to have some actual images to display for each bug. Let’s update our app to support images.

Of course, the first step is to update the database model, but let’s create a snapshot of our current database schema first! This will help with testing, because SQLDelight can automatically check at compile-time that your migrations are correct.

The Gradle plugin for SQLDelight created a task with which the current schema is dumped into a file. The location of this folder is specified in the build script.

Press F10 to open the Gradle Launch dialog, then execute the generateDebugDatabaseSchema task. After it’s done, observe how a new file is created in the schemas folder. This is an empty database with the table structure at this moment in time.

Next, we will create a migration file and then invoke this Gradle task again to create the second version of the database for verification.

There are no hard requirements on where to put migration files in the sqldelight source set, but I like to create a folder called ‘migrations’ and put them all in there. After creating the folder, right-click once more, create a file called 1.sqm and open it up in the editor.

This change will affect the bug table, so write an ALTER TABLE statement for it and add a column with the correct type. If you need a refresher on how these statements work, please consult the internet for some additional resources.

I’d like the imageUrl to be an optional value so I won’t add a NOT NULL constraint here.

ALTER TABLE bug
ADD COLUMN imageUrl TEXT;

This is already it for the migration file! If Android Studio complains at this point that it cannot resolve the bug table, please remove the migration file again and make sure to run the generation task for the old schema first!

Copy the contents of that new column and open up the script file for the bug table. Add the column to its CREATE TABLE statement as well, pasting it from the clipboard.

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

Immediately, the generated Bug class will update and the database schema will adjust accordingly.

Next, we have to update a few other query functions, such that they include the imageUrl correctly. The first place that comes to mind is the ‘insert’ function in the bug script.

We will add an imageUrl parameter up here and insert that column’s value alongside the others. Similarly, add the imageUrl column to the findById() function!

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

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

findById:
-SELECT bug.bugId, name, description, size, weight, attack, defense FROM bug
+SELECT bug.bugId, name, description, imageUrl, size, weight, attack, defense FROM bug
NATURAL JOIN bugAttributes
WHERE bug.bugId = :bugId;

The final change needs to be added to the inCollection script: Open it and add the imageUrl column to the listBugsInCollection method.

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

Let’s propagate the recent database changes through the app’s codebase in order to connect bug images to it. Open the DatabaseRepository class and find the addBug() method.

Of course, this also needs another parameter now, so simply add it to the method signature somewhere and provide it to the BugQueries call.

fun addBug(
    collectionId: Long,
    name: String,
    description: String?,
+    imageUrl: String?,
    size: String,
    weight: String,
    attack: Int,
    defense: Int,
    quantity: Int
) {
    database.bugQueries.transaction {
-        database.bugQueries.insert(name, description, imageUrl, size, weight, attack, defense)
+        database.bugQueries.insert(name, description, size, weight, attack, defense)

        val bugId = database.bugQueries.getLastInsertedId().executeAsOne()

        database.inCollectionQueries.addBugToCollection(
            collectionId = collectionId,
            bugId = bugId,
            quantity = quantity
        )
    }
}

The same change must be applied to getBugById() and listBugsInCollection(): add the imageUrl parameter to those and replace the null placeholders with that parameter.

fun getBugById(bugId: Long): Query<BugDetails> {
    return database.bugQueries
-        .findById(bugId) { bugId, name, description, size, weight, attack, defense ->
-            BugDetails(bugId, name, null, description, size, weight, attack, defense)
-        }
+        .findById(bugId) { bugId, name, description, imageUrl, size, weight, attack, defense ->
+            BugDetails(bugId, name, imageUrl, description, size, weight, attack, defense)
+        }
}

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

Finally, follow the trail to the CollectionDetailsViewModel class, where the repository method is called from. One more time, add a new parameter to the call and utilize the existing imageUrl field from our BugDetails class.

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,
+        imageUrl = bug.imageUrl,
        size = bug.size,
        weight = bug.weight,
        attack = bug.atk,
        defense = bug.def,
        quantity = 1
    )
}

That’s it! Hit the Run button to launch the app and add a few more bugs. With our database upgraded to version 2, images are associated with every bug and stored alongside the remaining data!

Before we move on, execute the Gradle task for generating the database schema one more time and note how there are now two DB files in this folder. We will learn shortly how to leverage these files to always ensure that our migrations are correct.

By the way, for those that have prior experience with SQL on Android, you may be wondering where we bump the version number of the database. With SQLDelight, you don’t need to worry about it - again, all of this is handled automatically under the hood.

However, if you do need a callback in the code for a specific migration, this can be done as well. Open the App class and find the place where the Callback object is created with the database schema. In here, it’s possible to register callbacks for certain version upgrades.

We won’t need it in the sample app but let me quickly demonstrate it: the secondary parameter of the callback constructor accepts a variable number of AfterVersion objects.

Add the desired version number in the constructor and also, a lambda function at the end. This block is executed during the database upgrade, so if you need to be notified, this is the place to be.

databaseRepository = DatabaseRepository(
    AndroidSqliteDriver(
        schema = Database.Schema,
        context = this,
        name = "bugs.db",
+        callback = object : AndroidSqliteDriver.Callback(
+            Database.Schema,
+            AfterVersion(2) {
+                Log.d("App", "Called when upgrading to version 2")
+            }
+        ) {
            override fun onConfigure(db: SupportSQLiteDatabase) {
            super.onConfigure(db)
            db.setForeignKeyConstraintsEnabled(true)
            }
        }
    )
)