Build a data service in Ballerina

This guide helps you understand the basics of Ballerina constructs, which allow you to work with data services.

Set up the prerequisites

To complete this tutorial, you need:

  1. Ballerina 2202.0.0 (Swan Lake) or greater
  2. A text editor

Tip: Preferably, Visual Studio Code with the Ballerina extension installed.

  1. A command terminal

Understand the implementation

This tutorial describes how to connect to a MySQL database and perform queries against it using Ballerina via a basic use case of creating, maintaining, and interacting with a database of employees in an organization. It also elaborates on how you can create an HTTP RESTful API using Ballerina that can be used to perform basic CRUD operations on the database.

Data Service Architecture

Info: The outlined methodology can be used to work with PostgreSQL, SQL Server, OracleDB, or any other relational database as well using the PostgreSQL,MSSQL, OracleDB, and JDBC connectors for Ballerina respectively.

Set up a MySQL server instance

Select one out of the methods below to set up a MySQL server.

Tip: Keep the connection and authentication details for connecting to the MySQL server including the hostname, port, username and password noted down.

  1. Install a MySQL server on your machine locally by downloading and installing MySQL for different platforms.
  2. Use a cross-platform web-server solution such as XAMPP or WampServer.
  3. Use Docker to create a MySQL server deployment.
  4. Use a cloud-based MySQL solution such as Google’s CloudSQL, Amazon’s RDS for MySQL, or Microsoft’s Azure database for MySQL.

Create a database and table

Connect to the MySQL server using the terminal (or any other preferred method), and execute the commands below to create a database and table.

Create the service package

Ballerina uses packages to group code. You need to create a Ballerina package and write the business logic in it. In the terminal, execute the command below to create the Ballerina package for the implementation.

Info: For more information on Ballerina packages, see Organizing Ballerina code.

bal new data_service

This creates a directory named data_service with the files below.

.
├── data_service
│   ├── Ballerina.toml
│   └── main.bal

Tip: Remove the automatically-created main.bal file as you are not going to use it in this guide.

Create the service

Create a record to represent an employee

In Ballerina, records are a data type that maps keys to values. Define a closed record to represent a single row in the Employees table in the main.bal file.

Info: This record type is the basis for interacting with the database.

Add the MySQL driver

The MySQL driver JAR is necessary to connect to and interact with a MySQL server. Select one out of the methods below to add it.

  1. Import the ballerinax/mysql.driver package in your main.bal file. This package bundles the latest MySQL driver so that the MySQL connector can be used in Ballerina packages easily.

  2. Update the Ballerina.toml file with the Maven dependency params for the MySQL driver.

  3. Download the driver JAR manually and update the path in the Ballerina.toml file

Define MySQL configurations

In the package directory, create a new file named Config.toml and specify the configurations below to connect to the MySQL database.

To redefine the above variables and access them from within your Ballerina program, add the code below in the main.bal file.

Note: For more information on defining configurable variables in Ballerina, see Provide values to configurable variables.

Connect to the database

You can connect to the MySQL database by creating a client.

Import the required packages

To import the mysql and sql packages for creating the client, add the code below in the main.bal file.

Create the MySQL client

To use the mysql:Client to the database, add the code below in the main.bal file.

Run the MySQL client

Execute the command below to run the client.

bal run

If the program runs without throwing an error, that indicates that the connection has been established successfully. This client can be defined globally and be used across all parts of the program.

Info: The MySQL package provides additional connection options and the ability to configure connection pool properties when connecting to the database which, are not covered in this tutorial. To learn more about this, see mysql:Client.

Execute the queries

The mysql:Client provides two primary remote methods for performing queries.

  1. query() - Executes an SQL query and returns the results (rows) from the query. The queryRow() method is a variation of this method, which returns at most a single row from the result.

  2. execute() - Executes an SQL query and returns only the metadata of the execution.

To use the query(), queryRow(), and execute() methods, which can perform basic CRUD operations against the MySQL database, add the code below to the main.bal file.

The main.bal file complete code

Expose the database via an HTTP RESTful API

After you have defined the methods necessary to manipulate the database, expose these selectively via an HTTP RESTful API.

Import the required packages

For this, create a file named service.bal inside the Ballerina package directory (ata_service), and add the code below to import the Ballerina HTTP module.

Create a service

To create the service, add the code below to the service.bal file.

Create the resource functions

Within this service, you can define resource functions to provide access to the database. The code snippet below demonstrates a resource function that can be used to create a new employee via a POST request.

The service.bal file complete code

The complete code in the service.bal will be as follows.

Run the service

Execute the command below to run the service.

bal run

You view the output below.

Info: This creates an /employees endpoint on port 8080, which can be accessed via a browser by visiting http://locahost:8080/employees.

Try the service

Invoke the defined resource function by sending the POST request below to http://localhost:8080/employees with the required data as a JSON payload.

curl --location --request POST 'http://localhost:8080/employees/' \
    --header 'Content-Type: text/plain' \
    --data-raw '{
        "employee_id": 6,
        "first_name": "test",
        "last_name": "test",
        "email": "test@test.com",
        "phone": "882 771 110",
        "hire_date": {
            "year": 2021,
            "month": 12,
            "day": 16
        },
        "manager_id": 1,
        "job_title": "Sales Manager"
    }'

Learn more

To learn more about MySQL and HTTP support in Ballerina, see the following: