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 named wso2mi, and the Employees 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 select RDBMS 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 variable employee_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 the EmployeeNumber 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, and Email) 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"
    }
}

Property	Description
Mapping Name	`employee_number`
Query Parameter	`EmployeeNumber`
Parameter Type	`SCALAR`
SQL Type	`STRING`