> ## Content Index
> Fetch the complete content index at: https://honstain.com/llms.txt
> Use this file to discover other available public pages before exploring further.

# Scalatra 2.6.4 with Slick and PostgreSQL
- URL: https://honstain.com/scalatra-2-6-4-postgresql/
- Published: 2019-02-09T21:43:37.000Z
- Updated: 2026-06-21T00:33:22.000Z
- Description: Adding support for PostgreSQL to a Scala Scalatra REST service.
- Author: Anthony Honstain
- Tags: Scala, Scalatra, PostgreSQL, #Import 2024-01-27 15:52

## Overview

Building on the last several posts (creating a [Scalatra service](https://honstain.com/scalatra-giter8/) and [supporting REST](https://honstain.com/rest-in-a-scalatra-service/)), 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:

- Scalatra version 2.6.4 [http://scalatra.org/](http://scalatra.org/?ref=honstain.com)
- Scala version 2.12.6 [https://www.scala-lang.org/](https://www.scala-lang.org/?ref=honstain.com)
- PostgreSQL 10.6 `PostgreSQL 10.6 (Ubuntu 10.6-0ubuntu0.18.10.1) on x86_64-pc-linux-gnu` installed locally on Ubuntu 18.10 using `sudo apt install postgresql` [https://www.postgresql.org/](https://www.postgresql.org/?ref=honstain.com)
- Ubuntu 18.10 [http://releases.ubuntu.com/18.10/](http://releases.ubuntu.com/18.10/?ref=honstain.com) (I tend to run this in VMware Workstation 15 Player for convenience)

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:

- [http://scalatra.org//guides/2.6/persistence/introduction.html](http://scalatra.org//guides/2.6/persistence/introduction.html?ref=honstain.com)
- [http://scalatra.org/guides/2.6/persistence/slick.html](http://scalatra.org/guides/2.6/persistence/slick.html?ref=honstain.com)

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**.

- Install PostgreSQL - I have opted to use the Ubuntu package `sudo apt install postgresql`. I found this resource helpful (DigitalOcean produces some very helpful guides) [https://www.digitalocean.com/community/tutorials/how-to-install-and-use-postgresql-on-ubuntu-18-04](https://www.digitalocean.com/community/tutorials/how-to-install-and-use-postgresql-on-ubuntu-18-04?ref=honstain.com)
- Create a user and a new DB

```bash
sudo -i -u postgres
> createuser --interactive
> createdb toyinventory

```

- Allow access to the local database via trust authentication (I am using this because I have a single-user workstation for development - PostgreSQL assumes anyone who can connect is authorized with whatever user they want). Some additional references if your interested [https://www.postgresql.org/docs/9.1/auth-pg-hba-conf.html](https://www.postgresql.org/docs/9.1/auth-pg-hba-conf.html?ref=honstain.com) and [https://www.postgresql.org/docs/9.1/auth-methods.html#AUTH-TRUST](https://www.postgresql.org/docs/9.1/auth-methods.html?ref=honstain.com#AUTH-TRUST)
- Use the editor of your choice to open your `pg_hba.conf` file. `sudo emacs /etc/postgresql/10/main/pg_hba.conf` and set the IPv4 and IPv6 to trust.

![](https://honstain.com/content/images/2019/02/image-10.png)

- 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](http://postgresguide.com/utilities/psql.html?ref=honstain.com)

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

![](https://honstain.com/content/images/2019/02/image-11.png)

### 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:

```sql
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](https://stackoverflow.com/questions/4848964/postgresql-difference-between-text-and-varchar-character-varying?ref=honstain.com) and [https://www.depesz.com/2010/03/02/charx-vs-varcharx-vs-varchar-vs-text/](https://www.depesz.com/2010/03/02/charx-vs-varcharx-vs-varchar-vs-text/?ref=honstain.com)

Insert some data to work with:

```sql
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](http://slick.lightbend.com/doc/3.3.0/gettingstarted.html?ref=honstain.com) 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` 

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

```

- Slick version 3.3.0 [http://slick.lightbend.com/doc/3.3.0/](http://slick.lightbend.com/doc/3.3.0/?ref=honstain.com)
- PostgreSQL JDBC [https://github.com/pgjdbc/pgjdbc](https://github.com/pgjdbc/pgjdbc?ref=honstain.com) If you went through the Slick documentation you will see that [http://slick.lightbend.com/doc/3.3.0/database.html](http://slick.lightbend.com/doc/3.3.0/database.html?ref=honstain.com) recommended version `9.4-1206-jdbc42` and I have opted to use the most recent version of `42.2.5`.

### 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.

```scala
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.

```scala
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.

```scala
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

```scala
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/](https://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 

![](https://honstain.com/content/images/2019/02/image-18.png)

```scala
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).

![](https://honstain.com/content/images/2019/02/image-14.png)

## 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

- Slick Documentation [http://scalatra.org/guides/2.6/persistence/slick.html](http://scalatra.org/guides/2.6/persistence/slick.html?ref=honstain.com)
- Slick Queries [http://slick.lightbend.com/doc/3.3.0/queries.html](http://slick.lightbend.com/doc/3.3.0/queries.html?ref=honstain.com)
- PSQL cheat sheet [http://postgresguide.com/utilities/psql.html](http://postgresguide.com/utilities/psql.html?ref=honstain.com)
- This guide helped me by demonstrating some basic postgres queries from Slick [http://queirozf.com/entries/scala-slick-simple-example-on-connecting-to-a-postgresql-database](http://queirozf.com/entries/scala-slick-simple-example-on-connecting-to-a-postgresql-database?ref=honstain.com)