Setting up an SQLDelight database on your android application using Kotlin
Tags: android, kotlin
Published: 2022-06-14 10:12:52

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.
![preferences > plugins > marketplace](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/2bq8yodqoixod38zv39k.png)

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.
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/v5rrqpw05y6tbu5q6t5o.png)

I'll name mine `AndroidPartyDB.sq`.
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/2j9f7qyxo1nkbuxvebvg.png)

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](https://github.com/Czeach/AndroidParty)

Cheers! :)