How to write dynamic SQL queries with sql""" interpolation in slick

Question

I am new to Scala and Slick and trying to write a plain SQL queries with Slick interpolation.

Case 1: I want the generalize the code so that queries are stored as constants.

for instance:

val SQL_ALL_TABLE_METADATA: String = """SELECT DISTINCT table_name, column_name, data_type
                                            FROM information_schema.columns
                                                    WHERE table_schema = 'apollo' OR table_schema = 'dpa' ORDER BY table_name""";

And create plain query from constant something like

var plainQuery = sql"""$SQL_ALL_TABLE_METADATA""".as[List[String]]

Case 2: Replace a part of the query

For instance: get information on column f_name from table 'table1'

var column= "f_name"
var plainQuery = sql"""SELECT $column FROM table1""".as[String]

When I try the above cases it is not working as it looks like query is binding statically on compile time.

Please note that as of now I want to use plain SQL and use advanced Slick API in future.

Answer 1

Case 1

Why not simply have this?

val SQL_ALL_TABLE_METADATA: StaticQuery = sql"""SELECT DISTINCT table_name, column_name, data_type
                                            FROM information_schema.columns
                                                    WHERE table_schema = 'apollo' OR table_schema = 'dpa' ORDER BY table_name"""

var plainQuery = SQL_ALL_TABLE_METADATA.as[List[String]]

Case 2

Use #$ instead of $

var column= "f_name"
var plainQuery = sql"""SELECT #$column FROM table1""".as[String]