In Scala, how can I get Play's Models and Forms to play nicely with Squeryl and PostgreSQL?

Question

I'm currently reading Manning's Play for Scala, and playing with code as I do so.

I'm finding that having a Long "id" field on my Product model seems to get in the way of form submission to create the new Product, resulting in the following error:

    Execution exception
    [RuntimeException: Exception while executing statement : ERROR: relation "s_products_id" does not exist
      Position: 123
    errorCode: 0, sqlState: 42P01
    insert into "products" ("id", "name", "description", "is_active", "pieces", "embedded_video_code", "ean") values (nextval('"s_products_id"'),?,?,?,?,?,?)
    jdbcParams:[Purple Paperclips ,Luscious,true,100,null,1234567890242]]

In D:\Tutorials\Workspaces\Scala\Play2Paperclips\app\util\products\ProductSquerylHelper.scala:73
    70  def insert(product: Product): Product = inTransaction
    71  {
    72    val defensiveProductCopy = product.copy()
    73    productsTable.insert(defensiveProductCopy)
    74  }
    75
    76  def update(product: Product)          = inTransaction { productsTable.update(product)    }
    77  def delete(product: Product)          = inTransaction { productsTable.delete(product.id) }
    78

And, if I try to make the id field Option[Long], I start getting errors like

Cannot prove that models.Product<: <org.squeryl.KeyedEntity[Some[Option[Long]]].

What is the best way to enable this form to work as intended?

@* \app\views\products\edit.scala.html *@
@(productForm: Form[Product])(implicit flash: Flash, lang: Lang)
@import helper._
@import helper.twitterBootstrap._
@main(Messages("products.form")) {
    <h2>@Messages("products.form")</h2>

    @helper.form(action = routes.Products.save()) {
        <fieldset>
            <legend>
                @Messages("products.details", Messages("products.new"))
            </legend>
            @helper.inputText(productForm("ean"))
            @helper.inputText(productForm("name"))
            @helper.textarea(productForm("description"))
            @helper.inputText(productForm("pieces"))
            @helper.checkbox(productForm("isActive"))

        </fieldset>
        <p><input type="submit" class="btn primary" value='@Messages("products.new.submit")'></p>
    }
}

This is the model:

    case class Product (
                     id                 : Long,
                     ean                : Long,           // ean: International/[E]uropean [A]rticle [N]umber
                     name               : String,
                     description        : String,
                     pieces             : Int,

                     @Column("is_active")
                     isActive           : Boolean,

                     @Column("embedded_video_code")
                     embeddedVideoCode  : Option[String]  // See http://squeryl.org/schema-definition.html
                     ) extends KeyedEntity[Long]
{
  def this(id : Long, ean : Long, name : String, description : String) = this(id, ean, name, description, 0, false, None)

  lazy val stockItems: OneToMany[StockItem] = Database.productToStockItemsRelation.left(this)
}

This is the Form, as well as mapping with apply and unapply methods:

    object ProductFormHelper
{
  // -------------------------------------------------------------------

  val productForm: Form[Product] = Form(productFormMapping)

  private def productFormMapping = mapping (
    "id"                -> optional(longNumber),
    "ean"               -> longNumber.verifying("validation.ean.duplicate", ProductDAO.findByEan(_).isEmpty),
    "name"              -> nonEmptyText,
    "description"       -> nonEmptyText,
    "pieces"            -> number,
    "isActive"          -> boolean,
    "embeddedVideoCode" -> optional(text)
  ) (productFormApply) (productFormUnpply)

  private def productFormApply(
                                id                 : Option[Long],
                                ean                : Long,           // ean: International/[E]uropean [A]rticle [N]umber
                                name               : String,
                                description        : String,
                                pieces             : Int,
                                isActive           : Boolean,

                                embeddedVideoCode  : Option[String]  // See http://squeryl.org/schema-definition.html
                                )  =
  {
    val productId = id match
    {
      case Some(long) => long
      case None       => -1L
    }

    Product.apply(productId, ean, name, description, pieces, isActive, embeddedVideoCode)
  }

  private def productFormUnpply(product: Product) =
  {
    Option(Some(product.id), product.ean, product.name, product.description, product.pieces, product.isActive, product.embeddedVideoCode)
  }

}

Here is my Controller's save method:

def save = Action
  {
    implicit request =>
    {
      val newProductForm = ProductFormHelper.productForm.bindFromRequest()
          newProductForm.fold(
            hasErrors =
              {
                form => Redirect(routes.Products.newProduct()).flashing(Flash(form.data) + ("error" -> Messages("validation.errors")))
              },

            success =
              {
                newProduct =>
                {
                                    val insertedProduct = ProductDAO.insert(newProduct)
              val message = Messages("products.new.success", insertedProduct.name)
              Redirect(routes.Products.showByEan(insertedProduct.ean)).flashing("success" -> message)
                }
            }
          )
    }
  }

Here is my insert method:

      def insert(product: Product): Product = inTransaction
  {
    val defensiveProductCopy = product.copy()
    productsTable.insert(defensiveProductCopy)
  }

And this is the Database Schema:

 object Database extends Schema
{
  val productsTable   : Table[Product]   = table[Product]  ("products")
  on(productsTable)   { product   => declare{product.id   is (autoIncremented)}}

}

score 2 · Answer 1 · edited May 23 '17 at 12:34

Seems like you've resolved your issue already, but for posterity, the issue is that Squeryl assumes that a Long id field is an auto-incremented primary key, and with PostgreSQL it will use a sequence to retrieve the id value before inserting the row. There are two general fixes you could make here:

You don't want it to be an auto-incremented value. Disable the key generation in your schema with

on(productsTable) { product => declare{product.id is (primaryKey)}}

Explicitly specifying that the id field is a primary key without explicitly specifying that it's auto-incremented will disable generation of values

You want an auto-incremented value. There are actually two solutions. First, you can create the s_product_id sequence, which Squeryl will do for you if you use it's schema generation tool. If that name doesn't suit your conventions, then you can also specify a different naming convention for sequences either field by field or for every sequence by subclassing PostgreSqlAdapter and overriding

def createSequenceName(fmd: FieldMetaData)

Cheers for the response. For now, I think I'll stick with the solution I came up with (i.e. "It works, so I won't fix it") but if I find a need to work specifically with Squeryl (as opposed to Anorm or something else) in the future, I'll bear your response in mind. :-) — Brian Kessler, Mar 10 '15 at 09:40

score 0 · Accepted Answer · answered Mar 05 '15 at 14:08

Since nobody seems to know or care how to get Squeryl to work (or I'm just not patient or loyal enough to the ORM), I figured out how to get this to work with Anorm instead:

// In ProductDAO.scala
          def insert(product: Product): Option[Product] =
      {
        ProductAnormHelper.insert(product) match
        {
          case Some(insertedProduct) =>
          {
            Cache.set("product-" + product.id, insertedProduct)
            Some(insertedProduct)
          }

          case None => { None }
        }
      }

// In ProductAnormHelper.scala

      def insert(product:Product): Option[Product] =
      {
        insertAndReturnId(product) match
        {
          case Some(productId) => Some(Product(productId, product.ean, product.name, product.description, product.pieces, product.isActive, product.embeddedVideoCode))
          case None            => None
        }
      }

      def insertAndReturnId (product: Product): Option[Long] = DB.withConnection
      {
        implicit connection =>
        {
          SQL( """INSERT INTO products (ean,   name,   description,   pieces,  is_active,  embedded_video_code)
            |VALUES              ({ean}, {name}, {description}, {pieces}, {isActive}, {embeddedVideoCode})
            |""".stripMargin).on(
              //"id"                -> product.id,
            "ean"                 -> product.ean,
            "name"                -> product.name,
            "description"         -> product.description,
            "pieces"              -> product.pieces,
            "isActive"            -> product.isActive, "embeddedVideoCode"   -> product.embeddedVideoCode
          ).
            executeInsert()
        }
      }

In Scala, how can I get Play's Models and Forms to play nicely with Squeryl and PostgreSQL?

2 Answers2