Adding Database Persistence

In this section of the tutorial, we will add a database to our application to store the RA/Dec coordinates entered in the UI instead of the local list variable in the backend. We will be using postgres to persist our data. We will be using the Jooq DSL to write our queries, which is packaged in the CSW Database Service.

First, we will update our backend server to use the database, then we will set up the database itself, and then we will run the application.

Update backend Implementation

First, we have to make the CSW Database Service accessible in our project.
Add a csw-database dependency in project/Libs.scala

Scala

val `csw-database` = "com.github.tmtsoftware.csw" %% "csw-database" % "3.0.1"

Use the csw-database dependency in your build.sbt file and reload the project in your IDE (in IntelliJ, this can be done from the sbt tab, typically on the right side of the IDE).

Scala

libraryDependencies ++= Seq(
  Libs.`esw-http-template-wiring` % "compile->compile;test->test",
  Libs.`csw-database`, // <---
  Libs.`embedded-keycloak` % Test,

Create a Database access class

Now we can implement our database access code. Go to the impl package and add a repository class RaDecRepository.scala using the DSL context provided by JOOQ as part of the CSW Database Service. This will be constructed and injected later in our wiring.

Scala

class RaDecRepository(dsl: DSLContext)(implicit ec: ExecutionContext) {
}

Add a method to this class to insert data into the DB. The query method of the DSLContext is used to construct an SQL INSERT statement to insert our formatted RA/Dec strings along with a UUID. The CSW Database Service provides an asynchronous execute method, which returns a negative value on error. Since the insert is done asynchronously, this method returns a Future.

Scala

def insertRaDec(formattedRa: String, formattedDec: String): Future[String] = {
  val id = UUID.randomUUID().toString
  dsl
    .query(s"INSERT INTO RaDecValues (id,formattedRa,formattedDec) values (?,?,?)", id, formattedRa, formattedDec)
    .executeAsyncScala()
    .map {
      case x if x < 0 => throw new RuntimeException(s"Failed to insert the (ra ,dec) value ($formattedRa, $formattedDec )")
      case _          => id
    }
}

We will similarly add a method to get data from the DB:

Scala

def getRaDecValues: Future[scala.List[RaDecResponse]] =
  dsl.resultQuery("SELECT * from RaDecValues").fetchAsyncScala[RaDecResponse]

Add the necessary imports. It should look something like this:

Scala

import csw.database.scaladsl.JooqExtentions.{RichQuery, RichResultQuery}
import org.jooq.DSLContext
import org.tmt.sample.core.models.RaDecResponse

import java.util.UUID
import scala.concurrent.{ExecutionContext, Future}

Update backend service implementation

Update RaDecImpl.scala to inject the repository dependency. We will also need an implicit execution context curried into this class since our RaDecRepository class requires one.

Scala

class RaDecImpl(raDecRepository: RaDecRepository)(implicit ec: ExecutionContext) extends RaDecService {

Update the raDecToString implementation in RaDecImpl.scala to use the insert query method instead the locally stored list.

Scala

override def raDecToString(raDecRequest: RaDecRequest): Future[RaDecResponse] = {
  val formattedRa  = Angle.raToString(raDecRequest.raInDecimals)
  val formattedDec = Angle.deToString(raDecRequest.decInDecimals)
  raDecRepository.insertRaDec(formattedRa, formattedDec).map(id => RaDecResponse(id, formattedRa, formattedDec))
}

Update the getRaDecValues implementation in RaDecImpl.scala to use the get values query method.

Scala

override def getRaDecValues: Future[scala.List[RaDecResponse]] =
  raDecRepository.getRaDecValues

References to the locally stored list can now be deleted.

Update wiring

Now we need to put everything together by updating the wiring.

First, create the DB setup in SampleWiring.scala

Scala

private lazy val databaseServiceFactory = new DatabaseServiceFactory(actorRuntime.typedSystem)
private val dbName                      = settings.config.getString("dbName")
private val dbUsernameHolder            = settings.config.getString("dbUsernameHolder")
private val dbPasswordHolder            = settings.config.getString("dbPasswordHolder")
private lazy val dslContext: DSLContext =
  Await.result(
    databaseServiceFactory.makeDsl(cswServices.locationService, dbName, dbUsernameHolder, dbPasswordHolder),
    10.seconds
  )

Here you can see the database name, username, and password are obtained from the application configuration. These value are used to create the JOOQ DSLContext passed into our repository class. Let’s instantiate the repository, passing in our DSL context, and then update the implementation reference to receive the repository.

Scala

import actorRuntime.ec
lazy val repository = new RaDecRepository(dslContext)
lazy val raDecImpl  = new RaDecImpl(repository)

We now need to update our application configuration file with the database configuration. Edit application.conf in the src/main/resources folder.

Scala

http-server {
  prefix: "ESW.sample"
  dbName: postgres
  dbUsernameHolder: DB_USERNAME
  dbPasswordHolder: DB_PASSWORD
}

The database username and password are obtained from the environment variables DB_USERNAME and DB_PASSWORD respectively. We will set these variables later before we run our backend.

Run the new application

A PostgreSQL database is used in our application. It must first be installed and then initially configured to work with our application.

Database setup

Follow the installation guide to download and install PostgreSQL on your machine, if not already installed. Link

At this point, we will re-run csw-services with the CSW Database Service enabled. This will run an PostgreSQL instance that we can then configure.

The following instructions show how to run the Database Service along with the Location Service and the Authentication and Authorization Service:

cs install csw-services:v4.0.0-M1
csw-services start -k -d

Login to postgres with your default user and create a new user to be used with our application. The example code below sets up a user “postgres” with a password “postgres”, but you can use different credentials if you prefer.

psql -d postgres
postgres => CREATE USER postgres with password 'postgres';
postgres => \q
psql -d postgres -U postgres

For this application, we will create an RADECVALUES table in the database that the application can use to perform fetch and insert queries. The following commands can be used to create a table

postgres =>
CREATE TABLE RADECVALUES(
id TEXT             PRIMARY KEY     NOT NULL,
formattedRa TEXT                    NOT NULL,
formattedDec TEXT                    NOT NULL
);

As mentioned above, we depend on environment variables to pick up your username and password for the database, thus DB_USERNAME and DB_PASSWORD need to be set to the credentials you provided above.

To set environment variables, use the command

export DB_USERNAME=<VALUE> DB_PASSWORD=<VALUE>

Now, we are ready to run the backend application:

sbt:backend> run start

Test your application either with the UI or by using apptest.http as described in previous tutorials, and verify the data is saved in your postgres table. This can be done in psql using the TABLE command:

postgres => TABLE RADECVALUES