Slick 3 Reference and Examples

Last updated:
Slick 3 Reference and Examples
Source

A lot has changed in Slick 3.x in comparison with 2.x.

Some general differences between Slick 2.x and 3.x:

  • All Database operations now return Futures rather than normal types
  • Must use Option[columntype]for nullable columns
  • Stuff like O.Nullable and O.NotNull is not used anymore

All code is available on github.com/queirozfcom/slick_sandbox

These are some examples of common stuff you might want to do on Slick 3.x.

Raw SQL

In all cases, variable timeout is of type scala.concurrent.duration.FiniteDuration

Select

val q = sql"select u.name from users u".as[String]

Await.result(
  db.run(q).map { res =>
    // res is a Vector[String]
    println(res)
  }, timeout)

Insert

// queries that don't return a resultset use 'sqlu'
val q = sqlu"insert into users(name,password) values ('john','mypasswd')"

Await.result(
  db.run(q).map{ res =>
    // prints the number of affected rows
    // which amount to 1 if the insert was successful
    println(res)
  }, timeout)

Update

val q = sqlu"update users u set u.name='mary' where u.name = 'john'"

Await.result(
  db.run(q).map{ res =>
    // again, the number of affected rows
    println(res)
  }, timeout)

Handling Errors

The Database driver may throw Exceptions (for instance, if you try to add a record with a duplicate value for a UNIQUE column). To capture errors, use recover:

val id = "1"
val name = "peter"
val password = "passwd"

val q = sqlu"insert into users(id,name,password) values($id,$name,$password)"

Await.result(db.run(q).map { res =>
  res match{
    case 1 => println("all good")
    case 0 => println("couldn't add record for some reason")
  }
}.recover{
  case e:Exception =>println("Caught exception: "+e.getMessage)
}, timeout)

Transactions

Use transactions for stuff that needs to fail be successful atomically or for sequences of steps that must be run one after the other (no other database operations may occur between them):

// insert or update, depending on whether that user already exists

val q = sql"select name from users where name='john'".as[String]

val qInsert = sqlu"insert into users(name,email,password) values('john','john@example.com','somepasswd')"

val qUpdate = sqlu"update users set email='john@example.com' where name='john'"

// this needs to be executed in a transaction
// because it might get written to after we run select
val actions = for {
  exists <- q
  affectedRows <- if (exists.isEmpty) qInsert else qUpdate
} yield affectedRows

db.run(actions.transactionally).map { affectedRows =>
  affectedRows match {
    case 1 => println("all ok!")
    case 0 => println("no rows were affected!")
  }
}.recover {
  case e: java.sql.SQLException => println("ERROR: "+e.getMessage)
}

Queries using Case Classes

Supporting code at the end of this guide (Scala classes)

Slick lets you define case classes (e.g. User) and TableQuerys, so that you can write type-safe SQL queries:

Select

Use mapfor projections:

val q = users.map(_.name)

Await.result(
  db.run(q.result).map { res =>
    // res is a Vector[String]
    println(res)
  }, timeout)

And filter for selections:

val q = users.filter(user => user.name === "john")

Await.result(
  db.run(q.result).map { res =>
    // res is a Vector[User]
    println(res)
  }, timeout)

Inserts (with auto-increment)

// use zero because the database will generate a new ID
val newUser = (users returning users.map(_.id)) += User(0,name=name,password=password,email=None)

// note that the newly-added id is returned instead of
// the number of affected rows
Await.result(db.run(newUser).map { newId =>
  newId match {
    case x:Long => println(s"last entry added had id $x")
  }
}.recover {
  case e:  java.sql.SQLException => println("Caught exception: " + e.getMessage)
}, timeout)

Updates

// update users u set u.name='peter' where u.id=1
val q = users.filter(_.id === 1L).map(_.name).update("peter")

Await.result(
  db.run(q).map { numAffectedRows =>
    println(numAffectedRows)
  }, timeout)

Delete

// delete from users u where u.id=1
val q = users.filter(_.id === 1L).delete

Await.result(
  db.run(q).map { numAffectedRows =>
    println(numAffectedRows)
  }, timeout)

Joins

Now suppose every user belongs to a user group, i.e. it has a groupId attribute. Assume further that we also have a UserGroupsTable table (much like our old UsersTable, but for groups.

This is not the only way to write joins. Slick 3 Official Docs on Joins

Our objective in this snippet is to fetch all users that belong to a given group.

// for this snippet , we'll be using a 
// slightly modified version of class User

val userGroups = TableQuery[UserGroupsTable]
val users - TableQuery[UsersTable]
val myGroupId = 1

// in real life you could just filter the users having the given
// group id but let's use this as an example
val query = for{
  ug <- userGroups if ug.id === myGroupId
  u <- users if u.groupId === ug.id
} yield (u)

// db operations are async so we must wait
// on them
Await.result(
  db.run(query).map { users =>
    println(s" ${users.size} users belong to group ${myGroupId}")
  }, timeout)

Transactions

val qDelete = users.filter(_.name === "john").delete
val addDuplicateUser = users += User(1, name = sampleUsername, password = "passwd", email = None)

val q1 = (users returning users.map(_.id)) += User(0, name = name1, password = "passwd", email = None)

// compose actions using for
val actions = for {
  affectedRows <- qDelete

  // use '_' if you don't care about the result
  _ <- addSampleUser

  // exception is thrown if affectedRows is different from 1
  // exceptions thrown in the block cause the transaction to be rolled back
  newUserId1 <- if (affectedRows == 1) q1 else DBIO.failed(new SQLException("Abort"))

} yield (newUserId1)

// the result of these actions will be whatever has been yielded
// from the 'for' block, i.e. a Long representing the added user ID
Await.result(
  db.run(actions.transactionally).map { res =>
    println(s"The newly-added user had the following id $res")
  }.recover {
    case e: java.sql.SQLException => println("Caught exception in the for block: " + e.getMessage)
  }, timeout)


Schema

This is the schema used for these examples:

CREATE TABLE users (
    id INT(11) NOT NULL AUTO_INCREMENT,
    name varchar(255) NOT NULL,
    password varchar(255) NOT NULL,
    email varchar(255) DEFAULT NULL,
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE UNIQUE INDEX "unique_name" ON "users"("name");

Row and Table Classes

In Slick, you can create a Case Class to hold each row and a regular Class for your table:

// rows returned will be represented as an instance of this class
case class User(id: Long, name: String, password: String, email: Option[String])

//a database table for the above case class
class UsersTable(tag: Tag) extends Table[User](tag, "users") {
  def id = column[Long]("id", O.PrimaryKey, O.AutoInc)
  def name = column[String]("name")
  def password = column[String]("password")
  def email = column[Option[String]]("email")

  def uniqueName = index("unique_name", (name), unique = true)

  def * = (id, name, password, email) <>(User.tupled, User.unapply)
}

More information

Dialogue & Discussion