How to get auto incrementing id using slick / plainSQL?

Question

Is there a native slick / plainSQL solution to get the automatically incremented identifier of the current INSERT?

userId is an automatic incremental field in my mySQL table.

sql""" INSERT INTO `table`(`email`) OUTPUT INSERTED.userId VALUES (" [e-mail] ") """.as[Int].firstOption 

Help would be greatly appreciated.

Greetings Oliver

Answer 1

Thanks, cvogt for the help in the discussion. I think it would be useful to submit PR, since this is a very common and useful functionality that should not be absent in slick plainSQL queries .

Finally, I found a workaround to replace the missing built-in function as follows.

During the same session, I resolve two requests. The first is the INSERT , the second is the SELECT LAST_INSERT_ID() , which returns the new automatically generated value that was set for the AUTO_INCREMENT column by the recently executed INSERT (1). More details here: MySQL Reference - LAST_INSERT_ID ()

 Database.forDataSource(dataSource).withDynSession { sqlu"""INSERT INTO `users`(`email`) VALUES (" [e-mail] ") """.firstOption match { case Some(num) if num == 1 => sql"SELECT LAST_INSERT_ID()".as[Long].firstOption() case None => None } } 

It works for me right now. If there are any improvements, feel free to post your decision.

Answer 2

Depends on the database.

For MS SQL it is SCOPE_IDENTITY (), for mySQL it is LAST_INSERT_ID (). Try to find the equivalent for your database if it is not listed above.

Added cvogt :

There is currently no built-in function for plain SQL for plain SQL, and there is no access to the main jdbc expression when using Slick sql"..." interpolation sql"..." or StaticQuery , which allows you to access getGeneratedKeys . Perhaps you could fix the SQL interpolator and StatementInvoker to allow this. This is only 150 LOC. It might be worth taking a picture and sending PR.

However, you could use one of the Slick session , for example withPreparedInsertStatement , which terminates the jdbc connection to work with the jdbc statement. I created a PR to add documentation about this: https://github.com/slick/slick/pull/691