Connecting to H2 Databases of the WSO2 Identity Server

Since H2 databases are not recommended for production, there is no valid usage of the H2 console feature in production environments and is only useful during development tasks. Having the H2 console inside the product might give unnecessary security vulnerabilities — hence now has been removed from the WSO2 Identity Server (from 5.12.0 Alpha 15 onwards). Therefore you will not be able to use the embedded H2 console of the IS for your testing purposes.

In this blog I will take you through 2 methods to connect to your H2 databases when the embedded H2 console of the WSO2 IS is not available.

Note: Since the databases will be locked while being used by the IS server, if you want to connect to the database while the IS server is running, you should connect the DBs in the Automatic mixed mode.

IS Server Configurations

Add the following configurations to the IS server to start the server DBs in the automatic mixed mode.

1. Open the deployment.toml file available at <IS-HOME>/repository/conf/ directory.
2. Add the AUTO_SERVER=TRUE config to the database URL of the database.identity_db and database.shared_db as below.

[database.identity_db]
type = "h2"
url = "jdbc:h2:./repository/database/WSO2IDENTITY_DB;DB_CLOSE_ON_EXIT=FALSE;LOCK_TIMEOUT=60000;AUTO_SERVER=TRUE"
username = "wso2carbon"
password = "wso2carbon"
[database.shared_db]
type = "h2"
url = "jdbc:h2:./repository/database/WSO2SHARED_DB;DB_CLOSE_ON_EXIT=FALSE;LOCK_TIMEOUT=60000;AUTO_SERVER=TRUE"
username = "wso2carbon"
password = "wso2carbon"

Note: As the H2 version is updated (to 2.2.220) in IS 7.0.0, the DB_CLOSE_ON_EXIT=FALSE parameter should not be used in the H2 url.

Sample Url to be used for IS 7.0.0 onwards:

url = "jdbc:h2:./repository/database/WSO2IDENTITY_DB;LOCK_TIMEOUT=60000;AUTO_SERVER=TRUE"

3. Restart the IS server.

Note: Only the IDENTITY_DB and SHARED_DB can be configured in the AUTO_SERVER mode from the deployment.toml.

If you need to add this config to the CARBON_DB, the master-datasources.xml.j2 in the <IS-HOME>/repository/resources/conf/templates/repository/conf/datasources directory needs to be changed.

1. Append the AUTO_SERVER=TRUE config to the <url> tag of the WSO2_CARBON_DB datasource.

<url>jdbc:h2:async:./repository/database/WSO2CARBON_DB;DB_CLOSE_ON_EXIT=FALSE;AUTO_SERVER=TRUE</url>

Now the WSO2_CARBON_DB datasource configurations should look as follows:

<datasource>
<name>WSO2_CARBON_DB</name>
<description>The datasource used for registry and user manager</description>
<jndiConfig>
<name>jdbc/WSO2CarbonDB</name>
</jndiConfig>
<definition type="RDBMS">
<configuration><url>jdbc:h2:async:./repository/database/WSO2CARBON_DB;DB_CLOSE_ON_EXIT=FALSE;AUTO_SERVER=TRUE</url>
<username>wso2carbon</username>
<password>wso2carbon</password>
<driverClassName>org.h2.Driver</driverClassName><maxActive>50</maxActive>
<maxWait>60000</maxWait>
<testOnBorrow>true</testOnBorrow>
<validationQuery>SELECT 1</validationQuery><validationInterval>30000</validationInterval><defaultAutoCommit>true</defaultAutoCommit>
</configuration>
</definition>
</datasource>

2. Restart the IS server.

Externally Connecting to the Databases

Use one of the following methods to connect to the databases externally and explore the database content.

1. Using a H2 jar

1. Download the latest H2 jar (2.1.210 as of now) from the official H2 database website.
2. Unzip the downloaded zip folder to a directory of your choice and open a terminal inside the bin folder.
3. Type the following command to start the H2 server.

java -jar h2*.jar

Once the server is started, a browser will be automatically opened at http://localhost:8082/ as shown below.

4. Provide the data relevant to the database you need to login and click Connect.

Note: Add the AUTO_SERVER=TRUE config at the end of the JDBC URL to allow access to the database while the IS server is running.

E.g: If you need to login to the WSO2IDENTITY_DB, provide the following details.

• Saved Settings: Generic H2 (Embedded)
• Setting Name: Generic H2 (Embedded)
• Driver Class: org.h2.Driver
• JDBC URL: jdbc:h2:/<IS_HOME>/repository/database/WSO2IDENTITY_DB;AUTO_SERVER=TRUE
• User Name: wso2carbon
• Password: wso2carbon

Now you can successfully connect to your database and interact with the data using this console.

2. Using DBeaver

In this section, I will show you how to connect to a H2 database using DBeaver.

1. First, install the DBeaver software from official DBeaver website.
2. Open DBeaver and create a new Database connection by selecting Database → New Database Connection → Embedded → H2 Embedded → Next

Note: If you have 2 H2 Embedded options to choose at the Select your database window as shown below, choose the H2 Embedded V2 database which is the compatible option for the 2.x H2 version.

3. In the Main tab, provide the database details as shown below.

Provide the file path of the database file for the Path field and the JDBC URL will be automatically set according to the given path.

Note: Add the AUTO_SERVER=TRUE config at the end of the JDBC URL to allow access to the database while the IS server is running.

E.g: If you need to login to the WSO2IDENTITY_DB, provide the following details.

• Path: /<IS_HOME>/repository/database/WSO2IDENTITY_DB;AUTO_SERVER=TRUE
• Username: wso2carbon
• Password: wso2carbon

4. Go to the Driver Properties tab and download the latest H2 driver (2.1.210 as of now) if the driver is not already available.

5. Click on Finish and you will now see the WSO2IDENTITY_DB db under the database navigator.

Enjoy working with your databases!!!

References

1. http://www.h2database.com/html/features.html#auto_mixed_mode
2. https://www.h2database.com/html/download.html
3. https://dbeaver.io/download/