How to define Squeryl schemas for two different db in Play

Question

I'd like to add a second temporary DB (filebased H2) to our Play 2.1 app. But can't figure out how and where to create a Squeryl schema for the second DB.

The first schema is already defined in a scala class that extends org.squeryl.Schema. Where should I put table definitions for the second DB?

Thanks for any hints.

By digging around, I found a hint in the Squeryl Google group: https://groups.google.com/forum/#!topic/squeryl/kdUmp1-hOmA - I guess I need to create a different Schema class and override Schema.name attribute. Still not sure how to tell Squeryl which schema to use for what DB — alboko, Jan 19 '14 at 22:34

jcern · Accepted Answer · 2014-01-22T14:58:04.667

1

I've not used Play so you may need to tweak this around a bit to get it to work, but you should be able to create a new session for that database, something like:

object MySecondDBSchema extends SquerylSchema with SchemaDefaults {
  def newSession[A](f: => A) =  {
    transaction(new org.squeryl.Session(//get java.sql.Connection, new H2Adapter)) {
      f
    }
  }

  val myTable = table[MyTable]
}

or, if you just want a connection and plan to deal with transactions on your own.

def newSession[A](f: => A) =  {
  using(new org.squeryl.Session(//get java.sql.Connection, new H2Adapter)) {
    //start your own transaction
    f
  }
}

Then in your code, when you want to access that other database you can wrap your queries in that:

def myMethodToAccessFirstDB() = {
  import MySecondDBSchema._

  newSession{ 
    //access alternate database
    from(myTable)(t => select(t)).toList 
  }
}

def myMethodToAccessDefaultDB() = {
  import DefaultSchema._ //Or whatever your default Schema is named

  //access default database
  from(otherTable)(ot => select(ot))
}

It is logical to define and organize the tables in a second object that extends org.squeryl.Schema but any query made within the newSession block will be performed against the second database.

As for overriding the name attribute, that will change the default schema in the database but is not necessary for creating a separate Squeryl schema.

edited Jan 22 '14 at 14:58

answered Jan 20 '14 at 15:32

jcern

7,798
4
39
47

@jcem - thanks. My question was about creating a second schema and binding that schema to the second DB, not about the sessions. I sort of figured out the session part. In any case, is it a good practice to create a new Session for each H2 query? Should I better cache it somewhere (in Play Cache, for instance)? – alboko Jan 20 '14 at 18:42
Sorry, I may have understood. Do you want to create a second schema on the existing database - like: `db.schema1.tablename` and `db.schema2.tablename` or are you looking to use a second, separate database together with your existing one? If the latter, then what I posted would apply. If the former, then you were on the right track - you would just import both Squeryl Schema's and query against your table definitions (just watch out for namespace conflicts). The question seemed to mention two separate DBs, which was why I answered the way I did. – jcern Jan 20 '14 at 19:00
@jcem: the latter - a separate database along with existing one. The second database should have a completely different schema. And I still don't get how to tell Squeryl to use another `Schema` class for the second DB. – alboko Jan 20 '14 at 23:17
@Alex, I updated the example to provide a bit of a more complete example. Let me know if that makes more sense. – jcern Jan 22 '14 at 14:59
thanks! That's exactly I was looking for. Somehow I didn't get that I can use appropriate imports or fully qualified names for the tables. Answer accepted! – alboko Jan 22 '14 at 18:30

score 0 · Answer 2 · answered Apr 16 '15 at 07:04

In addition to jcern answer: don't forget to close connection. My variant:

object MySecondDBSchema extends Schema {

  var ds : DataSource = _

  def newSession[A](f: => A) = {
    var conn: Option[Connection] = None
    try {
      conn = Some(ds.getConnection)
      val proxyConn = conn.map {
        case a : ArraySupportProxyConnection => a
        case c : C3P0ProxyConnection => new ArraySupportProxyConnection(c)
      }.get
      using[A](new org.squeryl.Session(proxyConn, new AwesomePostgreSqlAdapter)) {
        inTransaction {
          f
        }
      }
    }
    finally {
      conn.foreach(_.close())
    }
  }

  ...
}

How to define Squeryl schemas for two different db in Play

2 Answers2