Adding Database Persistence

In this section of the tutorial, we will add a database to our backend application to store the RA/Dec coordinates entered through the UI instead of the state variable in the backend. With this change our coordinates will be stored between observing runs, and we won’t lose all our precious coordinates.

This flow also shows how to use the CSW Database Service in a backend and how to pass database query results back to the UI. We will be using the CSW Database Service’s Jooq DSL to write our queries. Under the covers CSW Database Service is using the PostgreSQL relational database to persist our data.

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 the 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

copysourceval `csw-database` = "com.github.tmtsoftware.csw" %% "csw-database" % "4.0.0"

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). Or type reload at the sbt prompt.

Scala

copysourcelibraryDependencies ++= 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 in the backend 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

copysourceclass 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

copysourcedef 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

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

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

Scala

copysourceimport 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

copysourceclass 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

copysourceoverride 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

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

References to the locally stored coordinate list can now be deleted since it is no longer needed.

Update wiring

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

First, create the DB setup in SampleWiring.scala

Scala

copysourceprivate 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 values 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

copysourceimport 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

copysourcehttp-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

CSW Database Service uses the PostgreSQL database, which must be installed for the application to work properly. Once it is installed, the application database must be initially configured with to work with our application.

Database setup

Follow the installation guide to download and install PostgreSQL on your machine, if not already installed Link. See also the CSW documentation. For linux specific troubleshooting refer

At this point, we will re-run csw-services with the CSW Database Service enabled. This will run a 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 using csw-services:

cs install csw-services
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

Now for the application initialization. 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,
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:sample-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 => SELECT * FROM RADECVALUES;