How to use UUID in VARCHAR column using 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 have to deal with the problem of converting UUID to String. This is the definition of my column:

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 suggests 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 , the results become empty. Does anyone know how I can force Slick to use VARCHAR instead of BINARY (16) to convert the data?

EDIT:

With an implicit query, this query does not return any results:

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

But it does:

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

EDIT2:

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

Answer 1

Do you know about empty values ​​coming from db? I just checked my implementation (in one of the projects, exactly the same solution) and it seems to work fine. Make sure that your physical column (in the database) is actually of type VARCHAR (and not some binary file).

EDIT: Well, the problem is that defining a UUID as BINARY already included in the MySQL profile. A quick (though maybe a little dirty) rethinking it globally would be as follows:

 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 the usual api :

 import profile.CustomMySqlProfile.api._ 

This definitely works - but keep in mind that now the UUID will be displayed in a string throughout your application (instead of the default binary). Basically, I needed to redefine the profile just because the UUID conversion is built-in. You should usually use type mappings.

A similar problem was solved in a similar way: https://github.com/slick/slick/issues/1446 (also UUID, but for the Oracle driver).