Anthony Honstain - Dev Notes

Scalatra 2.6.4 with Slick and PostgreSQL

Scala Feb 9, 2019

Overview

Building on the last several posts (creating a Scalatra service and supporting REST), we would now like to add support for a database. I have chosen to use PostgreSQL for this guide.

Versions being used in this guide:

Assumptions:

  • You already have a basic Scalatra service started.

Details

You can start with the official Scalatra guide for integrating with a persistence framework and the manual for Slick:

I wanted to start with Slick based on the positive things I had heard about it from my peers more knowledgeable in Scala. However I wanted to start with PostgreSQL instead of H2 (as I was interested in being able to do some naive bench-marking and eventually run the service in Heroku).

Getting Your Database Running

If you prefer to run Docker or have an alternative approach, feel free to skip this section.

This may not be the right path for you, this is optimized for local development and direct administration of the DB. It is NOT SECURE, and NOT MEANT FOR PRODUCTION.

sudo -i -u postgres
> createuser --interactive
> createdb toyinventory
  • UPDATE TO GUIDE - I also ended up setting "local" to trust also so that I could easily get assess with psql from the command line
  • Restart PostgreSQL sudo service postgresql restart

You can connect and interact with the database via psql using the account you previously created. sudo -i -u toyinventory psql.

You can connect and interact with the database via psql using the account you previously created. psql -U toyinventory. This can be a helpful guide if your PSQL is rusty http://postgresguide.com/utilities/psql.html

You can then validate by accessing the database using IntelliJ, as I frequently prefer to execute queries and inspect the database from IntelliJ.

Creating Table and Records

You will want a simple table and some records to start working with, for this guide we will avoid going into schema management. First, create your table:

CREATE TABLE inventory
(
  id bigserial NOT NULL,
  sku text,
  qty integer, -- https://www.postgresql.org/docs/10/datatype-numeric.html
  description text,
  CONSTRAINT pk PRIMARY KEY (id)
)

A little off topic, but some interesting references if you are considering what column type to use for strings with PostgreSQL https://stackoverflow.com/questions/4848964/postgresql-difference-between-text-and-varchar-character-varying and https://www.depesz.com/2010/03/02/charx-vs-varcharx-vs-varchar-vs-text/

Insert some data to work with:

INSERT INTO inventory(sku, qty, description) VALUES 
('ZL101', 1, 'Black shoes'), 
('ZL102', 0, 'Red dress'), 
('ZL103', 4, 'Block of wood');

Adding the Necessary Scalatra Dependencies

Now that you have a running database, let's get Scalatra to talk to it.

If you started with the Slick documentation http://slick.lightbend.com/doc/3.3.0/gettingstarted.html you found it also used the H2 database. I found it difficult to follow (but that's probably my weakness more than anything).

I added the following dependencies to my projects build.sbt 

libraryDependencies ++= Seq(
  "com.typesafe.slick" %% "slick" % "3.3.0",
  "org.postgresql" % "postgresql" % "42.2.5", // org.postgresql.ds.PGSimpleDataSource dependency
)

Connecting to the Database

I want to see the code talk to the DB, so I ignored proper management of connections and dependencies to get things started.

I added the following imports to my Servlet that is responsible for the REST endpoints. NOTE - this was where I got tripped up trying to follow the other guides, there are a number of ways to use this library, and if you let IntelliJ handle the auto-import it is very likely that end up with a confusing mess.

import slick.jdbc.PostgresProfile.api._

import scala.concurrent.{Await, Future}
import scala.concurrent.duration.Duration

Now specific the code needed to make the database connection, I have opted to retrieve the postgres user and password from environment variables as opposed to checking them in directly to source code.

val postgres_user = sys.env("postgres_user")
val postgres_password = sys.env("postgres_password")
val connectionUrl = s"jdbc:postgresql://localhost:5432/toyinventory?user=${postgres_user}&password=${postgres_password}"

Now specific a class to model your database record.

class InventoryRecord(tag: Tag) extends
  Table[(Int, String, Int, String)](tag, "inventory") {

  def id = column[Int]("id")
  def sku = column[String]("sku")
  def qty = column[Int]("qty")
  def description = column[String]("description")

  def * = (id, sku, qty, description)
}

Now your ready to run a SELECT all query using Slick

val db = Database.forURL(connectionUrl, driver = "org.postgresql.Driver")

try {
  val users = TableQuery[InventoryRecord]
  val query = users.map(_.sku)
  val action = query.result
  val result: Future[Seq[String]] = db.run(action)
  val futureResult = Await.result(result, Duration.Inf)
  futureResult.map { sku => logger.debug(s"SKU: ${sku}") }
} finally db.close

Now that you have all of the pieces, I slammed that code into my GET endpoint that I defined in http://honstain.com/rest-in-a-scalatra-service/ and sent some HTTP requests.

Don't forget to set your environment variables if you opt to run via IntelliJ 

import org.scalatra._
import org.slf4j.LoggerFactory
// JSON-related libraries
import org.json4s.{DefaultFormats, Formats}
// JSON handling support from Scalatra
import org.scalatra.json._

import slick.jdbc.PostgresProfile.api._

import scala.concurrent.{Await, Future}
import scala.concurrent.duration.Duration
import scala.concurrent.ExecutionContext.Implicits.global

class ToyInventory extends ScalatraServlet with JacksonJsonSupport {

  val logger = LoggerFactory.getLogger(getClass)
  protected implicit val jsonFormats: Formats = DefaultFormats

  before() {
    contentType = formats("json")
  }

  get("/") {
    val postgres_user = sys.env("postgres_user")
    val postgres_password = sys.env("postgres_password")
    val connectionUrl = s"jdbc:postgresql://localhost:5432/toyinventory?user=${postgres_user}&password=${postgres_password}"

    val db = Database.forURL(connectionUrl, driver = "org.postgresql.Driver")

    try {
      val users = TableQuery[InventoryRecord]
      val query = users.map(_.sku)
      val action = query.result
      val result: Future[Seq[String]] = db.run(action)
      val futureResult = Await.result(result, Duration.Inf)
      futureResult.map { sku => logger.debug(s"SKU: ${sku}") }
    } finally db.close

    InventoryData.all
  }

  post("/") {
    val newInventory = parsedBody.extract[Inventory]
    logger.debug(s"Creating inventory sku:${newInventory.sku}")
    logger.debug("Creating inventory {}", newInventory.toString)
    InventoryData.all = newInventory :: InventoryData.all
    newInventory
  }

}

case class Inventory(sku: String, qty: Int, description: String)

object InventoryData {

  var all = List(
    Inventory("ZL101", 1, "Black shoes"),
    Inventory("ZL102", 0, "Red dress"),
    Inventory("ZL103", 4, "Block of wood"),
  )
}

class InventoryRecord(tag: Tag) extends
  Table[(Int, String, Int, String)](tag, "inventory") {

  def id = column[Int]("id")
  def sku = column[String]("sku")
  def qty = column[Int]("qty")
  def description = column[String]("description")

  def * = (id, sku, qty, description)
}

The result of this generated the following logs, Slick generated a significant amount of logging that I thought was very detailed (but would probably immediately trim down).

Summary

Now that you have a basic query working, you can start writing more advanced queries. You will also want to start managing your database initialization and connections more appropriately.

References I Found Helpful

Tags

Anthony Honstain

Recommended for you