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.

Solution 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]

Solution 2

I'm posting answer which is not using interpolation, maybe someone find it helpful.

I solved it that way in tests, executeUpdate method return actual result of query. I was able to have dynamic query from String variable.

dbConnection = JdbcBackend.Database.forURL(url = dbConfig.getString("db.url"), driver = "org.h2.Driver")
val createTablesSqlQuery:String = //read_from_file
dbConnection.createSession().createStatement().executeUpdate(createTablesSqlQuery)

Helpful topic was this one: https://groups.google.com/forum/#!topic/scalaquery/OxAgtcCPMyg

Comments

[ciuncan]

Is the interpolation in case 2 sanitized? If not, is there a way to sanitize strings that we are going to interpolate using #$?

[Dimitri]

@ciuncan No it's not sanitized. I can't answer your second question. It can be anything: a column name, a table name, a schema, a query, part of a query...

[ciuncan]

@Dimitri Ah, you are right. In my case it is column names, and string literals to generate constant column values. Is it also dependent on database, or jdbc just handles that? If that's the case I am working with Postgresql. (Edit: I imaged jdbc could have some way to sanitize such strings for interpolation)

[Dimitri]

@ciuncan Sorry, I can't tell if such function is available.

[ciuncan]

Ok thank you. For anyone who needs such a thing, I found quoteIdentifier function in JdbcDriver driver trait, and I think it should be useful with escaping column and table names. I will look further and report here if I find anything relevant.

[Łukasz Gawron]

Answering your question "Why not simply have this?" someone may want to have sql query in a file, after reading it to String I just want to run the query. Working on that atm, but its hard to do that, case sqlu produces "?" from my query interpolated.

[techkuz]

PSQLException: ERROR: syntax error at or near "#"