Save complex JSON response in SQLite with Room

Question

I'm trying to implement caching of a JSON API response with Room.
The response I get in JSON follows this data class structure:

@Serializable
data class ApiDataResponse(
    val success: Boolean,
    val message: String? = null,
    val albums: List<AlbumResponse> = emptyList()
)

@Serializable
data class AlbumResponse(
    val id: String,
    val title: String,
    val createdBy: String,
    val enabled: Boolean,
    val keywords: List<String>,
    val pics: List<PicResponse>
)

@Serializable
data class PicResponse(
    val picUrl: String,
    val emojis: List<String>
)

Notes:

• @Serializable is from kotlinx.serialization library to parse the JSON response.
• These response data classes are only used inside my datasource layer, the view layer doesn't care about an ApiDataResponse and only knows a "pure" version of AlbumResponse called Album and a "pure" version of PicResponse called Pic (by "pure" I mean a data class without external library annotations).

So to implement this cache with Room I could discard the ApiDataResponse and save only the contents of AlbumResponse (and consequently PicResponse), having new data classes for Room entities following this idea:

@Entity(tableName = "albums")
data class AlbumEntity(
    @PrimaryKey(autoGenerate = false)
    val id: String,
    val title: String,
    val createdBy: String,
    val enabled: Boolean,
    val keywords: List<String>, // obstacle here
    val pics: List<PicEntity> // obstacle here
)

// obstacle here
// @Entity
data class PicEntity(
    val picUrl: String,
    val emojis: List<String>
)

I already know how to save simple data in Room, with the simplest JSON I was able to do this task, the problem is that in this more complex scenario I have no idea how to achieve this goal. So I wish someone could guide me in this situation.

Answer 1

Maybe it's a little late, but I would still like to add some interesting information regarding MikeT's answer.

It is not necessary to create a new data class just to transform a custom object into a JSON with TypeConverter, for example:

@Entity(tableName = "albums")
data class AlbumEntity(
    @PrimaryKey(autoGenerate = false)
    val id: String,
    val title: String,
    val createdBy: String,
    val enabled: Boolean,
    val keywords: List<String>,
    val pics: List<PicEntity> // can be converted directly
)

import kotlinx.serialization.Serializable

@Serializable // to be able to do the serialize with the kotlinx.serialization
data class PicEntity(
    val picUrl: String,
    val emojis: List<String>
)

With just these two data classes we can build the TypeConverters as follows:

import androidx.room.TypeConverter
import kotlinx.serialization.decodeFromString
import kotlinx.serialization.encodeToString
import kotlinx.serialization.json.Json

class DatabaseConverter {
    private val json = Json

    @TypeConverter
    fun convertStringListToString(strings: List<String>): String =
        json.encodeToString(strings)

    @TypeConverter
    fun convertStringToStringList(string: String): List<String> =
        json.decodeFromString(string)

    @TypeConverter
    fun convertPicEntityListToString(picsEntity: List<PicEntity>): String =
        json.encodeToString(picsEntity)

    @TypeConverter
    fun convertStringToPicEntityList(string: String): List<PicEntity> =
        json.decodeFromString(string)
}

Code to create an example dummy list:

object DummyAlbums {
    fun createList(): List<AlbumEntity> = listOf(
        AlbumEntity(
            id = "0001",
            title = "Album AB",
            createdBy = "Created by AB",
            enabled = true,
            keywords = listOf("ab"),
            pics = dummyPics(albumId = "0001", size = 0)
        ),
        AlbumEntity(
            id = "0002",
            title = "Album CD",
            createdBy = "Created by CD",
            enabled = false,
            keywords = listOf("cd", "c", "d"),
            pics = dummyPics(albumId = "0002", size = 1)
        ),
        AlbumEntity(
            id = "0003",
            title = "Album EF",
            createdBy = "Created by EF",
            enabled = true,
            keywords = listOf(),
            pics = dummyPics(albumId = "0003", size = 2)
        )
    )

    private fun dummyPics(
        albumId: String,
        size: Int
    ) = List(size = size) { index ->
        PicEntity(
            picUrl = "url.com/$albumId/${index + 1}",
            emojis = listOf(":)", "^^")
        )
    }
}

So we can have the following data in table:

I wanted to highlight this detail because maybe it can be important for someone to have a table with the cleanest data. And in even more specific cases, to have it clean, you can do the conversion manually using Kotlin functions, such as joinToString(), split(), etc.

Answer 2

I believe the issue is with columns as lists.

What you could do is add the following classes so the Lists are embedded within a class:-

data class StringList(
    val stringList: List<String>
)
data class PicEntityList(
    val picEntityList: List<PicEntity>
)

and then change AlbumEntity to use the above instead of the Lists, as per:-

@Entity(tableName = "albums")
data class AlbumEntity(
    @PrimaryKey(autoGenerate = false)
    val id: String,
    val title: String,
    val createdBy: String,
    val enabled: Boolean,
    //val keywords: List<String>, // obstacle here
    val keywords: StringList, /// now not an obstacle
    //val pics: List<PicEntity> // obstacle here
    val emojis: PicEntityList// now not an obstacle
)

To be able to store the "complex" (single object) you need to convert this so some TypeConverters e.g.

class RoomTypeConverters{
    @TypeConverter
    fun convertStringListToJSON(stringList: StringList): String = Gson().toJson(stringList)
    @TypeConverter
    fun convertJSONToStringList(json: String): StringList = Gson().fromJson(json,StringList::class.java)
    @TypeConverter
    fun convertPicEntityListToJSON(picEntityList: PicEntityList): String = Gson().toJson(picEntityList)
    @TypeConverter
    fun convertJSONToPicEntityList(json: String): PicEntityList = Gson().fromJson(json,PicEntityList::class.java)
}

• note this utilises the dependency com.google.code.gson

You then need to have the @TypeConverters annotation to cover the appropriate scope (at the @Database level is the most scope). Note the plural rather than singular, they are different.

To demonstrate the above works, First some functions in an interface annotated with @Dao :-

@Dao
interface AlbumDao {
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    fun insert(albumEntity: AlbumEntity): Long
    @Query("SELECT * FROM albums")
    fun getAllAlbums(): List<AlbumEntity>
}

Second an @Database annotated class (note the @TypeConverters annotation) :-

@TypeConverters(RoomTypeConverters::class)
@Database(entities = [AlbumEntity::class], exportSchema = false, version = 1)
abstract class TheDatabase: RoomDatabase() {
    abstract fun getAlbumDao(): AlbumDao

    companion object {
        @Volatile
        private var instance: TheDatabase?=null
        fun getInstance(context: Context): TheDatabase {
            if (instance==null) {
                instance = Room.databaseBuilder(context,TheDatabase::class.java,"album.db")
                    .allowMainThreadQueries()
                    .build()
            }
            return instance as TheDatabase
        }
    }
}

Third some activity code to actually do something (insert some Albums and then extract them writing the extracted data to the Log) :-

class MainActivity : AppCompatActivity() {
    lateinit var db: TheDatabase
    lateinit var dao: AlbumDao
    override fun onCreate(savedInstanceState: Bundle?) {
        super.onCreate(savedInstanceState)
        setContentView(R.layout.activity_main)
        db = TheDatabase.getInstance(this)
        dao = db.getAlbumDao()

        dao.insert(AlbumEntity(
            "Album001", "The First Album","Fred",false,
            StringList(listOf("The","First","Album")),
            PicEntityList(
                listOf(
                    PicEntity("PE001", listOf("emoji1","emoji2","emoji3")),
                    PicEntity("PE002",listOf("emoji10")),
                    PicEntity("PE003", listOf("emoji20","emoji21"))
                ))
        ))
        dao.insert(AlbumEntity(
            "Album002","This is the Second Album","Mary", true,
            StringList(listOf("keya","keyb","keyc","keyd","keye")),
            PicEntityList(
                listOf(
                    PicEntity("PE011", listOf("emoji30","emoji31")),
                    PicEntity("PE012", listOf("emoji1","emoji10","emoji20","emoji30"))
            ))
        ))
        for (a in dao.getAllAlbums()) {
            logAlbum(a)
        }
    }

    fun logAlbum(albumEntity: AlbumEntity) {
        val keywords = StringBuilder()
        for(s in albumEntity.keywords.stringList) {
            keywords.append("\n\t$s")
        }
        val pelog = StringBuilder()
        for (pe in albumEntity.emojis.picEntityList) {
            pelog.append("\n\tURL is ${pe.picUrl}")
            for (emoji in pe.emojis) {
                pelog.append("\n\t\tEmoji is ${emoji}")
            }
        }
        Log.d(
            "ALBUMINFO",
            "Album id is ${albumEntity.id} " +
                    "Title is ${albumEntity.title} " +
                    "CreateBy ${albumEntity.createdBy} " +
                    "Enabled=${albumEntity.enabled}. " +
                    "It has ${albumEntity.keywords.stringList.size} keywords. " +
                    "They are $keywords\n. " +
                    "It has ${albumEntity.emojis.picEntityList.size} emojis. " +
                    "They are ${pelog}"
        )
    }
}

• Run on the main thread for convenience and brevity

When run then the log contains:-

D/ALBUMINFO: Album id is Album001 Title is The First Album CreateBy Fred Enabled=false. It has 3 keywords. They are 
        The
        First
        Album
    . It has 3 emojis. They are 
        URL is PE001
            Emoji is emoji1
            Emoji is emoji2
            Emoji is emoji3
        URL is PE002
            Emoji is emoji10
        URL is PE003
            Emoji is emoji20
            Emoji is emoji21
            
            
D/ALBUMINFO: Album id is Album002 Title is This is the Second Album CreateBy Mary Enabled=true. It has 5 keywords. They are 
        keya
        keyb
        keyc
        keyd
        keye
    . It has 2 emojis. They are 
        URL is PE011
            Emoji is emoji30
            Emoji is emoji31
        URL is PE012
            Emoji is emoji1
            Emoji is emoji10
            Emoji is emoji20
            Emoji is emoji30

• i.e. the 2 albums have been extracted along with the appropriate embedded lists.

The Albums table itself (via App Inspection) consists of :-

An Alternative, and from a Database perspective, better approach, instead of embedding lists as a single value (String), would have the lists as related tables (with a one-many or a many-many relationship).