Scala Slick configuration for Postgres Slaves/Read Replicas

Question

I am trying to connect my Scala application to a Postgres cluster consisting of one master node and 3 slaves/read replicas. My application.conf looks like this today:

slick {
  dbs {
    default {
      driver = "com.company.division.db.ExtendedPgDriver$"
      db {
        driver = "org.postgresql.Driver"
        url = "jdbc:postgresql://"${?DB_ADDR}":"${?DB_PORT}"/"${?DB_NAME}
        user = ${?DB_USERNAME}
        password = ${?DB_PASSWORD}
      }
    }
  }
}

Based on Postgres' documentation, I can define the master and slaves all in one JDBC URL, which will give me some failover capabilities, like this:

jdbc:postgresql://host1:port1,host2:port2/database

However, if I want to separate my connections by read and write capabilities, I have to define two JDBC URls, like this:

jdbc:postgresql://node1,node2,node3/database?targetServerType=master
jdbc:postgresql://node1,node2,node3/database?targetServerType=preferSlave&loadBalanceHosts=true

How can I define two JDBC URLs within Slick? Should I define two separate entities under slick.dbs, or can my slick.dbs.default.db entity have multiple multiple URLs defined?

Answer 1

Found an answer from Daniel Westheide's blog post. To summarize, it can be done with a DB wrapper class and custom Effect types that provides specific rules to control where read-only queries are directed vs. write queries are directed.

Then your slick file would look like this:

slick {
  dbs {
    default {
      driver = "com.yourdomain.db.ExtendedPgDriver$"
      db {
        driver = "org.postgresql.Driver"
        url = "jdbc:postgresql://"${?DB_PORT_5432_TCP_ADDR}":"${?DB_PORT_5432_TCP_PORT}"/"${?DB_NAME}
        user = ${?DB_USERNAME}
        password = ${?DB_PASSWORD}
      }
    }
    readonly {
      driver = "com.yourdomain.db.ExtendedPgDriver$"
      db {
        driver = "org.postgresql.Driver"
        url = ${DB_READ_REPLICA_URL}
        user = ${?DB_USERNAME}
        password = ${?DB_PASSWORD}
      }
    }
  }
}

And it's up to your DB wrapper class to route queries to either 'default' or 'readonly'