Selecting many arbitrary columns in Slick

Question

I'm trying to run a SELECT *-style query in Slick against a view. Ideally, I'd end up with a function that you can pass a set of column names as a Seq[String] and it'd execute the query SELECT col1, col2, ... FROM view. If that's not feasible then it'd be acceptable to just return all columns (SELECT * FROM view).

I know I could do this by defining a Table class for this view as well as an accompanying case class, but some of these views have hundreds of columns (which would require nested tuples, given that there's a 22 element limit on tuples), and there are many views I'd have to do this for, so that's a lot of code to write/generate/maintain. I'd like to avoid that if at all possible.

Is this doable in Slick? Either by defining a Table class without having to define columns in static code (initializing some class with a dynamic column string list would be acceptable), or by just generating a SQL query ourselves using sql"""...""" and somehow getting .as[...] to handle returning a set of columns of arbitrary length?

I don't need to do anything special with the actual column types: on the database side we've got a mixture of types, but if they're all treated as text columns on the Slick side that'd be fine with me.

Answer 1

Slick is not a good fit for your requirements.

The moment you want to access arbitrary columns (for example by using a List[String] of column names), you're losing all the type safety benefits that Slick provides because it cannot validate the schema.

This means you'll have to leave Slick's table classes behind and go for a completely dynamic solution using generated SQL (using String queries).

While Slick does allow you to write SQL manually (take a look at the StaticQuery.query functions), other libraries are better suited for the task. I recommend taking a look at ScalikeJDBC.