Slick and Unit Testing

[Chris DaMour]

As a forward, we are new to scala & slick and the end result of this question might just be "that's just not how you do it" but we'd appreciate some considered thought before giving us that answer.  thx

We're building a standard CRUD application with an SQL RDBMS as the persistence mechanism and PLAY as the MVC based UI.  Pretty standard stuff, system has suppliers, suppliers have widgets, widgets have availability.  We have 3 Table class derivatives for these 3 DB tables (Widgets, Availability, Suppliers).  One app route maps to a controller action that displays all suppliers, another all widgets for a supplier, and yet another that shows all widgets with the supplier info that is available today.

We like the for comprehension syntax in our controllers to build up the model that we hand off to our views.  This works very well for us when the app is running.

Where we are lost is how to Mock the results of a slick query in our unit tests.  All we are looking to do is given a SLICK query mock the result.  IE if the controller's body has something like:

 for( w <- Widgets; if w.SupplierID === someId ) yield w )

We're looking to mock the output of the comprehension so that, if say it was converted to a list, it would only contain what our mock scenario produces (maybe it's 1 entry, maybe it's 3 entries, maybe it's no entries)

A lot of the examples I see tell me to put that code in a DAO method, IE something named getWidgetsBySupplierId( someId:int) = {...} and then just mock that DAO interface/trait.  This is NOT what we want to do (we don't want to create a DAO method for every different way we query the system, we would like to use the for comprehension directly in our controllers).  We're not looking for a debate about this preference or a convincing argument as to why we should absolutely use the DAO pattern please.

One solution we've seen in the wild is to use an in memory database like H2 and populate it with the data sets we desire prior to the tests.  This works, but it adds 1 layer that we'd like to eliminate if possible.

We can see 2 possible solutions to this problem (there may be more, please share if so):

1. Make the Widgets an injectable dependency and Mock the pieces that slick requires so our arbitrary results are used

2. Create a Mock Slick Driver (kind of like the Junit Selenium driver) that allows for arbitrary results given a slick expression

Has anyone gone down either of these paths (or something else to achieve the goal) and if so can you give some examples or pointers? Or are people generally just populating an in memory database to get this feature set?

thanks for any insight!

[Christopher Vogt]

I don't think a Mock Slick Driver exists yet. You could consider writing
it. It should not be too hard, but may be a challenge being new to
Scala. Here is info to help:

We do have an in-memory driver in 2.0 that works on collections. It is
not very well tested in practice, but that's irrelevant here. You could
just copy and paste it as a foundation and change the code to make it a
mock-driver.

The code is here
https://github.com/slick/slick/blob/master/src/main/scala/scala/slick/memory/MemoryProfile.scala

You will have to build your own executors and invokers to return mock
data instead of execution results. We are in the process of migrating
from the invoker model to the executor model, the former of which may be
fully replaced by the latter some time next year.

Executor and invoker follow the virtual class pattern. (The theory of it
is explained here if needed:
http://lampwww.epfl.ch/~odersky/papers/ScalableComponent.html)

The gist is (just as it is done in MemoryProfile/MemoryDriver):

1. Subclass the QueryExecutorDef of the super-class and override methods
as desired (replacing them by your mock logic).
2. Implement the factory method used by Slick to create one when needed.
def createQueryExecutor
3. "Tie the knot" on the virtual class by defining the corresponding
abstract type as your customized subclass:
type QueryExecutor[R] = QueryExecutorDef[R]

This way you get .run to work and mockable. For now we only have
InsertInvoker in MemoryProfile. No update support and for the (soon to
be deprecated) .list method. We may lift that restriction in the not so
far future, but no promises. We also accept PRs. Though expect strong
opinions and quite some discussion on this one as it is a core component
of Slick.

This is all 2.0, but using 2.0 is a good decision anyways if you are
just starting out with the project. Then you don't need to migrate very
soon. Updated documentation will be released before Christmas.

If you are going to try this, please consider open sourcing it early as
you go. This will be useful for other people and that way you can get
feedback from us or others. Also on your Scala code since you are rather
new.

Good luck :),

Chris

[Chris DaMour]

So you are suggesting route #2, is this because route #1 is not possible or just significantly harder?

Also, can you give some background on the genesis/purpose of the in-memory driver?  Is the idea that you don't need a DB at all?  Any examples of using it available?

[Brian Schlining]

Also, can you give some background on the genesis/purpose of the in-memory driver?  Is the idea that you don't need a DB at all?  Any examples of using it available?

Sorry, I wasn’t paying attention to most of this thread. But I do a lot of work with in-memory databases so I’d thought I’d chime in. You can use either h2 or Apache Derby for in-memory only databases. If you’re not already, you should refactor your code to use dependency injection of the Cake-pattern to mix in your database parameters. Here’s some example code below, hope it helps! (Sorry, slick 1 code below) :

//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

import scala.slick.driver.ExtendedProfile

import scala.slick.session.Database

// — 1. Base trait that stores a databases connection info

trait DatabaseProfile {

  val profile: ExtendedProfile

  def database: Database

}

// — 2. define the tables inside DatabaseComponents trait

trait DatabaseComponents { this: DatabaseProfile =>

  import profile.simple._

 

  // Define tables

  object FooBars extends Table[FooBar](…) {

// your slick code for defining a table

  }

  object OtherData extends Table[OtherDatum](…) {

        // more slick code

  }

}

import slick.session.Database

import slick.driver.{ SQLServerDriver, ExtendedProfile }

// — 3. Define a production profile

SQLServerProfile extends DatabaseProfile {

  val profile: ExtendedProfile = SQLServerDriver

  def database: Database = Database.forURL("jdbc:jtds:sqlserver://some.database.org:1433/MyDatabase",

    driver = "net.sourceforge.jtds.jdbc.Driver",

    user = “DB_User",

    password = "Password")

}

// — 4. Define an in-memory database profile (or two)

import slick.driver.DerbyDriver

trait DerbyProfile extends DatabaseProfile {

  val profile: ExtendedProfile = DerbyDriver

  lazy val database: Database = Database.forURL(s"jdbc:derby:memory:$name;create=true”, 

    driver = "org.apache.derby.jdbc.ClientDriver")

}

import slick.driver.H2Driver

trait H2Profile extends DatabaseProfile {

  val profile: ExtendedProfile = H2Driver

  lazy val database: Database = Database.forURL(s"jdbc:h2:mem:$name;DB_CLOSE_DELAY=-1”, 

    driver = "org.h2.Driver")

}

// The objects below allow us to access 3 different databases using the same table

// definitions. (defined in DatabaseComponents)

object ProductionComponents extends DatabaseComponents with SQLServerProfile

object DerbyTestComponents extends DatabaseComponents with DerbyProfile

object H2TestComponents extends DatabaseComponents with H2Profile

Brian

[Christopher Vogt]

> So you are suggesting route #2, is this because route #1 is not possible
> or just significantly harder?

I didn't completely get what you imagined as #1 exactly. Are you
thinking about replacing the Widgets Query object by a mocked one? That
does not sound helpful. It ends up as a purely information-carrying node
in Slick's abstract syntax tree. It does not contain the logic doing the
queries. That is done externally by the query compiler, respectively the
executor and invoker. So if you want to influence what happens when you
query you need to mock the driver, not the Widgets object.

Option #3: move all queries and withSession/Transaction calls out of
your Controller into a DAO and mock that one. Requires boiler plate but
is clean and simple.

Option #2 would be ore generic and helpful for everyone..

> Also, can you give some background on the genesis/purpose of the
> in-memory driver? Is the idea that you don't need a DB at all? Any
> examples of using it available?

We run all tests extending TestkitTest[RelationalTestDB] in
https://github.com/slick/slick/tree/master/slick-testkit/src/main
against all drivers including the in-memory one. The use case for the
driver are distributed queries: You can experimentally join tables from
different databases and engines in Slick 2.0. Slick splits the query
into the parts that can be run in each db/engine, runs them
independently and does the rest locally using the in-memory driver.

Tests are here, querying a Derby and H2 database together
https://github.com/slick/slick/blob/9dae36d3c70c3dad4037eaa9af60bd36f7c7cb1f/slick-testkit/src/test/scala/scala/slick/test/memory/DistributedQueryingTest.scala

Chris

[Chris DaMour]

Yes that was the thought for #1, and now i understand why it would not be helpful, thanks!

Option #3 is what I'm hoping to avoid, but it does seem pretty prevalent in the community at large.

Thanks for the explanation on what the in memory driver is used for, that is a pretty awesome use case (admittedly with potential for negative performance) reminds me of the IBM MashupHub product offering.

Looking forward to the 2.0 docs being published.

We will weigh going to the mock driver route vs just populating an in memory database, will share any output.

[Christopher Vogt]

> Thanks for the explanation on what the in memory driver is used for,
> that is a pretty awesome use case (admittedly with potential for
> negative performance) reminds me of the IBM MashupHub product offering.

Performance is bad if a lot of data needs to be loaded into memory. For
the future you can imagine user-exposed hints system or a profiler to
decide where the join should happen, locally or in one of the involved
backends, to minimize data transfer.

> We will weigh going to the mock driver route vs just populating an in
> memory database, will share any output.

Please do!

Chris