This content originally appeared on DEV Community and was authored by Thad Guidry
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" : "sk@skfilms.com",
"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" : "sk@skfilms.com",
"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": "sk@skfilms.com",
"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
This content originally appeared on DEV Community and was authored by Thad Guidry
Thad Guidry | Sciencx (2024-10-18T03:14:32+00:00) How to connect and query multiple databases with a single REST API. Retrieved from https://www.scien.cx/2024/10/18/how-to-connect-and-query-multiple-databases-with-a-single-rest-api/
Please log in to upload a file.
There are no updates yet.
Click the Upload button above to add an update.