Supporting Infrastructure

There are a host of supporting technologies which can be used when running Genesis applications. Applications can be set up to use the technologies which match your enterprise setup.

Data storage

Data storage is essential to all applications. Genesis applications have optionality around storing data

Databases

Your Genesis application can be configured to use one of the following database technologies to store the data model and its associated data.

By default, new projects are set up with H2 database, which enables you to learn and create simple trial applications without any configuration. This is intended to be used in development environments only, not for production.

For production environments (and local production like testing), Postgresql is our recommended choice, however Genesis supports the following database technologies:

Database	FoundationDB	Postgresql	MSSQL	Oracle	H2
Technology	NOSQL	SQL	SQL	SQL	SQL
Unlimited operation size	❌	✔️	✔️	✔️	✔️
Supported in production	✔️	✔️	✔️	✔️	❌

FoundationDB

Genesis provides two database solutions built on FoundationDB.

FDB and FDB2 are equivalent in terms of configuration, but they store data using a different data model.

FDB uses a separate key-value for each table column.
FDB2 stores all data in a single key-value.

Each mode has implications in terms of database limitations, as FDB can only store a maximum of 10MB data per transaction and 1MB data per key-value.

Therefore, for applications using small table records, FDB2 is more efficient. For applications using large table records, FDB is more efficient.

System definitions

For FDB and FDB2 layers, there are additional system definition items that you can apply in the genesis-system-definitions.kts file:

Setting	Description
`FdbClusterFile`	A path to an fdb.cluster file. If this item is not defined, the default fdb.cluster file will be used. This file is located in /etc/foundationdb/fdb.cluster
`DbNamespace`	A name for the internal FDB directory to use.
`DbThreadsMin`	The minimum number of threads to be created in the FDB layer thread pool. Defaults to the minimum of 4 or the number of processing units
`DbThreadsMax`	The maximum number of threads to be created in the FDB layer thread pool. Defaults to the maximum of 4 or the number of processing units multiplied by 2
`DbThreadKeepAliveSeconds`	Sets how many seconds a thread created over the `DbThreadsMin` value can live. If a thread is idle for a total of `DbThreadKeepAliveSeconds` and it was created as an additional thread (i.e. outside the `DbThreadsMin` threshold), it will be destroyed. Defaults to 5 minutes
`DbOptimisticConcurrencyMode`	Set the Optimistic Concurrency mode. Available values: STRICT, LAX and NONE. Defaults to NONE.

Sample configurations

Min and max thread count

systemDefinition {
    global {
        ...
        item(name = "DbThreadsMin", value = "5")
        item(name = "DbThreadsMax", value = "15")
        ...
    }
    ...
}

Thread timeouts to two minutes

systemDefinition {
    global {
        ...
        item(name = "DbThreadKeepAliveSeconds", value = "120")
        ...
    }
    ...
}

Cluster file location on windows

systemDefinition {
    global {
        ...
        item(name = "FdbClusterFile", value = "C:\\Genesis\fdb\fdb.cluster")
        ...
    }
    ...
}

SQL

The following SQL based technologies are supported:

PostgreSQL (recommended)
MS SQL Server
Oracle

This page covers the common configuration options for setting up these databases in your Genesis application. Then, for each technology, it gives details of JDBC URL formats for connecting to the database, along with any other specific requirements.

Finally, since the first thing you will need to do is to provide a username and password to connect, we provide some simple examples of configurations that achieve this.

Common system definitions

Whichever SQL technology you are using, you need to configure it correctly in your genesis-system-definitions.kts file. The following settings are available for you to add. The DbHost setting is mandatory. All settings apply at the JVM level.

Setting	Description
`DbHost`	JDBC URL needed to connect to the SQL database. We accept JDBC URL formats for PostgreSQL and MSSQL. This JDBC URL contains other important information, such as port and target database name
`DbUsername`	The db username
`DbPassword`	The db password
`DbSqlConnectionPoolSize`	This property is deprecated in favour of DbSqlMaxPoolSize.
`DbSqlMaxPoolSize`	For each JVM process (Data Server, Request Server, etc) that connects to the database, this is the maximum number of SQL connections to be held by the SQL connection pool
`DbQuotedIdentifiers`	If set to true, all SQL tables and fields will be queried using quotes for each identifier to avoid potential name clashes. For example, when the db layer queries the TRADE table with a particular TRADE_ID for all fields, it will do something like `SELECT t_trade WHERE "TRADE_ID" = 1`. However, if set to false, it will use `SELECT t_trade WHERE TRADE_ID = 1`
`DbThreadsMin`	The minimum number of threads to be created in the SQL layer thread pool. Defaults to the minimum of 4 or the number of processing units (CPUs)
`DbThreadsMax`	The maximum number of threads to be created in the SQL layer thread pool. Defaults to the maximum of 4 or the number of processing units (CPUs) multiplied by 2
`DbSqlConnectionTimeoutMillis`	Controls the maximum number of milliseconds that a client will wait for a connection from the pool. When exceeded, an SQLException will be thrown (Default: 30000ms)
`DbSqlIdleTimeoutMillis`	Controls the maximum time that a connection is allowed to sit idle in the pool. The minimum allowed value is 10000ms (10 seconds). Default: 600000 (10 minutes)
`DbSqlKeepaliveTimeMillis`	Controls how frequently the pool manager will attempt to keep a connection alive, in order to prevent it from being timed out by the database or network infrastructure. Default: 0 (disabled)
`DbSqlMaxLifetimeMillis`	Controls the maximum lifetime of a connection in the pool.
`DbSqlConnectionTestQuery`	This is the query that will be executed just before a connection is given to you from the pool in order to validate that the connection to the database is still alive.
`DbSqlMinimumIdleMillis`	Controls the minimum number of idle connections that the connection library tries to maintain in the pool. Default: same as maximumPoolSize
`DbSqlCatalog`	Sets the default catalog for databases that support the concept of catalogs. If this property is not specified, the default catalog defined by the JDBC driver is used. Default: driver default
`DbSqlConnectionInitSql`	Sets an SQL statement that will be executed after every new connection is created before adding it to the pool. If this SQL is not valid or throws an exception, it will be treated as a connection failure and the standard retry logic will be followed. Default: none
`DbSqlValidationTimeoutMillis`	Controls the maximum length of time for which a connection will be tested to determine whether it is still alive. This value must be less than the connectionTimeout. Lowest acceptable validation timeout is 250 ms. Default: 5000
`DbSqlLeakDetectionThresholdMillis`	Controls the length of time that a connection can be out of the pool before a message is logged indicating a possible connection leak. Lowest acceptable value for enabling leak detection is 2000 (2 seconds). Default: 0 (disabled)
`DbSqlCachePrepStmts`	Enable or disable prepared statement cache. Default: true
`DbSqlPrepStmtCacheSize`	Set the maximum number of cached prepared statements. Default: 250
`DbSqlPrepStmtCacheSqlLimit`	Define the maximum length of SQL statements that can be cached. Default: 2048
`DbOptimisticConcurrencyMode`	Set the Optimistic Concurrency mode. Available values: STRICT, LAX and NONE. Defaults to NONE.
`SqlMaxParametersPerRequest`	For Postgres and SQL Server databases only. Set to enable bulk operations for the database (`insertAll`, `getAllAsList`, etc). This speeds up priming (in views, Genesis uses `getAllAsList` internally to get joined records) and makes modify, delete and insert operations much faster. -1 sets to the Platform default, which is 5000 for Postgres and 2000 for SQL Server. Other database technologies ignore this setting.
`DbSqlResultSetFetchSize`	Defines the number of rows that will be fetched from the database when more rows are needed while executing a statement. Default: 10000

PostgreSQL

The JDBC URL for PostgreSQL takes the following form:

jdbc:postgresql://host:port/database

host and port are optional
default value for host is localhost
default value for port is 5432

In addition to the standard connection parameters, the URL accepts several additional parameters:

user - the database user
password - the database user password
ssl - whether to use SSL. The mere presence of this parameter specifies an SSL connection

More information on connection configuration can be found in the jdbc documentation.

info

For optimum performance using PostgreSQL, set the SqlMaxParametersPerRequest parameter to -1 in your application's system-definitions.kts file.

If you want PostgreSQL to work with different namespaces/schemas, you must add the following system definition items to your genesis-system-definitions.kts file:

Setting	Description
DbMode	This can be one of two values: POSTGRESQL if you want PostgreSQL to work with namespaces/schemas and LEGACY, which is the default mode; it always stores the dictionary in a table called `dictionary` and a schema called `metadata`.
DbNamespace	This is the namespace/schema of database. This allows you to segregate data from different Genesis apps whilst using a single database.

SQL Server

info

For optimum performance using SQL Server, set the SqlMaxParametersPerRequest parameter to -1 in your application's system-definitions.kts file.

The JDBC URL for MS SQL Server takes the following form:

jdbc:sqlserver://serverName[\instanceName][:port]][;property=value[;property=value]

instanceName and port are optional
If no instanceName is specified, a connection to the default instance is made
The default value for port is 1433

In addition to the standard connection parameters, the URL accepts several additional parameters:

user - the database user
password - the database user password
databaseName - the name of the database to connect to. If not stated, a connection is made to the default database.
trustServerCertificate - set to true to specify that the driver doesn't validate the server TLS/SSL certificate. This is useful for first-time run and development purposes. Detailed information about the property can be found here and information on encryption here.

Microsoft provides a full list of connection properties, including encryption and certificate authentication properties.

You can also consult the SSL documentation for MSSQL JDBC driver documentation.

Oracle

The JDBC URL for Oracle can take the following forms:

jdbc:oracle:thin:[username/password]@[protocol]//host[:port][/service_name]

jdbc:oracle:thin:[username/password]@host[:port]:sid

username/password, protocol, port, and service_name are optional
The default username/password are both null. If these are not provided, a connection will attempt to use standard connection parameters
The default protocol is tcp
The default value for port is 1521

In addition to the standard connection parameters, the URL accepts several additional parameters:

user - the database user. If not stated, a connection will try and use the value provided by the DbUsername system definition (if present)
password - the database user password. If not stated, a connection will try and use the value provided by the DbPassword system definition (if present)
databaseName - the name of the database to connect to. If not stated, a connection is made to the default database

For a full list of connection properties, including encryption and certificate authentication properties, see the jdbc support documents.

Sample configurations

User name and password as part of the Connection URL

In this case, the username and password are unencrypted. This is not recommended for Production environments.

systemDefinition {
    global {
        ...
        item(name = "DbLayer", value = "SQL")
        item(name = "DbHost", value = "jdbc:sqlserver://pdb:6060;databaseName=trades;user=MyUserName;password=dbpassword;")
        ...
    }    
    ...
}

User name and password as system definition items

In this case, the username and password are unencrypted. This is not recommended for Production environments.

systemDefinition {
    global {
        ...
        item(name = "DbLayer", value = "SQL")
        item(name = "DbHost", value = "jdbc:postgresql://pdb:6060/trades")
        item(name = "DbUsername", value = "dbuser")
        item(name = "DbPassword", value = "dbpassword")
        ...
    }
    ...
}

User name and password as encrypted system environment variables

In this case, the username and password have been encrypted.

systemDefinition {
    global {
        ...
        item(name = "DbLayer", value = "SQL")
        item(name = "DbHost", value = "jdbc:oracle:thin://pdb:6060;databaseName=trades")
        item(name = "DbUsername", value = System.getenv("DBUSERNAME"), encrypted = true)
        item(name = "DbPassword",  value = System.getenv("DBPASSWORD"), encrypted = true)
        ...
    }
    ...
}

File Storage

There are option when it comes to file storage. File storage is easy to configure in business components such as Document Management and where using integration capabilities such as Data Pipelines

Local storage : useful for many application's however this can cause issues in clustered or container set-ups unless the filesystem is mounted across all servers.
Cloud storage : managed cloud file storage services, such as AWS S3, are sometimes preferred in HA and container set-ups.
Sharepoint : Sharepoint is commonly used in financial services and allows end users to also access files easily outside of an application.

Log Storage

As standard, Genesis uses log4j logging, and it logs to the local host's disk. You might prefer to set up a log shipper and push Genesis application logs to it. Genesis works well with all the major log shipping utilities.

For more details on configuring logging see our logging page

Firewall

The standard ports opened between Genesis hosts in a given environment cluster are listed in the table below.

Port	Used for
80	Web traffic
443	Secured web traffic
5000	Zero MQ outbound traffic
5001	Zero MQ inbound traffic

info

Development teams can configure these to use different port numbers. Make them aware of these settings so that you avoid clashes with internal policies or set-ups.

Load Balancer

The Genesis platform does not include a Load Balancer. We encourage you to use your enterprise’s preferred LB. Usually, this is set up to point to the nginx server running on each Genesis host. Genesis application environment servers provide a healthcheck endpoint, which the Load Balancer can use to determine server health.

Genesis supports round-robin and sticky-session set-ups.

Update queue

The Genesis platform abstracts the database layer so that developers don’t have to worry about implementation details. Regardless of the underlying DB, all database actions taken are published to an update queue. Other Genesis processes listen out for this and act upon relevant updates.

Internally on a given host, and in fixed-size environment deployments, ZeroMQ is used for the update queue. Netty + Apache Pekko are used to manage the cluster nodes and distribute database updates between the nodes. These libraries run on the cluster nodes, and no additional infrastructure is required

Instead of ZeroMQ, MQTT or JMS can be used for the update queue. Usually, this is hosted separately from the application hosts or containers, and the queue is shared between hosts or containers.

Client server interaction

nginx

Genesis installations usually use nginx as a lightweight web server for:

serving up SSP (static single page) web client content
proxying web client (and web API) requests to the Genesis server
- essentially, any client requests with /gwf will be routed to the Genesis services with /gwf stripped off.

See our config management page for details on configuring nginx.

Other web servers can also be used. If you need to use a different one, this is fine; it simply needs to be set up to perform the same actions that nginx is used for.

Data storage​

Databases​

FoundationDB​

System definitions​

Min and max thread count​

Thread timeouts to two minutes​

Cluster file location on windows​

SQL​

Common system definitions​

PostgreSQL​

SQL Server​

Oracle​

Sample configurations​

User name and password as part of the Connection URL​

User name and password as system definition items​

User name and password as encrypted system environment variables​

File Storage​

Log Storage​

Firewall​

Load Balancer​

Update queue​

Client server interaction​

nginx​

Data storage

Databases

FoundationDB

System definitions

Min and max thread count

Thread timeouts to two minutes

Cluster file location on windows

SQL

Common system definitions

PostgreSQL

SQL Server

Oracle

Sample configurations

User name and password as part of the Connection URL

User name and password as system definition items

User name and password as encrypted system environment variables

File Storage

Log Storage

Firewall

Load Balancer

Update queue

Client server interaction

nginx