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.
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.
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.
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
-
source
val 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
-
source
dbFactory = 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.
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.
- 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
-
source
dbFactory .makeDsl(locationService, "postgres", "DB_WRITE_USERNAME", "DB_WRITE_PASSWORD") .foreach((dsl: DSLContext) => this.dsl = dsl) // save returned dsl to a local variable
- Java
-
source
dbFactory .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
-
source
dbFactory .makeDsl() .foreach((dsl: DSLContext) => this.dsl = dsl) // save returned dsl to a local variable
- Java
-
source
dbFactory .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
}
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
-
source
val 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
-
source
Query 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
-
source
val 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
-
source
String 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) + "]"));
- The insert statements above gets mapped to prepared statements underneath at JDBC layer and values like
movie_1
,movie_2
and2
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());
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
-
source
val 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
-
source
Query 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.
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.