Using SqlDelight

So, from previous tutorials, we have created two models that are fetched from API, let’s use SqlDelight to save it inside our database

@Serializable

data class Weather(val coord: Coordinate, val base: String) @Serializable

data class Coordinate(val lon: Float, val lat: Float)

For creating SQL tables and requests SqlDelight uses a special file with the instruction that has to be placed inside the sqlDelight folder.

Create a WeatherModel.sq file inside \Shared\src\commonMain\sqldelight\com\regin\startmultiplatform\db\model (note: this has been changed from 1.0.3 to 1.1.3)

We want to insert new items and pull them from the database, WeatherModel.sq has the following code:

import com.regin.startmultiplatform.Coordinate; CREATE TABLE WeatherModel (

coordinate TEXT as Coordinate,

base TEXT NOT NULL

); insertItem:

INSERT OR FAIL INTO WeatherModel(coordinate, base) VALUES (?, ?); selectAll:

SELECT *

FROM WeatherModel;

Since 1.1.0 SqlDelight we have to create a database through new API, so let's create AnyDatabaseName.kt (the name can be any)

fun createDatabase(driver: SqlDriver): AnyNameDatabase {

val coordinateAdapter = object : ColumnAdapter<Coordinate, String> {

override fun decode(databaseValue: String): Coordinate {

val split = databaseValue.split(":")

return Coordinate(split[0].toFloat(), split[1].toFloat())

}



override fun encode(value: Coordinate): String {

return "${value.lat}:${value.lon}"

}

}



return AnyNameDatabase(

driver,

WeatherModel.Adapter(

coordinateAdapter = coordinateAdapter

)

)

}

Also, we need Dao for Weather and repository which will be making an API call and persisting the data

class WeatherDao(database: AnyNameDatabase) {



private val db = database.weatherModelQueries



internal fun insert(item: Weather) {

db.insertItem(

base = item.base,

coordinate = item.coord

)

}



internal fun select():List<WeatherModel> = db.selectAll().executeAsList()

}

WeatherRepository

class WeatherRepository(

private val weatherApi: WeatherApi,

private val weatherDao: WeatherDao

) {



suspend fun fetchWeather(): Weather {

val result = weatherApi.fetchWeather()

weatherDao.insert(result)

return result

}



fun selectFromDb() = weatherDao.select()

}

And the last step, we need to create Dao and repository inside the Android app module

class MainActivity : AppCompatActivity(), CoroutineScope { private val job = Job() override val coroutineContext: CoroutineContext

get() = job override fun onCreate(savedInstanceState: Bundle?) {

super.onCreate(savedInstanceState) val engine = OkHttpEngine(OkHttpConfig().apply {

HttpLoggingInterceptor().apply {

level = HttpLoggingInterceptor.Level.BODY

}

}) //DO NOT DO THE SAME IN YOUR PRODUCTION CODE, use DI

val config = SupportSQLiteOpenHelper.Configuration.builder(this)

.name("database.db")

.callback(object : SupportSQLiteOpenHelper.Callback(1) {

override fun onCreate(db: SupportSQLiteDatabase) {

val driver = AndroidSqliteDriver(db)

AnyNameDatabase.Schema.create(driver)

}



override fun onUpgrade(db: SupportSQLiteDatabase?, oldVersion: Int, newVersion: Int) {

}



})

.build()



val sqlHelper = FrameworkSQLiteOpenHelperFactory().create(config)



val database = createDatabase(AndroidSqliteDriver(sqlHelper))



val weatherDao = WeatherDao(database) val weatherApi = WeatherApi(engine)

val weatherRepository = WeatherRepository(weatherApi, weatherDao)

launch(Dispatchers.Main) {

try {

val result = withContext(Dispatchers.IO) { weatherRepository.fetchWeather() }

Toast.makeText(this@MainActivity, result.toString(), Toast.LENGTH_LONG).show()

} catch (e: Exception) {

e.printStackTrace()

Toast.makeText(this@MainActivity, e.message, Toast.LENGTH_LONG).show()

}

} launch(Dispatchers.Main) {

val result = withContext(Dispatchers.IO) {weatherRepository.selectFromDb()}

Toast.makeText(this@MainActivity, "result from db ${result.toString()}", Toast.LENGTH_LONG).show()

}

} override fun onDestroy() {

super.onDestroy()

job.cancel()

}

}

NOTE: I tried to simplify everything here to keep it clear, don’t do like that, use DI to inject API, DAO and Repository

You might be wondering, what is CoordinateAdapter? This is an adapter that can encode/decode your custom values to use it inside the database as like it is a common type. You could find the more information here https://github.com/square/sqldelight

And here is the result after the launch of the application

Summary

Now you can share almost every aspect of your application between different platforms, there are still a lot of stuff to think about, such as logging or making cool cross-platform dependency injection that might be covered in the future.

Thanks for reading, as usual, you can find all the code inside this repo https://github.com/ReginFell/startmultiplatform