Getting SQL Server to work with Play Framework

I’m using the jTDS driver with Play 2.5.8 (which uses HikariCP) and kept getting exceptions like the following when trying to create connection pools on startup.

[info] application - Creating Pool for datasource 'default'
[error] c.z.h.p.PoolBase - HikariPool-1 - Failed to execute isValid() for connection, configure connection test query. (null)
[info] application - Creating Pool for datasource 'default'
[error] c.z.h.p.PoolBase - HikariPool-2 - Failed to execute isValid() for connection, configure connection test query. (null)
[info] application - Creating Pool for datasource 'default'
[error] c.z.h.p.PoolBase - HikariPool-3 - Failed to execute isValid() for connection, configure connection test query. (null)
[info] application - Creating Pool for datasource 'default'
[error] c.z.h.p.PoolBase - HikariPool-4 - Failed to execute isValid() for connection, configure connection test query. (null)
[error] application - 

! @71ejcojkn - Internal server error, for (GET) [/] ->

play.api.Configuration$$anon$1: Configuration error[Cannot connect to database [default]]
    at play.api.Configuration$.configError(Configuration.scala:154)
    at play.api.Configuration.reportError(Configuration.scala:806)
    at play.api.db.DefaultDBApi$$anonfun$connect$1.apply(DefaultDBApi.scala:48)
    at play.api.db.DefaultDBApi$$anonfun$connect$1.apply(DefaultDBApi.scala:42)
    at scala.collection.immutable.List.foreach(List.scala:381)
    at play.api.db.DefaultDBApi.connect(DefaultDBApi.scala:42)
    at play.api.db.DBApiProvider.get$lzycompute(DBModule.scala:72)
    at play.api.db.DBApiProvider.get(DBModule.scala:62)
    at play.api.db.DBApiProvider.get(DBModule.scala:58)
    at com.google.inject.internal.ProviderInternalFactory.provision(ProviderInternalFactory.java:81)
Caused by: play.api.Configuration$$anon$1: Configuration error[Failed to initialize pool: null]
    at play.api.Configuration$.configError(Configuration.scala:154)
    at play.api.PlayConfig.reportError(Configuration.scala:996)
    at play.api.db.HikariCPConnectionPool.create(HikariCPModule.scala:70)
    at play.api.db.PooledDatabase.createDataSource(Databases.scala:199)
    at play.api.db.DefaultDatabase.dataSource$lzycompute(Databases.scala:123)
    at play.api.db.DefaultDatabase.dataSource(Databases.scala:121)
    at play.api.db.DefaultDatabase.getConnection(Databases.scala:142)
    at play.api.db.DefaultDatabase.getConnection(Databases.scala:138)
    at play.api.db.DefaultDBApi$$anonfun$connect$1.apply(DefaultDBApi.scala:44)
    at play.api.db.DefaultDBApi$$anonfun$connect$1.apply(DefaultDBApi.scala:42)
Caused by: com.zaxxer.hikari.pool.HikariPool$PoolInitializationException: Failed to initialize pool: null
    at com.zaxxer.hikari.pool.HikariPool.checkFailFast(HikariPool.java:512)
    at com.zaxxer.hikari.pool.HikariPool.<init>(HikariPool.java:105)
    at com.zaxxer.hikari.HikariDataSource.<init>(HikariDataSource.java:71)
    at play.api.db.HikariCPConnectionPool$$anonfun$1.apply(HikariCPModule.scala:58)
    at play.api.db.HikariCPConnectionPool$$anonfun$1.apply(HikariCPModule.scala:54)
    at scala.util.Try$.apply(Try.scala:192)
    at play.api.db.HikariCPConnectionPool.create(HikariCPModule.scala:54)
    at play.api.db.PooledDatabase.createDataSource(Databases.scala:199)
    at play.api.db.DefaultDatabase.dataSource$lzycompute(Databases.scala:123)
    at play.api.db.DefaultDatabase.dataSource(Databases.scala:121)
Caused by: java.lang.AbstractMethodError: null
    at net.sourceforge.jtds.jdbc.JtdsConnection.isValid(JtdsConnection.java:2812)
    at com.zaxxer.hikari.pool.PoolBase.checkDriverSupport(PoolBase.java:400)
    at com.zaxxer.hikari.pool.PoolBase.setupConnection(PoolBase.java:375)
    at com.zaxxer.hikari.pool.PoolBase.newConnection(PoolBase.java:346)
    at com.zaxxer.hikari.pool.HikariPool.checkFailFast(HikariPool.java:506)
    at com.zaxxer.hikari.pool.HikariPool.<init>(HikariPool.java:105)
    at com.zaxxer.hikari.HikariDataSource.<init>(HikariDataSource.java:71)
    at play.api.db.HikariCPConnectionPool$$anonfun$1.apply(HikariCPModule.scala:58)
    at play.api.db.HikariCPConnectionPool$$anonfun$1.apply(HikariCPModule.scala:54)
    at scala.util.Try$.apply(Try.scala:192)

Googling it didn’t yield much until I pasted more of the stacktrace than normal. Finally found this in the Hikari-CP mailing list, which I guess I probably should have figured out from the stacktrace itself. This stackoverflow answer is really good, too.

Essentially, they both say that the jTDS library doesn’t implement the isValid method that HikariCP relies on. So the fix is to manually set the validation query in the HikariCP settings. It’s pretty simple.

play.db {
  prototype {
    hikaricp.connectionTestQuery = "SELECT 1"
  }
}

And that’s the sauce. I’m also going to add some readonly settings + disable ebean evolutions because that’s what I’m doing for my project.

play.evolutions {
  db.default.enabled = false
}

db {
  default.hikaricp.readOnly = true
}