How do I write slick table definitions with nullable columns?

Question

This table definition worked until I realized that having nullable columns meant that I needed to use Option[String] instead of just String. That was the only change I made and this is what my code looks like now.

class RespondentTableDef(tag: Tag) extends Table[Respondent](tag, "respondent") {

  def id = column[Long]("id", O.PrimaryKey)
  def uuid = column[String]("uuid")
  def version = column[Long]("version")
  def task = column[Long]("task")
  def firstName = column[Option[String]]("first_name")
  def lastName = column[Option[String]]("last_name")
  def ageGroup = column[Option[String]]("age_group")
  def incomeLevel = column[Option[String]]("income_level")
  def employmentStatus = column[Option[String]]("employment_status")
  def maritalStatus = column[Option[String]]("marital_status")
  def housingStatus = column[Option[String]]("housing_status")
  def educationStatus = column[Option[String]]("education_status")
  def gender = column[Option[String]]("gender")

  override def * =
    (id, uuid, version, task, firstName, lastName, ageGroup, incomeLevel, employmentStatus, maritalStatus, housingStatus, educationStatus, gender) <> (Respondent.tupled, Respondent.unapply)
}

I am getting this error when it compiles.

[error] /Users/roy/adivinate/survey2/app/model/Respondent.scala:45: No matching Shape found.
[error] Slick does not know how to map the given types.
[error] Possible causes: T in Table[T] does not match your * projection. Or you use an unsupported type in a Query (e.g. scala List).
[error]   Required level: slick.lifted.FlatShapeLevel
[error]      Source type: (slick.lifted.Rep[Long], slick.lifted.Rep[String], slick.lifted.Rep[Long], slick.lifted.Rep[Long], slick.lifted.Rep[Option[String]], slick.lifted.Rep[Option[String]], slick.lifted.Rep[Option[String]], slick.lifted.Rep[Option[String]], slick.lifted.Rep[Option[String]], slick.lifted.Rep[Option[String]], slick.lifted.Rep[Option[String]], slick.lifted.Rep[Option[String]], slick.lifted.Rep[Option[String]])
[error]    Unpacked type: (Long, String, Long, Long, String, String, String, String, String, String, String, String, String)
[error]      Packed type: Any
[error]     (id, uuid, version, task, firstName, lastName, ageGroup, incomeLevel, employmentStatus, maritalStatus, housingStatus, educationStatus, gender) <> (Respondent.tupled, Respondent.unapply)
[error]                                                                                                                                                    ^
[error] one error found
[error] (compile:compileIncremental) Compilation failed
[error] Total time: 5 s, completed Dec 21, 2016 8:53:17 PM

Answer 1

It's basically a simple thing - your case class needs to have these fields optional. E.g. instead of having (in your case class): firstName: String you should have firstName: Option[String] .

Answer 2

Don't use option in definition of the column. If you have Option[String] column should be still String.

def firstName = column[Option[String]]("first_name") //Bad
def firstName = column[String]("first_name") //Good

When you define select (def *) use .? to define Option

  override def * =
    (id, uuid, version, task, firstName.?, lastName.?, ageGroup.?, incomeLevel.?, employmentStatus.?, maritalStatus.?, housingStatus.?, educationStatus.?, gender.?) <> (Respondent.tupled, Respondent.unapply)

Also if you want to update (in TableQuery):

def updateExistingName(name : String) : DBIO[Int] =
{
    map(_.firstName).update(name)
}

def updateOptionName(nameCanBeNone: Option[String]) : DBIO[Int] =
{
    map(_.firstName.?).update(nameCanBeNone)
}

Answer 3

Declare your field in the model class as Option[T] instead of T to make that corresponding column Nullable

Lets understand this with an example

case class Foo(name: String, rating: Option[Int])

class Foos(tag: Tag) extends Table[Foo](tag, "foos") {
  def name = column[String]("name") //name is not null
  def rating = column[Option[Int]]("rating") //rating is nullable
  def * = (name, rating) <> (Foo.tupled, Foo.unapply)
}

If you want to make something nullable just declare it as Option field and thats it slick will understand and generate sql with that particular field as nullable.

The above design is seamless and sound with Scala Option design. The meaning is option in Scala is directly converted to the Nullable in sql.

In older versions of Slick

You had to tell that particular column is not null by explicitly passing O.NotNull in the column declaration, but its not required in new version of slick