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.

Note

Jooq is a Java library that provides a higher level API for accessing data i.e. DDL support, DML support, fetch, batch execution, prepared statements, safety against sql injection, connection pooling, etc. To know more about Jooq and its features, please refer to this link.

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.

Note

Postgres is started by csw-services and should not be running before that. If it is, you might get this error message:

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

Particularly on Linux systems, make sure that Postgres is not started at boot time, for example with this command:

systemctl status postgresql
systemctl disable postgresql

Also, the Postgres data directory, pointed to by the required PGDATA environment variable, should belong to the user running csw-services (or at least have read/write access). The Linux system version of this directory will normally belong to the Postgres user and so should not be used for the database service. It is recommended to create a data directory especially for use with the Database Service. (Versions installed on MacOS with brew run under the user’s name, so it is not a problem there.)

The Database Service requires a Postgres version of 8.2 or greater (Currently version 13.x). The data directory (pointed to by the PGDATA environment variable) needs to match the Postgres version and can be initialized with the command:

initdb $PGDATA -E utf8

A user can be created by running psql postgres -h localhost and enterring:

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

On MacOS, when Postgres is installed with brew, a Postgres user with your user name is automatically created.

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.

Note

Creating a new DatabaseServiceFactory does not mean a new connection to PostgreSQL server will be created. Database connections are managed in a pool by the underlying Database Service implementation. Hence, creating multiple DatabaseServiceFactory instances per component can be considered pretty cheap and harmless. But it is also possible to save the instance returned by DatabaseServiceFactory and pass it around your component.

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
sourceval 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
sourcedbFactory = 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.

Note

See the PostgreSQL docs or this site for help with creating users, passwords, and roles in PostgreSQL.

The psql interactive CLI client is provided with PostgreSQL. It can be used to connect to PostgreSQL and create users (as well as many other maintenance commands). If the Database Service is started with csw-services, the database server is started on port 5432.

Eventually, all TMT user logins will all have these environment variables set with the agreed upon read-only user and password.

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.

Hint
  • Any exception encountered while connecting to PostgreSQL server will be wrapped in DatabaseException.

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
sourcedbFactory
  .makeDsl(locationService, "postgres", "DB_WRITE_USERNAME", "DB_WRITE_PASSWORD")
  .foreach((dsl: DSLContext) => this.dsl = dsl) // save returned dsl to a local variable
Java
sourcedbFactory
        .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
sourcedbFactory
  .makeDsl()
  .foreach((dsl: DSLContext) => this.dsl = dsl) // save returned dsl to a local variable
Java
sourcedbFactory
        .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
}
Note

By default, CSW configures HikariCP connection pool for managing connections with PostgreSQL server. To know more about HikariCP please refer to this link.

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
sourceval 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
sourceQuery 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
sourceval 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
sourceString 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) + "]"));
Note
  • The insert statements above gets mapped to prepared statements underneath at JDBC layer and values like movie_1, movie_2 and 2 from the example are bound to the dynamic parameters of these generated prepared statements.
  • As prepared statements provide safety against SQL injection, it is recommended to use prepared statements instead of static SQL statements whenever there is a need to dynamically bind values.
  • In the above example, two insert statements are batched together and sent to PostgreSQL server in a single call. executeBatchAsync/executeBatch maps to batch statements underneath at JDBC layer.

Select

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

Scala
source// 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
source// 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());
Note

Make sure that variable name and type of Films class is same as column’s name and type in database. This is necessary for successful mapping of table fields to domain model class.

Stored Function

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

Scala
sourceval 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
sourceQuery 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.

Note

If there is a syntax error in SQL queries, the Future/CompletableFuture returned will fail with CompletionException and the CompletionStage will fail with an ExecutionException. But both CompletionException and ExecutionException will have Jooq’s DataAccessException underneath as cause.

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