Setting up an SQLDelight database on your android application using Kotlin

There are a number of options to choose from when looking to have a database on your android application. In this article, we will be setting up an SQLDelight database in your already existing android application. To follow along with this article, you should have a basic knowledge of:

• Android and Android Studio
• Kotlin
• Hilt for dependency injection
• SQL

Room database is a database framework in the Android Architecture component and is usually the go-to option for android developers. It wraps SQLite to allow for robust database access while still providing the full power of SQLite.

SQLDelight, on the other hand, is a database framework developed by Square. It generates type-safe Kotlin APIs from your SQL statements. The premise of SQLDelight is: Write SQLite code and let the Gradle plugin generate APIs to run your queries for you.

Some of the pros of SQLDelight is that it:

• generates type-safe code from the SQL statements.
• provides IDE features that make writing and maintaining SQL easy.
• has cross-platform (compatible with KMM) support which means it can be used in both Android and iOS.
• is better when dealing with multi-table databases.

I prefer it to Room database because i think it's easier to set up and use, but that's just me.

Some of its cons are that it:

• involves writing more SQL code than you would when using Room database.
• is rumoured to be slower than Room database.

Well, let's get into it!

The first thing we'll need to do is to install the SQLDelight plugin on android studio. Navigate to the 'plugins' section on Android Studio preferences, search for "sqldelight" and install the plugin. You would need to restart AS after the install is complete.

For the next step, we'll go ahead to add the necessary dependencies. First we have to add the SQLDelight gradle plugin in our project's top-level build.gradle file as so:

buildscript {
    repositories {
        google()
        mavenCentral()
    }
    dependencies {
        classpath("com.squareup.sqldelight:gradle-plugin:1.5.3")
    }
}

Next we'll apply the gradle plugin in our app or module's build.gradle:

apply plugin: 'com.squareup.sqldelight'

The above line of code can also be written as:

plugins {
    id 'com.squareup.sqldelight'
}

Still in our module's build.gradle file, we'll add the following dependencies:

dependencies {
   // SQLDelight
    implementation "com.squareup.sqldelight:runtime:1.5.3"
    implementation "com.squareup.sqldelight:android-driver:1.5.3"
}

Then sync the project.

Next, we'll need to create a file that'll hold our SQL statements. This file has to be defined in the main package. For this, it is recommended to switch from Android view to Project view. Right-click the main package and create a directory named sqldelight, then add two other directories as so: sqldelight/com/czech/androidparty/cache. Right-click on the cache file and create a new .sq file as shown below.

I'll name mine AndroidPartyDB.sq.

Now that we have the .sq file that'll house our SQL statements, let's proceed to writing said statements in the file. The first statement in the SQL file creates a table. In the file we'll write the code below.

CREATE TABLE Data_Entity(
    distance INTEGER NOT NULL PRIMARY KEY,
    name TEXT NOT NULL
);

_NB: The PRIMARY KEY constraint uniquely identifies each record in a table. Primary keys must contain UNIQUE values, and cannot contain NULL values. A table can have only ONE primary key; every table can have (but does not have to) a primary key._

SQLDelight will generate a Data_Entity data class which represents our table and a Database class with a Schema object which we will use to create the database and run statements on it. In the spirit of clean code, we will be creating an instance of the Schema object and using it to create our database in separate classes. This is also because we want to provide our database in a module using Hilt (for dependency injection). Let's write some code!

class DriverFactory(private val context: Context) {

    fun createDriver(): SqlDriver {
        return AndroidSqliteDriver(Database.Schema, context, "androidParty.db")
    }
}

The above block is our DriverFactory class where we have created an instance of our database Schema object in the createDriver() function. We will use this class in the next block of code

class AndroidPartyDatabaseFactory(private val driverFactory: DriverFactory) {

    fun createDriver(): Database {
        return Database(
            driver = driverFactory.createDriver()
        )
    }
}

As you can see, the AndroidPartyDatabaseFactory class takes in the DriverFactory class as a parameter which is used to pass the createDriver() function as the value of our database driver.

We have created our database. Before we move further, we will add labeled statements to our .sq file. For each labeled statement, a type-safe function which will be available at runtime will be generated. Files with labeled statements in them will have a queries file generated for them matching the name of your .sq file (eg: for our AndroidPartyDB.sq file, an AndroidPartyDBQueries file will be generated). This queries file will be used to perform database operations. So, inside your .sq file, add the following SQL code:

insertData:
INSERT OR REPLACE
INTO Data_Entity(
    name,
    distance
) VALUES (?, ?);

getData:
SELECT  * FROM Data_Entity;

deleteData:
DELETE FROM Data_Entity;

As the names imply, insertData adds new data to the database, getData fetches already saved data and deleteData deletes data from the database.
Now rebuild the project to generate the needed files.

Next, we will create an interface containing functions for all the labels we added to the .sq file above:

interface AndroidPartyCache {

    fun insertData(data: DataList)

    fun insertData(dataList: List<DataList>)

    fun getData(): List<DataList>

    fun deleteData()
}

DataList is a serializable Data Class holding all the entries in our database

@Serializable
class DataList(
    val name: String,
    val distance: Int
)

The next step is to create an implementation class for the above interface but before we do that, we need to provide our AndroidPartyDatabaseFactory with hilt because we will be injecting it into the class. For this we will create a new hilt Module and provide our database.

@Module
@InstallIn(SingletonComponent::class)
object CacheModule {

    @Provides
    @Singleton
    fun provideAndroidPartyDatabaseFactoryDriver(
        context: BaseApplication
    ): Database {
        return AndroidPartyDatabaseFactory(
            driverFactory = DriverFactory(context = context)
        ).createDriver()
    }
}

Now let's create a new kotlin class which implements the AndroidPartyCache interface and inject the Database already provided with hilt:

class AndroidPartyCacheImpl @Inject constructor(
    database: Database
): AndroidPartyCache {

}

Inside the class, create a queries variable to get the generated Database queries as so:

private val queries: AndroidPartyDBQueries = database.androidPartyDBQueries

We'll override the functions of the parent interface and perform the necessary operations inside them. At the end, our AndroidPartyCacheImpl will look like this:

class AndroidPartyCacheImpl @Inject constructor(
    database: Database
): AndroidPartyCache {

    private val queries: AndroidPartyDBQueries = database.androidPartyDBQueries

    override fun insertData(data: DataList) {
        queries.insertData(
            name = data.name,
            distance = data.distance.toLong()
        )
    }

    override fun insertData(dataList: List<DataList>) {
        for (data in dataList) {
            insertData(data)
        }
    }

    override fun getData(): List<DataList> {
        return queries.getData().executeAsList().toDataList()
    }

    override fun deleteData() {
        return queries.transaction {
            queries.deleteData()
        }
    }
}

A couple of things to note:

• In the first insertData() function, the distance entry is converted to a long using toLong() because the INTEGER type in our SQL is converted to a type Long when a kotlin version of our Data_Entity table is created so we can only insert a Long.

• In getData(), executeAsList() is used to convert List<Data_Entity> to a mutable list, it is a generated function. toDataList() is an extension function used to convert a List<Data_Entity> back to List<DataList> when fetching data from the database as seen below:
  

object SqlConverter {

    private fun Data_Entity.toData(): DataList {
        return DataList(
            name = name,
            distance = distance.toInt()
        )
    }

    fun List<Data_Entity>.toDataList(): List<DataList> {
        return map { it.toData() }
    }
}

Finally, we can provide the AndroidPartyCache with hilt so it can be injected wherever we want to use it.

@Provides
    @Singleton
    fun provideAndroidPartyCache(
        database: Database
    ): AndroidPartyCache {
        return AndroidPartyCacheImpl(
            database = database
        )
    }

That's about it! Thanks for sticking with me and congrats on making it to the end. I hope you find this article helpful.
The complete project can be found here

Cheers! :)