scalaplayframeworkslickslick-3.0

Custom LocalDateTime parser in Slick 3


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?


Solution

  • 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$"
    }