Fragments

Fragment[A] encapsulates a SQL statement, together with an Encoder for a statement parameter of type A. Fragments are normally constructed via the sql interpolator, which constructs a fragment of a type determined by interpolated parameter encoders, if any.

// A fragment with no interpolated encoders.
val f1 = sql"SELECT 42"
// f1: Fragment[Void] = Fragment(
//   parts = List(Left(value = "SELECT 42")),
//   encoder = Codec(void),
//   origin = Origin(file = "Fragments.md", line = 26)
// )

// A fragment with an interpolated encoder.
val f2 = sql"SELECT foo FROM bar WHERE baz = $int8"
// f2: Fragment[Long] = Fragment(
//   parts = List(
//     Left(value = "SELECT foo FROM bar WHERE baz = "),
//     Right(value = cats.data.IndexedStateT@166cbd64),
//     Left(value = "")
//   ),
//   encoder = Codec(int8),
//   origin = Origin(file = "Fragments.md", line = 29)
// )

The following sections provide more details on constructing fragments.

Interpolating Parameter Encoders

Interpolated encoders are replaced with statement parameters (see the first element shown in the Fragment string representation below).

val f3 = sql"foo $int4 bar $varchar bar"
// f3: Fragment[Int *: String *: EmptyTuple] = Fragment(
//   parts = List(
//     Left(value = "foo "),
//     Right(value = cats.data.IndexedStateT@1f07cbf4),
//     Left(value = " bar "),
//     Right(value = cats.data.IndexedStateT@e4746b5),
//     Left(value = " bar")
//   ),
//   encoder = Codec(int4, varchar),
//   origin = Origin(file = "Fragments.md", line = 35)
// )

The resulting statement is prepared, and arguments (encoded) are passed separately as part of the extended query protocol. Skunk never interpolates statement arguments.

Some encoder combinators have an effect on the SQL that is generated when they are interpolated. In the examples below we will use Fragment's sql member for clarity.

Interpolating an encoder product (as constructed with *: for instance) yields a comma-separated sequence of parameters in the resulting SQL.

sql"foo ${int4 *: varchar} bar".sql
// res0: String = "foo $1, $2 bar"

The values combinator wraps an encoder's SQL in parentheses.

sql"foo ${(int4 *: varchar).values} bar".sql
// res1: String = "foo ($1, $2) bar"

The list combinator yields a sequence of parameters, one per element (this is why we must know the length when constructing the fragment).

sql"foo ${int4.list(4)} bar".sql
// res2: String = "foo $1, $2, $3, $4 bar"

When used in combination these can be quite useful.

sql"INSERT ... VALUES ${(int4 *: varchar).values.list(3)}".sql
// res3: String = "INSERT ... VALUES ($1, $2), ($3, $4), ($5, $6)"

Interpolating Literal Strings

Parameters can only appear in syntactic positions where values can appear (you cannot use a parameter in place of a table name for instance). In these cases you can interpolate a literal strings, by escaping it with #$.

Here is an example with an iterpolated literal string, as well as a normal parameter.

val table = "my_table"
val frag  = sql"SELECT foo, bar FROM #$table where foo = $int4"

The resulting SQL will contain table verbatim.

frag.sql
// res4: String = "SELECT foo, bar FROM my_table where foo = $1"

Composing Fragments

Fragment products operate like encoder products, appending the resulting SQL.

val f4 = sql"SELECT $int4, foo FROM blah WHERE "
// f4: Fragment[Int] = Fragment(
//   parts = List(
//     Left(value = "SELECT "),
//     Right(value = cats.data.IndexedStateT@1f07cbf4),
//     Left(value = ", foo FROM blah WHERE ")
//   ),
//   encoder = Codec(int4),
//   origin = Origin(file = "Fragments.md", line = 80)
// )

val f5 = sql"bar = $varchar"
// f5: Fragment[String] = Fragment(
//   parts = List(
//     Left(value = "bar = "),
//     Right(value = cats.data.IndexedStateT@e4746b5),
//     Left(value = "")
//   ),
//   encoder = Codec(varchar),
//   origin = Origin(file = "Fragments.md", line = 83)
// )

val f6 = f4 *: f5
// f6: Fragment[Int *: String *: EmptyTuple] = Fragment(
//   parts = List(
//     Left(value = "SELECT "),
//     Right(value = cats.data.IndexedStateT@1f07cbf4),
//     Left(value = ", foo FROM blah WHERE "),
//     Left(value = "bar = "),
//     Right(value = cats.data.IndexedStateT@e4746b5),
//     Left(value = "")
//   ),
//   encoder = Encoder(int4, varchar),
//   origin = Origin(file = "Fragments.md", line = 80)
// )

Alternatively we can interpolate fragments inside one another.

val f7 = sql"bar = $varchar"
// f7: Fragment[String] = Fragment(
//   parts = List(
//     Left(value = "bar = "),
//     Right(value = cats.data.IndexedStateT@e4746b5),
//     Left(value = "")
//   ),
//   encoder = Codec(varchar),
//   origin = Origin(file = "Fragments.md", line = 92)
// )

val f8 = sql"SELECT $int4, foo FROM blah WHERE $f7 AND x = $int2"
// f8: Fragment[Int *: String *: Short *: EmptyTuple] = Fragment(
//   parts = List(
//     Left(value = "SELECT "),
//     Right(value = cats.data.IndexedStateT@1f07cbf4),
//     Left(value = ", foo FROM blah WHERE "),
//     Left(value = "bar = "),
//     Right(value = cats.data.IndexedStateT@e4746b5),
//     Left(value = ""),
//     Left(value = " AND x = "),
//     Right(value = cats.data.IndexedStateT@66fb07a6),
//     Left(value = "")
//   ),
//   encoder = Encoder(int4, varchar, int2),
//   origin = Origin(file = "Fragments.md", line = 95)
// )

Note how the output type is computed, and parameters are renumbered as necessary.

Contramapping Fragments

Fragments form a contravariant semigroupal functor, and this can be tupled (with *: as above) and can be contramapped to change their input type.

case class Person(name: String, age: Int)

val f9 = sql"INSERT ... VALUES ($varchar, $int4)"
// f9: Fragment[String *: Int *: EmptyTuple] = Fragment(
//   parts = List(
//     Left(value = "INSERT ... VALUES ("),
//     Right(value = cats.data.IndexedStateT@e4746b5),
//     Left(value = ", "),
//     Right(value = cats.data.IndexedStateT@1f07cbf4),
//     Left(value = ")")
//   ),
//   encoder = Codec(varchar, int4),
//   origin = Origin(file = "Fragments.md", line = 104)
// )

// note the return type
val f10 = f9.contramap[Person](p => (p.name, p.age))
// f10: Fragment[Person] = Fragment(
//   parts = List(
//     Left(value = "INSERT ... VALUES ("),
//     Right(value = cats.data.IndexedStateT@e4746b5),
//     Left(value = ", "),
//     Right(value = cats.data.IndexedStateT@1f07cbf4),
//     Left(value = ")")
//   ),
//   encoder = Encoder(varchar, int4),
//   origin = Origin(file = "Fragments.md", line = 104)
// )

// alternatively
val f11 = f9.to[Person]
// f11: Fragment[Person] = Fragment(
//   parts = List(
//     Left(value = "INSERT ... VALUES ("),
//     Right(value = cats.data.IndexedStateT@e4746b5),
//     Left(value = ", "),
//     Right(value = cats.data.IndexedStateT@1f07cbf4),
//     Left(value = ")")
//   ),
//   encoder = Encoder(varchar, int4),
//   origin = Origin(file = "Fragments.md", line = 104)
// )

Applied Fragments

It is sometimes useful to bind a fragment to a set of arguments. The resulting AppliedFragment forms a monoid, with an opaque argument of an existential argument type. Applied fragments can be useful when constructing statements on the fly.

def countryQuery(name: Option[String], pop: Option[Int]): AppliedFragment = {

  // Our base query
  val base = sql"SELECT code FROM country"

  // Some filter conditions
  val nameLike       = sql"name LIKE $varchar"
  val popGreaterThan = sql"population > $int4"

  // Applied fragments for conditions, if any.
  val conds: List[AppliedFragment] =
    List(
      name.map(nameLike),
      pop .map(popGreaterThan),
    ).flatten

  // The composed filter.
  val filter =
    if (conds.isEmpty) AppliedFragment.empty
    else conds.foldSmash(void" WHERE ", void" AND ", AppliedFragment.empty)

  // Prepend the base query and we're done.
  base(Void) |+| filter

}

countryQuery(Some("Un%"), Some(99999)).fragment.sql
// res5: String = "SELECT code FROM country WHERE name LIKE $1 AND population > $2"

countryQuery(Some("Un%"), None).fragment.sql
// res6: String = "SELECT code FROM country WHERE name LIKE $1"

countryQuery(None, None).fragment.sql
// res7: String = "SELECT code FROM country"

To prepare and execute some af: AppliedFragment you must extract its underlying fragment: Query[af.A] and arg: af.A members, as below.

def usage(s: Session[IO]) = {
  val f = countryQuery(Some("Un%"), None) // AppliedFragment
  val q = f.fragment.query(varchar)       // Query[f.A, String]
  s.prepare(q).flatMap(_.stream(f.argument, 64).compile.to(List))
}