SQLDelight in Android: Getting Started

When it comes to database logic, validation and integrity play an important role - arguably even moreso than other application code. As developers, we have to make sure that the schema of the database is always consistent and correct, especially when migrations come into play that transform the schema over time.

It would be highly annoying for users if their app crashed after an update, just because of an inconsistent change to the app’s database layer.

SQLDelight takes an active part in helping developers to write correct code. Among the settings that we can configure in the SQLDelight block in the build script is a flag called ‘verifyMigrations’.

I strongly suggest to keep this flag enabled at all times: if it is, then a new type of verification task is added to your project. Let’s execute it from our Android Studio by pressing F10 and searching for it in the list of Gradle tasks.

The plugin generates a task for each variant of the app and a catch-all task called ‘verifySqldelightMigrations’ to check every variant at once. Let’s execute that one!

Note how all variant tasks are executed one after the other, but how no other output occurs. This is what you’d like to see: If this task is successful, the database schema is consistent and correct from version 1 all the way to the latest version. I’m feeling particularly cheeky today, so let’s do an unsolicited change and destroy this integrity!

Open the script file of the bug table and add some random column to the CREATE TABLE statement. I’ll call it ‘foo’ and make it an INTEGER column - this is just for demonstration purposes so it doesn’t matter what you choose.

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

Now, remember that the sq files always reflect the latest revision of the table, so in theory, I would need to also create a migration file with an ALTER TABLE statement. Execute the verification task one more time and observe how it fails now.

SQLDelight is tripping over the fact that it found a column that hadn’t been announced with a migration file. Basically, the way it works is that the plugin will create two databases and compare their content afterwards: one database is created fresh, using the content of all script files and nothing else.

The second database uses the first file in the schema folder and transitions that database to the latest state using all migration files that it can find along the way. At the end of this process, both databases should be absolutely identical.

In our case here, the migrated database is missing the ‘foo’ column - only the fresh database has it. That’s why SQLDelight throws this error.

Again, to fix this, we would have to create a file called 3.sqm and put the addition of the new column in there, too. We can remove the foo column now as we won’t need it and close the script file again.

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

Automated schema validation is one of the best features of SQLDelight, but it’s not the only piece of its testing story. The folks over at Cashapp put a lot of thought into the design of their database library and the introduction of the SqlDriver interface, which is the access point to get down to the database.

In the main source code of the Android app, we already utilize the AndroidSqliteDriver implementation and give that to the DatabaseRepository in order to handle this stuff.

For unit test code, the repository can a different implementation without ties to the Android ecosystem to do the same work! This practice of replacing the real implementation of a component with another during testing is quite common, and with SQLDelight, it’s made very straightforward because all the pieces are provided to us out-of-the-box. Let’s check this out!

Open the build script of the app module and add a dependency on JUnit to the testImplementation config in Gradle.

dependencies {
    testImplementation "junit:junit:4.13.2"
}

Other than that, we also add a dependency on another SQLDelight artifact as well - the “sqlite-driver”. This is a different implementation of the SqlDriver interface and can be used for testing.

Note how we use the same version variable again to ensure that all libraries are compatible with each other.

dependencies {
    testImplementation "com.squareup.sqldelight:sqlite-driver:$sqldelightVersion"
}

After adding all of this, sync the project to make it available to the test code.

Next, create the test source set by right-clicking the src folder of the app module, selecting New > Directory and entering ‘test/java’. In here, let’s create a package structure similar to the main source set. You guessed it, it’s another right-click, then go to New > Package and enter the package name of the sample app.

Since we will add a test forthe DatabaseRepository class, let’s add “repository” to the end of the declaration. I like having the class and its unit tests in a mirrored package structure like this.

Finally, right-click that new package, create a new class called ‘DatabaseRepositoryTests’ and open it.

Let’s write a test to verify that adding a new collection works as expected. As you know, test methods are annotated with JUnit’s @Test annotation; for the name, I’m using a descriptive text and Kotlin’s backticks.

@Test
fun `test creating a new collection`() {
}

In the method, we will create a new repository with a special driver for testing, then call it’s addCollection() method and finally check that a repository with that name actually exists.

@Test
fun `test creating a new collection`() {
+    val repository = DatabaseRepository(...)
+
+    repository.addCollection("New Collection")
+
+    val collections = repository.listCollections().executeAsList()
+    assertEquals(1, collections.size)
+    assertEquals("New Collection", collections.first().name)
}

The test method is easy enough to write, but what about the driver that we give to the repository? Since this is a unit test, we don’t have access to Android stuff, so the AndroidSqliteDriver cannot be used.

This is the reason that we added another driver just for testing! Add a private field to the test class and name it driver. Initialize it as a new JdbcSqliteDriver and pass the special constant IN_MEMORY to its constructor.

Jdbc stands for Java Database Connectivity, an API for dealing with database in the non-Android Java world. The IN_MEMORY constant needs to be imported, so allow the IDE to do it when asked. Finally, pass this variable to the constructor of the DatabaseRepository inside the test!

+private val driver = JdbcSqliteDriver(IN_MEMORY).also(Database.Schema::create)

@Test
fun `test creating a new collection`() {
+    val repository = DatabaseRepository(driver)

    repository.addCollection("New Collection")

    val collections = repository.listCollections().executeAsList()
    assertEquals(1, collections.size)
    assertEquals("New Collection", collections.first().name)
}

Basically, each unit test would get a new database created in-memory, not on disk. This way, your tests don’t interfere with one another since everybody has a clean slate to work with.

There is one thing that we have to do for the Jdbc driver, and that is to create the database schema when the driver is created. To do this, add an ‘also()’ block after the driver’s constructor call. In there, use the driver to call the create() method of the database schema.

The Android driver does this for us under the hood, but here we need to do it manually. This call here can be converted into a method reference, so put the cursor inside of it, hit Alt+Enter or Option+Enter to show available actions and select “convert lambda to reference”. Very tidy!

-private val driver = JdbcSqliteDriver(IN_MEMORY)
+private val driver = JdbcSqliteDriver(IN_MEMORY).also(Database.Schema::create)

Alright, this should be it for the first unit test of our repository. Click on the Play button next to the test class name on the side to execute its tests. And there you go! The first of many tests of the database layer.

Just to show that this does work correctly, let me make this test fail by changing the name of the collection.

You can see that the data pulled from the database now uses the name “Great Collection”, but the assertion expected “New Collection” instead. The memory database works and we can continue writing many more tests to verify our logic!