Testing WSO2 products on different dbs using docker | Part 1

This blog will explain how we can test WSO2 products on different databases using docker images. Commonly of the WSO2 products support H2, MySQL, DB2, PostgreSQL, Oracle and MsSQL. You can find more information about the database compatibility here. WSO2 products are shipped with embedded H2 by default. By changing the configuration you can simple change the database. Here I will explaining how to simply use docker containers as databases to configure with WSO2 products. So you can test your implementation or bug fixes in all the supported databases easily.

What is Docker ?

Docker is bit like a virtual machine. But unlikely, rather than creating a whole virtual operating system, Docker allows applications to use the same Linux kernel as the system that they’re running on and only requires applications be shipped with things not already running on the host computer. This gives a significant performance boost and reduces the size of the application.

First install docker in your computer. This link have good instructions about installation guide lines for Ubuntu. Follow them to install the docker engine.

Some useful commands to handle docker

View all the containers in docker engine using this command.

docker ps -a

If the container is not running, you can start it using,

docker start <containterName/ContainerId>

Command to log on to a running contanier

docker exec -i -t <containterName/ContainerId> /bin/bash

To stop the docker container

docker stop <containterName/ContainerId>

Before we begin

I will be using WSO2 Identity and access management server in this blog. If you want to test with other products, change the master-datasources.xml accordingly.

Please note that, these procedures are only for testing. No production standards are followed.

Download corresponding JDBC connectors and copy the JAR file only to wso2 server <PRODUCT_HOME>/repository/components/lib.

If you want to change the default user store in IAM server from LDAP to this RDBMS, follow this to configure user store to RDBMS.

You can use dbeaver database tool to view created databases.

MySQL

MySQL is the world’s most popular open source database. With its proven performance, reliability and ease-of-use.

Create the docker container

If you want to test on with specific MySQL version, change latest to specific version in the following command.

docker run -p 3306:3306 -- name=mysql -e MYSQL_ROOT_PASSWORD=root -d mysql:latest

This command will download the docker image of the latest MySQL server and create a container with name “mysql”. The default root password will be root. Container port 3306 will be mapped to host port 3306.

If you have any services running in 3306 in your host machine, then change the host port.

Log on into docker container and create the database

docker exec -i -t mysql /bin/bash

mysql -u root -p

create database regdb character set latin1;

Here a new database will be created with name regdb.

Then edit the default data source configuration in the <PRODUCT_HOME>/repository/conf/datasources/master-datasources.xml file with the created database name and user details.

<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:mysql://localhost:3306/regdb</url>
               <username>root</username>
               <password>root</password>
               <driverClassName>com.mysql.jdbc.Driver</driverClassName>
               <maxActive>80</maxActive>
               <maxWait>60000</maxWait>
               <minIdle>5</minIdle>
               <testOnBorrow>true</testOnBorrow>
               <validationQuery>SELECT 1</validationQuery>
               <validationInterval>30000</validationInterval>
               <defaultAutoCommit>false</defaultAutoCommit>
           </configuration>
       </definition>
</datasource>

DB2

DB2 is a family of relational database management system (RDBMS) products from IBM that serve a number of different operating system platforms.

Create the docker container

docker run \
 --name db2 \
 -p 50000:50000 \
 -e DB2INST1_PASSWORD=db2inst1 \
 -e LICENSE=accept \
 -d ibmcom/db2express-c db2start

Log on into docker container and create database.

docker exec -i -t db2 /bin/bash

su - db2inst1

db2 create database test

db2 connect to test user db2inst1 using db2inst1

Then edit the default datasource configuration in the <PRODUCT_HOME>/repository/conf/datasources/master-datasources.xml file with the created database name and user details.

<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:db2://localhost:50000/test</url>
                <username>db2inst1</username>
                <password>db2inst1</password>
                <driverClassName>com.ibm.db2.jcc.DB2Driver</driverClassName>
                <maxActive>80</maxActive>
                <maxWait>360000</maxWait>
                <minIdle>5</minIdle>
                <testOnBorrow>true</testOnBorrow>
                <validationQuery>SELECT 1 FROM SYSIBM.SYSDUMMY1</validationQuery>
                <validationInterval>30000</validationInterval>
                <defaultAutoCommit>false</defaultAutoCommit>
            </configuration>
      </definition>
</datasource>

Make sure the validationQuery is like this. Due to known issue

<validationQuery>SELECT 1 FROM SYSIBM.SYSDUMMY1</validationQuery>

PostgreSQL

PostgreSQL is a powerful, open source object-relational database system that uses and extends the SQL language combined with many features that safely store and scale the most complicated data workloads.

Create the docker container

docker run -d -p 5432:5432 --name postgres -e POSTGRES_PASSWORD=wso2carbon postgres

Log on into docker container, create user and database

docker exec -i -t postgres /bin/bash

su — postgres

createuser regadmin

createdb testdb

Get inside the sql command line

psql

Run the following command to set user password and access

alter user regadmin with encrypted password ‘regadmin’;
grant all privileges on database testdb to regadmin;

Edit the default datasource configuration in the <PRODUCT_HOME>/repository/conf/datasources/master-datasources.xml file with the created database name and user details.

<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:postgresql://localhost:5432/testdb</url>
                 <username>regadmin</username>
                 <password>regadmin</password>
                 <driverClassName>org.postgresql.Driver</driverClassName>
                 <maxActive>80</maxActive>
                 <maxWait>60000</maxWait>.
                 <testOnBorrow>true</testOnBorrow>
                 <validationQuery>SELECT 1</validationQuery>
                 <validationInterval>30000</validationInterval>
                 <defaultAutoCommit>false</defaultAutoCommit>
             </configuration>
         </definition>
</datasource>

In the next part, we can go through MsSQL and Oracle databases set up guides in docker.