How to connect and query multiple databases with a single REST API

Intro

As a web developer or database admin, creating a single REST API for querying your databases together should be easy, but it is often quite difficult.

What if you could skip using an ORM, and use technology that auto-creates a REST API to query your databases together?

Open-source DB2Rest can allow your frontend to access your multiple separate databases where DB2Rest automatically exposes a safe and secure REST API to easily query, join, or push data records to store into your databases.

Config

We can use a JSON structure (and provide the config as a large environment variable string) or use a YAML file to provide the configuration for DB2Rest to work with multiple databases.

Here's an example of a YAML file which you can type or generate by whichever template mechanism you desire (for extra security, any values such as passwords can be replaced at instantiation time or after DB2Rest deployment). Each database needs to be given a unique id of your choosing.

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"

After saving configuration, or setting the environment variable string for config, we can start DB2Rest with a single command:

$ java -jar db2rest-1.2.3.jar

Making REST API calls to multiple databases

Once DB2Rest is running, we can make REST API calls from our application or frontend code to query, or insert data into our databases. Let's see how to insert a record via curl into our DB1 database's employee table.

Notice the url endpoint that DB2Rest created and exposes for you by automatically inspecting all schema & tables of your configured databases upon its startup:

POST Request

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" : "[e-mail]",
"created_on" : "2015-04-14T11:07:36.639Z"
}'

HTTP Response from DB2Rest after successful write

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

To store data records to DB2 we simply change the url like so:

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" : "[e-mail]",
"created_on" : "2015-04-14T11:07:36.639Z"
}'

Querying data

Let's see how querying the table employee from our DB2 looks like after we inserted the record previously. Instead of curl, let's use httpie:

HTTPie Request

http GET http://[IP_ADDRESS]:[PORT]/v1/rdbms/DB2/employee \
User-Agent:insomnia/8.6.1

HTTP Response

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

Restricting Schema for multiple databases

You can even restrict schema that will be exposed by DB2Rest's auto REST API. This is done by using a schemas add rule into the configuration of DB2Rest.

Below, we want to limit the accessible schema (objects, tables) to only the public and hrms schemas that DB2Rest will automatically create URL endpoints for us (no other schema will be reachable, and DB2Rest will immediately return an error response):

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"

POST Request

http GET http://[IP_ADDRESS]:[PORT]/v1/rdbms/DB1/admin \
User-Agent:insomnia/8.6.1

HTTP Error Response

[
    {
        "errorCategory": "Data-access-error",
        "timestamp:": "2015-04-14T11:07:36.639+00:00"
    }
]

Advanced Querying

DB2Rest also supports easy to use advanced querying and filtering, not only querying entire tables or inserting rows!

http GET 'http://localhost:8080/v1/rdbms/DB1/employee?filter=last_name==Green' \
User-Agent:insomnia/8.6.1

even multiple filters can be combined, using operators such as AND ; (the official docs have many more examples of powerful expression syntax available)

http GET 'http://localhost:8080/v1/rdbms/DB1/employee?filter=last_name==Green;first_name==David' \
User-Agent:insomnia/8.6.1

For more information see the official docs at https://db2rest.com/docs/intro or join the DB2Rest Discord channel