Custom LocalDateTime parser in Slick 3

Question

I'm converting a bunch of java.sql.Timestamp columns from my Slick 3 models into LocalDateTime. My database backend is MySQL 8 and the columns I'm converting are either TIMESTAMP or DATETIME.

I ran into issues with MySQL returning dates in format yyyy-MM-dd HH:mm:ss, while LocalDateTime.parse expects yyyy-MM-dd'T'HH:mm:ss. This results in runtime errors such as java.time.format.DateTimeParseException: Text '2022-12-05 08:01:08' could not be parsed at index 10.

It found that it could be solved by using a custom formatter, like this:

private val formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss")
val localDateTimeMapper: BaseColumnType[LocalDateTime] = MappedJdbcType.base[LocalDateTime, String](
    ldt => ldt.format(formatter),
    s => LocalDateTime.parse(s, formatter)
  )

Normally I would define the formatter as implicit, but it creates a compile error in the model: No implicits found for parameter tt: TypedType[LocalDateTime]. Applying the formatter explicitly works wonderful for column[LocalDateTime], but does not work for column[Option[LocalDateTime]] (causes Type mismatch, required TypedType[Option[LocalDateTime]]).

class Users(tag: Tag) extends Table[User](tag, "users") {
  def uuid           = column[UUID]("uuid", O.PrimaryKey)
  def name           = column[String]("name")
  def email          = column[String]("email")
  def lastSignedInAt = column[Option[LocalDateTime]]("last_signed_in_at")(localDateTimeMapper)
  def createdAt      = column[LocalDateTime]("created_at")(localDateTimeMapper)

  override def * = (uuid, name, email, lastSignedInAt, createdAt) <> (User.tupled, User.unapply)
}

Other custom types (such as enums) works without issues using the implicit formatter approach, but I suspect the issue here is that Slick has a LocalDateTime-mapper that I'm trying to override. From what I can tell Slick wants LocalDateTime objects to be stored as VARCHAR rather than date types, but I don't want to convert the database columns.

Any advise on how I can make my custom formatter work (or use built in functionality in Slick) to allow LocalDateTime to work with MySQL's date types?

Answer 1

I eventually found a way that works by extending Slick's MySQLProfile:

package lib

import slick.jdbc.JdbcProfile

import java.sql.PreparedStatement
import java.sql.ResultSet
import java.time.LocalDateTime
import java.time.format.DateTimeFormatter

trait ExMySQLProfile extends JdbcProfile with slick.jdbc.MySQLProfile { driver =>
  private val localDateTimeFormatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss")

  override val columnTypes = new ExJdbcTypes

  class ExJdbcTypes extends super.JdbcTypes {

    @inline
    private[this] def stringToMySqlString(value: String): String = {
      value match {
        case null => "NULL"
        case _ =>
          val sb = new StringBuilder
          sb.append('\'')
          for (c <- value) c match {
            case '\'' => sb.append("\\'")
            case '"'  => sb.append("\\\"")
            case 0    => sb.append("\\0")
            case 26   => sb.append("\\Z")
            case '\b' => sb.append("\\b")
            case '\n' => sb.append("\\n")
            case '\r' => sb.append("\\r")
            case '\t' => sb.append("\\t")
            case '\\' => sb.append("\\\\")
            case _    => sb.append(c)
          }
          sb.append('\'')
          sb.toString
      }
    }

    /**
      * Override LocalDateTime handler, to parse values as we expect them.
      *
      * The default implementation in Slick does not support TIMESTAMP or DATETIME
      * columns, but expects timestamps to be stored as VARCHAR
      */
    override val localDateTimeType: LocalDateTimeJdbcType = new LocalDateTimeJdbcType {
      override def sqlType: Int = java.sql.Types.TIMESTAMP
      override def setValue(v: LocalDateTime, p: PreparedStatement, idx: Int): Unit = {
        p.setString(idx, if (v == null) null else v.toString)
      }
      override def getValue(r: ResultSet, idx: Int): LocalDateTime = {
        r.getString(idx) match {
          case null          => null
          case iso8601String => LocalDateTime.parse(iso8601String, localDateTimeFormatter)
        }
      }
      override def updateValue(v: LocalDateTime, r: ResultSet, idx: Int) = {
        r.updateString(idx, if (v == null) null else v.format(localDateTimeFormatter))
      }
      override def valueToSQLLiteral(value: LocalDateTime): String = {
        stringToMySqlString(value.format(localDateTimeFormatter))
      }
    }

  }
}

trait MySQLProfile extends ExMySQLProfile {}

object MySQLProfile extends MySQLProfile

In my application.conf I've configured the profile with:

slick.dbs.default {
  profile = "lib.MySQLProfile$"
}