Slick 3 Reference and Examples

Slick 3 Reference and Examples

Last updated:

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

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



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

Await.result( { res =>
    // res is a Vector[String]
  }, timeout)


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

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


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

Await.result({ res =>
    // again, the number of affected rows
  }, 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( { res =>
  res match{
    case 1 => println("all good")
    case 0 => println("couldn't add record for some reason")
  case e:Exception =>println("Caught exception: "+e.getMessage)
}, timeout)


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','','somepasswd')"

val qUpdate = sqlu"update users set email='' 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 { 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:


Use mapfor projections:

val q =

Await.result( { res =>
    // res is a Vector[String]
  }, timeout)

And filter for selections:

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

Await.result( { res =>
    // res is a Vector[User]
  }, timeout)

Inserts (with auto-increment)

// use zero because the database will generate a new ID
val newUser = (users returning += 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( { 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)


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

Await.result( { numAffectedRows =>
  }, timeout)


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

Await.result( { numAffectedRows =>
  }, timeout)


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 === myGroupId
  u <- users if u.groupId ===
} yield (u)

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


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

val q1 = (users returning += 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( { 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)


This is the schema used for these examples:

    name varchar(255) NOT NULL,
    password varchar(255) NOT NULL,
    email varchar(255) DEFAULT NULL,
    PRIMARY KEY (id)
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