Slick plain sql query with pagination

Question

I have something like this, using Akka, Alpakka + Slick

 Slick
 .source(
     sql"""select #${onlyTheseColumns.mkString(",")} from #${dbSource.table}"""
    .as[Map[String, String]]
    .withStatementParameters(rsType = ResultSetType.ForwardOnly, rsConcurrency = ResultSetConcurrency.ReadOnly, fetchSize = batchSize)
    .transactionally
).map( doSomething )...

I want to update this plain sql query with skipping the first N-th element. But that is very DB specific.

Is is possible to get the pagination bit generated by Slick? [like for type-safe queries one just do a drop, filter, take?]

ps: I don't have the Schema, so I cannot go the type-safe way, just want all tables as Map, filter, drop etc on them. ps2: at akka level, the flow.drop works, but it's not optimal/slow, coz it still consumes the rows. Cheers

score 0 · Answer 1 · answered Jan 29 '20 at 20:11

0

Since you are using the plain SQL, you have to provide a workable SQL in code snippet. Plain SQL may not type-safe, but agile.

BTW, the most optimal way is to skip N-th element by Database, such as limit in mysql.

answered Jan 29 '20 at 20:11

YouXiang-Wang

1,119
6
15

score 0 · Answer 2 · answered Apr 01 '20 at 00:43

depending on your database engine, you could use something like

val page = 1
val pageSize = 10
val query = sql"""
  select #${onlyTheseColumns.mkString(",")} 
  from #${dbSource.table} 
  limit #${pageSize + 1}
  offset #${pageSize * (page - 1)}
"""

the pageSize+1 part tells you whether the next page exists

score 0 · Answer 3 · answered Apr 01 '20 at 08:56

I want to update this plain sql query with skipping the first N-th element. But that is very DB specific.

As you're concerned about changing the SQL for different databases, I suggest you abstract away that part of the SQL and decide what to do based on the Slick profile being used.

If you are working with multiple database product, you've probably already abstracted away from any specific profile, perhaps using JdbcProfile. In that case you could place your "skip N elements" helper in a class and use the active slickProfile to decide on the SQL to use. (As an alternative you could of course check via some other means, such as an environment value you set).

In practice that could be something like this:

case class Paginate(profile: slick.jdbc.JdbcProfile) {
  // Return the correct LIMIT/OFFSET SQL for the current Slick profile
  def page(size: Int, firstRow: Int): String =
    if (profile.isInstanceOf[slick.jdbc.H2Profile]) {
      s"LIMIT $size OFFSET $firstRow"
    } else if (profile.isInstanceOf[slick.jdbc.MySQLProfile]) {
      s"LIMIT $firstRow, $size"
    } else {
      // And so on... or a default
      // Danger: I've no idea if the above SQL is correct - it's just placeholder
      ???
    }
}

Which you could use as:

// Import your profile
import slick.jdbc.H2Profile.api._

val paginate = Paginate(slickProfile)

val action: DBIO[Seq[Int]] = 
  sql""" SELECT cols FROM table #${paginate.page(100, 10)}""".as[Int]

In this way, you get to isolate (and control) RDBMS-specific SQL in one place.

To make the helper more usable, and as slickProfile is implicit, you could instead write:

def page(size: Int, firstRow: Int)(implicit profile: slick.jdbc.JdbcProfile) = 
    // Logic for deciding on SQL goes here

I feel obliged to comment that using a splice (#$) in plain SQL opens you to SQL injection attacks if any of the values are provided by a user.

Slick plain sql query with pagination

3 Answers3