Slick 3.0 Insert and then get Auto Increment Value

Question

I have written this code which works perfectly

class Items(tag: Tag) extends Table[Item](tag, "ITEMS") {
  def id = column[Long]("ITEMS_ID", O.PrimaryKey, O.AutoInc)
  def name = column[String]("ITEMS_NAME")
  def price = column[Double]("ITEMS_PRICE")
  def * = (id, name, price) <> ((Item.apply _).tupled, Item.unapply _)
}

object Shop extends Shop{
  val items = TableQuery[Items]
  val db = Database.forConfig("h2mem1")

  def create(name: String, price: Double) : Int = {
    val action = items ++= Seq(Item(0, name, price))
    val future1 = db.run(action)
    val future2 = future1 map {result => 
      result map {x => x}
    }
    Await.result(future2, Duration.Inf).getOrElse(0)
  }
}

This code works but the return value is number of records inserted. But I want to return the value of the AutoInc after the insert has been done.

i did google and found few articles

Slick 3.0.0 AutoIncrement Composite Key

Returning the auto incrementing value after an insert using slick

But somehow these do not answer the question cleanly.

Solution 1

Here's the relevant documentation page, according to which, you should construct a query like this:

val insertQuery = items returning items.map(_.id) into ((item, id) => item.copy(id = id))

def create(name: String, price: Double) : Future[Item] = {
  val action = insertQuery += Item(0, name, price)   
  db.run(action)
}

Solution 2

Try this one instead:

def create(name: String, price: Double): Future[Int] = db.run {
    (items returning items.map(_.id)) += Item(0, name, price)
}

Comments

[Patryk Ćwiek]

+1. I found that Play-Slick-Silhouette Typesafe Activator template is a pretty good introduction how to use Slick 3.0 in a 'real-life' setting, for example here and here, including the 'return assigned identity' problem.

[Vincil Bishop]

If I could give you an upvote of 100+ I would. This is probably the most concise answer to this question I have seen.

[ps0604]

this may be obvious, but I'm new in slick/scala, in what variable the autoincrement column is stored? how to get it?

[dcastro]

@ps0604 The query above constructs an instance of Item with the (auto-incremented) id set to 0. When a row is inserted into the database, it creates a copy of this instance of Item, where the new copy has its id set to, for example, 8 instead of 0 - (item, id) => item.copy(id = id). So, to answer your question, you'll find the autoincremented value in item.id.

[ps0604]

where exactly is item.id? I don't see any item object accessible after you run the action

[dcastro]

@ps0604 db.run will return a Future[Item] (note the method's signature). After awaiting the future, you'll get an Item.

[JulienD]

It will only work if your table has less than 22 columns, because otherwise HLists will replace case classes and there will be no copy method.

[Andrew Norman]

having the db.run(action) right there will break transactionality if there is a greater chain of db work going on: stackoverflow.com/questions/41219200/…