Building a Hospital Management Service with Ballerina and MySQL Database Integration

In our previous article, we introduced how to create a simple hospital management service using Ballerina. The service allowed us to manage patient data with basic operations like adding, viewing, and listing patients, all stored in an in-memory map. While this was a great starting point, real-world applications often require more robust and persistent data storage.

In this article, we’ll take the next step and integrate a MySQL database with our hospital management service. This will allow us to persist patient records and retrieve them even after the service is restarted. By the end, you’ll have a RESTful API that interacts with a MySQL database to perform CRUD operations on patient data.

Step 1: Setting Up MySQL

Before diving into the code, we need to set up a MySQL database to store patient records.

Installing MySQL

If you haven’t installed MySQL on your machine yet, you can download and install it from the official MySQL website. Follow the setup instructions according to your operating system.

Once installed, log into MySQL,

mysql -u root -p

Creating the Database and Table

After logging into MySQL, you can create a database for storing hospital data. For our example, we’ll create a hospital_db database and a patients table to store patient information,

CREATE DATABASE hospital_db;
USE hospital_db;

CREATE TABLE patients (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    age INT NOT NULL,
    disease VARCHAR(255) NOT NULL
);

This table will hold each patient’s ID, name, age, and the disease they are diagnosed with.

Step 2: Adding MySQL to the Ballerina Project

First, we need to add the MySQL connector to our project. Inside the project directory, run the following command to add the MySQL package,

bal add mysql

This will download and include the necessary MySQL drivers for Ballerina to interact with the database.

Step 3: Modifying the Service to Connect with MySQL

Now, let’s modify the hospital management service to integrate with the MySQL database. We’ll update our service so that instead of storing data in an in-memory map, it will store and retrieve patient information from the database.

Database Configuration

In Ballerina, database connectivity is straightforward. We will create a MySQL client by providing the necessary connection details such as host, username, password, and database name.

Updated Service Code

Here is the updated version of the service that uses a MySQL database to manage patient data,

import ballerina/http;
import ballerina/sql;
import ballerinax/mysql.driver as _;

# Database Configuration
configurable string dbHost = "localhost";
configurable string dbUser = "root";
configurable string dbPassword = "root";
configurable string dbName = "hospital_db";

// Initialize MySQL client
mysql:Client dbClient = check new (host = dbHost, user = dbUser, password = dbPassword, database = dbName);

type Patient record {
    int id?;
    string name;
    int age;
    string disease;
};

service /hospital on new http:Listener(8080) {

    // Add a new patient
    resource function post patients(http:Caller caller, http:Request req) returns error? {
        json payload = check req.getJsonPayload();
        Patient newPatient = check payload.cloneWithType(Patient);

        // Insert patient data into the database
        sql:ParameterizedQuery sqlQuery = `INSERT INTO patients (name, age, disease)
                                           VALUES (${newPatient.name}, ${newPatient.age}, ${newPatient.disease})`;
        var result = dbClient->execute(sqlQuery);
        if result is sql:UpdateResult {
            newPatient.id = result.lastInsertId.toInt();
            check caller->respond({message: "Patient added successfully", id: newPatient.id});
        } else {
            check caller->respond({message: "Failed to add patient"});
        }
    }

    // View patient by ID
    resource function get patients/[int id](http:Caller caller, http:Request req) returns error? {
        Patient patient;
        sql:ParameterizedQuery sqlQuery = `SELECT id, name, age, disease FROM patients WHERE id = ${id}`;
        stream<Patient, sql:Error?> patientStream = dbClient->query(sqlQuery, Patient);

        error? e = patientStream.forEach(function(Patient result) {
            patient = result;
        });
        
        if patient.id is int {
            check caller->respond(patient);
        } else {
            check caller->respond({message: "Patient not found"});
        }
    }

    // List all patients
    resource function get patients(http:Caller caller, http:Request req) returns error? {
        Patient[] patientsList = [];
        sql:ParameterizedQuery sqlQuery = `SELECT id, name, age, disease FROM patients`;

        stream<Patient, sql:Error?> patientStream = dbClient->query(sqlQuery, Patient);

        error? e = patientStream.forEach(function(Patient result) {
            patientsList.push(result);
        });

        check caller->respond(patientsList);
    }
}

What’s Changed?

MySQL Client Initialization: We configure the MySQL client with connection details (host, user, password, database).

Adding a Patient: When a patient is added via the POST /patients endpoint, we insert the data into the patients table in the database using an SQL INSERT query.

Viewing and Listing Patients: When retrieving patients, we query the database using SQL SELECT queries to either fetch a patient by their ID or list all patients.

Step 4: Running the Service

With the code ready, we can now run the updated Ballerina service and connect it to the MySQL database.

Start the MySQL service and make sure your database is running. Then, run the Ballerina service,

ballerina run hospital-service

The service will now interact with the MySQL database to store and retrieve patient records.

Step 5: Testing the Service

You can use curl or Postman to test the API endpoints as before, but this time the data will be persisted in the MySQL database.

Adding a Patient

To add a new patient, use the following curl command,

curl -X POST "http://localhost:8080/hospital/patients" \
     -H "Content-Type: application/json" \
     -d '{"name": "John Doe", "age": 35, "disease": "Flu"}'

If successful, you should receive a response indicating that the patient has been added, along with their ID.

Viewing a Patient by ID

To retrieve a patient’s details, you can query by their ID,

curl http://localhost:8080/hospital/patients/1

Listing All Patients

To retrieve a list of all patients, use,

curl http://localhost:8080/hospital/patients

Conclusion

By integrating MySQL into our Ballerina hospital management service, we’ve taken a step closer to building a real-world application. Now, patient records are stored persistently in a relational database, making the service more reliable and scalable.