Exposed: support for native sql?

[mvysny]

Exposed looks great! Yet at some point I fear that some complex queries will not be representable by Kotlin magic. And, frankly,

(Users innerJoin Cities).slice(Users.name, Cities.name).
            select {(Users.id.eq("andrey") or Users.name.eq("Sergey")) and
                    Users.id.eq("sergey") and Users.cityId.eq(Cities.id)}.forEach {
            println("${it[Users.name]} lives in ${it[Cities.name]}")
        }

Is too high-level magic for my tired brain :-)

I would prefer something like this for complex queries (you know one of those queries hand-tuned for perfection, which runs only 5 hours instead of 10 weeks):

"select u.name, c.name from user u inner join city c where blah blah".map(Users.name, Cities.name).forEach { ... }

Anyway, thanks for a fresh wind in the stale waters of JPA ;) It's good to have a framework where I can represent tables with objects, but without the 1st level cache/LazyInitializationException/other goodies from the JPA world. Yet, I want to be in charge of creating/migrating tables (of the DDL), and of queries more complex than "gimme all users in this city".

[Tapac]

Hi @mvysny , thank you for warm words.

About "native sql", you might try something like this:

TransactionManager.current().exec("select u.name, c.name from user u inner join city c where blah blah") { rs ->
     val result = arrayListOf<Pair<String, String>>()
     while (rs.next()) {
          result += rs.getString("u.name") to rs.getString("c.name") 
     }
     result
}.forEeach { ... }

or more generic

fun <T:Any> String.execAndMap(transform : (ResultSet) -> T) : List<T> {
     val result = arrayListOf<T>()
     TransactionManager.current().exec("") { rs ->
          while (rs.next()) {
               result += transform(rs)
          }
     }
     return result
}

"select u.name, c.name from user u inner join city c where blah blah".execAndMap { rs ->
    rs.getString("u.name") to rs.getString("c.name") 
}

[istvanszoboszlai]

Hi @Tapac,

thanks for the example. I think, however, there is a small error in the more generic one. The line

TransactionManager.current().exec("") { rs ->

should be

TransactionManager.current().exec(this) { rs ->

in my opinion.

[Tapac]

@istvanszoboszlai you are right! To be honest I don't ran this code, so it not surprise that it contains bug:D Glad you find it.

[jsonbrooks]

How can you prevent SQL injection if you use this approach, just passing in a raw string?

[Tapac]

@jsonbrooks , do not use user-provided data in such queries?

[jsonbrooks]

It just seems like a very common use case to me if you do need custom native queries.

Would it be possible to expose an exec method with a PreparedStatement as input instead?

[Tapac]

@jsonbrooks
First of all, we already struggle from what Exposed is tightly bound to a jdbc implementation, what prevents us from making it cross-platform or use async-drivers, so we don't want to make it even more depended.

Also, it's not so hard to make such function and place it locally:

fun Transaction.exec(sql: String, body: PreparedStatement.() -> Unit) : ResultSet? {
    return connection.prepareStatement(sql).apply(body).run {
        if (sql.toLowerCase().startsWith("select "))
            executeQuery()
        else {
            executeUpdate()
            resultSet
        }
    }
}

[AdityaAnand1]

Would it be possible to define triggers in this way?

[Tapac]

@AdityaAnand1 , yes if you provide raw SQL with complete trigger creation text

[istvanszoboszlai]

First of all, we already struggle from what Exposed is tightly bound to a jdbc implementation, what prevents us from making it cross-platform or use async-drivers...

Wow, @Tapac, does it mean that you already work on adding support for async drivers?
I am so amazed by the fact that in the µ-services world there is still no proper async RDBMS support.

[Tapac]

@istvanszoboszlai , yes, I have a plan to try support async drivers, but not sure how it should be implemented without making every function suspend. So I can't estimate when this functionality will be released, but this feature is one of the topmost in the priority list.

[Linuxea]

Hi @mvysny , thank you for warm words.

About "native sql", you might try something like this:

TransactionManager.current().exec("select u.name, c.name from user u inner join city c where blah blah") { rs ->
     val result = arrayListOf<Pair<String, String>>()
     while (rs.next()) {
          result += rs.getString("u.name") to rs.getString("c.name") 
     }
     result
}.forEeach { ... }

or more generic

fun <T:Any> String.execAndMap(transform : (ResultSet) -> T) : List<T> {
     val result = arrayListOf<T>()
     TransactionManager.current().exec("") { rs ->
          while (rs.next()) {
               result += transform(rs)
          }
     }
     return result
}

"select u.name, c.name from user u inner join city c where blah blah".execAndMap { rs ->
    rs.getString("u.name") to rs.getString("c.name") 
}

Excuse me. I don't know why show me the code as follows:

My exposed version:

<jetbrains.exposed.version>0.16.1</jetbrains.exposed.version>

[Linuxea]

Hi @mvysny , thank you for warm words.
About "native sql", you might try something like this:

TransactionManager.current().exec("select u.name, c.name from user u inner join city c where blah blah") { rs ->
     val result = arrayListOf<Pair<String, String>>()
     while (rs.next()) {
          result += rs.getString("u.name") to rs.getString("c.name") 
     }
     result
}.forEeach { ... }

or more generic

fun <T:Any> String.execAndMap(transform : (ResultSet) -> T) : List<T> {
     val result = arrayListOf<T>()
     TransactionManager.current().exec("") { rs ->
          while (rs.next()) {
               result += transform(rs)
          }
     }
     return result
}

"select u.name, c.name from user u inner join city c where blah blah".execAndMap { rs ->
    rs.getString("u.name") to rs.getString("c.name") 
}

Excuse me. I don't know why show me the code as follows:

My exposed version:

<jetbrains.exposed.version>0.16.1</jetbrains.exposed.version>

My solution:

[Tapac]

Just remove unneeded parenteses:

[Linuxea]

Just remove unneeded parenteses:

Haaa. Thank you very much!
I am fresh in kotlin, now I am more understand them.