How to explicitly specify value of AutoInc column in Slick

Question

By default, Slick ignores values in columns with O.AutoInc flag and lets database to fill that value during insert operation.

But sometimes i need to insert some specific value for auto incrementing column and Slick still ignores it. Is there any way to do that?

I know, i can make second table definition, without O.AutoInc flag, but i'm looking for more elegant way to do it.

Update: Here is my case class and table definition:

case class Transaction (id: Long, timestamp: LocalDateTime, comment: Option[String])
class Transactions(tag: Tag) extends Table[Transaction](tag, "tx") {
  implicit val localDTtoDate = MappedColumnType.base[LocalDateTime, Timestamp] (
    l => Timestamp.valueOf(l),
    d => d.toLocalDateTime
  )

  def id = column[Long]("id", O.PrimaryKey, O.AutoInc)
  def timestamp = column[LocalDateTime]("ts")
  def comment = column[Option[String]]("comment")
  def * = (id, timestamp, comment) <> ((Transaction.apply _).tupled, Transaction.unapply)
}

Answer 1

If you mark your id field as Optional, you should be able to insert with a slight adjustment to your * projection:

case class Transaction (id: Option[Long], timestamp: LocalDateTime,
  comment: Option[String])
class Transactions(tag: Tag) extends Table[Transaction](tag, "tx") {
  // Use the ? method on your id column to map it to an option.
  def * = (id.?, timestamp, comment) <> (
    (Transaction.apply _).tupled, Transaction.unapply)
}

With this, rows that you insert with id=None will generate a new id, while rows with id=Some(value) will set id=value. To read back the inserted id, use returning ... into:

// `tx` is a Transaction instance, `transactions` is a
// TableQuery[Transactions] instance.
(transactions.returning(transactions.map(_.id)).into { (_, id) =>
  tx.copy(id = id)
}) += tx

Answer 2

I found this question while struggling with a very similar issue. Sadly, we could not make the suggested solution work either.

We decided to let the O.AutoInc go, but kept the sequence and the default value of the column to be the nextval of that sequence

  Column  |  Type  |                 Modifiers
----------+--------+----------------------------------------------
 id       | bigint | not null default nextval('id_seq'::regclass)

Then we just omitted the id during insertion when it needed to be generated automatically.

Link to the relevant part of the documentation: http://slick.lightbend.com/doc/3.1.0/queries.html#inserting

In this exact case it would look something like this:

if (you have an explicit id) transactions += tx
else transactions.map(t => (t.timestamp, t.comment)) += (tx.timestamp, tx.comment)