Defining and reading a nullable date column in Slick 3.x

Question

I have a table with a column type date. This column accepts null values, therefore, I declared it as an Option (see field perDate below). The issue is that apparently the implicit conversion from/to java.time.LocalDate/java.sql.Date is incorrect as reading from this table when perDate is null fails with the error:

slick.SlickException: Read NULL value (null) for ResultSet column <computed>

This is the Slick table definition, including the implicit function:

import java.sql.Date
import java.time.LocalDate

class FormulaDB(tag: Tag) extends Table[Formula](tag, "formulas") {

  def sk = column[Int]("sk", O.PrimaryKey, O.AutoInc)
  def name = column[String]("name")
  def descrip = column[Option[String]]("descrip")
  def formula = column[Option[String]]("formula")
  def notes = column[Option[String]]("notes")
  def periodicity = column[Int]("periodicity")
  def perDate = column[Option[LocalDate]]("per_date")(localDateColumnType)

  def * = (sk, name, descrip, formula, notes, periodicity, perDate) <> 
       ((Formula.apply _).tupled, Formula.unapply)


  implicit val localDateColumnType = MappedColumnType.base[Option[LocalDate], Date](
     {
        case Some(localDate) => Date.valueOf(localDate)
        case None => null
     },{
        sqlDate => if (sqlDate != null) Some(sqlDate.toLocalDate) else None
     }
  )

}

Answer 1

Actually your implicit conversion from/to java.time.LocalDate/java.sql.Date is not incorrect.

I have faced the same error, and doing some research I found that the Node created by the Slick SQL Compiler is actually of type MappedJdbcType[Scala.Option -> LocalDate], and not Option[LocalDate].

That is the reason why when the mapping compiler create the column converter for your def perDate it is creating a Base ResultConverterand not a Option ResultConverter

Here is the Slick code for the base converter:

def base[T](ti: JdbcType[T], name: String, idx: Int) = (ti.scalaType match {
    case ScalaBaseType.byteType => new BaseResultConverter[Byte](ti.asInstanceOf[JdbcType[Byte]], name, idx)
    case ScalaBaseType.shortType => new BaseResultConverter[Short](ti.asInstanceOf[JdbcType[Short]], name, idx)
    case ScalaBaseType.intType => new BaseResultConverter[Int](ti.asInstanceOf[JdbcType[Int]], name, idx)
    case ScalaBaseType.longType => new BaseResultConverter[Long](ti.asInstanceOf[JdbcType[Long]], name, idx)
    case ScalaBaseType.charType => new BaseResultConverter[Char](ti.asInstanceOf[JdbcType[Char]], name, idx)
    case ScalaBaseType.floatType => new BaseResultConverter[Float](ti.asInstanceOf[JdbcType[Float]], name, idx)
    case ScalaBaseType.doubleType => new BaseResultConverter[Double](ti.asInstanceOf[JdbcType[Double]], name, idx)
    case ScalaBaseType.booleanType => new BaseResultConverter[Boolean](ti.asInstanceOf[JdbcType[Boolean]], name, idx)
    case _ => new BaseResultConverter[T](ti.asInstanceOf[JdbcType[T]], name, idx) {
      override def read(pr: ResultSet) = {
        val v = ti.getValue(pr, idx)
        if(v.asInstanceOf[AnyRef] eq null) throw new SlickException("Read NULL value ("+v+") for ResultSet column "+name)
        v
      }
    }
  }).asInstanceOf[ResultConverter[JdbcResultConverterDomain, T]]

Unfortunately I have no solution for this problem, what I suggest as a workaround, is to map your perDate property as follows:

import java.sql.Date
import java.time.LocalDate

class FormulaDB(tag: Tag) extends Table[Formula](tag, "formulas") {

  def sk = column[Int]("sk", O.PrimaryKey, O.AutoInc)
  def name = column[String]("name")
  def descrip = column[Option[String]]("descrip")
  def formula = column[Option[String]]("formula")
  def notes = column[Option[String]]("notes")
  def periodicity = column[Int]("periodicity")
  def perDate = column[Option[Date]]("per_date")

  def toLocalDate(time : Option[Date]) : Option[LocalDate] = time.map(t => t.toLocalDate))  
  def toSQLDate(localDate : Option[LocalDate]) : Option[Date] = localDate.map(localDate => Date.valueOf(localDate)))  


  private type FormulaEntityTupleType = (Int, String, Option[String], Option[String], Option[String], Int, Option[Date])

  private val formulaShapedValue = (sk, name, descrip, formula, notes, periodicity, perDate).shaped[FormulaEntityTupleType]

  private val toFormulaRow: (FormulaEntityTupleType => Formula) = { formulaTuple => {
      Formula(formulaTuple._1, formulaTuple._2, formulaTuple._3, formulaTuple._4, formulaTuple._5, formulaTuple._6, toLocalDate(formulaTuple._7))
     }
  }

  private val toFormulaTuple: (Formula => Option[FormulaEntityTupleType]) = { formulaRow =>
   Some((formulaRow.sk, formulaRow.name, formulaRow.descrip, formulaRow.formula, formulaRow.notes, formulaRow.periodicity, toSQLDate(formulaRow.perDate)))
  }

  def * = formulaShapedValue <> (toFormulaRow, toFormulaTuple)

Hopefully the answer comes not too late.

Answer 2

I'm pretty sure the problem is that your'e returning null from your mapping function instead of None.

Try rewriting your mapping function as a function from LocalDate to Date:

implicit val localDateColumnType = MappedColumnType.base[LocalDate, Date](
  {
    localDate => Date.valueOf(localDate)
  },{
    sqlDate => sqlDate.toLocalDate
  }
)

Alternately, mapping from Option[LocalDate] to Option[Date] should work:

implicit val localDateColumnType =
  MappedColumnType.base[Option[LocalDate], Option[Date]](
    {
      localDateOption => localDateOption.map(Date.valueOf)
    },{
      sqlDateOption => sqlDateOption.map(_.toLocalDate)
    }
  )