How to expose your database as a managed API with WSO2 in 10 minutes
Database technologies are growing at a somewhat slower rate than other technology areas. But still, it is a major component of any enterprise software architecture. Due to the lack of innovation in the user experience side of the database technologies and the amount of “fear” people having when exposing databases to the end users have put the databases into a corner within the enterprise software systems. If you need to access a particular data set from a database table, you need to go through several layers and go after DB admins to get your work done. Databases have become the “precious” things in the enterprise. But do you need databases to be that way forever?
The answer for the question is a clear “No”. With the advancements of Application Programming Interface (API) driven access to your data and more secure technologies like “OAuth2” has made it possible to expose your databases to your end users with security and control. When you hear about these kinds of technical jargons, you might think that this is another year-long project to expose the databases securely to the end users. But in this post, I’m going to showcase how you can achieve this within 10 minutes with WSO2 technology (Don’t get blown away :))
Let’s see how we can achieve this requirement through a simple diagram.
At the top section of the above figure 1, you can see the end user on the RHS and the database in the LHS. The end user will use a standard HTTP client to access the managed REST service which is exposed by the middle component. In the middle component, there are 2 sections
- Exposing the database as an OData service
- Exposing the OData service as a managed API
If you are not familiar with OData, you can refer to the following link which explains the concept behind OData specification. At a very high level, it is a technology which can be used to expose a database as a REST service by utilizing the different REST methods which map to basic CRUD operations within the database without any intermediate configuration.
In order to achieve this functionality, I’m using WSO2 which provides an easy to use, high performing, highly customizable technology. As depicted in the lower section of the above figure 1, the following WSO2 products are used in this scenario.
- WSO2 Enterprise Integrator (EI) — To expose OData service by connecting to a MySQL database
- WSO2 API Manager (APIM) — To manage and securely expose the OData service to end users
You need to download WSO2 EI and WSO2 API Manager from the following links. In addition to that, you should have a database (obviously) and the relevant jdbc driver. I’m using MySQL database and you can download the driver from here.
- WSO2 EI — https://wso2.com/api-management/
- WSO2 APIM — https://wso2.com/integration/
Let’s get things started and kick-off your 10-minute timer.
Create the database and a table
Execute the following commands in a mysql terminal
mysql> create database api_odata_sample;
mysql> use database api_odata_sample;
mysql> CREATE TABLE IF NOT EXISTS Accounts(
accountId INTEGER NOT NULL AUTO_INCREMENT,
balance DOUBLE,
PRIMARY KEY (accountId)
);
Create the OData service in WSO2 EI
Let’s start the WSO2 Enterprise Integrator (WSO2 EI) by executing the below command from the EI_HOME/bin directory.
$ sh integrator.sh
This will start the WSO2 EI with the default port 9443. Now log into the management console by accessing the below URL.
Now create a new data service from the management console and provide the relevant URL for the mysql database connection and the table which we have created above.
You can select the “create” option from Home->Manage->Services->Data Service menu item. Then you can provide the relevant information as depicted in the below figure 2.
Then click on the “add new datasource” option and it will provide the window to create the data service. Provide the “datasource id” and select the “Datasource Type” as “RDBMS” which will reveal the UI mentioned below.
Provide the values as mentioned in the above figure and select the “Enable OData” check box to make sure that this data service is available through OData. Click on “Save” button in this window and click on the “Finish” button on the next window. The created data service configuration is shown below.
<data name=”ODataAPISample” serviceNamespace=”odata.test” transports=”http https local”>
<config enableOData=”true” id=”odata_sample_ds”>
<property name=”driverClassName”>com.mysql.jdbc.Driver</property>
<property name=”url”>jdbc:mysql://localhost:3306/api_odata_sample</property>
<property name=”username”>XXXX</property>
<property name=”password”>XXXXXXX</property>
</config>
</data>
Now the data service is up and running. You can check the data service by accessing the below URL which provides metadata about the “Accounts” table.
GET http://localhost:9763/odata/ODataAPISample/odata_sample_ds/Accounts
This should provide a response similar to below response.
{
“@odata.context”: “$metadata#Accounts”,
“value”: []
}
The format of the URL is created from the below template. If you have given a different name to the dataservice and the data source, you can create the URL by referring to the below template.
GET http://localhost:9763/odata/{dataserviceName}/{datasourceId}/
Now the data service is up and running.
Create the managed API in WSO2 API Manager
Let’s start the WSO2 API Manager by executing the below command from the APIM_HOME/bin directory.
$ sh wso2server.sh
Note: If you are running both APIM and EI in the same computer, you need to change the offset of the APIM node by changing the “offset” field value to “1” within the APIM_HOME/repository/conf/carbon.xml file.
Since I’m running both the instances in the same computer, the APIM instance will start with the 9444 port.
Let’s log into the API publisher interface and create the API which exposes the previously created OData service in a secured manner.
https://localhost:9444/publisher
Click on the “Add New API” button and select the option 3 from the next window which is “Design a New REST API” and click on “start creating”.
Provide the relevant information as depicted in the above figure and select the HTTP methods under the “API Definition” section and click on “Add” button which will add the relevant API resources with the selected methods to this API. Click on “Next: Implement” to move to the next section.
In this window, you need to provide the URL of the data service which was created in the previous step. You can use the below URL if you have followed the instructions as it is up until now.
http://localhost:9763/odata/ODataAPISample/odata_sample_ds
Now click on the “Next: Manage” button and go to the next screen.
In this window, select the “Subscription Tiers” as “Unlimited” and keep the other values as default. Now click on the “Save & Publish” button which will create the API and publish to the API store.
Now the API is created and available for use with a valid security (OAuth2) token.
Consume the API through API Store
Let’s log into the API Store and generate an OAuth2 token to access the protected API.
You can log into the API store with the username and password which you have configured (default values are admin:admin).
You should now see the “AccountAPI” which was created in the previous step and you can click on that API. You should “subscribe” to this API before consuming it. Let’s go ahead and click on the “Subscribe” button which will subscribe you to the API with “default application”.
Once you click on the “Subscribe” button, it will pop up another window and click on the “Go to subscriptions” options to see your current subscriptions and generate an access token.
Figure 8: Generate access token from store
Click on the “Production Keys” tab and it will provide you with the information about the OAuth2 specific details. Click on the “Generate Keys” button to generate an “OAuth2 token” which can be used to access the API. Once it is generated, it will be hidden for security purposes. You should click on the “Show keys” button to see the access token. You can copy the token into the clipboard by clicking on the copy icon at the left most of the “Access Token” text box.
Now you have the access token. Let’s execute the API using this token. Let’s say I have the below token generated.
f9979ea4–5838–3b66-bafb-9a46bc213f44
We can execute the API with the below mentioned CURL request.
curl -v -H “Authorization: Bearer f9979ea4–5838–3b66-bafb-9a46bc213f44” http://localhost:8281/accounts/v1
You should get a response similar to below.
{
“@odata.context”: “http://localhost:9773/odata/ODataAPISample/odata_sample_ds/$metadata",
“value”: [
{
“name”: “Accounts”,
“url”: “Accounts”
}
]
}
Now let’s play around with some OData operations so that you understand the real value of this created API. Let’s say you need to add an entry to the database table. All you need to do is send a POST request with the content similar to below.
curl -v -X POST -d ‘{ “balance” :124.12, “accountId” : 2 }’ -H “Content-Type: application/json” -H “Authorization: Bearer f9979ea4–5838–3b66-bafb-9a46bc213f44” http://localhost:8280/accounts/v1/Accounts
You should get a 201 created response with the below content.
{“@odata.context”:”$metadata#Accounts”,”@odata.etag”:”2858dc8b-783c-3b47–8245–4347f9cedecb”,”accountId”:2,”balance”:124.12}
Now if you send the GET request to retrieve information about the “Accounts” table, you should get the table entry which you have just created.
curl -v -H “Authorization: Bearer f9979ea4–5838–3b66-bafb-9a46bc213f44” http://localhost:8281/accounts/v1/Accounts
The response should be similar to below. Here you can see the information which you have updated in the previous step.
{“@odata.context”:”$metadata#Accounts”,”value”:[{“@odata.etag”:”2858dc8b-783c-3b47–8245–4347f9cedecb”,”accountId”:2,”balance”:124.12}]}
Thos are just a few examples of how you can play around with the database using the exposed REST API in a secured manner. You can learn more about OData by referring to the below tutorial.
https://www.odata.org/getting-started/understand-odata-in-6-steps/
Advantages of exposing your database as a managed API
This approach provides flexibility and security to your database while providing access to your databases to the users. Here are a few advantages.
- Ability to access all the tables in the database through a single secured API. If required, multiple APIs can be created for different tables.
- Ability to secure your database access through a more modern approach rather than depending on traditional username/password based security
- Ability to control the access to the database by applying throttling policies based on different users and roles
- Ability to improve the performance through caching provided by the API Manager
- Ability to monitor API access through API analytics
Moving to a modern microservices architecture
If you are in a microservices movement and needed to implement these services in a container friendly manner, you can use the WSO2 Micro Integrator and WSO2 API Microgateway in place of the WSO2 EI and WSO2 APIM respectively. You can learn more about these products by referring to the below documentation pages.
https://docs.wso2.com/display/AM260/Working+with+the+API+Microgateway
https://docs.wso2.com/display/EI640/Working+with+the+Micro+Integrator
