Skip to content

Data-backed Service

Ballerina is an open-source programming language that empowers developers to integrate their system easily with the support of connectors. In this guide, we are mainly focusing on building a database-backed RESTful web service with Ballerina. You can find other integration modules from the wso2-ballerina GitHub repository.

Data inside a database can be exposed to the outside world by using a database-backed RESTful web service. RESTful API calls enable you to add, view, update, and remove data stored in a database from the outside world.

What you'll build

You'll build an employee data management REST service that performs CRUD Operations (Create, Read, Update, Delete) on a MySQL database. Also, this guide walks you through the process of accessing relational data via the Ballerina language. The service will have the following functionalities.

  • Add new employees to the database via HTTP POST method.
  • Retrieve details of an existing employee from the database via HTTP GET method.
  • Update an existing employee in the database via the HTTP PUT method.
  • Delete an existing employee from the database via the HTTP DELETE method.

This service will deal with a MySQL database and expose the data operations as a web service. Refer to the following diagram to understand the complete end-to-end scenario.

database-backed RESTful web service

Before you begin

Get the code

Pull the module from Ballerina Central using the following command.

ballerina pull wso2/data_backed_service

Alternately, you can download the ZIP file and extract the contents to get the code.

Download ZIP

Implementation

If you want to skip the basics, you can download the git repo and directly move to the Testing section by skipping the Implementation section.

Create a project.

$ ballerina new data-backed-service

Navigate into the project directory and add a new module.

$ ballerina add data_backed_service

Create a folder called lib under the project root path. Copy the JDBC driver for MySQL into the lib folder.

Add a ballerina.conf file and rename the .bal files with meaningful names as shown in the project structure given below.

data-backed-service
├── Ballerina.toml
├── ballerina.conf
├── lib
│    └── mysql-connector-java-x.x.x.jar
└── src
    └── data_backed_service
        ├── resources
        ├── Module.md
        ├── employee_db_service.bal
        └── tests
            ├── resources
            └── employee_db_service_test.bal
Open the project with VS Code and write the integration implementation and tests in the employee_db_service.bal and employee_db_service_test.bal files respectively.

Developing the SQL Data-Backed Web Service

Ballerina language has built-in support for writing web services. The service keyword in Ballerina simply defines a web service. Inside the service block, we can have all the required resources. You can define a resource function inside the service. You can implement the business logic inside a resource function using Ballerina language syntax.

The Ballerina code for the employee data service with resource functions to add, retrieve, update, and delete employee data can be found in the employee_db_service.bal file.

import ballerina/config;
import ballerina/http;
import ballerina/jsonutils;
import ballerina/lang.'int as ints;
import ballerina/log;
import ballerinax/java.jdbc;

listener http:Listener httpListener = new(9090);

type Employee record {
    string name;
    int age;
    int ssn;
    int employeeId;
};

// Create SQL client for MySQL database
jdbc:Client employeeDB = new({
        url: config:getAsString("DATABASE_URL", "jdbc:mysql://127.0.0.1:3306/EMPLOYEE_RECORDS"),
        username: config:getAsString("DATABASE_USERNAME", "root"),
        password: config:getAsString("DATABASE_PASSWORD", "root"),
        dbOptions: { useSSL: false }
    });

// Service for the employee data service
@http:ServiceConfig {
    basePath: "/records"
}
service EmployeeData on httpListener {

    @http:ResourceConfig {
        methods: ["POST"],
        path: "/employee/"
    }
    resource function addEmployeeResource(http:Caller httpCaller, http:Request request) {
        // Initialize an empty http response message
        http:Response response = new;

        // Extract the data from the request payload
        var payloadJson = request.getJsonPayload();

        if (payloadJson is json) {
            Employee|error employeeData = Employee.constructFrom(payloadJson);

            if (employeeData is Employee) {
                // Validate JSON payload
                if (employeeData.name == "" || employeeData.age == 0 || employeeData.ssn == 0 ||
                    employeeData.employeeId == 0) {
                    response.setPayload("Error : json payload should contain
                    {name:<string>, age:<int>, ssn:<123456>, employeeId:<int>}");
                    response.statusCode = http:STATUS_BAD_REQUEST;
                } else {
                    // Invoke insertData function to save data in the MySQL database
                    json ret = insertData(employeeData.name, employeeData.age, employeeData.ssn,
                        employeeData.employeeId);
                    // Send the response back to the client with the employee data
                    response.setPayload(ret);
                }
            } else {
                // Send an error response in case of a conversion failure
                response.statusCode = http:STATUS_BAD_REQUEST;
                response.setPayload("Error: Please send the JSON payload in the correct format");
            }
        } else {
            // Send an error response in case of an error in retriving the request payload
            response.statusCode = http:STATUS_INTERNAL_SERVER_ERROR;
            response.setPayload("Error: An internal error occurred");
        }
        var respondRet = httpCaller->respond(response);
        if (respondRet is error) {
            // Log the error for the service maintainers.
            log:printError("Error responding to the client", err = respondRet);
        }
    }

    @http:ResourceConfig {
        methods: ["GET"],
        path: "/employee/{employeeId}"
    }
    resource function retrieveEmployeeResource(http:Caller httpCaller, http:Request request, string
        employeeId) {
        // Initialize an empty http response message
        http:Response response = new;
        // Convert the employeeId string to integer
        var empID = ints:fromString(employeeId);
        if (empID is int) {
            // Invoke retrieveById function to retrieve data from Mymysql database
            var employeeData = retrieveById(empID);
            // Send the response back to the client with the employee data
            response.setPayload(<@untainted> employeeData);
        } else {
            response.statusCode = http:STATUS_BAD_REQUEST;
            response.setPayload("Error: employeeId parameter should be a valid integer");
        }
        var respondRet = httpCaller->respond(response);
        if (respondRet is error) {
            // Log the error for the service maintainers.
            log:printError("Error responding to the client", err = respondRet);
        }
    }

    @http:ResourceConfig {
        methods: ["PUT"],
        path: "/employee/"
    }
    resource function updateEmployeeResource(http:Caller httpCaller, http:Request request) {
        // Initialize an empty http response message
        http:Response response = new;

        // Extract the data from the request payload
        var payloadJson = request.getJsonPayload();
        if (payloadJson is json) {
            Employee|error employeeData = Employee.constructFrom(payloadJson);

            if (employeeData is Employee) {
                if (employeeData.name == "" || employeeData.age == 0 || employeeData.ssn == 0 ||
                    employeeData.employeeId == 0) {
                    response.setPayload("Error : json payload should contain
                        {name:<string>, age:<int>, ssn:<123456>,employeeId:<int>} ");
                    response.statusCode = http:STATUS_BAD_REQUEST;
                } else {
                    // Invoke updateData function to update data in mysql database
                    json ret = updateData(employeeData.name, employeeData.age, employeeData.ssn,
                        employeeData.employeeId);
                    // Send the response back to the client with the employee data
                    response.setPayload(ret);
                }
            } else {
                // Send an error response in case of a conversion failure
                response.statusCode = http:STATUS_BAD_REQUEST;
                response.setPayload("Error: Please send the JSON payload in the correct format");
            }
        } else {
            // Send an error response in case of an error in retriving the request payload
            response.statusCode = http:STATUS_INTERNAL_SERVER_ERROR;
            response.setPayload("Error: An internal error occurred");
        }
        var respondRet = httpCaller->respond(response);
        if (respondRet is error) {
            // Log the error for the service maintainers.
            log:printError("Error responding to the client", err = respondRet);
        }
    }

    @http:ResourceConfig {
        methods: ["DELETE"],
        path: "/employee/{employeeId}"
    }
    resource function deleteEmployeeResource(http:Caller httpCaller, http:Request request, string
        employeeId) {
        // Initialize an empty http response message
        http:Response response = new;
        // Convert the employeeId string to integer
        var empID = ints:fromString(employeeId);
        if (empID is int) {
            var deleteStatus = deleteData(empID);
            // Send the response back to the client with the employee data
            response.setPayload(deleteStatus);
        } else {
            response.statusCode = http:STATUS_BAD_REQUEST;
            response.setPayload("Error: employeeId parameter should be a valid integer");
        }
        var respondRet = httpCaller->respond(response);
        if (respondRet is error) {
            // Log the error for the service maintainers.
            log:printError("Error responding to the client", err = respondRet);
        }
    }
}

public function insertData(string name, int age, int ssn, int employeeId) returns json {
    json updateStatus;
    string sqlString =
    "INSERT INTO EMPLOYEES (Name, Age, SSN, EmployeeID) VALUES (?,?,?,?)";
    // Insert data to SQL database by invoking update action
    var ret = employeeDB->update(sqlString, name, age, ssn, employeeId);
    // Check type to verify the validity of the result from database
    if (ret is jdbc:UpdateResult) {
        updateStatus = { "Status": "Data Inserted Successfully" };
    } else {
        updateStatus = { "Status": "Data Not Inserted", "Error": "Error occurred in data update" };
        // Log the error for the service maintainers.
        log:printError("Error occurred in data update", err = ret);
    }
    return updateStatus;
}

public function retrieveById(int employeeID) returns json {
    json jsonReturnValue = {};
    string sqlString = "SELECT * FROM EMPLOYEES WHERE EmployeeID = ?";
    // Retrieve employee data by invoking select remote function defined in ballerina sql client
    var ret = employeeDB->select(sqlString, (), employeeID);
    if (ret is table<record {}>) {
        // Convert the sql data table into JSON using type conversion
        jsonReturnValue = jsonutils:fromTable(ret);
    } else {
        jsonReturnValue = { "Status": "Data Not Found", "Error": "Error occurred in data retrieval" };
        log:printError("Error occurred in data retrieval", err = ret);
    }
    return jsonReturnValue;
}

public function updateData(string name, int age, int ssn, int employeeId) returns json {
    json updateStatus;
    string sqlString =
    "UPDATE EMPLOYEES SET Name = ?, Age = ?, SSN = ? WHERE EmployeeID  = ?";
    // Update existing data by invoking update remote function defined in ballerina sql client
    var ret = employeeDB->update(sqlString, name, age, ssn, employeeId);
    if (ret is jdbc:UpdateResult) {
        if (ret.updatedRowCount > 0) {
            updateStatus = { "Status": "Data Updated Successfully" };
        } else {
            updateStatus = { "Status": "Data Not Updated" };
        }
    } else {
        updateStatus = { "Status": "Data Not Updated",  "Error": "Error occurred during update operation" };
        // Log the error for the service maintainers.
        log:printError("Error occurred during update operation", err = ret);
    }
    return updateStatus;
}

public function deleteData(int employeeID) returns json {
    json updateStatus;

    string sqlString = "DELETE FROM EMPLOYEES WHERE EmployeeID = ?";
    // Delete existing data by invoking update remote function defined in ballerina sql client
    var ret = employeeDB->update(sqlString, employeeID);
    if (ret is jdbc:UpdateResult) {
        updateStatus = { "Status": "Data Deleted Successfully" };
    } else {
        updateStatus = { "Status": "Data Not Deleted",  "Error": "Error occurred during delete operation" };
        // Log the error for the service maintainers.
        log:printError("Error occurred during delete operation", err = ret);
    }
    return updateStatus;
}

A remote function in Ballerina indicates that it communicates with some remote service through the network. In this case, the remote service is a MySQL database. employeeDB is the reference name for the MySQL client object that encapsulates the aforementioned set of remote functions. The rest of the code is for preparing SQL queries and executing them by calling these remote functions of the Ballerina MySQL client.

You can implement custom functions in Ballerina that perform specific tasks. For this scenario, we have included the following functions to interact with the MySQL database.

  • insertData
  • retrieveById
  • updateData
  • deleteData

Testing

Before you begin

  • Download & run the SQL script initializeDataBase.sql provided inside the resources directory, to initialize the database and to create the required table.

    $ mysql -u username -p <initializeDataBase.sql 

  • Add database configurations to the ballerina.conf file

  • ballerina.conf file can be used to provide external configurations to Ballerina programs. Since this guide needs MySQL database integration, a Ballerina configuration file is used to provide the database connection properties to our Ballerina program. This configuration file has the following fields. Change these configurations with your connection properties accordingly.
    DATABASE_URL = "jdbc:mysql://127.0.0.1:3306/EMPLOYEE_RECORDS"
    DATABASE_USERNAME = "root"
    DATABASE_PASSWORD = "root"

Invoking the employee database service

Let’s build the module. Navigate to the project directory and execute the following command.

$ ballerina build data_backed_service

The build command would create an executable .jar file. Now run the .jar file created in the above step. Path to the ballerina.conf could be provided using the --b7a.config.file option.

$ java -jar target/bin/data_backed_service.jar --b7a.config.file=path/to/ballerina.conf/file
  • Now you can test the functionality of the employee database management RESTFul service by sending HTTP requests for each database operation. For example, this guide uses the cURL commands to test each operation of the employeeService as follows.

Add new employee

curl -v -X POST -d '{"name":"Alice", "age":20,"ssn":123456789,"employeeId":1}' \
"http://localhost:9090/records/employee" -H "Content-Type:application/json"
Output:
{"Status": "Data Inserted Successfully"}

Retrieve employee data

curl -v "http://localhost:9090/records/employee/1"
Output:
[{"EmployeeID": 1, "Name": "Alice", "Age": 20, "SSN": 123456789}]
Update an existing employee data
curl -v -X PUT -d '{"name":"Alice Updated", "age":30,"ssn":123456789,"employeeId":1}' \
"http://localhost:9090/records/employee" -H "Content-Type:application/json"
Output:
{"Status": "Data Updated Successfully"}

Delete employee data

curl -v -X DELETE "http://localhost:9090/records/employee/1"
Output:
{"Status": "Data Deleted Successfully"}

Top