Inserting database table rows with Slick 1.0.1

This tutorial shows you how to execute database inserts in a Slick application by building on the previous previous tutorials.

Inserting data with Slick turns out not to be as complex as querying, but there’s still more than one way to do it.

Summary

Perform the following tasks to complete this tutorial, and learn how to insert data.

Insert an instance of a mapped case class
Get the inserted database table row’s primary key
Insert using a tuple instead of a case class
Insert multiple rows using JDBC batch insert

Source code: https://github.com/lunatech-labs/play-slick-examples.

Insert an instance of a mapped case class

The most common way to insert data is probably to use mapped case classes, such as the ones in the previous tutorials. Let’s start with the table mapping from Mapping Slick query results to case classes, and paste this into a Play/Slick application’s Scala console (see Using the Scala console with Play and Slick). First, add the case class:

scala> import org.joda.money.Money
import org.joda.money.Money

scala> case class Cocktail(id: Option[Long], name: String, price: Money)
defined class Cocktail

Now add the database table mapping, including the mapRow and unMapRow functions that map between the * projection and a Cocktail case class instance, and the money function that creates a Joda Money instance.

// models.database.Cocktails
import scala.slick.driver.MySQLDriver.simple._
import org.joda.money.{CurrencyUnit, Money}
import java.math.RoundingMode

class Cocktails extends Table[Cocktail]("COCKTAIL") {

  def id = column[Long]("ID", O.AutoInc)
  def name = column[String]("NAME")
  def priceCurrency = column[String]("PRICE_CURRENCY")
  def priceAmount = column[BigDecimal]("PRICE_AMOUNT", O.DBType("DECIMAL(13,3)"))

  def * = id.? ~ name ~ priceCurrency ~ priceAmount <> (mapRow _, unMapRow _)

  private def mapRow(id: Option[Long], name: String, currency: String, amount: BigDecimal) = {
    Cocktail(id, name, money(currency, amount))
  }

  private def unMapRow(cocktail: Cocktail) = {
    val currency = cocktail.price.getCurrencyUnit.getCode
    val amount: BigDecimal = cocktail.price.getAmount
    val tuple = (cocktail.id, cocktail.name, currency, amount)
    Some(tuple)
  }

  private def money(currencyCode: String, amount: BigDecimal): Money = {
    val currency = CurrencyUnit.of(currencyCode)
    Money.of(currency, amount.bigDecimal, RoundingMode.DOWN)
  }
}

You can now see that Slick can generate an SQL insert statement from the table mapping the same way that it generates select statements.

scala> (new Cocktails).insertStatement
res: String = INSERT INTO `COCKTAIL` (`ID`,`NAME`,`PRICE_CURRENCY`,`PRICE_AMOUNT`) VALUES (?,?,?,?)

To actually perform the insert, Slick provides an insert method on the table object, which maps the case class to the table’s default project, populates the bind variables and executes the insert statement. You can add this to your model layer, along with the case class and finder methods:

 // models.Cocktail
package models

import models.database.Cocktails
import play.api.Play.current
import play.api.db.slick.Config.driver.simple._
import play.api.db.slick.DB
import scala.slick.session.Session
import org.joda.money.Money

case class Cocktail(id: Option[Long], name: String, price: Money)

object Cocktail {
  val table = new Cocktails

  def insert(cocktail: Cocktail): Unit = DB.withSession { implicit session: Session =>
    table.insert(cocktail)
  }

  // Finder methods…
}

Note that table.insert(cocktail) returns the number of rows inserted, in this case, but this is not especially useful which is why you don’t return anything - the function’s return type is Unit. In any case, an insert method like this is almost all you need.

Get the inserted database table row’s primary key

In a web application, you might add cocktails to the database as a result of filling in a form on a web page. After inserting a cocktail, it would be typical to redirect the browser to a web page that shows the new cocktail’s details.

Since our cocktail names are not necessarily unique, the easiest way to do this is to use the database primary key in the URL, e.g. /cocktail/42. However, if you use the code above, you won’t have this primary key - the Cocktail object’s id property.

To make the ID available, add another projection to the table definition:

// models.database.Cocktails
def forInsert = * returning id

Now you can change the model layer method to use this projection and return the ID:

// models.Cocktail
def insert(cocktail: Cocktail): Long = DB.withSession { implicit session: Session =>
  table.forInsert.insert(cocktail)
}

If you call this from the web application’s controller, you can now get the cocktail ID and use it to construct the URL for the HTTP redirect

Insert using a tuple instead of a case class

You don’t always need to use a mapped table, in which case you insert tuples instead of case class instances. Suppose you extend our application to record cocktails that are similar to each other, by adding a link table:

// models.database.Similarities
package models.database
import play.api.db.slick.Config.driver.simple._

class Similarities extends Table[(Long, Long)]("SIMILARITY") {
  def firstId = column[Long]("FIRST_COCKTAIL_ID")
  def secondId = column[Long]("SECOND_COCKTAIL_ID")
  def * = firstId ~ secondId
  def pk = primaryKey("pk_myTable2", firstId ~ secondId)
}

You could make a case class for a similarity', to insert, but this is simple enough to handle two cocktail IDs directly. In your model, pass the column values as arguments to the `insert function:

// models.Cocktail
def link(firstId: Long, secondId: Long): Unit = DB.withSession { implicit session: Session =>
  (new Similarities).insert(firstId, secondId)
}

If there were more columns, then you would pass more arguments to insert.

Insert multiple rows using JDBC batch insert

The final variation is to insert multiple rows. You probably don’t need this as much, because it is less common to insert multiple rows to the same table in one operation. In our example, perhaps you want to make the links bi-directional. To do this, pass two tuples to the table’s insertAll function - one for each link:

// models.Cocktail
def link(firstId: Long, secondId: Long): Unit = DB.withSession { implicit session: Session =>
  (new Similarities).insertAll(firstId -> secondId, secondId -> firstId)
}

Like the insertAll function accepts a variable number of arguments, like insert, but this time each one is a tuple that represents a whole row instead of a single value. This uses JDBC batch insert, which probably performs better than separate inserts if you are inserting a lot of data.

Next steps

Now you can insert data, you can:

define join queries
define aggregate and group-by queries
update and delete data.