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 datasources. 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 datasources 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 below steps to set up MySQL server.
1. Install the MySQL server.
2. Download the JDBC driver for MySQL from here. You will need this when you configure the MySQL server with the Micro Integrator.
3. Create a database named Employees.
```
CREATE DATABASE Employees;
```
4. 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';
5. 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 > Data Services.

Hover over Data Services and click the + icon that appears.

Enter a name for the data service:

Property Description

Data Service Name RDBMSDataService
Click Add Datasource.

To create the datasource connection specify the following values to create the new datasource:

Datasource Properties
Property	Value
Datasource ID	Datasource
Datasource Type	RDBMS
Datasource Type (Default/External)	Leave Default selected.
Database Engine	MySQL
Driver Class	com.mysql.jdbc.Driver
URL	jdbc:mysql://localhost:3306/Employees
User Name	user
Password	password

Click Add.
Click Create to create a data service with datasource.

Create a resource¶

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

Click new Data Service created in the previous step.
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 datasource. For more information, refer to Generate Data Services.

Configure data service¶

Let's write an SQL query to GET data from the MySQL datasource 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: Configure the Micro Integrator server¶

To add the MySQL database driver to the server:

Copy the MySQL driver JAR (see Setting up the Workspace) to /project-path/deployment/libs.

If the driver class does not exist in the relevant directory, you will get an exception such as Cannot load JDBC driver class com.mysql.jdbc.Driver when the Micro Integrator starts.

Step 4: 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.

Step 5: 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