Support natively data classes + repository based DAO

[sdeleuze]

As demonstrated in the geospatial-messenger application, data classes + repository based DAO are really nice to use, I think Exposed could help to use this pattern with proper documentation and maybe provide some helpers (mapper/unmapper).

See Database.kt and MessageRepsitory for more details.

[shafirov]

Have you got a chance to look at DAO section of readme.md? We don't use data classes here but there's framework for DAO entities

[sdeleuze]

Yes I did have a look and even tried it, but I really don't like this kind of pattern, this generates objects that mix data + function to interact with the database. While I understand that some people want to do that for example people coming from Ruby world (DAO entities are similar to ActiveRecord), such pattern is a blocking point for me and (I guess) for most developers coming from the Java world.

Most Java developers (and frameworks) prefer to enforce a clear separation of concern with pure data classes that are easy to serialize/deserialize and a software design with stateless web/service/dao layers, with transactions usually defined at service layer level (see related issue #25). I think this kind of pattern make sense for Kotlin too.

[shafirov]

Well, the point is, there's DAO in Exposed already. I don't think it makes sense to have 2 different approaches for the same thing in a library.

[sdeleuze]

I understand, I just found strange to prefer a Ruby/PHP approach over a well known pattern used in most of Java applications, but that's your choice. That's fine to me since as there is nothing preventing using a data classes + repository DAO approach like I did.

In term of documentation, in order to avoid confusing Java developers, my proposal would be to rename "DAO Sample" to "Entity DAO sample" and specify in a quick description that's similar to Active Record well known pattern (Ruby, PHP) because that's not obvious. Optionaly, maybe that could be nice to users to add a link to https://github.com/sdeleuze/geospatial-messenger or this blog post in order to show them that's a repository + data classes approach is possible if they want.

I close this issue, the real showstopper in Exposed for is #25, so let's focus on that one.

[sdeleuze]

(maybe better to let this issue open in order to wait your feedback about documentation so I reopen it)

[wundrian]

While I'd love to use the DAO framework, I then need to extend my classes to produce a serialization proxy (in effect, the data classes sdeleuze is using) for Jackson and co.

Could someone comment on why this approach was chosen and how this integrates into existing frameworks?

[shafirov]

Well, we don't build data classes just to serialize to json. We just emit json directly from DAO with json-builder APIs. Alternatively, there's enough reflective info in Entity and EntityClass to build serialization a-la Jackson.

Could someone comment on why this approach was chosen

It just works for us. It's typesafe, it doesn't bring additional layer of an ORM, it removes hell lot of a boilerplate of common queries.

[sdeleuze]

To add more thoughts about that, I think a lot of developers consider domain model as something that should not be polluted by technical concerns like annotations or extending a class, that not just about serialization. That allow clear separation of concern, and is a widely recognized best practice, so I still have some hope this will be officially supported and promoted on Exposed.

If you want another use case that is not about serialization, I have one : sharing domain model between the client and the server. We have worked with the kotlin2js team on a Kotlin client app, and I plan to update Geospatial Messenger to show Kotlin awesomeness with data classes being shared, that's only possible with the data class + repository pattern.

I understand the current DAO approach works for you, but I think the data class based approach is a more clean one, as powerful and as simple than the current one. I hope it will be supported (at least in addition to the ActiveRecord current one).

[GuiSim]

Would love to see that in Exposed. I use Kotlin to avoid boilerplate, not add more 😉

[mayconcancini]

In the company I work for, we predominantly use Java EE for Backend REST. I started studying kotlin / ktor / exposed as a more modern and productive alternative. However, as presented by the others, the fact that DAO does not work directly with kotlin data class will be a flaw for me. The Ktorm framework has already begun its first steps in this direction.

[Aditya94A]

Does anybody know any other good kotlin friendly ORM/DB libraries with less boilerplate?

I was really excited about this library, but having to do this

for every table and field is a bit too much 😕

And then I also have another service to wrap the queries for easier access via ktor API controllers:

There is a very noticeable scope of missing small things here or there in the entire setup to work with databases using Exposed leading to frequent bugs and annoyances. I'm just starting a new project that I expect will be pretty big, so I'm wondering if this will eventually turn into an un-maintainable nightmare.

[GuiSim]

We've had good success with jOOQ even with kotlin.. But Exposed with less boilerplate would be a great combo.

…

On Fri, Nov 15, 2019, 11:08 Aditya Anand ***@***.***> wrote: Does anybody know any other good kotlin friendly ORM/DB libraries with less boilerplate? I was really excited about this library, but having to do this [image: image] <https://user-images.githubusercontent.com/13274079/68925574-48c5a000-07a9-11ea-83e0-0ef6088f07be.png> for every table and field is a bit too much 😕 And then I also have another service to wrap the queries for easier access via ktor API controllers: [image: image] <https://user-images.githubusercontent.com/13274079/68957187-83522b80-07ef-11ea-9527-608d80d45189.png> There is a very noticeable scope of missing small things here or there in the entire setup to work with databases using Exposed leading to frequent bugs and annoyances. I'm just starting a new project that I expect will be pretty big, so I'm wondering if this will eventually turn into an un-maintainable nightmare. — You are receiving this because you commented. Reply to this email directly, view it on GitHub <#24?email_source=notifications&email_token=AAPIMBTYNI4LFINV63YNLCTQT3CODA5CNFSM4B6R5XOKYY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOEEF43KI#issuecomment-554421673>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AAPIMBVAENYNLIANAAN36XLQT3CODANCNFSM4B6R5XOA> .

[Aditya94A]

Any updates on this?

Such verbose syntax is not very maintainable and prone to human errors. Is there a plan to offer a better alternative in the library? @Tapac @shafirov @sergeyQx

Does anyone know any other good alternatives for this library?

[Tapac]

@AdityaAnand1 , yes it's the next thing in our roadmap.
But it will be released as an experimental first to gather feedback.

[Tapac]

Hi everyone in this thread.

I started to implement support for working with simple classes without defining tables and inherit from Entity.

Before I'll publish some experimental module I want to gather common use-cases to address them properly in implementation.

I have not very much experience with libraries like jooq and jdbi and I can imagine how you use them. I check their documentations but what confusing me the most is that there are a lot of plain sql with parameters binding by name or index. It looks very error-prone to me as we lose type-safety and everything could fail at runtime if you just miss the order of parameters.

At the moment I have a prototype where you can get table from any class and also use it like that:

data class TestEntity (
    val int : Int,
    val optInt: Int?
)

SchemaUtils.createTable(TestEntity::class.exposedTable)

Also, with simple CrudReposiory you could save or search your classes:

object TestRepo : ExposedCrudRepository<TestEntity>(TestEntity::class) 

val e1 = TestEntity(1, 0)
TestRepo.save(e1) // executes insert into table
val e2 = TestRepo.fromRow(TestEntity::class.exposedTable.selectAll().single())
val e3 = TestRepo.find { TestEntity::optInt.exposedColumn eq 0 }.single()

As you can see it's possible to convert class fields into column with same type and use it in a type-safe queries.

For me, it still too verbose in place TestEntity::optInt.exposedColumn but it can be easily replaced with something like TestEntity::optInt() or !TestEntity::optInt syntax.

Please share your thoughts and suggesions.

[Aditya94A]

Looks good. Here are some thoughts.

• How to specify column constraints like NOT NULL, PRIMARY KEY, DEFAULT, FOREIGN KEY etc. with this?
• How about using annotations for the same?
• Anything we can do to minimize boilerplate would be good 😃, extension functions, Utils methods etc.
• Would also love to see some advanced use cases such as Views and Joins and Triggers receive first party treatment

It would be ok if we need to extend the class from a super class as well, so that some extensions/utils could be added to the base class to further reduce boilerplate in the child classes

data class TestEntity (
    val int : Int,
    val optInt: Int?
) : BaseEntity()

[pjagielski]

Hi, If you're not scared of JPA annotations, we recently released https://github.com/TouK/krush which generates Exposed DSL mappings from JPA annotations. So using given TestEntity it could look something like this:

import javax.persistence.* // in future can be also our package for multiplatform support

@Entity 
data class TestEntity (
    @Id @GeneratedValue // for now it's mandatory but we plan to support entities without id 
    val id: Int? = null,
    val value: Int,  // changed name to value, 'int' fails in annotation processing for some reason
    val optInt: Int? = null
)

val entity = TestEntity(value = 2)

val persistedEntity = TestEntityTable.insert(entity) // insert is generated extension method
assertThat(persistedEntity.id).isNotNull()
val id = persistedEntity.id ?: throw IllegalArgumentException()

val fetchedEntity = TestEntityTable.select { TestEntityTable.id eq id }.singleOrNull()?.toTestEntity() ?: throw IllegalArgumentException() // toTestEntity is generated

val updatedEntity = fetchedEntity.copy(value = 2, optInt = 3)
TestEntityTable.update({ TestEntityTable.id eq id }) { it.from(updatedEntity) } // from is generated

val allEntities = TestEntityTable.selectAll().toTestEntityList()
assertThat(allEntities).containsExactly(updatedEntity)

I provided working example in our krush-example repository: https://github.com/TouK/krush-example/blob/master/src/test/kotlin/pl/touk/krush/TestEntityTest.kt

[paulschuetz]

Hello everyone, I recently got engaged with Exposed and also gave myself a try in implementing a CRUD repository wrapper around the Exposed DSL API. You can find it in the following gist: https://gist.github.com/paulschuetz/a1f0d63d01a5435b2118941c35838f3d

I hope it can maybe help some of you which are also not 100% satisfied with the DAO API and cannot wait for Exposed to natively support some kind of data repository pattern itself 😃 Would also love some feedback.

[endink]

Here is another idea for using DSL + simple class .

[BreimerR]

Hello everyone, made a wrapper to help in code generation and currently working with KSP and very much experimental.
https://github.com/BreimerR/Rebo
Would also love some feedback.

Can't figure out database migrations effectively yet.

The main reason for creation is to enable your data classes to function as is with Exposed Database. Extending with prebuild classes would affect Kotlin workflow because then it would mean for API-based or serialization needs you'd have to create other boilerplate code for code serialization. With annotations then you get well not clean code but neither is it highly annotated.

[liosha2007]

Hi! Could someone please tell me what is the advantage of creating class and object dublicating fields? There are too much dublications. I can't beleave that JetBrains, who created the best IDE and probably the best programming language can't implement functionality to work with DAO in better way. There must be big advantage to implement it this way.

[ESchouten]

Hi all,
I've been thinking about this too, I am looking for a way to keep my domain models pure data classes, so don't clutter them with JPA annotations, while preventing having to write and maintain a separate database definition like with Exposed DSL + DAO.
Komapper comes close, but uses KSP and meta classes to map annotations on, inducing the need to maintain these together with the domain models, and preventing IDE level type checking.

What I have in mind is a DSL based configuration per entity

Anyone seeing any (fundemental) obstacles on the road for a solution like this? Or knows a library that does something like this?

[apatrida]

@ESchouten I am guessing then that your query DSL would use class properties, which would have a lot of Something::myProp in each use (no shorthand for just ::myProp when out of the scope of that class). There is no way to then assign out an alias to that collection of objects to create a short-hand.

Your suggestion is incomplete unless you show use cases around how the generic data classes would be used for interacting with the data.

[ESchouten]

@apatrida Perhaps the way Kotlin-JDSL solves it might be an option. Interesting project nonetheless, what do you think?

[mbunderline76]

Hello everyone, I recently got engaged with Exposed and also gave myself a try in implementing a CRUD repository wrapper around the Exposed DSL API. You can find it in the following gist: https://gist.github.com/paulschuetz/a1f0d63d01a5435b2118941c35838f3d

I hope it can maybe help some of you which are also not 100% satisfied with the DAO API and cannot wait for Exposed to natively support some kind of data repository pattern itself smiley Would also love some feedback.

Hi, I was looking for some thing like this for a day!
thank you :)

[apatrida]

One thing all of this shows, is that we need a KEEP for allowing within-DSL overrides of more operators such as = >= <= !=, &&, ||, and the other ones causing us to write silly looking code in DSL's. Every DSL suffers from this eq() equal() dodging. Infix also starts to break down typically and you see more complex DSL's having to abandon it for many sections of the syntax the DSL is trying to allow...

Also needing to allow context(SomeClass::) to allow shorter references for properties.

(ignore errors in code, I'm typing in Github markdown editor, with a fake language change)

@MyDSL
override operator fun <T: KProperty1<Entity, R>>.comparison(other: R): None // ==

@MyDSL
override operator fun <T: KProperty1<Entity, R: Number>>.comparison(other: Number): None // ==

...

@MyDSL
override operator fun <T: KProperty1<Entity, R>>.logicalAnd(other: R): None // &&

fun <T: Entity> Whatever<T>.action(@AllowDslOperators @MyDSL context(T::) block.()->Unit) { ... }

// therefore we can just do...

myWhatever<Book>.action {
   ID == 1 && NAME == 'bulldog'
}

[apatrida]

@ESchouten I like the idea, but not all of the col(...) wrappers and the long property references and ::class everywhere (holding entity in local variable helps that). Think their API would benefit from an aliasing wrapper at the start.

with (entity(MyTable::class) alias "mt", entity(OtherTable::class) alias "ot") { mt, ot ->
    ...select
}

But then we would need the ability to assign a class reference to a variable that supports property references:

val x: KClass<Something> = Something::
select(x::ID)
  ...

in the JDSL code you cannot access a property from an alias. Nor can you use the same table twice outside of the join ON clause, because the scoping of the where clause isn't clear to which instance of the table you are referring (because you cannot have a reference to the entity that allows properties to be selected).

I think a failure here is using property references as they are noisy and too limiting at the moment. I'd rather KSP codegen the JPA into a simpler structure that is referencable that points back to the properties and allows things like table aliasing and shortening of the column references.

But an instance of something that holds property references can be used.

@Entity
class MyTable { // JPA
...
} 

class MyTableEntity(val table: MyTable = MyTable::class, val alias: String? = null) {
     val id = MyTable::id
     val name = MyTable::name
     val other = MyTable::other
}

Although at this point, why use property references at all, and not just go with something like Exposed does for table definitions.

[apatrida]

Jimmer (https://babyfish-ct.github.io/jimmer/docs/jimmer-sql/basic/usage) is also in this same line of thought...

[darkxanter]

Hello everyone, if anyone might be interested, I made a yet another code generator with KSP, but instead of processing data class entities, it generates data classes, mapping functions, and a CRUD repository for an Exposed table.
https://github.com/darkxanter/kesp

[urosjarc]

@Tapac:

data class TestEntity (
    val int : Int,
    val optInt: Int?
)
SchemaUtils.createTable(TestEntity::class.exposedTable)
object TestRepo : ExposedCrudRepository<TestEntity>(TestEntity::class) 

Can we have a taste of this sweet CrudRepository class generation?
I have 20 domain classes and its souch a pain to maintain 20 classes...

object TestSqlRepo : TestRepo, SqlRepo<Test>(name<Test>()) {
    val naslov = varchar(Test::naslov.name, STR_MEDIUM)
    val podnaslov = varchar(Test::podnaslov.name, STR_MEDIUM)
    val deadline = date(Test::deadline.name)
    val id_oseba = reference(Test::id_oseba.name, OsebaSqlRepo.id)

    override fun map(obj: Test, any: UpdateBuilder<Number>) {
        any[id] = obj.id.value
        any[naslov] = obj.naslov
        any[podnaslov] = obj.podnaslov
        any[deadline] = obj.deadline.toJavaLocalDate()
        any[id_oseba] = obj.id_oseba.value
    }

    override fun resultRow(R: ResultRow): Test {
        val deadline = R[deadline]
        return Test(
            id = Id(R[id]),
            naslov = R[naslov],
            podnaslov = R[podnaslov],
            id_oseba = Id(R[id_oseba]),
            deadline = deadline.toKotlinLocalDate()
        )
    }
}

[LukcyOne]

Tapac

I want to know if this issue has been shelved, as the roadmap hasn't been updated for 3 years. I don't know whether I need to keep waiting.

[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.