Slick 3.1.0, Oracle 11g, and UUID columns

UUIDs to the rescue

I recently decided to change our primary key strategy from an auto-incrementing Long to UUID (aka globally unique IDs). Early on in a project, this should be a relatively simple refactoring (neither the software nor any data has been released yet). First of all and most importantly, UUIDs provide uniqueness (with a high probability) across multiple databases. Second, if you are sending numeric identifiers back in JSON and not serializing / deserializing them as strings, you may be in for a rude surprise. Plain old Javascript does not support integers. All numbers in Javascript are represented as 64-bit floating point numbers, so naturally not every integer can be represented. If that doesn’t send shivers down your spine, you're made of stronger stuff than me. There are libraries to deal with this, such as BigInteger.js. But I digress…

Slick and Primary Keys

Currently we are using Slick 3.1.0 for persistence and Oracle 11g as our RDBMS. Slick provides out of the box support for auto-incrementing primary keys (note the use of a sentinel value of -1 to mean unassigned / no ID). A table defined as follows:

case class Student(id: Long = -1, firstName: String, lastName: String)
class StudentTable(tag: Tag) extends Table[Student](tag, "STUDENT") {
   def id = column[Long]("ID", O.PrimaryKey, O.AutoInc)
   def firstName = column[String]("FIRST_NAME", O.SqlType("VARCHAR(100)"))
   def lastName = column[String]("LAST_NAME", O.SqlType("VARCHAR(100)"))
}
// The following code can be used to get the DDL generated by Slick (for debugging, etc.)
val students = TableQuery[StudentTable]
val createStatements = students.schema.createStatements.mkString(";\r\n")

will generate the following Oracle DDL if you use Slick to generate it:

create table "STUDENT" ("ID" NUMBER(19) NOT NULL PRIMARY KEY,"FIRST_NAME" VARCHAR(100) NOT NULL,"LAST_NAME" VARCHAR(100) NOT NULL);
create sequence "STUDENT__ID_seq" start with 1 increment by 1;
create or replace trigger "STUDENT__ID_trg" before
  insert on "STUDENT" referencing new as new for each row when (new."ID" is null) begin
  select "STUDENT__ID_seq".nextval INTO :new."ID" from sys.dual;
end;

In fact, Slick also supports java.util.UUID as a column type (of course via implicits, did you even have to ask?) but key generation is left up to you¹. While one could certainly use java.util.UUID.randomUUID() to generate a key on the server side, I prefer handling this in the database. We can still resort to triggers to do our dirty work:

create table "STUDENT" ("ID" RAW(32) NOT NULL PRIMARY KEY,"FIRST_NAME" VARCHAR(100) NOT NULL,"LAST_NAME" VARCHAR(100) NOT NULL);
create or replace trigger "STUDENT__ID_trg" before
  insert on "STUDENT" referencing new as new for each row when (new."ID" is null or new."ID" = '00000000000000000000000000000000') begin
  select SYS_GUID() into :new."ID" from sys.dual;
end;

Note the column type is now RAW(32). So what that’s business about also checking if the id equals ‘00000000000000000000000000000000’? In order to represent an unassigned ID for new entities (haven’t been persisted yet), we can use a "nil" UUID where the most and least significant 8 bytes are zeroed out. This is safe because its not a UUID that would ever be generated. In Oracle this is represented as ‘00000000000000000000000000000000’ (no dashes). So why would you do this? In order to avoid using null in Scala:

object Entity {
  val UnassignedID = new java.util.UUID(0,0)
}
// Domain class
case class Student(id: UUID = Entity.UnassignedID, firstName: String, lastName: String)
// Saving the record
studentRepository.insert(Student(firstName = "Bobby", lastName = "Tables"))

I like this because the meaning of “unassigned” is now very clear in the code and type safe. When saving a record to the database, if either the id is `NULL` (to handle external clients writing directly to the database that would be inserting a `NULL`) or the “nil” UUID, a new id is generated. After testing this out for a single entity and verifying², I refactored the rest of the codebase.

java.sql.SQLException: Invalid column type 1111

I redeployed the application and hit a REST endpoint that sets up some fake data via our services³. Unfortunately the operation failed with an exception: java.sql.SQLException: Invalid column type 1111. I eventually tracked down the issue to a column defined as column[Option[UUID]] (a nullable foreign key to another table). Whenever an entity with such a column set to None (NULL in the database) was being saved, it would blow up. Ugh… Really?

Column type 1111 is the value assigned to java.sql.Types.OTHER. Slick was obviously using this type for UUID columns, but better to check their code to see what's happening. Slick provides database specific extensions of slick.jdbc.JdbcDriver, in this case com.typesafe.slick.driver.oracle.OracleDriver. Unfortunately in Slick 3.1.x and earlier, this is part of the closed source slick-extensions so I couldn't look at the Oracle specific code. Luckly for the next version of Slick, this has been open sourced and contributed back to Slick core, so I could look at it on the master branch.

Digging into com.typesafe.slick.driver.oracle.OracleProfile (now called "profiles") revealed the driver was using the default SQL type of java.sql.Types.OTHER for UUID columns as defined in UUIDJdbcType in slick.jdbc.JdbcTypesComponent. So what should it be? Looking at other Slick driver implementations, for example MySQLDriver, shows that the UUIDJdbcType.sqlType is being overriden and set to java.sql.BINARY. It turns out this is also the appropriate type to use for Oracle UUID (RAW(32), RAW(16)) columns.

Yuck, but open source for the win

You're not going to like the solution. For customizing Slick drivers, there's no other option than invasive inheritance 🙁 I ended up having to extend the OracleDriver and override the definition of UUIDJdbcType, like so:

trait CustomOracleDriver extends OracleDriver {
  override val columnTypes = new JdbcTypes
  class JdbcTypes extends super.JdbcTypes {
    override val uuidJdbcType = new UUIDJdbcType
    class UUIDJdbcType extends super.UUIDJdbcType {
      override def sqlType = java.sql.Types.BINARY
    }
  }
}
object CustomOracleDriver extends CustomOracleDriver

After adding this, I modified the slick.db.default.driver property to point to this custom implementation. I redeployed and this time the fake data setup ran to completion. I was relieved but did no dance of joy.

This is less than ideal because its a maintenance burden as changes are made to the Slick framework. I ended up submitting an issue on Github and it was responded to the very same day. A complete fix has been implemented across multiple drivers and may be included in 3.1.x or 3.2. Eventually CustomOracleDriver can go away!

Our stack

• Scala 2.11.7 (2.11.8 just released as of this writing)
• Play 2.4.6 (Play 2.5 just released as of this writing)
• Akka 2.4.1
• Slick 3.1.0
• Apache Spark 1.6
• jsblocks 0.3.3