Slick insert not working while trying to return inserted row

Question

My goal here is to retrieve the Board entity upon insert. If the entity exists then I just want to return the existing object (which coincides with the argument of the add method). Otherwise I'd like to return the new row inserted in the database.

I am using Play 2.7 with Slick 3.2 and MySQL 5.7.

The implementation is based on this answer which is more than insightful.

Also from Essential Slick

exec(messages returning messages += Message("Dave", "So... what do we do now?"))

DAO code

@Singleton
class SlickDao @Inject()(db: Database,implicit val playDefaultContext: ExecutionContext) extends MyDao {


    override def add(board: Board): Future[Board] = {
        val insert = Boards
                .filter(b => b.id === board.id && ).exists.result.flatMap { exists =>
            if (!exists) Boards returning Boards += board
            else DBIO.successful(board) // no-op - return specified board
        }.transactionally

        db.run(insert)
    }

EDIT: also tried replacing the += part with

Boards returning Boards.map(_.id) into { (b, boardId) => sb.copy(id = boardId) } += board

and this does not work either

The table definition is the following:

object Board {

    val Boards: TableQuery[BoardTable] = TableQuery[BoardTable]

    class BoardTable(tag: Tag) extends Table[BoardRow](tag, "BOARDS") {

        // columns
        def id = column[String]("ID", O.Length(128))
        def x = column[String]("X")
        def y = column[Option[Int]]("Y")

        // foreign key definitions
        .....
        // primary key definitions
        def pk = primaryKey("PK_BOARDS", (id,y))


        // default projection
        def * = (boardId, x, y).mapTo[BoardRow]

    }
}

I would expect that there would e a new row in the table but although the exists query gets executed

select exists(select `ID`, `X`, `Y`
              from `BOARDS`
              where ((`ID` = '92f10c23-2087-409a-9c4f-eb2d4d6c841f'));

and the result is false there is no insert.

There is neither any logging in the database that any insert statements are received (I am referring to the general_log file)

You have a couple of typos there, " sb => b.id === board.id && ", also a table with 2 Primary keys? — Pedro Correia Luís, Jun 05 '19 at 10:47
You're right. I fixed the typos (did not want to paste exactly my code and these are editing errors) and adjusted the table definition (by simultaneously adding new evolutions and SQL constraints). Unfortunately the error persists. No inserts happen whatsoever. — Niko, Jun 05 '19 at 11:29
try changing this def * = (boardId, x, y) <> (BoardRow.tupled, BoardRow.unapply) — Pedro Correia Luís, Jun 05 '19 at 11:42

score 0 · Accepted Answer · answered Jun 12 '19 at 09:53

So first of all the problem for the query execution was a mishandling of the futures that the DAO produced. I was assigning the insert statement to a future but this future was never submitted to an execution context. My bad even more so that I did not mention it in the description of the problem.

But when this was actually fixed I could see the actual error in the logs of my application. The stack trace was the following:

slick.SlickException: This DBMS allows only a single column to be returned from an INSERT, and that column must be an AutoInc column.
   at slick.jdbc.JdbcStatementBuilderComponent$JdbcCompiledInsert.buildReturnColumns(JdbcStatementBuilderComponent.scala:67)
   at slick.jdbc.JdbcActionComponent$ReturningInsertActionComposerImpl.x$17$lzycompute(JdbcActionComponent.scala:659)
   at slick.jdbc.JdbcActionComponent$ReturningInsertActionComposerImpl.x$17(JdbcActionComponent.scala:659)
   at slick.jdbc.JdbcActionComponent$ReturningInsertActionComposerImpl.keyColumns$lzycompute(JdbcActionComponent.scala:659)
   at slick.jdbc.JdbcActionComponent$ReturningInsertActionComposerImpl.keyColumns(JdbcActionComponent.scala:659)

So this is a MySQL thing in its core. I had to redesign my schema in order to make this retrieval after insert possible. This redesign includes an introduction of a dedicated primary key (completely unrelated to the business logic) which is also an AutoInc column as the stack trace prescribes.

In the end the solution becomes too involved and instead decided to use the actual argument of the add method to return if the insert was actually successful. So the implementation of the add method ended up being something like this

override def add(board: Board): Future[Board] = {
        db.run(Boards.insertOrUpdate(board).map(_ => board))
    }

while there was some appropriate Future error handling in the controller which was invoking the underlying repo.

If you're lucky enough and not using MySQL with Slick I suppose you might have been able to do this without a dedicated AutoInc primary key. If not then I suppose this is a one way road.

Slick insert not working while trying to return inserted row

1 Answers1