Skip to main content

Connect Multiple Databases

One of the super features of DB2Rest is that one single instance can work with multiple separate databases. For example a DB2Rest node can connect to a MySQL and PostgreSQL database.

note

If you have tables or views in your databases with the SAME NAME, you will have to use an HTTP header as mentioned in our FAQ, otherwise you are likely to get a DB object collision error.

Use Environment Variables​

The first method to connect DB2Rest with multiple databases is similar to running with one database as described in the getting started section. The multiple database configuration can be set as environment variables.

note

The environment variable SPRING_APPLICATION_JSON property combines database configurations and must be provided as a JSON format in a single line.

Set environment variable​

$ export SPRING_APPLICATION_JSON='{"app":{"databases":[{"id":"DB1","type":"POSTGRESQL","url":"jdbc:postgresql://localhost:5432/homidb","username":"root","password":"@Kolkata007"},{"id":"DB2","type":"MYSQL","url":"jdbc:mysql://localhost:3306/sakila","username":"root","password":"@Kolkata007"}]}}'

Run the command​

$ java -jar db2rest-1.4.1.jar

The JSON structure for the environment variable SPRING_APPLICATION_JSON is shown below.


{
  "app": {
    "databases": [
      {
        "id": "DB1",
        "type": "POSTGRESQL",
        "url": "jdbc:postgresql://localhost:5432/homidb",
        "username": "root",
        "password": "@Kolkata007"
      },
      {
        "id": "DB2",
        "type": "MYSQL",
        "url": "jdbc:mysql://localhost:3306/sakila",
        "username": "root",
        "password": "@Kolkata007"
      }
    ]
  }
}

Use Externalised Configuration File​

In this method the configuration will be loaded from YAML file.

Create Yaml file​

Create a Yaml file with name format application-{profile}.yml - for example application-local.yml file as shown below. In this case, the profile name is local. In this way you can create profile files for dev, prod etc. When no profile is used, the file name is just application.yml


app:
  databases:
    - id: DB1
      type: POSTGRESQL
      url: jdbc:postgresql://localhost:5432/homidb
      username: root
      password: "@Kolkata84"

    - id: DB2
      type: MYSQL
      url: jdbc:mysql://localhost:3306/sakila
      username: root
      password: "@Kolkata84"

note

The application-{profile}.yml file must be saved (in the same folder) along with the jar distribution of DB2REST.

Set Profile as Environment/System Property​

$ export SPRING_PROFILES_ACTIVE={profile-from-file-name} # in this case `local`

Run the command​

$ java -jar db2rest-1.4.1.jar
danger

The id attribute set above will be used to query/modify data from the different databases. Hence, ensure that it is unique.

Test Setup for PostGreSQL​

Insert Row​


curl --request POST \
--url http://[IP_ADDRESS]:[PORT]/v1/rdbms/DB1/employee \
--header 'Content-Type: application/json' \
--header 'User-Agent: insomnia/8.6.1' \
--data '{
"first_name" : "Salman",
"last_name" : "Khan",
"email" : "[email protected]",
"created_on" : "2015-04-14T11:07:36.639Z"
}'


HTTP Response​

{
	"row": 1,
	"keys": {
		"id": 1
	}
}

Read Row​


curl --request GET \
--url http://[IP_ADDRESS]:[PORT]/v1/rdbms/DB1/employee \
--header 'User-Agent: insomnia/8.6.1'

HTTP Response​

[
	{
		"id": 1,
		"first_name": "Salman",
		"last_name": "Khan",
		"email": "[email protected]",
		"created_on": "2015-04-14T11:07:36.639+00:00"
	}
]

Test Setup for MySQL​

Insert Row​


curl --request POST \
--url http://[IP_ADDRESS]:[PORT]/v1/rdbms/DB2/employee \
--header 'Content-Type: application/json' \
--header 'User-Agent: insomnia/8.6.1' \
--data '{
"first_name" : "Salman",
"last_name" : "Khan",
"email" : "[email protected]",
"created_on" : "2015-04-14T11:07:36.639Z"
}'


HTTP Response​

{
	"row": 1,
	"keys": {
		"id": 1
	}
}

Read Row​


curl --request GET \
--url http://[IP_ADDRESS]:[PORT]/v1/rdbms/DB2/employee \
--header 'User-Agent: insomnia/8.6.1'

HTTP Response​

[
	{
		"id": 1,
		"first_name": "Salman",
		"last_name": "Khan",
		"email": "[email protected]",
		"created_on": "2015-04-14T11:07:36.639+00:00"
	}
]

tip

All the configuration parameters (except 2-5) mentioned in the configuration guide can be used in the external configuration file style. This will override the default values of these parameters.

Restrict Schema​

Just like single database, it is also possible to restrict schema for metadata loading in case of multiple databases.


app:
  databases:
    - id: DB1
      type: POSTGRESQL
      url: jdbc:postgresql://localhost:5432/homidb
      username: root
      password: "@Kolkata84"
      schemas:
       - public
       - hrms

    - id: DB2
      type: MYSQL
      url: jdbc:mysql://localhost:3306/sakila
      username: root
      password: "@Kolkata84"

As shown in the example above, the schemas field can be specified as a list. In this case, only tables from the two schemas namely public and hrms will be accessible as REST APIs.

Finally, refer to the DB2Rest documentation for further learning and exploring API features.

For help, visit us on Discord or our GitHub Discussions