How to use UUID in a VARCHAR column with Slick?

Question

I have a MySQL 5.7 database with several tables that store UUIDs in columns with a VARCHAR data type. I am trying to convert our code base to use Slick 3.1.1, but I am facing a problem with UUID to String conversions. This is my column definition:

def myCol: Rep[UUID] = column[UUID]("myCol", SqlType("VARCHAR"))

When I run queries I get exceptions like this:

Got the exception java.sql.SQLException: Incorrect string value: '\xDA\xFD\xDAuOL...' for column 'myCol' at row 1

From what I understand, Slick assumes that UUIDs should be stored as binary column types so I tried to implicitly convert to String using:

implicit val uuidToString = MappedColumnType.base[UUID, String](_.toString, UUID.fromString)
def myCol: Rep[UUID] = column[UUID]("myCol", SqlType("VARCHAR"))(uuidToString)

This works for insertion, but when I select values with myCol results come empty. Anyone knows how can I force Slick to use VARCHAR instead of BINARY(16) for the data conversion?

EDIT:

With the implicit this query does not return any results:

db.run { table.filter(_.myCol === val)).result }

But this one does:

db.run { table.result }.map(_.filter(_.myCol == val))

EDIT2:

I have a small project with a demo here: https://github.com/nmatpt/slick-uuid-test

Answer 1

You sure about empty values coming from db? I just checked my implementation (in one of the projects, exactly same solution) and it seems to work perfectly. Make sure your physical column (in DB) is actually of type VARCHAR (and not some binary).

EDIT: OK, the problem that we have is that UUID definition as BINARY is already included in MySQL profile. Quick (though maybe a little dirty) was of redefining it globally would be like this:

package profile

import java.sql.{PreparedStatement, ResultSet}

import slick.ast._
import slick.jdbc.MySQLProfile

object CustomMySqlProfile  extends MySQLProfile {
  import java.util.UUID

  override val columnTypes = new JdbcTypes

  class JdbcTypes extends super.JdbcTypes {
    override val uuidJdbcType = new UUIDJdbcType {
      override def sqlTypeName(sym: Option[FieldSymbol]) = "UUID"
      override def valueToSQLLiteral(value: UUID) = "'" + value + "'"
      override def hasLiteralForm = true

      override def setValue(v: UUID, p: PreparedStatement, idx: Int) = p.setString(idx, toString(v))
      override def getValue(r: ResultSet, idx: Int) = fromString(r.getString(idx))
      override def updateValue(v: UUID, r: ResultSet, idx: Int) = r.updateString(idx, toString(v))

      private def toString(uuid: UUID) = if(uuid != null) uuid.toString else null
      private def fromString(uuidString: String) = if(uuidString != null) UUID.fromString(uuidString) else null
    }
  }
}

And then importing it instead of regular api:

import profile.CustomMySqlProfile.api._

This definitely works - but keep in mind that now UUID would be mapped to string throughout your whole application (instead of default binary). Basically I needed to override profile only because UUID conversion is built in. Normally you would use type mappings.

Similar thing got solved in a similar way here: https://github.com/slick/slick/issues/1446 (also UUID but for Oracle driver).

Answer 2

implicit val uuidToString = MappedColumnType.base[UUID, String](_.toString, UUID.fromString)

@QP, this also works fine for selects but its essential that this implicit conversion is in scope everywhere you construct queries for VARCHAR columns containing "UUID". Otherwise select with WHERE conditions will never match and return empty results.