Mapping a class as a column type within a class

Question

I understand how this is done when using types such as Long, Int, String etc.. But say I have a class that has fields within another class like so:

case class Foo(a:String, b:String)
case class Bar(foo:Option[Foo], c:String)

How would I set up a mapper for my custom type (the Foo in my Bar class)?

class Bars(tag:Tag) extends Table[Bar](tag, "BARS") {
  def foo = column[Foo]("FOO") // <- won't work
  def c = column[String]("C")

  def * = (foo, c) <> (Bar.tupled, Bar.unapply)
}

(documentation link)

Update:

DB Driver: slick.driver.PostgresDriver

Slick 2

I'm guessing the raw SQL would look like this:

"BARS" (
  "A" VARCHAR(254) NOT NULL,
  "B" VARCHAR(254) NOT NULL, 
  "C" VARCHAR(254) NOT NULL
);

Should be able to call Bar like so:

val bar = Bar(Foo("1", "2"), "3")
barTable.insert(bar)
bar.foo.a // 1
bar.foo.b // 2
bar.c // 3

What is the target database, and how should this class be represented there, in your opinion? — Ashalynd, Jul 22 '14 at 22:28

Ashalynd · Accepted Answer · 2014-07-23T08:41:40.303

You can write a mapper between the case class and some type that can be stored in the database.

See an example from Slick here:http://slick.typesafe.com/doc/1.0.0/lifted-embedding.html, at the end of the page.

One easy way in your case might be to transform your case class into json and store as a string. (And if your DB supports json type directly, like PostgreSQL, you can specify JSON type in column mapper, that would give you an advantage when making queries related to the content of your case classes.)

import org.json4s._
import org.json4s.native.Serialization
import org.json4s.native.Serialization.{read, write}

//place this in scope of your table definition
implicit val FooTypeMapper = MappedTypeMapper.base[Foo, String](
  { f => write(f) },    // map Foo to String
  { s => read[Too](s) } // map String to Foo
)

class Bars(tag:Tag) extends Table[Bar](tag, "BARS") {
  def foo = column[Foo]("FOO") // <- should work now
  def c = column[String]("C")

  def * = (foo, c) <> (Bar.tupled, Bar.unapply)
}

With PostgreSQL >=9.3, you can also write:

def foo = column[Foo]("FOO", O.DBType("json"))

So that DB treats your json properly.

UPDATE: there is a connection property that should be set if send a String for a JSON field. Something like this:

  val prop = new java.util.Properties
  prop.setProperty("stringtype", "unspecified")

  val db = Database.forURL(<db-uri>,
                           driver="org.postgresql.Driver",
                           user=<username>,
                           password=<password>,
                           prop=prop)

Hmm... I didn't want to use json (see raw sql description above) but I might consider it. Do you know how to implement this without json? (mapping `foo.a` and `foo.b` to their own string columns)? [similar post](http://blog.lunatech.com/2013/11/21/slick-case-classes) but it's for slick 1 — goo, Jul 22 '14 at 23:04
I actually kind of like your way of doing it. Any pros/cons as far as performance goes when doing it this way? I'm assuming when querying, pg will actually convert each `foo` column from json which is a bit slower, am I correct? Also, you're actually using `String` instead of json, will that make a difference? — goo, Jul 22 '14 at 23:51
not really, except that you might need to add a parameter when you establish a connection (will add it to the answer). Postgres 9.3 and later can handle JSON (i.e. you can do queries on parts of JSON field), and Postgres 9.4 even has more optimized jsonb format which makes later processing faster (though I haven't been investigating this much yet). — Ashalynd, Jul 23 '14 at 08:38

score 1 · Answer 2 · answered Aug 10 '15 at 11:00

You need to provide columns for A and B in the Bars table:

class Bars(tag:Tag) extends Table[Bar](tag, "BARS") {
  def a = column[String]("A")
  def b = column[String]("B")
  def foo = (a, b) <> (Foo.tupled, Foo.unapply)

  def c = column[String]("C")

  def * = (foo, c) <> (Bar.tupled, Bar.unapply)
}

Mapping a class as a column type within a class

2 Answers2