Can I string-build a Slick plain SQL query?

Question

I'm using Slick to assemble a plain SQL query.

I'd like to have part of the query constructed differently at runtime, depending on local variables.

This implies I need to concatenate multiple partial SqlActions (I think?).

I understand an alternative approach is to use #${plainSqlString} but this would require me to manually convert my parameters to literals (e.g. $delta to #$delta below). However, for String parameters this would require manual SQL sanitization... which I want to avoid!

val delta      = 42

val applyDelta =
  if (limitNegatives)
    "GREATEST(0, col_1 + $delta)"
  else
    "col_1 + $delta"

val statement =
  sqlu"""
        UPDATE table_foo
        SET    col_1=#${applyDelta}
      """

Any ideas how to string-build a SqlAction with parameters ($delta) in each SqlAction?

Knows Not Much Knows Not Much · Accepted Answer · 2018-02-25T14:54:39

val delta      = 42

val applyDelta =
  if (limitNegatives)
    sqlu"UPDATE table_foo set col1 = GREATEST(0, col_1 + ${delta})"
  else
    sqlu"UPDATE table_foo set col1 = col_1 + ${delta}"

the value of applydelta is a SqlAction which can be executed

Can I string-build a Slick plain SQL query?

1 Answers