Recently, I’ve been digging into Scala to understand how PostgreSQL would integrate inside of Scala code. Scala is an interesting language which runs on the JVM like Java but eliminates much of the Java boiler plate code increasing the productivity of developers. With many of the knowledgeable Java programmers advocating Scala and large success stories like Twitter becoming more common, there is a possibility that one day Scala will unseat Java and the dominate enterprise programming language in the future. Given this possibility, it is encouraging that PostgreSQL is respected in the Scala community, but unfortunately, what I’m finding, database persistence in general is still appears to be immature inside of Scala. There is an early module called DBC that attempts to provide database connectivity, but this initial attempt was lacking. Below is an example of running a simple query which I find very awkward:

object MainDBC {

  import scala.dbc._
  import scala.dbc.Syntax._
  import syntax.Statement._

  def main(args: Array[String]): Unit = {
    val db = database("jdbc:postgresql://localhost/scala","postgres","password")
    val res = db.executeStatement {
      (select fields (("pgbench_tellers.tid" of integer)
                  and ("pgbench_tellers.bid" of integer)
                  and ("pgbench_branches.bbalance" of integer))
         from "pgbench_tellers, pgbench_branches"
        where "pgbench_tellers.bid = pgbench_branches.bid")
    }

    println(" tid   bid   bbalance")
    println("----- ----- ----------")
    for(val i <- res) {
      for(val f <- i.fields) {
        val r = f.content.sqlString
        print(r + "      ".dropRight(r.length))
      }
      println()
    }
  }
}

There are a number of object layers available that may be much easier to use, but the nice thing about Scala running on the JVM is that you can leverage the low level JDBC interface and eliminate much of the awkwardness. Below is an example of the same query but using JDBC.

object MainJDBC {

  import java.sql.{Connection, DriverManager, ResultSet}

  def main(args: Array[String]): Unit = {
    classOf[org.postgresql.Driver]
    val db = DriverManager.getConnection("jdbc:postgresql://localhost/scala","postgres","password")
    val st = db.createStatement

    val res = st.executeQuery(
               "SELECT pgbench_tellers.tid, pgbench_tellers.bid, pgbench_branches.bbalance" +
               "  FROM pgbench_tellers, pgbench_branches " +
               " WHERE pgbench_tellers.bid = pgbench_branches.bid")

    println(" tid   bid   bbalance")
    println("----- ----- ----------")
    while (res.next) {
        for(val i <- 1 to res.getMetaData.getColumnCount) {
          val r = res.getInt(i).toString
          print(r + "      ".dropRight(r.length))
        }
        println
    }
    db.close
  }
}