What are some ways you all deal with database pers...

# server

[Cody Mikol]

What are some ways you all deal with database persistence? I have grown to very much dislike JPA because the DAOs can reference one another and create what I think is a gigantic ball of mud. I like the idea of using something like jdbcTemplate directly in conjunction with a defined RowMapper, but would also like some kind of CRUD interface abstraction on top of that for simple operations. Is there anything that does something similar before I wind up writing my own abstraction and then realizing its terrible a month later?

[jessel]

#exposed is fairly popular as a lightweight ORM that’s often used around here https://github.com/JetBrains/Exposed

[jessel]

Lets you define the schema in Kotlin for typesafe access and includes a DAO system that you can optionally use on top of it as you please. And doesn’t try to be smart and optimize things out of sight like Hibernate

[Cody Mikol]

I'd honestly like to avoid anything that tries to abstract sql itself

[Cody Mikol]

My ideal situation is that some interface could provide basic crud operations and then any additional queries could be written in valilla* sql

[Cody Mikol]

*using jdbcTemplates interpolation

[jessel]

Have you checked out jooq? If Exposed is too far from sql for you, that might be more your speed

[Cody Mikol]

I really just want plain sql using jdbctemplate

[Cody Mikol]

The main thing I's like is some kind of abstraction that handles basic crud operations via jdbcTemplate

[Cody Mikol]

and then I can add additional functionality using plain sql when the need arises

[Andrew O'Hara]

It sounds like you want something where you can write your own SQL, but have the mapping handled for you. I recently heard about a small little project that might do the trick for you. https://github.com/qualified-cactus/SqlObjectMapper

[rocketraman]

@Cody Mikol Definitely check out JOOQ. It's basically plain SQL, with the addition of type safety, when you use it with codegen.

[rocketraman]

You can also define Record mappers with JOOQ that work like Spring's RowMappers.

[rocketraman]

Or it can use JPA @Column annotations to do the mapping.

[simon.vergauwen]

I really like SqlDelight, but it's doesn't fully support all dialects yet. https://cashapp.github.io/sqldelight/

Example: https://github.com/nomisRev/ktor-arrow-example/tree/main/src/main/sqldelight/io/github/nomisrev/sqldelight

There is an escape hatch where you can write execute your own SQL over the JDBC Driver / DataSource. Like you would with vanilla DataSource.

before I wind up writing my own abstraction and then realizing its terrible a month later?

I've been there... but haven't taken it for a long spin in production.. Contributing things that you miss in SqlDelight, and temporarily working around it might be more worth your time.

[simon.vergauwen]

I am also a really like writing SQL, but haven't found a good solution in Kotlin/Spring. Scala has Doobie and Skunk but not Kotlin/Java alternative afaik.

[Robert Jaros]

When working with coroutines you would like a non-blocking solution. You can check R2DBC with https://www.komapper.org/ or https://spring.io/projects/spring-data-r2dbc

[hfhbd]

Or R2DBC with sqldelight

[Daniel Bergholm]

Check out Spring Data JDBC, https://github.com/spring-projects/spring-data-relational

[Jukka Siivonen]

Dalesbred https://dalesbred.org/ - without any magic

[Larry Meadors]

if you want to stay real close to SQL: https://mybatis.org/mybatis-dynamic-sql/docs/kotlinMyBatis3.html

[Larry Meadors]

jeff butler did some more examples of using ti with kotlin here: https://github.com/jeffgbutler/mybatis-kotlin-examples

[Larry Meadors]

i have used it with java since it's 1.x releases; using it with kotlin is not much different

[Cody Mikol]

Thanks everyone for your suggestions, in particular I really like the idea of SqlDelight, will definitely be diving into that