SQLDelight in Android: Getting Started

We have now declared the contents of our database by means of the script files and SQLDelight created classes each table. However, it also generated something else - a Database class! This is how we will access the tables from our codebase, so now is a good time to connect the database to the rest of our app.

The architecture of the sample app uses the MVVM pattern to communicate data between the UI and business logic layers.

The user’s actions flow through the layers from left to the right all the way until the database, then the data comes back in the other direction. No layer is skipped, ensuring optimal separation of concerns.

During the set-up of the library, we added a dependency on the “Android Driver” to the code. It provides the Android-specific implementation of a central SQLDelight API:

The SqlDriver. When we want to actually use the Database class that the library generated for us, we have to provide an implementation of the SqlDriver interface to its constructor.

This is how the library pulls off its multiplatform functionality: we are going to instantiate an AndroidSqliteDriver and pass it to the constructor, but we could also use one of the other drivers to run your database on these stacks instead if we built a Desktop app instead, for example.

We will see how to swap the underlying driver when it comes to testing our DatabaseRepository, but for now let’s hook up the Android driver to it first.

For the sake of simplicity, the app doesn’t use any dependency injection framework, so the singleton objects are stored inside its Application class. The DatabaseRepository implementation is “injected” into the ViewModel that talks to the Activity it’s connected to.

This repository class is the one that uses SQLDelight under the hood and exposes some methods to deal with the database to the ViewModels that use it. Right now, this repository is empty, so let’s change that!

Back in Android Studio, open up the App class in the main package. As I mentioned, this is where the global dependencies are stored for the bug collector app, and we can see that it creates an instance of the DatabaseRepository inside of the onCreate() method here.

We will make two changes now: First, we add a parameter for an SqlDriver to the DatabaseRepository constructor, and second, we will use this driver inside the repository to create our Database object.

Let’s move over to the DatabaseRepository and update its constructor first - just like that, the first step is already done.

-class DatabaseRepository
+class DatabaseRepository(driver: SqlDriver)

To address the second one, let’s create a Database object! Declare a private val inside the repository and call it ‘database’.

Then, simply instantiate the Database class and pass the driver to it! Note: The name of the Database class depends on what we have configured in our build script. This name here always matches the class name that we will use in the repository, so if you used any other name, please adjust the cosntructor call accordingly!

Okay, this is actually everything for the repository class for now. We will add more methods to it in the future so that the database field is actually used, but in terms of set-up, this is all we have to do here.

-class DatabaseRepository(driver: SqlDriver)
+class DatabaseRepository(driver: SqlDriver) {
+    private val database = Database(driver)
+}

Going back to the App class, we will now fill in the newly added parameter for the DatabaseRepository constructor. As mentioned previously, the Android-specific implementation of the SqlDriver interface is called AndroidSqliteDriver.

Let’s create one here and go through its own parameters, because there is a bunch of them. First, it needs to know the ‘schema’ of the database, so basically the creation statements for every table that we have. Don’t worry, it’s all auto-generated once more.

There is a property on the Database class itself that expresses the schema, so all we need to write here is ‘Database.Schema’. The next parameter is a context and should be fairly straightforward.

We are in the Application class, which conveniently is a context, so let’s use the self-reference here. Next up, a name must be given to the database file on disk. This can be anything you like and I will use “bugs.db”.

databaseRepository = DatabaseRepository(
+    AndroidSqliteDriver(
+       schema = Database.Schema,
+       context = this,
+       name = "bugs.db",
+    )
)

That’s all the required parameters that the AndroidSqliteDriver needs: The remaining ones are useful for customizability.

We are going to use one of the optional parameters in fact and I’d recommend you using this one as well. We can give a callback object to the driver, which will be called when the database has been configured, deleted, upgraded and so on.

Remember how we added constraints to some of our foreign keys in the SQLDelight script files? Here’s an example from the bugAttributes table.

It has a foreign key reference to the Bug table and this constraint here: “ON DELETE CASCADE”. This kind of statement is actually disabled on Android by default and we must explicitly enable it to make it work.

The callback object is the perfect opportunity to do this! Back in the App class, add the callback parameter after the name and create an anonymous implementation of the AndroidSqliteDriver Callback class. This class needs to know the database schema as well, so provide it through the constructor one more time.

In the body down below, override the method named “onConfigure”. It gets called whenever SQLDelight creates, upgrades or opens the database and provides you with a parameter of type SupportSQLiteDatabase.

This is a class from the AndroidX library for databases, which the Android Driver secretly uses under the hood. On this db object, we can configure our foreign key constraints with this call.

databaseRepository = DatabaseRepository(
    AndroidSqliteDriver(
       schema = Database.Schema,
       context = this,
       name = "bugs.db",
+      callback = object : AndroidSqliteDriver.Callback(Database.Schema) {
+           override fun onConfigure(db: SupportSQLiteDatabase) {
+               super.onConfigure(db)
+               db.setForeignKeyConstraintsEnabled(true)
+           }
+      }
    )
)

That’s it! Our database is instantiated and the Repository could go ahead and make use of it now. There should not be any compilation errors and the app should start just like before.