Scala Slick - Insert in table with omitting some columns and returning primary key of new line

Question

For a scala project, I'm using play-slick with play-slick-evolutions both version 5.0.0 and I generate my db classes with slick-codegen version 3.3.3.

I have a table with a primary key column and some columns with default values. I want to insert one row without mentioning the primary key column nor any columns with default values. Ideally, this action should return the new primary key of the created row.

My problem is that the generated code from slick-codegen seems to only allow to insert full rows because it uses an own case class for the rows. This is how the generated code looks (without the comments):

case class SalesOrderRow(idSalesOrder: Int, fkCustomer: Int, createdAt: java.sql.Timestamp, createdBy: Option[String] = None)

implicit def GetResultSalesOrderRow(implicit e0: GR[Int], e1: GR[java.sql.Timestamp], e2: GR[Option[String]]): GR[SalesOrderRow] = GR{
  prs => import prs._
  SalesOrderRow.tupled((<<[Int], <<[Int], <<[java.sql.Timestamp], <<?[String]))
}

class SalesOrder(_tableTag: Tag) extends profile.api.Table[SalesOrderRow](_tableTag, Some("test"), "sales_order") {
  def * = (idSalesOrder, fkCustomer, createdAt, createdBy) <> (SalesOrderRow.tupled, SalesOrderRow.unapply)
  def ? = ((Rep.Some(idSalesOrder), Rep.Some(fkCustomer), Rep.Some(createdAt), createdBy)).shaped.<>({r=>import r._; _1.map(_=> SalesOrderRow.tupled((_1.get, _2.get, _3.get, _4)))}, (_:Any) =>  throw new Exception("Inserting into ? projection not supported."))

  val idSalesOrder: Rep[Int] = column[Int]("id_sales_order", O.AutoInc, O.PrimaryKey)
  val fkCustomer: Rep[Int] = column[Int]("fk_customer")
  val createdAt: Rep[java.sql.Timestamp] = column[java.sql.Timestamp]("created_at")
  val createdBy: Rep[Option[String]] = column[Option[String]]("created_by", O.Length(20,varying=true), O.Default(None))
}

lazy val SalesOrder = new TableQuery(tag => new SalesOrder(tag))

With this generated code I could now insert a row with mentioning the full column:

val insertActionsNotSoNice =
  DBIO.seq(
    salesOrders += Tables.SalesOrderRow(0, 3, new Timestamp(System.currentTimeMillis()), Some("这个不好"))
  )

But I want to omit the primary key at the beginning and the timestamp parameter that has a default value. But can't do something like this.

val insertActionsNotCompiling =
  DBIO.seq(
    salesOrders.map(so => (so.fkCustomer, so.createdBy) += (3, Some("这个好")))
  )

I found many examples with something like the latter approach in the slick documentation and in the web but it was always the case that their classes used for the database did have a tuple instead of an own row class. Like...

class Coffees(tag: Tag) extends Table[(String, Int, Double, Int, Int)](tag, "COFFEES")

instead of

class Coffees(_tableTag: Tag) extends profile.api.Table[CoffeesRow](_tableTag, Some("test"), "coffee")

Do I have to throw the slick-codegen out of my project and write all the classes myself to fit my needs or is my composition of libraries with play-slick wrong? Or is there a simple trick to omit columns when inserting what isn't documented?

Answer 1

After some days of trying several things, I found one solution.

You can map over a query to select only the columns you want to instert and then use the returning if you want to return the id of the inserted row.

import dao.Tables.profile.api._

// ...

val salesOrders = TableQuery[SalesOrder]
val insertStatement = salesOrders.map(so => (so.fkCustomer, so.createdBy)) returning salesOrders.map(_.idSalesOrder) into ((_, id) => id)

Then you can write something like:

val newSalesOrderIdFuture = db.run(insertStatement += (5, Some("有效")))

So I omitted the primary key column (id_sales_order) and a timestamp column that defaults to now() in the database (created_at) in my insert statement.