Is it possible to use IN clause in plain sql Slick for integers?

Question

There is a similar question here but it doesn't actually answer the question.

Is it possible to use IN clause in plain sql Slick?

Note that this is actually part of a larger and more complex query, so I do need to use plain sql instead of slick's lifted embedding. Something like the following will be good:

val ids = List(2,4,9)
sql"SELECT * FROM coffee WHERE id IN ($ids)"

Answer 1

I have written a small extension to Slick that addresses exactly this problem: https://github.com/rtkaczyk/inslick

For the given example the solution would be:

import accode.inslick.syntax._

val ids = List(2,4,9)
sqli"SELECT * FROM coffee WHERE id IN *$ids"

Additionally InSlick works with iterables of tuples or case classes. It's available for all Slick 3.x versions and Scala versions 2.11 - 2.13. We've been using it in production for several months at the company I work for.

The interpolation is safe from SQL injection. It utilises a macro which rewrites the query in a way similar to trydofor's answer