Scala, play - cannot insert into Postgresql database

Question

I have a simple scala and play code for inserting product into database.

My database config in application.conf looks like:

db.default.hikaricp.connectionTestQuery = "SELECT 1"

db.default.driver=org.postgresql.Driver
db.default.url="jdbc:postgresql://localhost:5432/shop"
db.default.user="postgres"
db.default.password="root"

Table definition and crud operations:

case class Product(id: Long, name: String, description: String, price: BigDecimal, amount: Int)

case class ProductFormData(name: String, description: String, price: BigDecimal, amount: Int)

object ProductForm {

  val form = Form(
    mapping(
      "name" -> nonEmptyText,
      "description" -> nonEmptyText,
      "price" -> bigDecimal,
      "amount" -> number
    )(ProductFormData.apply)(ProductFormData.unapply)
  )
}

class ProductTableDef(tag: Tag) extends Table[Product](tag, "product") {

  def id = column[Long]("id", O.PrimaryKey, O.AutoInc)

  def name = column[String]("name")

  def description = column[String]("description")

  def price = column[BigDecimal]("price")

  def amount = column[Int]("amount")

  override def * =
    (id, name, description, price, amount) <> (Product.tupled, Product.unapply)
}

object Products {

  val products = TableQuery[ProductTableDef]

  private def db: Database = Database.forDataSource(DB.getDataSource())

  def add(product: Product): Future[Int] = {
    try db.run(products += product)
    finally db.close
  }

  def delete(id: Long): Future[Int] = {
    db.run(products.filter(_.id === id).delete)
  }

  def get(id: Long): Future[Option[Product]] = {
    db.run(products.filter(_.id === id).result.headOption)
  }

  def listAll: Future[Seq[Product]] = {
    db.run(products.result)
  }
}

service:

object ProductService {
  def addProduct(product: Product): Future[Int] = {
    Products.add(product)
  }
}

and controller:

def create() = Action(parse.json) { request =>
    val name = (request.body \ "name").as[String]
    val description = (request.body \ "description").as[String]
    val price = (request.body \ "price").as[BigDecimal]
    val amount = (request.body \ "amount").as[Int]

    val product = Product(0, name, description, price, amount)
    ProductService.addProduct(product)

    Ok("name : " + product.name)
  }

Everything looks good, no errors in process (I use postman, creating json and send it to server). But after all there is no data in databse. Even table is not created in database. I really don't know why this cannot be add to database.

EDIT:

create table "Product" ("id" BIGSERIAL NOT NULL PRIMARY KEY,"name" VARCHAR(254) NOT NULL,"description" VARCHAR(254) NOT NULL,"price" Decimal, "amount" BIGINT NOT NULL);

This is a script which I use to create table manually, then I try to save data frm request into database. From request everything is read fine (object Product is created) but no data still is safe into database.

EDIT 2:

case class Product(id: Option[Long], name: String, description: String, price: BigDecimal, amount: Int)

class ProductTableDef(tag: Tag) extends Table[Product](tag, "product") {

  def id = column[Long]("id", O.PrimaryKey, O.AutoInc)

  def name = column[String]("name")

  def description = column[String]("description")

  def price = column[BigDecimal]("price")

  def amount = column[Int]("amount")

  override def * =
    (id.?, name, description, price, amount) <> (Product.tupled, Product.unapply)
}

I updated models and dao with Option auto increment field, but It didn't help.

Answer 1

Play action in controller asynchronous by default. So rendering finished before call to database finished. Call to database is a slow operation and this is condisidered as side effect: network + IO operation. Simple way to check this is to put next code before rendering:

 Thread.sleep(2000)

What you actually should do is something like:

def add = Action.async( parse.json(userReads) ) { request =>
    val results = userRepo.insert(  UserData( None, request.body.name, request.body.note ) )
    results.map(_ => Ok("done") )

For creating required tables you should use something like:

val setup = DBIO.seq(
  // Create the tables, including primary and foreign keys
  (suppliers.schema ++ coffees.schema).create,


  //
)

val setupFuture = db.run(setup)

Here is slick api documentation: http://slick.lightbend.com/doc/3.0.0/gettingstarted.html#schema

Not sure there you will place this logic in you webApp

Try to see SQL generated by Slick:

Update you method accordingly def add(product: Product): Future[Int] = { val action = products += product

   val sql = action.result.statements.toString()

   // this is SQL query which slick will try run against postGreed 
   // you should be able to run it manually from SQL console to see reason why this failing 
   println(sql)

db.run( action )

}

Answer 2

Finally it works. I changed my add function into:

 def add(product: Product): Unit = {
    try {
      Await.result(db.run(DBIO.seq(
        products.schema.create,
        products += (product),
        products.result.map(println))), Duration.Inf)
    } finally db.close
  }

and now schema is created and data is added into database.