Scala Slick: Simple Example on Connecting to a PostgreSQL Database

Scala Slick: Simple Example on Connecting to a PostgreSQL Database

Last updated:

This is a small article with some examples on how to connect to a PostgreSQL backend using Slick so you can integrate your Scala apps to a Database Server.

HEADS UP This post is about Slick 2.x

I've used Postgres for this example because it seems that it is the most commonly used database used in the Linux world (for serious work, that is) other than big guy Oracle.

Other than a functioning PostgreSQL server running (see installing on Ubuntu for info) you'll also need sbt (download) because we'll need to download some dependencies and sbt is the simplest way to get them.

I don't think there are many Slick examples lying around and for this reason I've added this article and a few others you can find on this website.

This example is better suited for linux but it may be useful for other platforms as well.

Create a table called users on a PostgreSQL database like this:

CREATE TABLE users
(
  id bigserial NOT NULL,
  username character varying,
  CONSTRAINT pk PRIMARY KEY (id)
)

Add these two records for testing

insert into users(username) values ('john'), ('mary');

Create a directory called slick_example/ somewhere you have writing access to

Now create these directories and empty files under the directory you just created, so that it looks like this:

slick_example/
|- src/
    |- scala/
        |-Main.scala
|-build.sbt

Add this to build.sbt

libraryDependencies ++= Seq(
  "org.postgresql" % "postgresql" % "9.3-1100-jdbc4",
  "com.typesafe.slick" %% "slick" % "2.1.0",
  "org.slf4j" % "slf4j-nop" % "1.6.4"
)

Add this to Main.scala

Replace my-db with your database name and postgres/postgres with your username/password, respectively 

import scala.slick.driver.PostgresDriver.simple._

object Main {

  // this is a class that represents the table I've created in the database
  class Users(tag: Tag) extends Table[(Int, String)](tag, "users") {
    def id = column[Int]("id")
    def username = column[String]("username")
    def * = (id, username)
  }

  def main(args: Array[String]): Unit = {

    // my database server is located on the localhost
    // database name is "my-db"
    // username is "postgres"
    // and password is "postgres"
    val connectionUrl = "jdbc:postgresql://localhost/my-db?user=postgres&password=postgres"

    Database.forURL(connectionUrl, driver = "org.postgresql.Driver") withSession {
      implicit session =>
        val users = TableQuery[Users]

        // SELECT * FROM users
        users.list foreach { row =>
          println("user with id " + row._1 + " has username " + row._2)
        }

        // SELECT * FROM users WHERE username='john'
        users.filter(_.username === "john").list foreach { row => 
           println("user whose username is 'john' has id "+row._1 )
        }
    }
  }
}

Run the project

In slick_example/, run the project using sbt:

slick_example$ sbt run

If all goes well, sbt will download the needed dependencies, compile the project and you should see the following output lines: 

[info] Running Main 
user with id 1 has username john
user with id 2 has username mary
user whose username is 'john' has id 1
[success] Total time: 4 s, completed Jan 15, 2015 4:30:33 AM

Now you can continue the journey by yourself. Here are some links to get you started:

slick postgres

Archive