How do you print the select statements for the following Slick queries?

Question

I would like to find out which of the following queries would be the most efficient for getting a row count on a table, so I'm trying to print out the select statements. I know you can add.selectStatement to a Queryable but don't know if this tells me the complete truth because I'll have to remove the result generating code, e.g. .list.length and replace it with .selectStatement. Slick probably picks up that you are looking for the length and optimises further, so I want to see the select statement for the whole query, including the SQL that will be generated because of the .list.length, or .count).first

Query(MyTable).list.length

(for{mt <- MyTable} yield mt).list.length

(for{mt <- MyTable} yield mt.count).first

Answer 1

In Slick 3.1.0 (and I suppose in 3.0) you can make very cool sql debug:

[DEBUG] - slick.jdbc.JdbcBackend.statement - Preparing statement: select "id", "email", "name", "password" from "users" where ("email" = '[email protected]') and ("password" = ext.crypt('123456',"password"))
[DEBUG] - slick.jdbc.JdbcBackend.benchmark - Execution of prepared statement took 56ms
[DEBUG] - slick.jdbc.StatementInvoker.result - /----------------------+---------------+-------+----------------------\
[DEBUG] - slick.jdbc.StatementInvoker.result - | 1                    | 2             | 3     | 4                    |
[DEBUG] - slick.jdbc.StatementInvoker.result - | id                   | email         | name  | password             |
[DEBUG] - slick.jdbc.StatementInvoker.result - |----------------------+---------------+-------+----------------------|
[DEBUG] - slick.jdbc.StatementInvoker.result - | 4fe6e5c3-af74-40f... | [email protected] | petya | $2a$10$WyOrBy7p48... |
[DEBUG] - slick.jdbc.StatementInvoker.result - \----------------------+---------------+-------+----------------------/

I use only logback configuration for logging, so it's very easy turn on:

<logger name="slick" level="INFO" />
<logger name="slick.jdbc" level="DEBUG" />

Answer 2

In Playframework 2.4.x with Slick 3.0+ use following entry:

<logger name="slick.jdbc" level="DEBUG"/>

Answer 3

In play-2.2.1 with slick 2.0.0, in application.conf have:

logger.scala.slick.jdbc.JdbcBackend.statement=DEBUG

Answer 4

In Slick 3.0 you can now directly get the SQL for execution directly

val q = coffees.filter(_.supID === 15)
val action = q.delete
val affectedRowsCount: Future[Int] = db.run(action)
val sql = action.statements.head

See http://slick.typesafe.com/doc/3.0.0/queries.html#querying