Encoders

An encoder is needed any time you want to send a value to Postgres; i.e., any time a statement has parameters. Although it is possible to implement the Encoder interface directly, this requires knowledge of Postgres data formats and is not something you typically do as an end user. Instead you will use one or more existing encoders (see Schema Types) composed or transformed as desired.

Base Encoders

Base encoders are provided for many Postgres types (see Schema Types). These encoders translate to a single placeholders in your SQL statement.

Here is a statement with an interpolated base encoder.

sql"SELECT name FROM country WHERE code = $varchar"
// res0: Fragment[String] = Fragment(
//   parts = List(
//     Left(value = "SELECT name FROM country WHERE code = "),
//     Right(value = cats.data.IndexedStateT@e4746b5),
//     Left(value = "")
//   ),
//   encoder = Codec(varchar),
//   origin = Origin(file = "Encoders.md", line = 22)
// )

If there is more than one interpolated encoder (or an encoder composed of multiple smaller encoders, as we will see below) there will be multiple placeholders in the SQL statement, numbered sequentially.

sql"SELECT name FROM country WHERE code = $varchar AND population < $int8"
// res1: Fragment[String *: Long *: EmptyTuple] = Fragment(
//   parts = List(
//     Left(value = "SELECT name FROM country WHERE code = "),
//     Right(value = cats.data.IndexedStateT@e4746b5),
//     Left(value = " AND population < "),
//     Right(value = cats.data.IndexedStateT@166cbd64),
//     Left(value = "")
//   ),
//   encoder = Codec(varchar, int8),
//   origin = Origin(file = "Encoders.md", line = 29)
// )

Composite Encoders

Given two encoders a: Encoder[A] and b: Encoder[B] we can create a composite encoder a ~ b of type Encoder[(A, B)]. Such an encoder expands to a sequence of placholders separated by commas.

Here is a statement with a composite encoder constructed from two base encoders.

sql"INSERT INTO person (name, age) VALUES (${varchar ~ int4})"
// res2: Fragment[String ~ Int] = Fragment(
//   parts = List(
//     Left(value = "INSERT INTO person (name, age) VALUES ("),
//     Right(value = cats.data.IndexedStateT@3ebdb7df),
//     Left(value = ")")
//   ),
//   encoder = Codec(varchar, int4),
//   origin = Origin(file = "Encoders.md", line = 36)
// )

Here is a statement with a base encoder and a composite encoder constructed from three base encoders. Note that the composite structure is "flattened" in the resulting SQL statement; it is purely for convenience on the Scala side.

val enc = varchar ~ int4 ~ float4
// enc: Codec[String ~ Int ~ Float] = Codec(varchar, int4, float4)

sql"INSERT INTO person (comment, name, age, weight, comment) VALUES ($text, $enc)"
// res3: Fragment[String *: String ~ Int ~ Float *: EmptyTuple] = Fragment(
//   parts = List(
//     Left(
//       value = "INSERT INTO person (comment, name, age, weight, comment) VALUES ("
//     ),
//     Right(value = cats.data.IndexedStateT@7f9431ac),
//     Left(value = ", "),
//     Right(value = cats.data.IndexedStateT@696193e0),
//     Left(value = ")")
//   ),
//   encoder = Codec(text, varchar, int4, float4),
//   origin = Origin(file = "Encoders.md", line = 46)
// )

Combinators

The values combinator adds parentheses around an encoder's generated placeholders.

val enc = (varchar ~ int4).values
// enc: Encoder[String ~ Int] = Encoder(varchar, int4)

sql"INSERT INTO person (name, age) VALUES $enc"
// res4: Fragment[String ~ Int] = Fragment(
//   parts = List(
//     Left(value = "INSERT INTO person (name, age) VALUES "),
//     Right(value = cats.data.IndexedStateT@1a159435),
//     Left(value = "")
//   ),
//   encoder = Encoder(varchar, int4),
//   origin = Origin(file = "Encoders.md", line = 56)
// )

This can be very useful in combination with the list combinator, which creates an encoder for a list of values of a given length.

val enc = (varchar ~ int4).values
// enc: Encoder[String ~ Int] = Encoder(varchar, int4)

sql"INSERT INTO person (name, age) VALUES ${enc.list(3)}"
// res5: Fragment[List[String ~ Int]] = Fragment(
//   parts = List(
//     Left(value = "INSERT INTO person (name, age) VALUES "),
//     Right(value = cats.data.IndexedStateT@4945dbe7),
//     Left(value = "")
//   ),
//   encoder = Encoder(varchar, int4, varchar, int4, varchar, int4),
//   origin = Origin(file = "Encoders.md", line = 66)
// )

Transforming the Input Type

An Encoder[A] consumes a value of type A and encodes it for transmission Postgres, so it can therefore also consume anything that we can turn into an A. We do this with contramap.

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

val person = (varchar *: int4).values.contramap((p: Person) => (p.name, p.age))
// person: Encoder[Person] = Encoder(varchar, int4)

sql"INSERT INTO person (name, age) VALUES $person"
// res6: Fragment[Person] = Fragment(
//   parts = List(
//     Left(value = "INSERT INTO person (name, age) VALUES "),
//     Right(value = cats.data.IndexedStateT@17829a32),
//     Left(value = "")
//   ),
//   encoder = Encoder(varchar, int4),
//   origin = Origin(file = "Encoders.md", line = 79)
// )

Because contramapping from case classes is so common, Skunk provides to which adapts an encoder to a case class of the same structure.

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

val person = (varchar *: int4).values.to[Person]
// person: Encoder[Person] = Encoder(varchar, int4)

sql"INSERT INTO person (name, age) VALUES $person"
// res8: Fragment[Person] = Fragment(
//   parts = List(
//     Left(value = "INSERT INTO person (name, age) VALUES "),
//     Right(value = cats.data.IndexedStateT@7299e5b1),
//     Left(value = "")
//   ),
//   encoder = Encoder(varchar, int4),
//   origin = Origin(file = "Encoders.md", line = 106)
// )