Slick 2 Examples: Querying and Modifying Data

Slick 2 Examples: Querying and Modifying Data

Last updated:
Slick 2 Examples: Querying and Modifying Data
Source

It's a little bit hard to find documentation for slick so here are a few examples.

HEADS-UP These examples were tested against Slick 2.1 and PostgreSQL but they should work on other DBMSs as well (you will just need to provide your own connection details and the driver you're using)

Defining a class and a matching database table

This is done so that query results will be returned as instances of a nice and solid Class you've defined. This is the approach in most ORMs (Object-relational models).

This code expects your database to have a users table defined. You can use this sql snippet to create it

import java.sql.Timestamp
import scala.slick.driver.PostgresDriver.simple._

case class User(
 id: Long,
 username: String,
 email: Option[String],
 password: String,
 created: Timestamp)

//a simple table called 'users'
class Users(tag: Tag) extends Table[User](tag, "users") {

  def id = column[Long]("id", O.PrimaryKey, O.AutoInc)
  def username = column[String]("username", O.NotNull)
  // an Option[] in the case class maps to a Nullable field here
  def email = column[String]("email", O.Nullable)
  def password = column[String]("password", O.NotNull)
  // this is a hack for postgresql; if you're using another DB, comment this out
  // and the corresponding field in the case class
  def created = column[Timestamp]("created_at", O.NotNull, O.DBType("timestamp default now()"))

  // usernames should be unique
  def idx = index("users_unique_username", (username), unique = true)

  //define the "shape" of a single data record
  //we're saying that an object of class User (our case class) should be returned
  def * = (id, username, email.?, password,created) <> (User.tupled, User.unapply)
}

Simple selects

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

// the connection url is the only part where there's coupling with the actual RDBMS used
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 )
    }
}

Now suppose that, in addition to our table users (represented by the class User), there's another table called cars (represented by a case class Car(which I won't write here)) created like this:

-- this may be slightly different from the way you create
-- tables in your dbms of choice but I'm just creating a simple
-- table with an auto-inc pk, a name and a foreign key here
CREATE TABLE cars (
    id        bigserial  NOT NULL,
    name character varying(100) NOT NULL,
    user_id bigint NOT NULL,
   CONSTRAINT car_pk PRIMARY KEY (id),
   CONSTRAINT car_fk FOREIGN KEY(user_id) REFERENCES users(id)
);

So assuming you've created a case class Carand also a class Carsto represent the entity and the table, respectively (just see the example above for User and Users), this is how you'd use Slick to create an inner join to fetch all cars that belong to the user whose username is "john".

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

// the connection url is the only part where there's coupling with the actual RDBMS used
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]
    val cars = TableQuery[Cars]

    // notice the type of this variable
    val carsOwnedByJohn: List[Car] = { 
      for{
        (u, c) <- users innerJoin cars on ( _.id === _.userId) if u.username === "john"
      } yield(c)
    }.list
}

More join examples in the official slick docs

Inserting a record and getting back the new ID

(for tables with auto incrementing ids, for instance)

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

// the connection url is the only part where there's coupling with the actual RDBMS used
val connectionUrl = "jdbc:postgresql://localhost/my-db?user=postgres&password=postgres"
Database.forURL(connectionUrl, driver = "org.postgresql.Driver") withSession {
  implicit session =>
    # suppose we now have a Persons table definition class  
    val persons = TableQuery[Persons]

    // using Try because this operation may fail
    Try( (persons returning persons.map(_.id)) += Person("John", 45) ) match {
      case Success(id) => println("Success! New ID is: "+id)
      case Failure(_) => println("An error occurred!")
    }
}

Updating some (or all) attributes in a database row

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

// the connection url is the only part where there's coupling with the actual RDBMS used
val connectionUrl = "jdbc:postgresql://localhost/my-db?user=postgres&password=postgres"
Database.forURL(connectionUrl, driver = "org.postgresql.Driver") withSession {
  implicit session =>
    val persons = TableQuery[Persons]

    // wrap this in a Try because this may fail for all sorts of reasons
    Try( persons.filter(_.id === 1).map(c => (c.name, c.age)).update(("John", 34)) ) match{
      case Success(_) => println("Record successfully updated!")
      case Failure(_) => println("An error occurred!")
    }
}

Deleting records by ID

This one is easy enough:

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

// the connection url is the only part where there's coupling with the actual RDBMS used
val connectionUrl = "jdbc:postgresql://localhost/my-db?user=postgres&password=postgres"
Database.forURL(connectionUrl, driver = "org.postgresql.Driver") withSession {
  implicit session =>
    val persons = TableQuery[Persons]

    val idToDelete = 1

    // wrap this in a Try because this may fail
    Try( persons.filter(_.id === idToDelete).delete  ) match{
      case Success(_) => println("Record successfully deleted!")
      case Failure(_) => println("An error occurred!")
    }
}

Raw SQL queries

Refer to Plain SQL query Examples on Slick 2.1.0 on the actual docs. They've got pretty good examples here.

References and other links

slick scala

Archive