Anorm, Rest-API

Integrating Anorm with an MySQL database in a Play Framework project involves configuring your database connection, setting up the database evolutions, and adjusting your application code to interact with the database. Anorm is a simple data access layer that uses plain SQL to interact with the database, making it a flexible choice for Scala developers. Here’s how you can set it up:

Step 1: Add Dependencies

First, ensure you have the necessary dependencies in your build.sbt file. You'll need the Play Slick dependency and the H2 database driver for local development. If not already included, add them like so:

libraryDependencies ++= Seq(
  guice,
  jdbc,
  "mysql" % "mysql-connector-java" % "8.0.33",
  "org.playframework.anorm" %% "anorm" % "2.7.0",
  "org.scalatestplus.play" %% "scalatestplus-play" % "7.0.1" % Test,
)

Replace version numbers with the latest versions suitable for your project setup.

Step 2: Configure the Database

In the conf/application.conf file, configure your MySQL database connection:

db.default.driver=com.mysql.cj.jdbc.Driver
db.default.url="jdbc:mysql://localhost:3306/todo"
db.default.user="root"
db.default.password= "mysql"

Setup the database connection pool: hikaricp

# Number of database connections
# See https://github.com/brettwooldridge/HikariCP/wiki/About-Pool-Sizing
fixedConnectionPool = 9

play.db {
  prototype {
    hikaricp.minimumIdle = ${fixedConnectionPool}
    hikaricp.maximumPoolSize = ${fixedConnectionPool}
  }
}

# Job queue sized to HikariCP connection pool
database.dispatcher {
  executor = "thread-pool-executor"
  throughput = 1
  thread-pool-executor {
    fixed-pool-size = ${fixedConnectionPool}
  }
}

Create the DatabaseExecutionContext

package models

import org.apache.pekko.actor.ActorSystem
import play.api.libs.concurrent.CustomExecutionContext
import javax.inject.*
/**
 * This class is a pointer to an execution context configured to point to "database.dispatcher"
 * in the "application.conf" file.
 */
@Singleton
class DatabaseExecutionContext @Inject()(system: ActorSystem) extends CustomExecutionContext(system, "database.dispatcher")

Step 3: Create database and table

It uses a todo database, which we'll have to create ourselves inside MySQL And a todo table has is created

create database todo;
use todo;       

create table todo ( 
    id bigint auto_increment primary key, 
    task varchar(255)  
);

Step 4: Interacting with the Database Using Anorm

Here we create a class Todo and the database service TodoRepository

package models

import javax.inject.Inject
import scala.util.{ Failure, Success }
import anorm._
import anorm.SqlParser.{ get, str }
import play.api.db.DBApi
import scala.concurrent.Future

case class Todo(id: Option[Long] = None, task: String)

object Todo {
  implicit def toParameters: ToParameterList[Todo] =
    Macro.toParameters[Todo]

  def unapply(c: Todo): Option[(Option[Long], String)]= Some((c.id, c.task))
}

@javax.inject.Singleton
class TodoRepository @Inject()(dbapi: DBApi)(implicit ec: DatabaseExecutionContext) {
  private val db = dbapi.database("default")
  
  private[models] val simple = {
    get[Option[Long]]("todo.id") ~ str("todo.task") map {
      case id ~ name => Todo(id, name)
    }
  }
  
  def list(): Future[List[Todo]] = Future {
    db.withConnection { implicit connection =>
      SQL"SELECT * FROM todo".as(simple.*)
    }
  }
  
  def create(todo: Todo): Future[Option[Long]] = Future {
    db.withConnection { implicit connection =>
      SQL("""insert into todo values ( {id}, {task})""")
        .bind(todo).executeInsert()
    }
  }(ec)
}

Step 5: HomeController Implementation

In your Play application under the controllers directory, create a file named HomeController.scala:

package controllers
import models.{Todo, TodoRepository}
import javax.inject.*
import play.api.*
import play.api.libs.json.{Json, OFormat}
import play.api.mvc.*
import scala.concurrent.ExecutionContext

@Singleton
class HomeController @Inject()(todoService: TodoRepository,
                               cc: MessagesControllerComponents)(implicit ec: ExecutionContext)
  extends MessagesAbstractController(cc) {
  
  implicit val toDoFormat: OFormat[Todo] = Json.format[Todo]

  def list: Action[AnyContent] = Action.async { implicit request =>
    todoService.list().map { todos =>
      Ok(Json.toJson(todos))
    }
  }

  def create: Action[AnyContent] = Action.async { implicit request =>
    val json = request.body.asJson.get
    val todoItem = json.as[Todo]
    todoService.create(todoItem).map { id =>
      Ok(Json.obj("id" -> id))
    }
  }
}

routes

# Routes
# This file defines all application routes (Higher priority routes first)
# ~~~~
GET     /todos                      controllers.HomeController.list()
POST     /todos                     controllers.HomeController.create()

Step 6: Running Your Application

Start your Play application using sbt:

sbt run

Step 7: Test with cURL

Test the todo app with cURL

POST

curl -X POST http://localhost:9000/todos \
-H "Content-Type: application/json" \
-d '{"task":"programming"}'

GET

curl -H "Accept: application/json" http://localhost:9000/todos

Result:

[
  {
    "task":"programming",
    "id":1
  }
]