Using Auto Incrementing fields with PostgreSQL and Slick

We're using a slightly different approach. Instead of creating a further projection, we request the next id for a table, copy it into the case class and use the default projection '*' for inserting the table entry.

For postgres it looks like this:

Let your Table-Objects implement this trait

trait TableWithId { this: Table[_] =>
  /**
   * can be overriden if the plural of tablename is irregular
   **/
  val idColName: String = s"${tableName.dropRight(1)}_id"
  def id = column[Int](s"${idColName}", O.PrimaryKey, O.AutoInc)
  def getNextId = (Q[Int] + s"""select nextval('"${tableName}_${idColName}_seq"')""").first
  }

All your entity case classes need a method like this (should also be defined in a trait):

case class Entity (...) {
  def withId(newId: Id): Entity = this.copy(id = Some(newId)
}

New entities can now be inserted this way:

object Entities extends Table[Entity]("entities") with TableWithId {
  override val idColName: String = "entity_id"
  ...
  def save(entity: Entity) = this insert entity.withId(getNextId) 
}

The code is still not DRY, because you need to define the withId method for each table. Furthermore you have to request the next id before you insert an entity which might lead to performance impacts, but shouldn't be notable unless you insert thousands of entries at a time.

The main advantage is that there is no need for a second projection what makes the code less error prone, in particular for tables having many columns.

I tackled this problem in an different way. Since I expect my User objects to always have an id in my application logic and the only point where one would not have it is during the insertion to the database, I use an auxiliary NewUser case class which doesn't have an id.

case class User(id: Int, first: String, last: String)
case class NewUser(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 _)
  def autoInc = first ~ last <> (NewUser, NewUser.unapply _) returning id
}

val id = Users.autoInc.insert(NewUser("John", "Doe"))

Again, User maps 1:1 to the database entry/row while NewUser could be replaced by a tuple if you wanted to avoid having the extra case class, since it is only used as a data container for the insert invocation.

EDIT: If you want more safety (with somewhat increased verbosity) you can make use of a trait for the case classes like so:

trait UserT {
  def first: String
  def last: String
}
case class User(id: Int, first: String, last: String) extends UserT
case class NewUser(first: String, last: String) extends UserT
// ... the rest remains intact

In this case you would apply your model changes to the trait first (including any mixins you might need), and optionally add default values to the NewUser.

Author's opinion: I still prefer the no-trait solution as it is more compact and changes to the model are a matter of copy-pasting the User params and then removing the id (auto-inc primary key), both in case class declaration and in table projections.

This is working here:

object Application extends Table[(Long, String)]("application") {   
    def idlApplication = column[Long]("idlapplication", O.PrimaryKey, O.AutoInc)
    def appName = column[String]("appname")
    def * = idlApplication ~ appName
    def autoInc = appName returning idlApplication
}

var id = Application.autoInc.insert("App1")

This is how my SQL looks:

CREATE TABLE application
(idlapplication BIGSERIAL PRIMARY KEY,
appName VARCHAR(500));

Update:

The specific problem with regard to a mapped table with User (as in the question) can be solved as follows:

  def forInsert = first ~ last <>
    ({ (f, l) => User(None, f, l) }, { u:User => Some((u.first, u.last)) })

This is from the test cases in the Slick git repository.