Update rows with composite primary key generated from database with slick codegen

Question

I am working on a scala project which uses slick as it's database access library. I am trying to update a row with the following definition where there is a composite key.

class TableName(tag: Tag) extends Table[TableName](tag, "table_name"){
    def keyPart1 = column[String]("key_part_1", O.Length(100, varying = true))
    def keyPart2 = column[String]("key_part_2", O.Length(100, varying = true))
    // More columns defined
    def pk = primaryKey("t_composite_pk", (keyPart1, keyPart2))
    def * (keyPart1, keyPart2, ..more columns..) <> (TableNameRow.tupled, TableNameRow)
}

The use of the insertOrUpdate or update methods from JdbcActionComponent will not work to insert or update a value. There is a known issue in slick with composite primary keys that will prevent these methods from functioning correctly as it is unable to determine which identifier it should relate to. However there is a workaround. If you add the O.PrimaryKey value as a parameter of the rows that compose the composite primary key then slick will be able to properly determine the key.

class TableName(tag: Tag) extends Table[TableName](tag, "table_name"){
    def keyPart1 = column[String]("key_part_1", O.Length(100, varying = true), O.PrimaryKey)
    def keyPart2 = column[String]("key_part_2", O.Length(100, varying = true), O.PrimaryKey)
    // More columns defined
    def pk = primaryKey("t_composite_pk", (keyPart1, keyPart2))
    def * (keyPart1, keyPart2, ..more columns..) <> (TableNameRow.tupled, TableNameRow)
}

The project as now been migrated to use slick codegen and the code schema is now dynamically generated from the database's schema. This generated schema does not have the work around for composite primary keys.

Is there an approach to make slick codegen function with composite primary keys to allow the use of insertOrUpdate or update? Codegen does have some support for composite primary keys, but it is only for the generation of the following value:

/** Primary key of TableName(database name table_name_pk) */
val pk = primaryKey("table_name_pk", (keyPart1, keyPart2))

This however does not appear to be enough for slick to be able to identify the rows correctly. If that cannot be made to work is there an other approach which could be used to update these rows?

It is the policy to use codegen and to not commit the generate table scala definition so modifying the generated file is not an option — Louis Querel

Louis Querel Louis Querel · Accepted Answer · 2016-04-29T15:59:18

A work around that was identified by reviewing other posts concerning updates was the use of filter(...).update(...) from the this post. It is possible to use the following approach to update:

// Where the following was defined by the codegen
/** Collection-like TableQuery object for table TableName*/
lazy val TableName = new TableQuery(tag => new TableName(tag))

// We can then filter on the primary key values
def updateMethod(row: TableNameRow) = 
    TableName
       .filter(x => x.keyPart1 === row.keyPart1 && x.keyPart2 === row.keyPart2)
       .update(row)

It can be applied to allow updates of rows with a composite key given that we emulate the key through the filter. However, this is a workaround and does not allow the use of certain functions from JdbcActionComponent.

Update rows with composite primary key generated from database with slick codegen

1 Answers