Slick: insert and return whole row

// Question on Slick: how to insert a case class instance to DB and get it back
// with all the autogenerated fields filled?
// Example is based on DB schema described in https://pastebin.com/5qhdsZp2 and https://pastebin.com/vdGqiwQ3

// FacultyDao.scala:
package dao

import javax.inject.Inject
import scala.concurrent.{ExecutionContext, Future}
import play.api.db.slick.{DatabaseConfigProvider, HasDatabaseConfigProvider}
import play.db.NamedDatabase
import slick.jdbc.JdbcProfile
import slick.lifted.ProvenShape

case class Faculty(facultyId: Option[Long], name: String, isArts: Boolean)

class FacultyDao @Inject() (
    @NamedDatabase("students") val dbConfigProvider: DatabaseConfigProvider
  )(implicit val ec: ExecutionContext) extends HasDatabaseConfigProvider[JdbcProfile] {

  import profile.api._

  private class FacultyTable(tag: Tag) extends Table[Faculty](tag, "faculty") {
    def facultyId: Rep[Long] = column[Long]("faculty_id", O.PrimaryKey, O.AutoInc)
    def name: Rep[String] = column[String]("name")
    def isArts: Rep[Boolean] = column[Boolean]("isArts")

    override def * : ProvenShape[Faculty] = (facultyId.?, name, isArts) <> (Faculty.tupled, Faculty.unapply)
  }

  private lazy val table = TableQuery[FacultyTable]

  def insert(faculty: Faculty): Future[Long] = {
    val query = table.returning(table.map(_.facultyId)) += faculty // "+=" will skip "O.AutoInc" in SQL Query so that DB Engine will take care of that
    println(query.statements.mkString)
    db.run(query)
  }
}

// CampusController.scala:
package controllers

import javax.inject.Inject
import scala.concurrent.ExecutionContext
import play.api.mvc._
import dao.{Faculty, FacultyDao}

/**
 * CampusController
 * @param cc Controller Components
 */
class CampusController @Inject()(
    facultyDao: FacultyDao,
    cc: ControllerComponents
  )(implicit val ec: ExecutionContext) extends AbstractController(cc) {

  // don't forget to add in routes: "POST   /test/faculty/:name    controllers.CampusController.insertNewFaculty(name)"
  def insertNewFaculty(name: String): Action[AnyContent] = Action.async {
    val faculty = Faculty(facultyId = None, name, isArts = false) // please note that facultyId = None (auto-generated)
    facultyDao.insert(faculty) map { newId: Long =>
      val newFaculty = faculty.copy(facultyId = Some(newId))      // here we substitute with a new generated value
      Ok(s"New faculty is $newFaculty")
    }
  }
}



// 1. Here we go!
// localhost:9000/test/faculty/phililogy
// Stdout: insert into `faculty` (`name`,`isArts`)  values (?,?)
// Output: New faculty is Faculty(Some(2),phililogy,false)



// 2. Now let's see more interesting example:
// in DAO:
def insert(faculty: Faculty): Future[Faculty] = {
  val query = table.returning(table.map(t => t)) += faculty    // "t => t" will return the whole row!
  println(query.statements.mkString)
  db.run(query)
}

// in Controller:
facultyDao.insert(faculty) map { newFaculty: Faculty =>   // newFaculty is already here!
  Ok(s"New faculty is $newFaculty")
}

// localhost:9000/test/faculty/history
// Output: [SlickException: This DBMS allows only a single column to be returned from an INSERT, and that column must be an AutoInc column.]
// WTF? MySQL doesn't support it :'(



// 3. OK, MySQL can't return the whole row, so I'd prefer this way instead:
def insert(faculty: Faculty): Future[Faculty] = {
  val query = table.returning(table.map(_.facultyId)) += faculty
  println(query.statements.mkString)
  db.run(query) map {id => faculty.copy(facultyId = Some(id))}
}

// localhost:9000/test/faculty/history
// Stdout: insert into `faculty` (`name`,`isArts`)  values (?,?)
// Output: New faculty is Faculty(Some(3),history,false)