Proper way to serialize/deserialize DAOs entities.

[lamba92]

My use case is Ktor + Exposed

[felix19350]

What I like to do for that scenario is do define a data class that represents the domain model you wish to send/receive via REST or whatnot, and then you transform your DAO to and from it.

Bare-bones example:

data class MyAppUser(val email: String, val realName:String)

internal object MyAppUserTable : LongIdTable("my_app_user_table") {
    val email = varchar("user_email", 255).uniqueIndex()
    val realName = varchar("real_name", 255)
}

internal class MyAppUserDAO(id: EntityID<Long>) : LongEntity(id) {
    companion object : LongEntityClass<MyAppUserDAO>(MyAppUserTable)

    val email by MyAppUserTable.email
    val realName by MyAppUserTable.realName

    fun toModel():MyAppUser{
        return MyAppUser(email, realName)
    }
}

Although it adds a bit of extra code (the data class), the control you get over what gets serialized/deserialized is worth the cost IMHO. Speaking of serialization, once you have a data class, its trivial to get it working with ktor.

[lamba92]

That's the approach I'm going at the moment. I was looking for a solution that could allow me to expose the entire entity, id as well, and not to care on future modifications of the entity.

[felix19350]

Well I guess exposing the id is pretty easy:

data class MyAppUser(val id: Long, val email: String, val realName:String)

(...)
fun toModel():MyAppUser{
    return MyAppUser(id.value, email, realName)
}

[tjb]

@felix19350 Is it possible to get an example of how this would work? Specifically in a query? I attempted to do something similar to what you posted and I get the following error in my toModel() method

Property klass should be initialized before get

[felix19350]

@tylerbobella I put together a small gist, hope it helps: https://gist.github.com/felix19350/bcb39e50820dcc6872f624d2e925dd9a

[tjb]

@felix19350 you are a saint! thank you so much for this. I was able to figure it out a couple hours after I made that post and my solution is pretty similar to yours :) thanks again and i hope this helps other people who have been confused with this!!

Do you think this would be helpful to add to the docs somewhere? I do not mind documenting this if the repo author believes if would be beneficial.

[Tapac]

@felix19350 Did you try to replace data class with an interface? Will it work with ktor?

interface MyAppUserModel { val email: String, val realName: String }

internal class MyAppUserDAO(id: EntityID<Long>) : LongEntity(id), MyAppUserModel {
    companion object : LongEntityClass<MyAppUserDAO>(MyAppUserTable)

    override var email by MyAppUserTable.email
    override var realName by MyAppUserTable.realName   
}

I understand that it covers only serialization case, but if you just have to return entity to a client it might work and you don't need to call/define toModel() functions for every entity class.

[koperagen]

@Tapac I tried to solve problem this way, here is the code https://gist.github.com/DisPony/efeacfcff8833e679a6b8141d6764e4f Serialization gives me this result: {"id":7,"email":"t","realName":"f","field":"ddd"} Which mean Gson.toJson() serialize fields of actual class, not of interface. Without calling of .toModel() serialization fails with Exception in thread "main" java.lang.StackOverflowError.

[lamba92]

I think the problem is more general then finding a serialization solution. Supposing that you find a proper way to serialize a DAO entity, what about deserialization? What if the JSON you deserialize is missing a field? It means you want to set it null or that you just don't care?

I came to the conclusion that serialization/deserialization of an entity is not a concern of this library due to the model it uses (which is amazing!). Instead what I think is needed is a framework aware library that allows to integrate Exposed in the proper way into the framework itself. Have a look how Rest Repositories works for Spring! I need something like that for Ktor, where you create the DAOs and expose some REST endpoints with rules about the integrity of writes and so on. Unfortunately for my use-case, at the moment Exposed relies on JDBC drivers so no real coroutines implementation due to the synchronous nature of the drivers themselves.

I Hope one day JetBrains adds official Exposed support for Ktor with super-easy Rest repositories-like features. Until then, I think a DTO is the proper way!

One trick I am using right now for serialization only is to make the companion object of an entity class extend JsonSerializer and register it during installation of the serialization feature in Ktor. It works pretty well! I am handling deserialization and updates manully tho.

Hope it help!

[vincentlauvlwj]

Maybe you can try Ktorm, another ORM framework for Kotlin. Entity classes in Ktorm are designed serializable (both JDK serialization and Jackson are supported).

https://github.com/vincentlauvlwj/Ktorm

[VeselyJan92]

Edited: I found quite a clean way to parse DAO entties to data classes for example OfferItemDTO, OfferCategoryDTO and serialize them to JSON using kotlinx.serialization.

class OfferItem (id: EntityID<Long>) : Entity<Long>(id), DTO<OfferItemDTO> {
    companion object : EntityClass<Long, OfferItem>(OfferItems)

    var name        by OfferItems.name
    var price       by OfferItems.price
    var vat         by OfferItems.vat
    var categoryID  by OfferItems.category_id

    var category:OfferCategory by OfferCategory referencedOn OfferItems.category_id

    override fun dto(rel:List<String>): OfferItemDTO {
        val category = if(rel.contains(::category.toString())) category.dto(rel) else null
        return OfferItemDTO(id.value ,name, price.toString(), vat, category)
    }
}

class OfferCategory(id: EntityID<Long>) : Entity<Long>(id), DTO<OfferCategoryDTO> {
    var name        by OfferCategories.name
    var position    by OfferCategories.position
    var display     by OfferCategories.display
    var color       by OfferCategories.color
    var customer_id by OfferCategories.customer_id

    var customer by Customer referencedOn OfferCategories.customer_id
    val items by OfferItem referrersOn OfferItems.category_id

    companion object : EntityClass<Long, OfferCategory>(OfferCategories)

    override fun dto(rel:List<String>): OfferCategoryDTO {
        val items = if(rel.contains(::items.toString())) items.dto(rel) else null
        return OfferCategoryDTO(name, position, display, color, customer_id.value, items)
    }
}

Extension functions for collections:

interface DTO<T>{
    private fun dto(vararg rel: KProperty<*>): T = dto(rel.map { it.toString() })
    fun dto(rel:List<String>): T
}

fun  <T> Iterable<DTO<T>>.dto(rel:List<String>) : List<T>{
    return  this.toList().map { it.dto(rel) }
}

fun  <T> Iterable<DTO<T>>.dto(vararg rel: KProperty<*>) : List<T>{
    return  this.toList().map { it.dto(rel.map { it.toString() }) }
}

Now you can do something like this:

val dto:List<OfferCategoryDTO> = transaction {
    OfferCategory.all().with(OfferCategory::items).dto(OfferCategory::items)
}
val json = Json(JsonConfiguration.Default).stringify(OfferCategoryDTO.serializer().list, dto)

The only problem I have is how to compare two KPropery. Only working solution I found was using toString().

If there is somene who knows how well it perform. I would really appreciate it. I heven't tested yet.

[Krosxx]

I haved a simple test with fastjson, and passed.

first

add dependencies compile 'com.alibaba:fastjson:1.2.59'

custom code

val paramFilter = object : PropertyPreFilter {
        val ignorePs = arrayOf(
                "db",
                "klass",
                "readValues",
                "writeValues"
        )
        override fun apply(serializer: JSONSerializer?, `object`: Any?, name: String?): Boolean {
            return name !in ignorePs
        }
    }
    val idFilter = ValueFilter { obj, name, value ->
        if (obj is Entity<*> && name == "id" && value is EntityID<*>) {
            value.value
        } else value
    }

then use:

val log = RequestLog.findById(200L)
println(JSON.toJSONString(log, arrayOf(paramFilter, idFilter)))

output: {"id":200,"ip":"171.119.56.165"}

model:

object RlTable : LongIdTable("request_log") {
    val ip = varchar("ip", 50)
}

class RequestLog(id: EntityID<Long>) : LongEntity(id) {
    companion object : LongEntityClass<RequestLog>(RlTable)
    var ip by RlTable.ip
}

[lamba92]

Yeah but for every new entity you have to create a DTO and a serializer. That sucks!

[Jovines]

I don't know if that's what you want.Here's how I did it.

fun ResultRow.toMap(): Map<String, Any> {
    val mutableMap = mutableMapOf<String, Any>()
    val dataList = this::class.memberProperties.find { it.name == "data" }?.apply {
        isAccessible = true
    }?.call(this) as Array<*>
    fieldIndex.entries.forEach { entry ->
        val column = entry.key as Column<*>
        mutableMap[column.name] = dataList[entry.value] as Any
    }
    return mutableMap
}

Then you can use other tools to convert it into JSON, such as gson

[LarryJung]

this is what i did

println(exec("SELECT * FROM Cities") { rs -> recursiveExtract(rs, CityDto::class.java) })

fun <T> recursiveExtract(resultSet: ResultSet, clazz: Class<T>): List<T> {
    fun recursiveExtract_(resultSet: ResultSet, list: LinkedList<T>): List<T> {
        return if (resultSet.next()) {
            list.add(resultSet.mapTo(clazz))
            recursiveExtract_(resultSet, list)
        } else {
            list
        }
    }
    return recursiveExtract_(resultSet, LinkedList())
}

fun <T> ResultSet.mapTo(clazz: Class<T>): T {
    val constructor = clazz.getDeclaredConstructor(*clazz.declaredFields.map { it.type }.toTypedArray())
    val dataList = clazz.declaredFields.map {
        val nameField = clazz.getDeclaredField(it.name)
        nameField.isAccessible = true
        this.getObject(it.name, nameField.type)
    }
    return constructor.newInstance(*dataList.toTypedArray())
}

[BierDav]

A proper integration with ktor-serialization would be great

[christianitis]

A proper integration with ktor-serialization would be great

Agreed

[urosjarc]

A proper integration with ktor-serialization would be great

Joining cho cho train for the implementation of this feature...

[MikeDirven]

It is possible to automaticly parse a Exposed DAO Entity to a data class without the need of writing a converter function in all of the DAO's

I mainly use this in combination with Ktor to generate data classes that can be serialized throught Ktor content negotion plugin.

this code will handle it for you (I know its not perfect but thats bequase i am still working on it). It will only convert the defined variables of the dataclass specified, It is not needed to define all variable of the DAO in the dataclass.

import io.ktor.util.reflect.*
import org.jetbrains.exposed.dao.Entity
import org.jetbrains.exposed.dao.IntEntity
import org.jetbrains.exposed.dao.id.EntityID
import org.jetbrains.exposed.sql.LazySizedCollection
import org.jetbrains.exposed.sql.SizedIterable
import kotlin.reflect.KType
import kotlin.reflect.jvm.jvmErasure

inline fun <reified Dao: Entity<*>, reified Response> Dao.toResponse() : Response = this::class.members.let { doaParameters ->
    val response = Response::class
    val responseObjectParameters = Response::class.constructors.first { !it.parameters.any { it.name == "serializationConstructorMarker" } }.parameters
    response.constructors.first { !it.parameters.any { it.name == "serializationConstructorMarker" } }.call(
        *responseObjectParameters.map { responseParam ->
            doaParameters.find { it.name == responseParam.name }?.let { doaParameter ->
                when{
                    doaParameter.call(this)?.instanceOf(EntityID::class) ?: false -> (doaParameter.call(this) as EntityID<*>).value
                    doaParameter.call(this)?.instanceOf(IntEntity::class) ?: false -> (doaParameter.call(this) as Entity<*>).handleObject(responseParam.type)
                    doaParameter.call(this)?.instanceOf(LazySizedCollection::class) ?: false -> (doaParameter.call(this) as LazySizedCollection<Entity<*>>).handleList(responseParam.type)
                    doaParameter.call(this)?.instanceOf(SizedIterable::class) ?: false -> (doaParameter.call(this) as SizedIterable<Entity<*>>).handleList(responseParam.type)

                    else -> doaParameter.call(this)
                }
            }
        }.toTypedArray()
    )
}

inline fun <reified Dao: Entity<*>, reified Response> SizedIterable<Dao>.toResponse() : List<Response> = this.map { dao ->
    dao::class.members.let { doaParameters ->
        val response = Response::class
        val responseObjectParameters = Response::class.constructors.first { !it.parameters.any { it.name == "serializationConstructorMarker" } }.parameters
        response.constructors.first { !it.parameters.any { it.name == "serializationConstructorMarker" } }.call(
            *responseObjectParameters.map { responseParam ->
                doaParameters.find { it.name == responseParam.name }?.let { doaParameter ->
                    when{
                        doaParameter.call(dao)?.instanceOf(EntityID::class) ?: false -> (doaParameter.call(dao) as EntityID<*>).value
                        doaParameter.call(dao)?.instanceOf(LazySizedCollection::class) ?: false -> (doaParameter.call(dao) as LazySizedCollection<Entity<*>>).handleList(responseParam.type)
                        doaParameter.call(dao)?.instanceOf(SizedIterable::class) ?: false -> (doaParameter.call(dao) as SizedIterable<Entity<*>>).handleList(responseParam.type)
                        doaParameter.call(dao)?.instanceOf(IntEntity::class) ?: false -> (doaParameter.call(dao) as Entity<*>).handleObject(responseParam.type)
                        else -> try {
                            doaParameter.call(dao)
                        } catch (e: Exception){
                            dao.handleObject(responseParam.type)
                        }
                    }
                }
            }.toTypedArray()
        )
    }
}

fun <Dao: Entity<*>> Dao.handleObject(responseParam: KType) : Any = this::class.members.let { doaParameters ->
    val response = responseParam.jvmErasure
    response.constructors.first { !it.parameters.any { it.name == "serializationConstructorMarker" } }.call(
        *response.constructors.first { !it.parameters.any { it.name == "serializationConstructorMarker" } }.parameters.map { responseParam ->
            doaParameters.find { it.name == responseParam.name }?.let { doaParameter ->
                when{
                    doaParameter.call(this)?.instanceOf(EntityID::class) ?: false -> (doaParameter.call(this) as EntityID<*>).value
                    doaParameter.call(this)?.instanceOf(LazySizedCollection::class) ?: false -> (doaParameter.call(this) as LazySizedCollection<Entity<*>>).handleList(responseParam.type)
                    doaParameter.call(this)?.instanceOf(SizedIterable::class) ?: false -> (doaParameter.call(this) as SizedIterable<Entity<*>>).handleList(responseParam.type)
                    doaParameter.call(this)?.instanceOf(IntEntity::class) ?: false -> (doaParameter.call(this) as Entity<*>).handleObject(responseParam.type)
                    else -> doaParameter.call(this)
                }
            }
        }.toTypedArray()
    )
}

inline fun <reified Dao: Entity<*>> LazySizedCollection<Dao>.handleList(responseParam: KType): List<*> = this.wrapper.map { daoEntry ->
    val response = responseParam.arguments.first().type!!.jvmErasure
    response.constructors.first { !it.parameters.any { it.name == "serializationConstructorMarker" } }.call(
        *response.constructors.first { !it.parameters.any { it.name == "serializationConstructorMarker" } }.parameters.map { responseParam ->
            daoEntry::class.members.find { it.name == responseParam.name }?.let { doaParameter ->
                when{
                    doaParameter.call(daoEntry)?.instanceOf(EntityID::class) ?: false -> (doaParameter.call(daoEntry) as EntityID<*>).value
                    doaParameter.call(daoEntry)?.instanceOf(IntEntity::class) ?: false -> (doaParameter.call(daoEntry) as Entity<*>).handleObject(responseParam.type)
                    doaParameter.call(daoEntry)?.instanceOf(LazySizedCollection::class) ?: false -> (doaParameter.call(daoEntry) as LazySizedCollection<Entity<*>>).innerList(responseParam.type)
                    doaParameter.call(daoEntry)?.instanceOf(SizedIterable::class) ?: false -> (doaParameter.call(daoEntry) as SizedIterable<Entity<*>>).innerList(responseParam.type)
                    else -> try {
                        doaParameter.call(daoEntry)
                    } catch (e: Exception){
                        daoEntry.handleObject(responseParam.type)
                    }
                }
            }
        }.toTypedArray()
    )
}

inline fun <reified Dao: Entity<*>> SizedIterable<Dao>.handleList(responseParam: KType): List<*> = this.map { daoEntry ->
    val response = responseParam.arguments.first().type!!.jvmErasure
    response.constructors.first { !it.parameters.any { it.name == "serializationConstructorMarker" } }.call(
        *response.constructors.first { !it.parameters.any { it.name == "serializationConstructorMarker" } }.parameters.map { responseParam ->
            daoEntry::class.members.find { it.name == responseParam.name }?.let { doaParameter ->
                when{
                    doaParameter.call(daoEntry)?.instanceOf(EntityID::class) ?: false -> (doaParameter.call(daoEntry) as EntityID<*>).value
                    doaParameter.call(daoEntry)?.instanceOf(IntEntity::class) ?: false-> (doaParameter.call(daoEntry) as Entity<*>).handleObject(responseParam.type)
                    doaParameter.call(daoEntry)?.instanceOf(LazySizedCollection::class) ?: false-> (doaParameter.call(daoEntry) as LazySizedCollection<Entity<*>>).innerList(responseParam.type)
                    doaParameter.call(daoEntry)?.instanceOf(SizedIterable::class) ?: false-> (doaParameter.call(daoEntry) as SizedIterable<Entity<*>>).innerList(responseParam.type)
                    else -> try {
                        doaParameter.call(daoEntry)
                    } catch (e: Exception){
                        daoEntry.handleObject(responseParam.type)
                    }
                }
            }
        }.toTypedArray()
    )
}

fun LazySizedCollection<Entity<*>>.innerList(responseParam: KType) = this.handleList(responseParam)

fun SizedIterable<Entity<*>>.innerList(responseParam: KType) = this.handleList(responseParam)

to use this functionality you can just call the functi toResponse on the Entity for example (I like to define the types when i call the function to clarify my code but it is not necessary beqause it will inherit the types where possible.):

class TestDao(id: EntityID<Int>) : IntEntity(id) {
    companion object : IntEntityClass<TestDao>(TestTable)
    var sequelId by TestTable.sequelId
    var name     by TestTable.name
    var director by TestTable.director
}

data class TestResponse(
    var sequelId: Int,
    var name: String,
    var director: String
)

fun getAll() : List<TestResponse> = transaction {
   TestDao.all().toResponse<TestDao, TestResponse>()
}

fun getSingle(id: Int) : TestResponse = transaction {
    TestDao.findById(id)?.toResponse<TestDao, TestResponse>() 
        ?: throw NotFoundException("No entity found with id: $id")
}

[urosjarc]

https://github.com/urosjarc/db-messiah

SQL framework for Kotlin, built on top of JDBC and reflection. Focus on simplicity and smooth programming workflow.

I have created SQL framework to take a full usage of kotlin dataclasses because I was bothered with exposed API for quite some time and I had enough of it...

I was really hoping that exposed will make direct support for kotlin dataclasses but I was disapointed...

I have used db-messiah on all my own project and soon will go into the production with it. You are more than welcome to take a look I have poured inside all my expertise.

[austinarbor]

I was able to get DAO serialization working with Jackson with some tinkering. The trickiest thing for me was propagating @JsonIgnoreProperties annotations on the non-standard list implementations to prevent infinite recursion during serialization.

I haven't truly battle tested this yet, but so far looks good. I'm sure other minor changes will be needed but this should be a good head start

class EntityIDSerializer : StdSerializer<EntityID<*>>(EntityID::class.java) {
  override fun serialize(id: EntityID<*>, gen: JsonGenerator, provider: SerializerProvider,
  ) {
    gen.writeObject(id.value)
  }
}

@JsonIgnoreProperties(
    value = ["readValues", "writeValues", "_readValues", "klass", "db", "newEntity\$exposed_dao"],
)
class EntityMixIn

class LazySizedCollectionSerializer(private val bean: BeanProperty? = null) :
    StdSerializer<LazySizedCollection<*>>(
        LazySizedCollection::class.java,
    ),
    ContextualSerializer {
  override fun serialize(
      value: LazySizedCollection<*>,
      gen: JsonGenerator,
      provider: SerializerProvider,
  ) {
    gen.writeStartArray()
    // customize how you want to handle non-loaded associations here
    if (value.isLoaded()) {
      for (item in value) {
        if (item == null) {
          gen.writeNull()
        } else {
          val ser = provider.findValueSerializer(item::class.java, bean)
          ser.serialize(item, gen, provider)
        }
      }
    }
    gen.writeEndArray()
  }

  override fun createContextual(prov: SerializerProvider?, property: BeanProperty?): JsonSerializer<*> {
    return LazySizedCollectionSerializer(property)
  }
}

class SizedCollectionSerializer : StdSerializer<SizedCollection<*>>(SizedCollection::class.java) {
  override fun serialize(
    value: SizedCollection<*>,
    gen: JsonGenerator,
    provider: SerializerProvider,
  ) {
    gen.writeStartArray()
    for (v in value) {
      gen.writeObject(v)
    }
    gen.writeEndArray()
  }
}

class ExposedModule : SimpleModule("ExposedModule") {
  init {
    addSerializer(EntityIDSerializer())
    setMixInAnnotation(Entity::class.java, EntityMixIn::class.java)
    addSerializer(LazySizedCollectionSerializer())
    addSerializer(SizedCollectionSerializer())
  }
}

// usage
val mapper = JsonMapper.builder().addModules(ExposedModule(),..)

[mvysny]

I'm also in need of DTO classes, but for a slightly different reason: I'd like to have a DTO that I can freely edit in the UI and throwing away any changes when the user presses a "Cancel" button. Even though this use-case is different, I feel that the feature requests are related in essence. I've created a new feature request but I'm linking it from here: https://youtrack.jetbrains.com/issue/EXPOSED-463/Support-for-DTOs-detached-POJO-entities