How to Expose a Datasource as a Service¶

What you'll build¶

A data service provides a web service interface to access data that is stored in various data sources. The following sections describe how you can use Micro Integrator Extension for Visual Studio Code (MI for VS Code) to work with data services' artifacts.

Tip

Note that this feature is currently supported in Micro Integrator Extension for Visual Studio Code (MI for VS Code) for relational data sources and CSV files.

Let's get started!¶

Step 1: Set up the workspace¶

The following software and configurations are required to proceed with this tutorial:

Visual Studio Code (VS Code): with the Micro Integrator extension installed.
Java Development Kit (JDK): Ensure the JDK is properly configured in your system's PATH environment variable.
Apache Maven: Ensure Apache Maven is installed and its path is correctly set within the system's PATH environment variable.

Info

Follow the Install Micro Integrator for VS Code documentation for a complete installation guide.

MySQL server: Follow the steps below to set up the MySQL server.
1. Install the MySQL server.
2. Connect to the MySQL server using the MySQL client.
3. Download the JDBC driver for MySQL from here. Ensure that you download the JDBC driver version that matches your MySQL server version. You will need this when you configure the MySQL server with the Micro Integrator.
4. Create a database named Employees.
```
CREATE DATABASE Employees;
```
5. Create a user and grant the user access to the Database.
  
  CREATE USER 'user'@'localhost' IDENTIFIED BY 'password'; GRANT ALL PRIVILEGES ON Employees.* TO 'user'@'localhost';
6. Create the Employee table inside the Employees database:
```
USE Employees;
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);
```

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 DataSourceServiceTutorial as the Project Name.
Provide a location under Select Project Directory.
Click Create.

Create a data service with a data source¶

Navigate to the MI Project Explorer > Add artifact > Data Services.
Enter a name for the data service:

Property Description

Data Service Name RDBMSDataService
Click Add Datasource.

To create the data source connection specify the following values to create the new data source:

Property	Value
Datasource Identifier	`Datasource`
Datasource Type	`RDBMS`
Database Engine	`MySQL`
Hostname	`localhost`
Port	`3306`
Database Name	`Employees`
Username	`user`
Password	`password`
Driver Class	`com.mysql.jdbc.Driver`

Click Next.
You will be directed to the Select Database Driver window to choose a driver. Browse and select the driver file, such as a JAR file. For example: /Users/chathurangaj/Downloads/mysql-connector-j-8.3.0/mysql-connector-j-8.3.0.jar.
Click Next to complete creating the data source.
You will then see the Test Connection form, where you can test 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.
Once the connection is successful, click Create to finalize the data source creation.

Create a resource¶

Now, let's create a REST resource that can be used to invoke the query.

Click the new Data Service created in the previous step either from the side panel or from the overview page.
Click + Resources.
Enter the following resource details.

Property Description

Resource Path Employee/{EmployeeNumber}

Resource Method GET
Click Add.

Tip

Alternatively, you can generate a data service from a data source. For more information, refer to Generate Data Services.

Configure data service¶

Let's write an SQL query to GET data from the MySQL data source that you configured in the previous step:

Click new Resource created in the previous step.
Click Input Mapping in DataService View.
Click Add Parameter.

Specify the following values

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

Click Add. Then Click Submit.
Click Query in DataService View.
Specify the following values in the query details:

Parameter Value

Query ID GetEmployeeDetails

Datasource Datasource

SQL Query select EmployeeNumber, FirstName, LastName, Email from Employees where EmployeeNumber=:EmployeeNumber
Click Submit.
Click Transformation in DataService View.
Specify the following value:

Property Description

Grouped by Element Employees
Click Submit.
Click Output Mapping in DataService View.

Click Add Parameter. Specify the following values:

Property	Description
Mapping Type	Element
Datasource Type	column
Output Field Name	EmployeeNumber
Datasource Column Name	EmployeeNumber
Parameter Type	Scalar
Schema Type	String

Save the parameter.

Follow the same steps to create the following output parameters:

Mapping Type	Datasource Type	Output Field Name	Datasource Column Name	Parameter Type	Schema Type
Element	column	FirstName	FirstName	Scalar	string
Element	column	LastName	LastName	Scalar	string
Element	column	Email	Email	Scalar	string

Click Submit.

Step 3: Build and run the artifacts¶

Prerequisites

Before you begin, install Micro Integrator on your machine:

Go to the WSO2 Micro Integrator web page, click Download, provide necessary details, and then click Zip Archive to download the Micro Integrator distribution as a ZIP file.
Extract the ZIP file. The extracted folder will be referred as the <MI_HOME> folder.

Once you have downloaded and set up the Micro Integrator locally, follow the steps given below. Use one of the below two methods.

Click on the Command Palette on the top of the VS Code.
Type > to show the available commands.
Select MI: Add MI server.
Select Add MI server.
Select the folder where <MI_HOME> is located. This wll be set as the current server path.
Run the project.

Click the Build and Run icon located in the top right corner of the VS Code.

If the MySQL driver JAR does not exist in the /project-path/deployment/libs directory, you will get an exception such as Cannot load JDBC driver class com.mysql.jdbc.Driver when the Micro Integrator starts.

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¶

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

URL http://localhost:8290/services/RDBMSDataService.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/RDBMSDataService.HTTPEndpoint/Employee/3

Analyze the response¶

You will see the following response received by your HTTP Client:

<Employees xmlns="http://ws.wso2.org/dataservice">
  <EmployeeNumber>3</EmployeeNumber>
  <FirstName>Edgar</FirstName>
  <LastName>Code</LastName>
  <Email>[email protected]</Email>
</Employees>

Parameter	Value
Query ID	GetEmployeeDetails
Datasource	Datasource
SQL Query	select EmployeeNumber, FirstName, LastName, Email from Employees where EmployeeNumber=:EmployeeNumber

Method	`GET`
URL	`http://localhost:8290/services/RDBMSDataService.HTTPEndpoint/Employee/3`

Property	Description
Resource Path	Employee/{EmployeeNumber}
Resource Method	GET