How to log SQL queries, or log slow queries?

[adamw]

Is there a way to (configurably) log the generated SQL queries, or log queries which are slow to execute?

There are some logSql functions in util, but they seem unused.

[AugustNagro]

Yes; you should be able to set your log level to DEBUG, which will log all sql queries. Please let me know if that's not working. https://github.com/AugustNagro/magnum?tab=readme-ov-file#logging-sql-queries logSql is used here for example: https://github.com/AugustNagro/magnum/blob/master/magnum/src/main/scala/com/augustnagro/magnum/Query.scala

…

On Fri, Jul 26, 2024, 9:53 AM Adam Warski ***@***.***> wrote: Is there a way to (configurably) log the generated SQL queries, or log queries which are slow to execute? There are some logSql functions in util, but they seem unused. — Reply to this email directly, view it on GitHub <#32>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/ABQOKNTUF65YJDJRJOXQRTDZOJPHPAVCNFSM6AAAAABLQVQJIWVHI2DSMVQWIX3LMV43ASLTON2WKOZSGQZTENBRGM3TOMY> . You are receiving this because you are subscribed to this thread.Message ID: ***@***.***>

[adamw]

Ah! Finally got it to work. Turns out that private val Log = System.getLogger(getClass.getName) will create a logger for scala.Predef, instead of com.augustnagro.magnum - what I was expecting in my logback config file. I suppose this should rather be the magnum's package?

Also, it would be great to have some kind of "interceptor" - logSql is fine for basic logging, but for more advanced use cases (like logging slow queries), or plugging in some metrics - an interceptor would be needed I think.

[AugustNagro]

create a logger for scala.Predef, instead of com.augustnagro.magnum -

what I was expecting in my logback config file. I suppose this should rather be the magnum's package? Yes.. that's a bug; thanks for raising!

"interceptor"

Open to adding as well. Do you have an example in mind?

…

On Wed, Jul 31, 2024, 9:13 AM Adam Warski ***@***.***> wrote: Ah! Finally got it to work. Turns out that private val Log = System.getLogger(getClass.getName) will create a logger for scala.Predef, instead of com.augustnagro.magnum - what I was expecting in my logback config file. I suppose this should rather be the magnum's package? Also, it would be great to have some kind of "interceptor" - logSql is fine for basic logging, but for more advanced use cases (like logging slow queries), or plugging in some metrics - an interceptor would be needed I think. — Reply to this email directly, view it on GitHub <#32 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/ABQOKNRRZNS3YRARIHGX6OLZPDWJHAVCNFSM6AAAAABLQVQJIWVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDENRQGYZDMOJZGA> . You are receiving this because you commented.Message ID: ***@***.***>

[adamw]

I was using Doobie's LogHandler before and it worked well: https://github.com/tpolecat/doobie/blob/d1f84e64b026c0989818546c935c879e5dcc3598/modules/free/src/main/scala/doobie/util/log.scala#L122

So maybe something similar, passed as parameter to transact - just as connectionConfig is passed now?

[AugustNagro]

I've merged an MR to fix the logger name, thanks again for reporting. I'm aiming to make a new release next week.

For the 'interceptor' feature, I still like the idea. However I'm worried that If we add a new parameter to transact, it would require too much discipline to remember at every transact call site.

In doobie you can put the LogHandler in your Transactor and then it's applied to every query with the Transactor. I wonder if we could have something similar, like

case class MagDataSource(
  dataSource: DataSource,
  sqlLogger: SqlLogger = SqlLogger.NoOp,
  connectionConfig: Connection => Unit = con => ()
)

// new overloads
def connect[T](magDataSource: MagDataSource)(f: DbCon ?=> T): T = ???
def transact[T](magDataSource: MagDataSource)(f: DbTx ?=> T): T = ???

// updated classes
class DbCon private[magnum] (val connection: Connection, val sqlLogger: SqlLogger)
class DbTx private[magnum] (connection: Connection, sqlLogger: SqlLogger) extends DbCon(connection, sqlLogger)

[adamw]

Putting it in the data source would work as well, though this would make logging DS-global, while I think in practice you might have different warning thresholds for different transactions (or even queries).

Definitely adding a new mandatory parameter to transact is a no-go, though maybe an overload, or using configuration from the implicit scope? Where the default configuration would be not to log, or to log using TRACE/DEBUG?

[AugustNagro]

@adamw please review when you get a chance. Here's an example:

val transactor = Transactor(
  dataSource = ???,
  sqlLogger = SqlLogger.logSlowQueries(500.milliseconds),
  connectionConfig = con =>
    con.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ)
)

transact(transactor):
  sql"SELECT id from myUser".query[Long].run()

[adamw]

Looks great! And since Transactor is a case class, it will be trivial to update the config for a specific query/code path. Thanks :)