Pagination with Scala and Slick: How to Properly Build Select Queries

Implementing pagination in Scala projects using Slick is a very common task, but it’s not always clear how you can use Slick efficiently. It's obvious that requesting all records from a schema in one go is a bad idea no matter how many records are in that schema. But building efficient database requests is more than just getting a small number of values from a table.

Here are several aspects of implementing pagination on the server:

• The server should get only the necessary values, not all of them at once.
• The server should use an offset to get only the entries for a specific page.
• The query should return not only items, but also additional data to the client.
• The returned items should be filtered before they're sent to the client.

The third point needs clarification. What kind of additional data may the client application need? It's often necessary to return the total quantity of items, the availability of the next page, and other data depending on the front-end application needs.

We'll show a way to implement efficient pagination with Slick for Scala projects according to the requirements above.

Simple Pagination Example with Slick

We’ll start with a simple example of pagination with Slick. Let's say that our task is to get a list of comments that were published by a user. Your Scala application will have two entities — the user and the comment — with the one-to-many relation: a user can have many comments, and a single comment will belong to only one user.

Here’s what a Comment entity might look like (we won't really need the User entity to demonstrate how pagination with Slick and Scala can be done):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

case class Comment(
  id: Option[Long] = None,
  creatorId: Long,
  createdAt: Timestamp = new Timestamp(DateTime.now().getMillis)
)

object Comment extends ((Option[Long], Long, Timestamp) => Comment) {
  class Table(tag: Tag) extends Table[Comment](tag, "COMMENTS") {
    def id = column[Long]("ID", O.PrimaryKey, O.AutoInc)
    def creatorId = column[Long]("CREATOR_ID")
    def creator = foreignKey("userId", creatorId, TableQuery[User.Table])(
      targetColumns = _.id,
      onUpdate = ForeignKeyAction.NoAction,
      onDelete = ForeignKeyAction.Cascade
    )
    def createdAt = column[Timestamp]("CREATED_AT")
    def * = (id.?, creatorId, createdAt).mapTo[Comment]
  }
}

We can get the entire list of comments using the user ID provided by the client application:

1
2
3
4
5

val query = TableQuery[Comment.Table]

def findAll(userId: Long) = db.run {
  query.filter(_.creatorId === userId).result
}

This obvious implementation will strain the database layer too much (a user can have a thousand comments, and such request will get all of them), which is why we should use "paginated" select to make the database requests more efficient.

The rewritten method findAll() (below) will accept not only the user ID, but also a limit — the number of records to be rendered on a single page, and an offset — an integer value for the start record. Needless to say that the limit and offset values must be provided by the client application.

Let's use the Slick methods drop() for the offset and take() for the limit:

1
2
3
4
5

def query = TableQuery[Comment.Table]

def findAll(userId: Long, limit: Int, offset: Int) = db.run{
  query.filter(_.creatorId === userId).drop(offset).take(limit).result
}

The improved findAll() method does exactly what we wanted: it gets only the necessary number of comments thus helping us spare the bandwidth and performance of the hardware.

But, as we discussed in the introduction, that's not all we can do to make Slick pagination efficient. The next step is to modify the request in order to get more information about the records.

Getting More Information About the Records

When implementing server-side pagination, it’s often necessary to get additional data about the records. For example, you may need to know the total number of records so that the client application can show this number to the user. Additionally, it's good to know if there’s the next page, which will help you decide whether the client application should send a request for that next page.

To handle these additional data in a convenient way, you can create a model that will manage various data depending on the requirements of your application. Our model PaginatedResult will contain these three parameters: the list of comments, the total amount of comments, and the availability of the next page:

1
2
3
4
5

case class PaginatedResult[T](
  totalCount: Int, 
  entities: List[T], 
  hasNextPage: Boolean
)

We should rewrite findAll() accordingly to return PaginatedResult:

1
2
3
4
5
6
7
8
9
10
11
12
13
14

def query = TableQuery[Comment.Table]

def findAll(userId: Long, limit: Int, offset: Int) = db.run {
  for {
    comments <- query.filter(_.creatorId === userId)
                     .drop(offset).take(limit)
                     .result
    numberOfComments <- query.filter(_.creatorId === userId).length.result
  } yield PaginatedResult(
    totalCount = numberOfComments,
    entities = comments.toList,
    hasNextPage = numberOfComments - (offset + limit) > 0
  )
}

As we can see from the code above, the findAll() method gets the requested entities and their length totalCount. The code also calculates whether there's the next page.

Now that we've implemented pagination with Slick, one thing is left to discuss — how to sort the entities received from the database.

Sorting the Database Results With Slick

Sorting the database results is another task that we often need to implemented when working on server-side pagination. It's quite easy to change our pagination example to sort the returned data by a given parameter. Here’s the code that sorts comments by date:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

def query = TableQuery[Comment.Table]

def findAll(userId: Long, limit: Int, offset: Int) = db.run {
  for {
    comments <- query.filter(_.creatorId === userId)
                     .sortBy(_.createdAt)
                     .drop(offset).take(limit)
                     .result
    numberOfComments <- query.filter(_.creatorId === userId).length.result
  } yield PaginatedResult(
      totalCount = numberOfComments,
      entities = comments.toList,
      hasNextPage = numberOfComments - (offset + limit) > 0
  )
}

It's important to call the sortBy() method before you drop the records and take the necessary amount of elements. If you first call drop() and take(), and then try to sort the filtered entities with sortBy(), the elements will be incorrectly selected from the unsorted collection and only after that sorted among themselves.

The simple approach we've described enables you to request, filter, and sort entities received from database and, as a result, implement efficient pagination using Slick in your Scala application.