Getting autoincrement values with Slick library in Scala

scala slick

10,144

Solution 1

You can retrieve the generated value like this.

Add autoInc method to Users object.

def autoInc = id.? ~ first ~ last <> (User, User.unapply _) returning id

Use Users.autoInc.insert instead.

print(Users.autoInc.insert(User(None, "Jack", "Green" )))

Solution 2

The latest version of Slick (2.1.0) automatically takes care of ignoring the auto-incremented ids and the documentation demonstrates how to retrieve the id:

val userWithId =
  (users returning users.map(_.id)
         into ((user,id) => user.copy(id=Some(id)))
  ) += User(None, "Stefan", "Zeiger")

This is the full monty, where the retrieved value is then inserted back into the object you are adding to the table. If you just want to get your hands on the id itself:

val userId =
  (users returning users.map(_.id)) += User(None, "Stefan", "Zeiger")

Both code snippets were taken from the official docs. It seemed like this question needed updating for newcomers (like me) who have never used earlier versions of Slick.

10,144

Jack

Architect, coder, dreamer

Updated on June 04, 2022

Comments

Jack almost 2 years

How do I get the auto-incremented values for records inserted with Slick? The following code prints 1111. I would have expected it to print 1234

import scala.slick.driver.H2Driver.simple._

object TestMappedTable extends App{
    case class User(id: Option[Int], first: String, last: String)

    object Users extends Table[User]("users") {
        def id = column[Int]("id", O.PrimaryKey, O.AutoInc)
        def first = column[String]("first")
        def last = column[String]("last")
        def * = id.? ~ first ~ last <> (User, User.unapply _)
    }

  implicit val session = Database.forURL("jdbc:h2:mem:test1", driver = "org.h2.Driver").createSession()
  session.withTransaction{
    Users.ddl.create

    print(Users.insert(User(None, "Jack", "Green" )))
    print(Users.insert(User(None, "Joe", "Blue" )))
    print(Users.insert(User(None, "John", "Purple" )))
    print(Users.insert(User(None, "Jim", "Yellow" )))
  }
}

I'm using Slick 0.11.2 for Scala 2.10.0-RC1

Jack over 11 years

I kinda assumed it was, but turns out I was wrong. Thanks for the help.
Yaroslav over 10 years

Data manipulation queries usually return the number of rows affected.

Jack over 11 years

This worked great for the H2 database, but PostgreSQL does not like it. Since this is not quite the same problem, I asked it as a new question: stackoverflow.com/q/13199198/828757
AlexBrand almost 11 years

What can be done when tables are larger? maybe 15-20 columns... the forInsert method becomes a monster!
panther almost 9 years

This is more up to date answer. I am not sure if you can 'edit' the currently selected answer to add this as an update or at least link to this.
panther almost 9 years

This is now supported by Slick. See Brenden's answer below. Same is also possible in Slick 3.0.
User almost 9 years

But why is userId of type FixedSqlAction[Int, NoStream, Effect.Write]? (tested in Slick 3.0.0) How to get the Int / Long?
Chris Beach about 8 years

could that last line be user.copy(id = Some(id))?