How to implement master/slave structure with squeryl and play framework

Question

I am running a play framework website that uses squeryl and mysql database. I need to use squeryl to run all read queries to the slave and all write queries to the master.

How can I achieve this? either via squeryl or via jdbc connector itself.

Many thanks,

Answer 1

I don't tend to use MySQL myself, but here's an idea:

Based on the documentation here, the MySQL JDBC driver will round robin amongst the slaves if the readOnly attribute is properly set on the Connnection. In order to retrieve and change the current Connection you'll want to use code like

transaction {
  val conn = Session.currentSession.connection
  conn.setReadOnly(true)
  //Your code here
}

Even better, you can create your own readOnlyTransaction method:

def readOnlyTransaction(f: => Unit) = {
  transaction {
    val conn = Session.currentSession.connection
    val orig = conn.getReadOnly()
    conn.setReadOnly(true)
    f
    conn.setReadOnly(orig)
  }
}

Then use it like:

readOnlyTransaction {
  //Your code here
}

You'll probably want to clean that up a bit so the default readOnly state is reset if an exception occurs, but you get the general idea.