Retrieving Data

Executing a query when you don’t care about the result is as simple as:

// Death Star Query
sql"""DELETE FROM planets WHERE name="Alderaan"""".execute

However, when you execute a query, you usually want to interact with the result in some way in Scala. In Relate, you do this by defining a parser and passing it to one of many methods that will parse rows into the specified data structure (List, Map, etc.). In the following sections, we’ll show how to define parsers and how to use them.

Defining a Parser

Parsers are nothing more than functions that take a SqlRow representing a row of data and return the desired result. SqlRows have many methods that allow you to extract a column as an instance of a particular type. Here’s an example:

import com.lucidchart.relate.SqlRow

object Color extends Enumeration {
  val Green = Value(0)
  val Blue = Value(1)
  val Red = Value(2)
}

case class Jedi(
  name: String,
  lightSaberColor: Color.Value,
  species: Option[String]
)

def jediParser(row: SqlRow): Jedi = {
  Jedi(
    row.string("name"),
    Color(row.short("lightsaber_color")),
    row.stringOption("species")
  )
}

In this example, the created parser takes the value from the name column of the row as a string, the value of lightSaberColor as a short that is used for the Enumeration, and the value from the species column as a string option to instantiate a Jedi object. The SqlRow object has numerous methods to extract data from the row with the desired data type.

The parser can return any value, so it doesn’t necessarily need to be an instance of a case class. A Tuple, Seq, etc. would work equally well.

Applying a Parser

Applying a parser to a query only requires specifying the desired collection type to return. The following is an example using the parser created in the previous section:

sql"SELECT * FROM jedi".asList(jediParser)(connection)

This example would return a List[Jedi]. The parser can also be passed to the asSingle, asSingleOption, asSet, asSeq, asIterable, and asList methods to produce the respective collections.

Parsers that return a Tuple of size 2 can also be passed to the asMap method to get a Map. Here’s an example of its use (using the case class from the previous example):

def nameToJediParser(row: SqlRow): (String, Jedi) = {
  val jedi = Jedi(
    row.string("name"),
    Color(row.short("lightsaber_color")),
    row.stringOption("species")
  )
  (jedi.name, jedi)
}

// Map[String, Jedi]
sql"SELECT * FROM jedi".asMap(nameToJediParser)(connection)

Using RowParser Instances

It is common in Relate to use methods like .asList or asSingleOption and explicitly pass a parser to these methods. It’s also possible to allow the parser to be implicitly summoned. If define an implicit RowParser[Jedi] instead of defining jediParser as above you can then specify the type of list you wish to receive:

object Jedi {
  implicit val parser: RowParser[Jedi] = new RowParser[Jedi] {
    def parse(row: SqlRow): Jedi = {
      Jedi(
        row.string("name"),
        Color(row.short("lightsaber_color")),
        row.stringOption("species")
      )
    }
  }
}

// Parser a `Jedi` is simple enough:
sql"SELECT * FROM jedi".asList[Jedi]

Taking this one step further, you can use the .as method and specific the collection you’d like to end up with:

sql"SELECT * FROM jedi".as[List[Jedi]]

Any type that has an implicit RowParser available can be used with .as. Relate provides implicits for most common collections and types.

ColReader

Relate also provides a ColReader which is much like RowParser but operates at the column level. Instead of row.string("name") you can write row[String]("name"). For common types like String there isn’t much benefit to this approach. However if you wish a single column to represent a type that you control, simply provide an implicit ColReader for that type. The Jedi class has a lightSaberColor member that is a Color.Value. In the parser above Color(row.short("lightsaber_color")) was used. If we provide a ColReader as folows:

object Color extends Enumeration {
  val Green = Value(0)
  val Blue = Value(1)
  val Red = Value(2)

  implicit val colreader: ColReader[Value] = {
    ColReader.intReader.flatMap(id => ColReader[Value] { (_, _) =>
      values.find(_.id == id)
    })
  }
}

Then we can parse the lightsaber_color column with:

row[Color.Value]("lightsabe_color")

There is also a helper to create ColReader implicits for an Enumeration. You could replace the implementation of colreader above with:

implicit val colreader: ColReader[Value] = ColReader.enumReader(Color)

Relate provides an implicit ColReader for most common types.

Single Column Parsers

Sometimes a query retrieves only one column. Convenience methods are defined in RowParser for creating single column row parsers in these occasions. Below is an example of their use:

import com.lucidchart.relate.RowParser

sql"""
  SELECT name
  FROM jedi
  WHERE species="Human"
""".asList(RowParser.string("id"))

The RowParser object also contains definitions for bigInt, date, int, and string.

Single Value Parsers

In other cases, only one value is desired as the result of a query. For these scenarios, Relate provides a scalar method with which the desired type of the returned single value can be defined. The return value is wrapped in an Option. An example of its use is as follows:

sql"""
  SELECT species
  FROM jedi
  WHERE name="Yoda"
""".asScalarOption[String]

There is also a non-option version of this method, asScalar[A].

Retrieving Auto Increment Values on Insert

There also exist methods to retrieve the auto-incremented ids of inserted records. Given a table where the primary key was a bigint, here’s an example:

val id = sql"""
  INSERT INTO droids(name)
  VALUES ("BB-8")
""".executeInsertLong()

Ids can also be retrieved as integers with executeInsertInt. There also exist plural versions of these two methods: executeInsertInts and executeInsertLongs. Finally, in the case that the id is not an integer or bigint, use executeInsertSingle or executeInsertCollection, both of which take parsers capable of parsing your ids as their parameters.