Slick 3 Reference and Examples

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)

// 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)

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)

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)

// 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)
}

val q = users.map(_.name)

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

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

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

// 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)

// 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 from users u where u.id=1
val q = users.filter(_.id === 1L).delete

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

// 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)

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)

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");

// 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)
}