How can I retrieve the actual SQL query generated by Kotlin Exposed Library for MySQL?

Question

Is there a way we could know the actual sql query formed by Kotlin Exposed Library, that get's executed on database . We could also get the query executed on database by profilers , but i'm more interested if Exposed Library provides a way to do it.

For complex queries formed in code , I wish to get the actual sql query formed

Answer 1

Assuming you run queries on your table in transaction{} blocks, you can add a Logger to your transaction block. transaction{} has an extension function called addLogger() which takes SqlLogger. You can add the StdOutSqlLogger which is the default SqlLogger that comes with the exposed library. Here's an example;

fun getAllItemsFromDb(): List<Items> {
    return transaction {
        addLogger(StdOutSqlLogger) // Adds a logger that logs SQL statements
        Items
            .selectAll().let {
                fromResultRowToItems(it)
            }


    }
}

Anytime getAllItemsFromDb() is called, the logger will log the generated SQL to the console

You can also have more fine-grained control over logging your sql logging by implementing the SqlLogger interface and overriding the log function;

object MyQueryLogger : SqlLogger {
    override
    fun log(context: StatementContext, transaction: Transaction) {
        //Customize how you want your logging, for example, using SLF4j
        val logger = LoggerFactory.getLogger(javaClass)
        logger.info("SQL: ${context.expandArgs(transaction)}")
        logger.trace("SQL: ${context.expandArgs(transaction)}")
    }
}

And then in your transaction{} add your custom Logger

 fun getAllItemsFromDb(): List<Items> {
        return transaction {
            addLogger(MyQueryLogger ) // Add your custom logger
            Items
                .selectAll().let {
                    fromResultRowToItems(it)
                }
    
    
        }
    }