CODEWITHSUNDEEP
scalah2slick
javito
javito

Reputation: 1529

Slick 3.1.1: Insert entity with its relations in a single transaction

I have an entity Classified that has 2 relations: a Company and a Location. In order to persist the classified I need to know the ids of its relations which might require persisting an entity first (that is it might already exist in the database otherwise it should be inserted).

The ids are UUIDs assigned by the application (i.e. it's not autoincremented by the db) so the app assigns an id which will either end up being the entity id or be replaced by the actual id in the transaction if the entity already exists.

The code that does this is as follows:

def create(classified: Classified, company: Company, location: Location): Future[String] = {
val interaction = for {
  comp <- companies.filter(_.name === company.name).result.headOption flatMap {
    case None => companies returning companies.map(_.id) += company
    case Some(comp) => DBIO.successful(comp.id.get)
  }
  loc <- locations.filter(_.name === location.name).result.headOption flatMap {
    case None => locations returning locations.map(_.id) += location
    case Some(loc) => DBIO.successful(loc.id.get)
  }
  cl <- classifieds returning classifieds.map(_.id) += classified.copy(companyId = comp, locationId = loc)
} yield cl
db.run(interaction.transactionally)

}

The above works perfectly when ran against Postgres (which is the production database) but fails for H2 (which is the test and dev database) with the error: [SlickException: This DBMS allows only a single AutoInc column to be returned from an INSERT]

It looks like the H2 driver does not return ids unless they are of the autoincrement variety.

So, how can this transaction be written so that a) inserts happen in a single transaction b) with minimal db roundtrips and c) in a database neutral way ?

EDIT:

The above method is used from a controller like so:

classifiedDao.create(
      Classified(Some(UUID.randomUUID().toString), c.title, Jsoup.clean(c.body, Whitelist.basic()), c.refNo, "", ""),
      Company(Some(UUID.randomUUID().toString), c.companyName, c.companyEmail, None, None),
      Location(Some(UUID.randomUUID().toString), c.location, None)
    ).map(_ =>
      Redirect(routes.Classifieds.form()).flashing("success" -> "Classified submitted")
    )

Upvotes: 2

Views: 513

Answers (2)

javito
javito

Reputation: 1529

It turns out that retrieving the id from the db is the easy part since the code that does this already has the required type (DBIOAction), the hard part was getting the id after the insert but in that case I already knew the id because it's my code that set it. There's no need to use returning() and rely on the RDBMS.

Solution:

def create(classified: Classified, company: Company, location: Location): Future[String] = {
val interaction = for {
  comp <- companies.filter(_.name === company.name).result.headOption flatMap {
    case None => {
      companies += company
      DBIO.successful(company.id.get)
    }
    case Some(comp) => DBIO.successful(comp.id.get)
  }
  loc <- locations.filter(_.name === location.name).result.headOption flatMap {
    case None => {
      locations += location
      DBIO.successful(location.id.get)
    }
    case Some(loc) => DBIO.successful(loc.id.get)
  }
  cl <- {
    classifieds += classified.copy(companyId = comp, locationId = loc)
    DBIO.successful(classified.id.get)
  }
} yield cl
db.run(interaction.transactionally)
}

Upvotes: 1

Nagarjuna Pamu
Nagarjuna Pamu

Reputation: 14825

For Slick returning to work Id has to be auto generated primary key

Id should not only be primary key but also auto increment id.

Note

Many database systems only allow a single column to be returned which must be the table’s auto-incrementing primary key. If you ask for other columns a SlickException is thrown at runtime (unless the database actually supports it).

So write a function which returns Id after inserting in the database like this.

def getIdAfterInsert(entity: Entity): DBIO[EntityId] = {
  (entities += entity).flatMap { _ =>
    entities.filter(_.name === entity.name).result.flatMap {
      case Some(entity) => DBIO.successful(entity.id)
      case None => DBIO.fail(new Exception("something terrible happened"))
    }
  }.transactionally
}

Upvotes: 0

Related Questions