Parameterized queries

Since most database-driven applications will need to use user-supplied data as parameters to their queries, finagle-postgres supports parameterized queries through its prepared statement interface:

import com.twitter.finagle.Postgres
import com.twitter.finagle.postgres.Param._
import com.twitter.finagle.postgres.values.ValueEncoder._

import com.twitter.util.Await

// create the client based on environment variables
val client = {
  Postgres.Client()
    .withCredentials(sys.env("PG_USER"), sys.env.get("PG_PASSWORD"))
    .database(sys.env("PG_DBNAME"))
    .withSessionPool.maxSize(1)
    .withBinaryResults(true)
    .withBinaryParams(true)
    .newRichClient(sys.env("PG_HOST_PORT"))
}    

// execute a query that has no results - i.e. CREATE TABLE, UPDATE, INSERT, DELETE, etc.
val create = Await.result {
  client.prepareAndExecute("CREATE TABLE demo(id serial PRIMARY KEY, foo text)")
}

val insert = Await.result {
  client.prepareAndExecute("INSERT INTO demo(foo) VALUES ($1)", "foo": String)
}

// execute a query that has results - a function is given to treat the rows
val select = client.prepareAndQuery("SELECT * FROM demo WHERE foo = $1", "foo": String) {
  row => row.get[String]("foo")
}

Await.result(select)

Here, we used prepareAndExecute and prepareAndQuery rather than just execute and query. These methods take any number of parameters in addition to the SQL query. The given parameters are encoded and sent separately from the query, which means they won't be a potential vector for SQL injection attacks.

Next, read about Automatic case class marshalling