How to Expose a Datasource as a Service¶
What you'll build¶
In this tutorial, you will create a Data Service that exposes an RDBMS data source as a RESTful API.
What is a Data Service?
A Data Service in WSO2 Micro Integrator allows you to expose data from relational databases, CSV files, or other data sources as RESTful or SOAP APIs. It simplifies integration by handling database queries, input/output mappings, and data transformation without writing custom code. To explore Data Services in detail, see the Data Services documentation.
When a client sends a request to the Micro Integrator, the employee number will be extracted from the request, used in a SQL query to retrieve employee information, and the result will be returned in JSON format.
Let's get started!¶
Step 1: Set up the workspace¶
You need Visual Studio Code (VS Code) with the Micro Integrator for VS Code extension installed.
Info
See the Install Micro Integrator for VS Code documentation to learn how to install Micro Integrator for VS Code.
Set up MySQL server¶
Follow the steps below to set up the MySQL server.
-
Install the MySQL server.
-
Connect to the MySQL server using a MySQL client to create and populate the necessary database for this tutorial. Follow the steps below to create the
Company
database, a new user namedwso2mi
, and theEmployees
table. -
Create a database named
Company
.CREATE DATABASE Company;
-
Create a user and grant the user access to the Database.
CREATE USER 'wso2mi'@'localhost' IDENTIFIED BY 'wso2mi'; GRANT ALL PRIVILEGES ON Company.* TO 'wso2mi'@'localhost';
-
Create the Employees table inside the Company database:
USE Company; CREATE TABLE Employees (EmployeeNumber int(11) NOT NULL, FirstName varchar(255) NOT NULL, LastName varchar(255) DEFAULT NULL, Email varchar(255) DEFAULT NULL, Salary varchar(255)); INSERT INTO Employees (EmployeeNumber, FirstName, LastName, Email, Salary) values (3, "Edgar", "Code", "[email protected]", 100000);
-
Download the JDBC driver for MySQL from here. Make sure to download a version that is compatible with your MySQL server version. You will need this driver when configuring the MySQL server with the Micro Integrator.
Step 2: Create a data service¶
Follow the steps given below to create a new data service.
Create an Integration project¶
The Integration project will contain all the required artifacts for the integration solution.
-
Launch VS Code with the Micro Integrator extension installed.
-
Click on the Micro Integrator icon on the Activity Bar of the VS Code editor.
-
Click Create New Project on Design View.
Next, the Project Creation Form will be opened.
-
In the Project Creation Form, enter
EmployeeDataServiceTutorial
as the Project Name. -
Provide a location under Select Project Directory.
-
Click Create.
Once you click Create, the Add Artifact pane will be opened.
Create a data service with a data source¶
-
In the Add Artifact interface, click + View More under Create an Integration.
-
Select Data Service under Other Artifacts to open the Data Service Form.
-
Provide
EmployeeDataService
as the Data Service Name. In the next step, you will configure a datasource that enables the service to connect to the MySQL database. -
Click Add Datasource.
-
Set
EmployeeDatasource
as the Datasource Identifier, and selectRDBMS
for the Datasource Type, since this tutorial uses a MySQL database. Once you select the datasource type, the relevant configuration fields will be displayed.Property Value Description Database Engine Select MySQL
Type of the relational database. Hostname localhost
Hostname of the MySQL instance. Port 3306
Port used to access the MySQL instance. Database Name Company
Name of the database to connect to. Username wso2mi
Username for database authentication. Password wso2mi
Password for database authentication. Note
The rest of the configurations will be left as default. For advanced configurations, refer to Datasource Parameters.
-
Click Next.
-
You will be directed to the Select Database Driver window to choose a driver. Browse and select the driver JAR file that you downloaded in the Set up MySQL Server step, and then click Next.
You will then see the Test Connection form, where you can verify the connection to the data source using the provided username and password.
-
Click Test Connection to verify the connection. A success or failure message will appear based on the result.
-
Once the connection is successful, click Create to finalize the data source setup.
-
Finally, click Create in the Data Service form to add the Data Service to the integration project. In the next steps, you will learn how to create a resource and define a SQL query.
Create a resource¶
Now, let's create a REST resource that will be used to invoke the SQL query.
-
In the MI Project Explorer, select the EmployeeDataService that you created in the previous step.
-
In the Data Service Designer, click the + Resources button to add a new resource.
In the next step, you will create a resource that accepts the Employee Number as a path parameter. This value will be used to query the SQL database.
-
Enter the following resource details, then click Add to insert the resource into the Data Service.
Property Value Description Resource Path Employee/{EmployeeNumber}
The request URL should match this resource path. The {EmployeeNumber}
variable will be replaced with the value sent in the request.Resource Method GET
This resource will accept POST requests.
Tip
Alternatively, you can generate a Data Service directly from a data source. For more information, refer to Generate Data Services.
Configure data service¶
Let’s write an SQL query to retrieve data from the MySQL data source you configured in the previous step, using the Employee Number provided as a path parameter in the GET resource.
-
Open the DataService View of the newly created resource by clicking the
GET Employee/{EmployeeNumber}
resource under Resources in the Data Service Designer.Info
A single Data Service resource consists of the following key elements:
- Input Mapping – Binds incoming request parameters to SQL query variables.
- Query – Defines the database query using the mapped input parameters.
- Transformation – Defines the output type (such as XML or JSON) and allows optional reshaping of the query result before it is sent in the response.
- Output Mapping – Performs the actual mapping of the SQL query result to the selected response structure, organizing it into a user-friendly format.
-
Click on Input Mapping in the DataService view.
Here, you will map the
EmployeeNumber
path parameter to the SQL query variableemployee_number
, which will be used in the SQL query. -
Click Add Parameter, specify the following values, and click Save to add the input mapping for the Employee Number.
Property Description Mapping Name employee_number
Query Parameter EmployeeNumber
Parameter Type SCALAR
SQL Type STRING
-
Finally, click Submit in the Edit Input Mapping pane to complete the input mapping configuration.
-
Click on Query in the DataService view. Here, you will write the SQL query to retrieve employee data using the
employee_number
SQL query variable. -
Enter the following SQL query in the Query / Expression field, then click Submit to save the query.
SELECT EmployeeNumber, FirstName, LastName, Email FROM Employees WHERE EmployeeNumber=:employee_number
The
:employee_number
syntax is used to reference the SQL query variable you configured earlier. When the resource is invoked, the value passed through theEmployeeNumber
path parameter will be substituted into this query. -
Click on Transformation in the DataService view. Here, you will set the content type to JSON, as the client expects a JSON response.
-
Choose
JSON
as the Output Type, then click Submit to save the transformation settings. -
Click on Output Mapping in the DataService view. Here, you will define the response JSON using the SQL results (
EmployeeNumber
,FirstName
,LastName
, andEmail
) retrieved from the query defined earlier. -
Provide the following JSON template, then click Submit to save the output mapping.
{ "Employee":{ "EmployeeNumber":"$EmployeeNumber", "FirstName":"$FirstName", "LastName":"$LastName", "Email":"$Email" } }
In this JSON template, each placeholder (e.g.,
$EmployeeNumber
,$FirstName
) refers to a column in the SQL query result.
The Output Mapping binds each of these individual values to a corresponding field in the JSON response.Since we're returning a single record, the mapping is done for a single row, and each key in the
Employee
object maps directly to a column value from the query.
Step 3: Build and run the artifacts¶
Now that you have developed the data service using the Micro Integrator for the Visual Studio Code plugin, it's time to deploy the integration to the Micro Integrator server runtime.
Note
If you didn’t select a driver JAR file in the Create a Data Service with a Data Source step, make sure you have added the JAR file to the <Project_Path>/deployment/libs
directory, or copied it to the <MI_HOME>/lib
directory.
Click the Build and Run icon located in the top right corner of VS Code.
Step 4: Test the data service¶
Let's test the use case by sending a simple client request that invokes the service.
Send the client request¶
When you run the integration artifact as in Step 3, the Runtime Services interface is opened up. You can see all the available services.
Let's send a request to the API resource. You can use Postman or any other HTTP Client:
-
Open the Postman application. If you do not have the application, download it from here : Postman.
-
Add the request information as given below and click the Send button.
Method GET
Headers Accept=application/json
URL http://localhost:8290/services/EmployeeDataService.HTTPEndpoint/Employee/3
If you want to send the client request from your terminal:
- Install and set up cURL as your REST client.
- Execute the following command.
curl -X GET http://localhost:8290/services/EmployeeDataService.HTTPEndpoint/Employee/3 -H "Accept: application/json"
Analyze the response¶
You will see the following response received by your HTTP Client:
{
"Employee": {
"Email": "[email protected]",
"FirstName": "Edgar",
"EmployeeNumber": "3",
"LastName": "Code"
}
}