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.

Have you read up on how Evolutions work with Play? playframework.com/documentation/2.5.x/Evolutions — sheunis
– sheunis, Commented May 27, 2017 at 12:05
Yes, but my code is created with some tutorials where evolutions were not used. Can you explain what should I add instead of alone "evolutions" into build.sbt to trigger queries? — Developus
– Developus, Commented May 27, 2017 at 12:13
In your question you stated "Even table is not created in database.". Without Evolutions the table won't be created for you. You need to manually create the table in your postgres db using a SQL statement. Make sure it corresponds to your ProductTableDef. — sheunis
– sheunis, Commented May 27, 2017 at 12:22
I created manually table "Product" in database but still not data is add there. — Developus
– Developus, Commented May 27, 2017 at 12:36
if you need a tutorial on play+slick have a look at this tutorial I wrote (for play2.4.x) a couple of months ago: pedrorijo.com/blog/play-slick the code is available at Github: github.com/pedrorijo91/play-slick3-steps/tree/step3 and there's even a play2.5.x branch: github.com/pedrorijo91/play-slick3-steps/tree/play2.5 hope it helps — pedrorijo91
– pedrorijo91, Commented May 27, 2017 at 19:58

Pavel · Accepted Answer · 2017-05-27 13:58:00Z

2

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 )

}

edited May 27, 2017 at 13:58

answered May 27, 2017 at 12:48

Pavel

1,53522 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

17 Comments

Developus Over a year ago

Thanks, but did you create a table manually or something? It works for you when you added Thread.sleep()?

Pavel Over a year ago

Why you are expecting table to be created? Where logic for this ?? This should be done by other logic if you have some or manually. I would suggest flyway migration: flywaydb.org works good for me and simple to use as project evolves.

Developus Over a year ago

I do not expect. I think it should be automatically created after I trigger insert function.

Pavel Over a year ago

Well, there is an option to see SQL generated by Slick: could be printed to terminal this way: val sql = query.result.statements.toString() println(sql) I will update my answer for you examples

Pavel Over a year ago

Please update you question OR post new answer to close this thread. Thanks

|

Developus · Accepted Answer · 2017-05-27 14:34:29Z

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.

answered May 27, 2017 at 14:34

Developus

1,4622 gold badges18 silver badges59 bronze badges

1 Comment

Pavel Over a year ago

just general advise to be careful with Await.result , but as long as it works, its good! More reading about Await.results: docs.scala-lang.org/overviews/core/futures.html

Collectives™ on Stack Overflow

Scala, play - cannot insert into Postgresql database

2 Answers 2

17 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

17 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related