How to pass an array to a slick SQL plain query?

Question

How to pass an array to a slick SQL plain query? I tried as follows but it fails:

// "com.typesafe.slick" %% "slick" % "3.3.2",  // latest version
val ids = Array(1, 2, 3)
db.run(sql"""select name from person where id in ($ids)""".as[String])

Error: could not find implicit value for parameter e: slick.jdbc.SetParameter[Array[Int]]

However this ticket seems to say that it should work: https://github.com/tminglei/slick-pg/issues/131

Note: I am not interested in the following approach:

db.run(sql"""select name from person where id in #${ids.mkString("(", ",", ")")}""".as[Int])

score 1 · Accepted Answer · answered Oct 09 '19 at 14:25

1

The issue you linked points to a commit which adds this:

def mkArraySetParameter[T: ClassTag](/* ... */): SetParameter[Seq[T]]
def mkArrayOptionSetParameter[T: ClassTag](/* ... */): SetParameter[Option[Seq[T]]]

Note that they are not implicit.

You'll need to do something like

implicit val setIntArray: SetParameter[Array[Int]] = mkArraySetParameter[Int](...)

and make sure that is in scope when you try to construct your sql"..." string.

answered Oct 09 '19 at 14:25

Dylan

13,645
3
40
67

Ok, thanks. It's well explained here: https://github.com/tminglei/slick-pg – David Portabella Oct 09 '19 at 15:39
for mysql: https://github.com/foerster-technologies/slick-mysql – David Portabella Oct 09 '19 at 15:39

Mars Liu · Answer 2 · 2021-09-18T07:44:47.147

I meet same problem and searched it. And I resolved it with a implicit val like this:

    implicit val strListParameter: slick.jdbc.SetParameter[List[String]] =
      slick.jdbc.SetParameter[List[String]]{ (param, pointedParameters) =>
        pointedParameters.setString(f"{${param.mkString(", ")}}")
      }

put it into your slick-pg profile and import it with other val at where needed.

Or more strict, like this:

    implicit val strListParameter: slick.jdbc.SetParameter[List[String]] =
      slick.jdbc.SetParameter[List[String]]{ (param, pointedParameters) =>
        pointedParameters.setObject(param.toArray, java.sql.Types.ARRAY)
      }
    implicit val strSeqParameter: slick.jdbc.SetParameter[Seq[String]] =
      slick.jdbc.SetParameter[Seq[String]]{ (param, pointedParameters) =>
        pointedParameters.setObject(param.toArray, java.sql.Types.ARRAY)
      }

and use the val like:

val entries: Seq[String]
    val query = {
      sql"""select ... from xxx
where entry = ANY($entries)
order by ...
        """.as[(Column, Types, In, Here)]
    }

score 0 · Answer 3 · answered Mar 08 '23 at 10:39

To improve @Mars Liu and @Dylan answers please see complete example for PostgreSQL (replace varchar with desired type):

// "com.github.tminglei" %% "slick-pg" % "0.19.3"
import com.github.tminglei.slickpg.utils.PlainSQLUtils.mkArraySetParameter
implicit val setStringArray: SetParameter[Seq[String]] = mkArraySetParameter[String]( "varchar" )
// usage
sql"SELECT * FROM mytable WHERE id = ANY($ids);" // same as SELECT * FROM mytable WHERE id IN (?,?,?,...)

How to pass an array to a slick SQL plain query?

3 Answers3

Linked