Database Service

The Database Service is included in TMT Common Software for use by components that need the features of a relational database. The CSW Database Service provides a TMT-standard relational database and connection library. Databases created by the Database Service will be stored reliably at the site during operations.

The Database Service provides an API to manage database connections and access data in the TMT Software System. The service provides PostgreSQL as the underlying database server. It uses the Jooq library underneath to manage database access, connection pooling, etc.

The Database Service requires PostgreSQL server to be running on a machine. To start the PostgreSQL server for development and testing purposes, refer to Starting Apps for Development.

cannot create children while terminating or terminated
Failed to start Database Service!

systemctl status postgresql
systemctl disable postgresql

initdb $PGDATA -E utf8

CREATE USER <username>;
ALTER USER <username> WITH PASSWORD '<mypassword>';

Once the PostgreSQL database is up and running, the Database Service can be used to connect and access data. It is assumed that there will be more than one user type registered with PostgreSQL i.e. for read access, for write access, for admin access, etc.

Dependencies

To include the Database Service in a component, add this to your build.sbt file:

sbt

libraryDependencies += "com.github.tmtsoftware.csw" %% "csw-database" % "5.0.1"

Accessing Database Service

The Database Service is accessed differently than other CSW services in that it is not passed to a component through CswContext/JCswContext in the component’s ComponentHandlers. To access Database Service, developers create a DatabaseServiceFactory. A DatabaseServiceFactory can be created anywhere in the code using an ActorSystem and its creation is explained in next section.

Connect with Read Access

Our access approach is that all components can read any Database Service database, and clients that only need read access use the following factory method. However, a writer will need a special username/password with write access as shown below.

By default while connecting to PostgreSQL database, the Database Service will provide read access for data. To achieve that, create an instance of DatabaseServiceFactory and use it as shown below:

Scala

copysourceval dbFactory = new DatabaseServiceFactory(ctx.system)

dbFactory
  .makeDsl(locationService, "postgres")         // postgres is dbName
  .foreach((dsl: DSLContext) => this.dsl = dsl) // save returned dsl to a local variable

Java

copysourcedbFactory = new DatabaseServiceFactory(ctx.getSystem());

dbFactory
        .jMakeDsl(cswCtx.locationService(), "postgres") // postgres is dbName
        .thenAccept((DSLContext dsl) -> this.dsl = dsl);        // save the returned dsl to a local variable

The underlying database server is registered with the Location Service. makeDsl/jMakeDsl uses locationService to locate the PostgreSQL server running and connect to it. It connects to the database by the provided dbName. It picks the database username and password for read access profile from TMT-standard environment variables called DB_READ_USERNAME for username and DB_READ_PASSWORD for password, hence it is expected that developers will set these environment variables prior to using DatabaseServiceFactory. PostgreSQL should also be initialized with a read-only user and password that agrees with the values in the environment variables. This approach is used to keep from putting database login information in the source code.

makeDsl/jMakeDsl returns a Jooq type DSLContext. DSLContext provides the mechanism to access the data stored in PostgreSQL using the selected JDBC driver underneath. The usage of DSLContext in component development will be explained in later sections.

Connect with Write Access

In order to connect to PostgreSQL for write access (or any other access other than read), use the DatabaseServiceFactory as shown below with different environment variables:

Scala

copysourcedbFactory
  .makeDsl(locationService, "postgres", "DB_WRITE_USERNAME", "DB_WRITE_PASSWORD")
  .foreach((dsl: DSLContext) => this.dsl = dsl) // save returned dsl to a local variable

Java

copysourcedbFactory
        .jMakeDsl(cswCtx.locationService(), "postgres", "DB_WRITE_USERNAME", "DB_WRITE_PASSWORD")
        .thenAccept((DSLContext dsl) -> this.dsl = dsl);        // save the returned dsl to a local variable

Here the username and password for write access is picked from environment variables. e.g. - IRIS_DB_WRITE_USERNAME & IRIS_DB_WRITE_PASSWORD. Hence, it is expected from developers to set environment variables prior to using this method with the user name and password to use for write access.

Connect for Development or Testing

For development and testing purposes, all database connection properties can be provided from application.conf including username and password. This will not require setting any environment variables for credentials as described in previous sections. In order to do so, use the DatabaseServiceFactory as shown below:

Scala

copysourcedbFactory
  .makeDsl()
  .foreach((dsl: DSLContext) => this.dsl = dsl) // save returned dsl to a local variable

Java

copysourcedbFactory
        .jMakeDsl()
        .thenAccept((DSLContext dsl) -> this.dsl = dsl);        // save the returned dsl to a local variable

The reference for providing database properties is shown below:

csw-database.hikari-datasource {
  dataSourceClassName = org.postgresql.ds.PGSimpleDataSource
  dataSource {
    serverName = <server_name>
    portNumber = <port_number>
    databaseName = <database_name>
    user = <username>
    password = <password>
  }
  
  // Below are the default properties of HikariCP
  autoCommit = true
  connectionTimeout = 30000 (30 seconds)
  idleTimeout = 600000 (10 minutes)
  maxLifetime = 600000 (10 minutes)
  maximumPoolSize = 10
  minimumIdle = 10 (same as max pool size)
}

In order to override any property shown above, it needs to be defined in application.conf. For example. a sample application.conf can look as follows:

csw-database.hikari-datasource.dataSource {
  serverName = localhost
  portNumber = 5432
  databaseName = postgres
  user = postgres
  password = postgres
}

Using DSLContext

Once the DSLContext is returned from makeDsl/jMakeDsl, it can be used to provide plain SQL to the Database Service and get it executed on the PostgreSQL server.

The following sections show examples of most typical SQL use cases.

Create

To create a table, use the DSLContext as follows:

Scala

copysourceval createQuery: Query = dsl.query("CREATE TABLE films (id SERIAL PRIMARY KEY, Name VARCHAR (10) NOT NULL)")

import csw.database.scaladsl.JooqExtentions.RichQuery
val createResultF: Future[Integer] = createQuery.executeAsyncScala()
createResultF.foreach(result => println(s"Films table created with $result"))

Java

copysourceQuery createQuery = dsl.query("CREATE TABLE films (id SERIAL PRIMARY KEY, Name VARCHAR (10) NOT NULL)");
CompletionStage<Integer> createResultF = createQuery.executeAsync();
createResultF.thenAccept(result -> System.out.println("Films table created with " + result));

Insert

To insert data in a batch, use the DSLContext as follows:

Scala

copysourceval movie_2 = "movie_2"

val queries = dsl.queries(
  dsl.query("INSERT INTO films(name) VALUES (?)", "movie_1"),
  dsl.query("INSERT INTO films(id, name) VALUES (?, ?)", "2", movie_2)
)

import csw.database.scaladsl.JooqExtentions.RichQueries
val batchResultF: Future[List[Int]] = queries.executeBatchAsync()
batchResultF.foreach(results => println(s"executed queries [$queries] with results [$results]"))

Java

copysourceString movie_2 = "movie_2";

Queries queries = dsl.queries(
        dsl.query("INSERT INTO films(name) VALUES (?)", "movie_1"),
        dsl.query("INSERT INTO films(id, name) VALUES (?, ?)", 2, movie_2)
);

CompletableFuture<int[]> batchResultF = JooqHelper.executeBatch(queries);
batchResultF.thenAccept(results ->
        System.out.println("executed queries [" + queries + "] with results [" + Arrays.toString(results) + "]"));

Select

To select data from table, use the DSLContext as follows:

Scala

copysource// domain model
case class Films(id: Int, name: String) // variable name and type should be same as column's name and type in database

// fetch data from table and map it to Films class
val selectQuery = dsl.resultQuery("SELECT id, name FROM films WHERE id = ?", "1")

import csw.database.scaladsl.JooqExtentions.RichResultQuery
val selectResultF: Future[List[Films]] = selectQuery.fetchAsyncScala[Films]
selectResultF.foreach(names => s"Fetched names of films $names")

Java

copysource// domain model
class Films {
    private Integer id;  // variable name (id) and type (Integer) should be same as column's name and type in database
    private String name; // variable name (name) and type (String) should be same as column's name and type in database
}

// fetch data from table and map it to Films class
ResultQuery<org.jooq.Record> selectQuery = dsl.resultQuery("SELECT id, name FROM films WHERE id = ?", 1);
CompletableFuture<List<Films>> selectResultF = JooqHelper.fetchAsync(selectQuery, Films.class);
selectResultF.thenAccept(names -> System.out.println("Fetched names of films " + names));
// Alternatively, you can block and wait for the future result to complete:
// System.out.println("Fetched names of films " + selectResultF.get());

Stored Function

To create a stored function, use the DSLContext as follows:

Scala

copysourceval functionQuery = dsl
  .query(
    """
    |CREATE FUNCTION inc(val integer) RETURNS integer AS $$
    |BEGIN
    |RETURN val + 1;
    |END; $$
    |LANGUAGE PLPGSQL;
    """.stripMargin
  )

val functionResultF: Future[Integer] = functionQuery.executeAsyncScala()
functionResultF.foreach(result => println(s"Function inc created with $result"))

Java

copysourceQuery functionQuery = dsl.query("CREATE FUNCTION inc(val integer) RETURNS integer AS $$\n" +
        "BEGIN\n" +
        "RETURN val + 1;\n" +
        "END; $$\n" +
        "LANGUAGE PLPGSQL;");

CompletionStage<Integer> functionResultF = functionQuery.executeAsync();
functionResultF.thenAccept(result -> System.out.println("Function inc created with  " + result));

Similarly, any SQL queries can be written with the help of DSLContext including stored procedures.

These examples are just a start. Any SQL statement can be created and executed using the DSLContext.

Technical Description

See Database Service Technical Description.

Source code for examples

• Scala Example
• Java Example