Say Hello to SQLDelight

In the world of mobile development, databases are often used for local data storage on client devices.

SQLDelight generates typesafe kotlin APIs from your SQL statements. It verifies your schema, statements, and migrations at compile-time and provides IDE features like autocomplete and refactoring which make writing and maintaining SQL simple.

In Other Word, SQL delight is another database framework we can use for android just like room.

But Compare to Room the major advantage of SQLDelight is that it is a pure kotlin library so that means it is compatible with KMM (kotlin multi-platform) mobile app.

So here’s my getting started guide.

Step 1: Install the thing

• Project build.grade file:

dependencies {
    classpath 'com.squareup.sqldelight:gradle-plugin:1.5.2'
}

• App build.gradle file:

dependencies {

    // SQL Delight
    implementation "com.squareup.sqldelight:android-driver:1.5.2"
    implementation "com.squareup.sqldelight:coroutines-extensions-jvm:1.5.2"
}

sqldelight {
    PersonDatabase {
        packageName = "com.nitin.sqldelightsample"
    }
}

• IntelliJ Plugin (from Android Studio):

We’re all installed and ready to go…

Step 2: Write the SQL Statements

Lets get the folder structure in order first. Goto your ../src/main directory and create a folder called “sqldelight”.

This Picture will show you.

So now, inside the sqldelight/{your_structure} folder create a file. For this Example, I am Creating Person DB

So create a personEntity.sq file. This file is going to house your create table statement and any queries for that table.

CREATE TABLE personEntity (
    id INTEGER NOT NULL PRIMARY KEY,
    firstName TEXT NOT NULL,
    lastName TEXT NOT NULL
);

getPersonById:
SELECT *
FROM personEntity
WHERE id = :id;

getAllPersons:
SELECT *
FROM personEntity;

insertPerson:
INSERT OR REPLACE
INTO personEntity
VALUES(?, ?, ?);

deletePersonById:
DELETE FROM personEntity
WHERE id = :id;

The first statement in this file should be your CREATE TABLE statement;

All other queries need to be tagged. Now, the examples I’ve seen are using lowercase underscores

Step 3: Build

SQLDelight generated models.

It will generate DataClass for created table.

And Like this Person Database also.

Step 4 : Implemention

Now we can Create Simple Interface for CURD Operations for that Table.

interface PersonDataSource {

    suspend fun getPersonById(id: Long): PersonEntity?

    fun getAllPersons(): Flow<List<PersonEntity>>

    suspend fun deletePersonById(id: Long)

    suspend fun insertPerson(firstName: String, lastName: String, id: Long? = null)
}

Here we need to create the instance of SQLDriver, For this we have added in AppModule.

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

    @Provides
    @Singleton
    fun provideSqlDriver(app: Application): SqlDriver {
        return AndroidSqliteDriver(
            schema = PersonDatabase.Schema,
            context = app,
            name = "person.db"
        )
    }

    @Provides
    @Singleton
    fun providePersonDataSource(driver: SqlDriver): PersonDataSource {
        return PersonDataSourceImpl(PersonDatabase(driver))
    }
}

And the same Interface We can implement using Impl Class.

class PersonDataSourceImpl(
    db: PersonDatabase
): PersonDataSource {

    private val queries = db.personEntityQueries

    override suspend fun getPersonById(id: Long): PersonEntity? {
        return withContext(Dispatchers.IO) {
            queries.getPersonById(id).executeAsOneOrNull()
        }
    }

    override fun getAllPersons(): Flow<List<PersonEntity>> {
        return queries.getAllPersons().asFlow().mapToList()
    }

    override suspend fun deletePersonById(id: Long) {
        withContext(Dispatchers.IO) {
            queries.deletePersonById(id)
        }
    }

    override suspend fun insertPerson(firstName: String, lastName: String, id: Long?) {
        withContext(Dispatchers.IO) {
            queries.insertPerson(id, firstName, lastName)
        }
    }
}

We’re all done, for now.

And that’s it, we’ve covered the basics of using SQLDelight for typesafe Kotlin database operations.