SQL storage for Gentics Mesh

SQL storage for Gentics Mesh

Description

Despite Gentics Mesh provides an own noSQL graph storage in its bundle, there is now a possibility to use an external SQL database instead of it, either provided by the customer or served along with the main application. As of now the following RDBMSs are supported: MSSQL 2019+, Oracle 19+, PostgreSQL 14.4+, MariaDB 10.3+ (DBs not included).

Documentation

Table of Contents

Despite Gentics Mesh being bundled with a noSQL Graph Database as storage, there is an enterprise feature available to use a Relational Database as storage. The used Relational Database has to be provided and is not included or managed by Gentics Mesh.

As of now the following RDBMSs are supported:

  • MSSQL 2019+

  • Oracle 19+

  • PostgreSQL 14.4+

  • MariaDB 10.3+.

Configuration

The configuration for SQL RDBMS requires the database-specific parameters being provided.

Examples:

Configuration for PostgreSQL

JDBC Driver

Download postgresql-X.Y.Z.jar from https://jdbc.postgresql.org/download.html , place it at the server into the folder, reachable by Gentics Mesh.

DB configuration

Define the database and Mesh user. This example uses environment variables.

export POSTGRES_DB=mesh
export POSTGRES_USER=meshdbuser
export POSTGRES_PASSWORD=admin

Create the dedicated schema.

CREATE SCHEMA current;
Mesh Configuration
storageOptions:
  retryLimit: 10
  retryDelayMillis: 2000
  driverClass: "org.postgresql.Driver"
  dialectClass: "org.hibernate.dialect.PostgreSQL95Dialect"
  connectionUrl: "jdbc:postgresql://localhost:5432/"
  connectionUrlExtraParams: "?autosave=always&currentSchema=current"
  databaseName: "mesh"
  driverClasspath: "path/to/downloaded/driver/postgresql-X.Y.Z.jar"
  connectionUsername: "meshdbuser"
  connectionPassword: "admin"
  showSql: false
  formatSql: false
  hikariOptions:
    autocommit: false
    connectionTimeout: 10000
    idleTimeout: 600000
    maxLifetime: 1800000
    minimumIdleConnection: 10
    maxPoolSize: 10
    poolName: "MeshHikariCP"
    registerMBeans: false
    transactionIsolationLevel: null
    leakDetectionThreshold: 0
  synchronizeWrites: false
  synchronizeWritesTimeout: 6000

Configuration for MSSQL

This example uses inbuilt "SA" user.

JDBC Driver

Download sqljdbc_X.Y.Z.W_enu.zip from https://docs.microsoft.com/en-us/sql/connect/jdbc/download-microsoft-jdbc-driver-for-sql-server?view=sql-server-ver16 , unpack mssql-jdbc-X.Y.Z.jre11.jar at the server into the folder, reachable by Mesh.

DB configuration

Define the "SA" user password. This example uses environment variables.

export SA_PASSWORD=admin

Create the dedicated database:

CREATE DATABASE mesh;

Alter the isolation level

ALTER DATABASE mesh SET READ_COMMITTED_SNAPSHOT ON;
Mesh Configuration
storageOptions:
  retryLimit: 10
  retryDelayMillis: 2000
  driverClass: "com.microsoft.sqlserver.jdbc.SQLServerDriver"
  dialectClass: "com.gentics.mesh.enterprise.hibernate.dialect.SqlServer2012UnicodeDialect"
  connectionUrl: "jdbc:sqlserver://localhost:1433;databaseName="
  connectionUrlExtraParams: ";encrypt=false"
  databaseName: "mesh"
  driverClasspath: "path/to/extracted/driver/mssql-jdbc-X.Y.Z.jre11.jar"
  connectionUsername: "SA"
  connectionPassword: "admin"
  showSql: false
  formatSql: false
  hikariOptions:
    autocommit: false
    connectionTimeout: 10000
    idleTimeout: 600000
    maxLifetime: 1800000
    minimumIdleConnection: 10
    maxPoolSize: 10
    poolName: "MeshHikariCP"
    registerMBeans: false
    transactionIsolationLevel: null
    leakDetectionThreshold: 0
  synchronizeWrites: false
  synchronizeWritesTimeout: 6000

Configuration for MariaDB

JDBC Driver

Download mariadb-java-client-X.Y.Z.jar from https://mariadb.org/connector-java/all-releases/ , place it at the server into the folder, reachable by Mesh.

DB configuration

Define the database and Mesh user. This example uses environment variables.

export MYSQL_DATABASE=mesh
export MYSQL_USER=meshdbuser
export MYSQL_PASSWORD=admin

Install the isolation level for the database on start with the startup parameter

--transaction-isolation=READ-COMMITTED

Create the dedicated database:

CREATE DATABASE mesh;

Alter the access privileges for the Mesh user

GRANT ALL PRIVILEGES ON mesh.* TO 'meshdbuser'@'%';
Mesh Configuration
storageOptions:
  retryLimit: 10
  retryDelayMillis: 2000
  driverClass: "org.mariadb.jdbc.Driver"
  dialectClass: "com.gentics.mesh.hibernate.dialect.mariadb.uuidbinary.MariaDB103UuidAwareDialect"
  connectionUrl: "jdbc:mariadb://localhost:3306/"
  connectionUrlExtraParams: ""
  databaseName: "mesh"
  driverClasspath: "path/to/downloaded/driver/mariadb-java-client-X.Y.Z.jar"
  connectionUsername: "meshdbuser"
  connectionPassword: "admin"
  showSql: false
  formatSql: false
  hikariOptions:
    autocommit: false
    connectionTimeout: 10000
    idleTimeout: 600000
    maxLifetime: 1800000
    minimumIdleConnection: 10
    maxPoolSize: 10
    poolName: "MeshHikariCP"
    registerMBeans: false
    transactionIsolationLevel: null
    leakDetectionThreshold: 0
  synchronizeWrites: false
  synchronizeWritesTimeout: 6000
JDBC Driver

Download ojdbc11.jar from https://www.oracle.com/database/technologies/appdev/jdbc-downloads.html , place it at the server into the folder, reachable by Mesh.

DB configuration

Define the database and Mesh user. This example uses environment variables.

export ORACLE_DATABASE=mesh

Create the dedicated database:

CREATE DATABASE mesh;

Alter the number of simultaneously open cursors

ALTER SYSTEM SET OPEN_CURSORS=4444 SID='*' SCOPE=BOTH;

Create the dedicated user

CREATE USER meshdbuser IDENTIFIED BY "admin";
GRANT ALL PRIVILEGES TO meshdbuser;
Mesh Configuration
storageOptions:
  retryLimit: 10
  retryDelayMillis: 2000
  driverClass: "oracle.jdbc.driver.OracleDriver"
  dialectClass: "com.gentics.mesh.enterprise.hibernate.dialect.Oracle12cLongTextDialect"
  connectionUrl: "jdbc:oracle:thin:@localhost:1521/"
  connectionUrlExtraParams: ""
  databaseName: "mesh"
  driverClasspath: "path/to/downloaded/driver/ojdbc11.jar"
  connectionUsername: "meshdbuser"
  connectionPassword: "admin"
  showSql: false
  formatSql: false
  hikariOptions:
    autocommit: false
    connectionTimeout: 10000
    idleTimeout: 600000
    maxLifetime: 1800000
    minimumIdleConnection: 10
    maxPoolSize: 10
    poolName: "MeshHikariCP"
    registerMBeans: false
    transactionIsolationLevel: null
    leakDetectionThreshold: 0
  synchronizeWrites: false
  synchronizeWritesTimeout: 6000

Plugin Details

Gentics Mesh provides a feature of using one of the mainstream SQL RDBMSs as a storage for Mesh, to allow more control over the data being served.

Interested?

Version

1.9.x

License

commercial

Authors

Gentics