How to return full row using Slick's insertOrUpdate

Question

I am currently learning Play2, Scala and Slick 3.1, and am pretty stuck with the syntax for using insertOrUpdate and wonder if anyone can please help me.

What I want to do is to return the full row when using insertOrUpdate including the auto inc primary key, but I have only managed to return the number of updated/inserted rows.

Here is my table definition:

package models

final case class Report(session_id: Option[Long], session_name: String, tester_name: String, date: String, jira_ref: String,
                  duration: String, environment: String, notes: Option[String])

trait ReportDBTableDefinitions {

  import slick.driver.PostgresDriver.api._

  class Reports(tag: Tag) extends Table[Report](tag, "REPORTS") {

    def session_id = column[Long]("SESSION_ID", O.PrimaryKey, O.AutoInc)
    def session_name = column[String]("SESSION_NAME")
    def tester_name = column[String]("TESTER_NAME")
    def date = column[String]("DATE")
    def jira_ref = column[String]("JIRA_REF")
    def duration = column[String]("DURATION")
    def environment = column[String]("ENVIRONMENT")
    def notes = column[Option[String]]("NOTES")

    def * = (session_id.?, session_name, tester_name, date, jira_ref, duration, environment, notes) <> (Report.tupled, Report.unapply)
  }

  lazy val reportsTable = TableQuery[Reports]

}

Here is the section of my DAO that relates to insertOrUpdate, and it works just fine, but only returns the number of updated/inserted rows:

package models

import com.google.inject.Inject
import play.api.db.slick.DatabaseConfigProvider
import scala.concurrent.Future

class ReportsDAO @Inject()(protected val dbConfigProvider: DatabaseConfigProvider) extends DAOSlick {

  import driver.api._

  def save_report(report: Report): Future[Int] = {
    dbConfig.db.run(reportsTable.insertOrUpdate(report).transactionally)
  }
}

I have tried playing with "returning" but I can't get the syntax I need and keep getting type mismatches e.g. the below doesn't compile (because it's probably completely wrong!)

 def save_report(report: Report): Future[Report] = {
    dbConfig.db.run(reportsTable.returning(reportsTable).insertOrUpdate(report))
  }

Any help appreciated - I'm new to Scala and Slick so apologies if I'm missing something really obvious.

score 4 · Accepted Answer · answered Aug 30 '16 at 08:43

Solved - posting it incase it helps anyone else trying to do something similar:

//will return the new session_id on insert, and None on update
  def save_report(report: Report): Future[Option[Long]] = {
    val insertQuery = (reportsTable returning reportsTable.map(_.session_id)).insertOrUpdate(report)
    dbConfig.db.run(insertQuery)
  }

Works well - insertOrUpdate doesn't returning anything it seems on update, so if I need to get the updated data after the update operation I can then run a subsequent query to get the information using the session id.

Nagarjuna Pamu · Answer 2 · 2019-03-15T21:42:11.440

3

You cannot return whole Report, first return Id (returning(reportsTable.map(_.session_id))) and then get whole object

Check if report exists in the database if it exists update it, if not go ahead inserting the report into the database.

Note do above operations in all or none fashion by using Transactions

def getReportDBIO(id: Long): DBIO[Report] = reportsTable.filter(_.session_id === id).result.head

def save_report(report: Report): Future[Report] = {
  val query = reportsTable.filter(_.session_id === report.session_id)
  val existsAction = query.exists.result
  val insertOrUpdateAction = 
  (for { 
     exists <- existsAction
     result <- exists match {
        case true => 
        query.update(report).flatMap {_ => getReportDBIO(report.session_id)}.transactionally
        case false => {
           val insertAction = reportsTable.returning(reportsTable.map(_.session_id)) += report
           val finalAction = insertAction.flatMap( id => getReportDBIO(id)).transactionally //transactionally is important
            finalAction
        }
     }
  } yield result).transactionally

  dbConfig.db.run(insertOrUpdateAction)
}

Update your insertOrUpdate function accordingly

edited Mar 15 '19 at 21:42

answered Aug 29 '16 at 16:23

Nagarjuna Pamu

14,737
3
22
40

Many thanks for this - however insertorupdate is a Slick inbuilt function, so not something I can update as such. I was hoping to use it because it either updates the record or inserts if it doesn't exist - but I can't find any way of returning the whole row using it, or even the session ID. – jnhamilton Aug 29 '16 at 19:36
@jnhamilton you could use `save_report` instead of `insertOrUpdate` for this case. – Nagarjuna Pamu Aug 29 '16 at 19:38
Ah I see thanks, I'll try this tomorrow and update the ticket – jnhamilton Aug 29 '16 at 20:31
Tried this out thanks - it actually inserts a new record when you try and update rather than updating the existing record, but it has given me some ideas to try :) – jnhamilton Aug 30 '16 at 07:28

score 2 · Answer 3 · answered Nov 23 '17 at 10:38

You can return the full row, but it is an Option, as the documentation states, it will be empty on an update and will be a Some(...) representing the inserted row on an insert.

So the correct code would be

    def save_report(report: Report): Future[Option[Report]] = {dbConfig.db.run(reportsTable.returning(reportsTable).insertOrUpdate(report))}

How to return full row using Slick's insertOrUpdate

3 Answers3