Getting metadata in plain SQL statement in Slick 3.1.x

Question

In the following plain SQL statement in Slick I know beforehand that it will return a list of (String, String)

sql"""select c.name, s.name
      from coffees c, suppliers s
      where c.price < $price and s.id = c.sup_id""".as[(String, String)]

But what if I don't know the column types? Can I analyze the metadata and retrieve the values? In JDBC I could use getInt(n) and getString(n), is there anything similar in Slick?

Andy Hayden Andy Hayden · Accepted Answer · 2016-06-22T16:41:29

You can use tsql (Type-Checked SQL Statements):

tsql"""select c.name, s.name
      from coffees c, suppliers s
      where c.price < $price and s.id = c.sup_id"""

this will return a DBIO[Seq[(String, String)]] (depending on the column types).

produces a DBIOAction of the correct type without requiring a call to .as

Note: I've found it a little flakey (to the point of being unusable) with option types, so beware if your columns can be null (since null: String).

This requires a little bit of wiring up, you need @StaticDatabaseConfig (e.g. on your DAO), as these types are checked, against the database, at compile time:

# annotate the object
@StaticDatabaseConfig("file:src/main/resources/application.conf#tsql")
...

    val dc = DatabaseConfig.forAnnotation[JdbcProfile]
    import dc.driver.api._
    val db = dc.db

    # to pull out a Future[Seq[String, String]]
    # use db.run(tsql"...")
    # to pull out a Future[Option[(String, String)]]
    # use db.run(tsql"...".headOption)
    # etc.

Getting metadata in plain SQL statement in Slick 3.1.x

1 Answers