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

Solution 1

I've not been able to print the select statements using Slick, but Virtualeyes made a good suggestion: Look at the database logs!

Well, I test my Slick code in Scala Worksheets, and this is how you go about setting it up - For the worksheets and H2 you need to change the trace level in the database url, e.g.

implicit val session = Database.forURL(
"jdbc:h2:mem:test1;TRACE_LEVEL_FILE=4", 
driver = "org.h2.Driver")
.createSession()

This will tell H2 to log just about everything. Keep in mind though, that you will have to increase the 'maximum number or lines to output' in preferences -> Worksheet.

It also turns out that setting Slick at the right level of logging will serve the same purpose.

Thanks virtualeyes for alerting me to the elephant in the room :-)

Solution 2

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

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

Solution 3

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" />

Solution 4

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

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

Solution 5

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

View more solutions

Comments

[User]

that seems to work only for FixedSqlAction though? Doesn't work with a generic DBIO, e.g. a join