Sending writes to the mysql master and reads to slave in slick

Question

With Slick and a Master/Slave set up with MySQL, how do I ensure writes (INSERT, UPDATE, etc) are sent to the master and reads (SELECT) are send to the slaves?

Disrelish

Answer 1

According to the MySQL docs on this, we need to set Connection#setReadOnly(true|false).

A nice way to do this in slick is to add the following functions to your database code:

/**
 * Runs a block of read only database code. No transaction required.
 */
def readOnly[T](f: => T) = db withSession {
  Database.threadLocalSession.conn.setReadOnly(true)
  f
}

/**
 * Runs a block of read/write database code in a transaction. 
 * Any exceptions will rollback any writes.
 */
def readWrite[T](f: => T) = db withTransaction {
  Database.threadLocalSession.conn.setReadOnly(false)
  f
}

Then you can write queries like so:

/**
 * Goes to slave
 */
def findUser(id: String) = readOnly {
  sql"SELECT ... FROM user WHERE id = $id".as[User].firstOption
}

/**
 * Goes to master
 */
def createUser(id: String) = readWrite {
  sqlu"INSERT INTO user VALUES(...)".execute
}

Disrelish

In case you get "java.sql.SQLException: No database selected" when running "Database.threadLocalSession.conn.setReadOnly(false)", this got fixed for me by upgrading to MySQL Connector 5.1.32 – Woodprint