Plain SQL in Slick - How do I map a postgres array column to a List?

Question

I need to use plain sql to make a query in Slick, and then cast the resulting rows into the db models. I'm unable to figure out how to convert an array postgres column (in this case an array of strings) to a scala List.

Example query

sql"""SELECT n.id, n.created_at, n.values
      FROM names n
      WHERE n.id = $id""".as[NamesDB])

Where values is an array of strings.

DB Model:

case class NamesDB(
  id: Long
  createdAt: Timestamp,
  values: List[String]
)

and table definition:

class Names(tag: Tag) extends Table[NamesDB](tag, "names"){

  def id = column[Long]("id", O.PrimaryKey, O.AutoInc)
  def createdAt = column[Timestamp]("created_at")
  def values = column[List[String]]("values")

  override def * = (
    id,
    createdAt,
    values
  ) <> ((NamesDB.apply _).tupled, NamesDB.unapply)
}

This works, but it feels like there must be a better way:

implicit val NamesDBRecord = GetResult(r => {
    val id = r.nextLong()
    val createdAt = r.nextTimestamp()
    val values = r.nextString() // "{name1, name2}"
                .drop(1).dropRight(1)
                .split(",").toList // 

    NamesDB(id, createdAt, values)
  })

I have been attempting to read slick's source code to see how they convert pg column types to scala/java types, but it's not super clear where to look for this.

Dmytro Mitin · Accepted Answer · 2019-09-09 20:12:05Z

3

Try to use https://github.com/tminglei/slick-pg

import java.sql.Timestamp
import com.github.tminglei.slickpg._

trait MyPostgresProfile extends ExPostgresProfile with PgArraySupport {

  override val api = MyAPI

  object MyAPI extends API with ArrayImplicits 
}

object MyPostgresProfile extends MyPostgresProfile

import MyPostgresProfile.api._

case class NamesDB(
                    id: Long,
                    createdAt: Timestamp,
                    values: List[String]
                  )

class Names(tag: Tag) extends Table[NamesDB](tag, "names"){

  def id = column[Long]("id", O.PrimaryKey, O.AutoInc)
  def createdAt = column[Timestamp]("created_at")
  def values = column[List[String]]("values")

  override def * = (
    id,
    createdAt,
    values
  ) <> ((NamesDB.apply _).tupled, NamesDB.unapply)
}

libraryDependencies += "com.github.tminglei" %% "slick-pg" % "0.18.0"

For plain sql try

implicit val strList = GetResult[List[String]] (
  r => (1 to r.numColumns).map(_ => r.nextString()).toList
)
implicit val NamesDBRecord = GetResult(r => NamesDB(r.<<, r.<<, r.<<))

sql"""SELECT n.id, n.created_at, n.values
    FROM names n
    WHERE n.id = $id""".as[NamesDB]

edited Sep 9, 2019 at 20:12

answered Sep 9, 2019 at 18:42

Dmytro Mitin

53.1k3 gold badges34 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Adam Richard Over a year ago

This is all actually in our project already, and it works like a charm when using slick-generated sql. However, is there a way to use this setup to convert the PositionedResult returned from a plain sql query?

Adam Richard Over a year ago

Got it, this led to a working answer, which I'll post. Thank you.

Adam Richard · Accepted Answer · 2019-09-09 22:11:52Z

1

Solution:

Inspecting the pg_slick library, I found this:

private def simpleNextArray[T](r: PositionedResult): Option[Seq[T]] = {
  val value = r.rs.getArray(r.skip.currentPos)
  if (r.rs.wasNull) None else Some(
    value.getArray.asInstanceOf[Array[Any]].map(_.asInstanceOf[T]))
}

Leading to this implementation:

implicit val strList = GetResult[List[String]] (r =>
  r.rs.getArray(r.skip.currentPos)
   .getArray
   .asInstanceOf[Array[Any]]
   .toList
   .map(_.toString())
)

implicit val getNamesDB = GetResult(r =>
  NamesDB(r.<<, r.<<, r.<<)
)

answered Sep 9, 2019 at 22:11

Adam Richard

1296 bronze badges

Collectives™ on Stack Overflow

Plain SQL in Slick - How do I map a postgres array column to a List?

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related