Use Bal Persist with existing databases

bal persist

This article was written using Ballerina Swan Lake Update 9.0 (2201.9.0)

In this article, the new database introspection feature for bal persist is discussed. At the end of this article, we will have built a small application to try out the introspection feature.

Here is the outline of the article.

1. Ballerina and Bal Persist
2. Why do we need introspection?
3. Application architecture
4. Setting up a database
5. Initializing bal persist
6. Introspecting the database
7. Generating the client API
8. Writing application code

You can also refer to the source code of the final application from the link below.

GitHub - hasathcharu/bal-persist-introspection-tutorial

Ballerina and Bal persist

Ballerina is a programming language specialized in integration and cloud-native application development. bal persist is a feature in Ballerina that enables developers to seamlessly bring persistence to their applications.

The bal persist abstracts away the persistence logic from common data stores such as MySQL, PostgreSQL, MSSQL, Google Sheets, and Redis, while exposing developers to a generated client API that is easy to use and predictable. This article discusses the newly introduced database introspection feature in bal persist. In Update 09, bal persist has introspection support for MySQL data stores and support for other data stores such as PostgreSQL and MSSQL will be added in a future release.

Why do we need introspection?

When it comes to bal persist, it is capable of defining your own data model as Ballerina records and having bal persist generate the necessary client API and database initialization scripts to get you up and running with your preferred data store.

In this scenario, bal persist previously could not integrate with existing databases that were not created according to Ballerina naming conventions, and even still, you had to manually write Ballerina records to define the data model, which is a tedious and time consuming task.

Introspection comes into play here as it lets you generate the data model automatically by reading the schema of a database to which you gave access. However, you don’t have to worry about anything because it doesn’t make the schema of the database change in any way. In this tutorial, the process of using introspection and writing a small Ballerina application is demonstrated. First, let’s discuss the architecture of the application we are building.

Application architecture

The application we are going to build is a small back-end for a hospital system. However, this tutorial is not going to implement the REST APIs as it is out of scope for this article.

Now, let’s take a look at the architecture in the abstract.

Hospital Application Architecture

Here, what we have is a single application that is using bal persist and it will be integrated with an existing MySQL database with the following schema.

ER diagram for the hospital

Now that we have an understanding of the architecture of the application we are building, let’s set up the database so that we can start building our application.

Setting up a database

In this section, we will set up a MySQL database instance using docker.

First, let’s create a docker-compose.yml file to configure our MySQL instance.

services:
  persist-mysql:
    image: mysql
    environment:
      MYSQL_ROOT_PASSWORD: Test123#
    ports:
      - "3306:3306"
    volumes:
      - ./init.sql:/docker-entrypoint-initdb.d/1.sql

Now, create the init.sql file in the same directory as the docker-compose.yml file.

CREATE DATABASE hospital;
USE hospital;

CREATE TABLE Doctor (
   id INT NOT NULL,
   name VARCHAR(191) NOT NULL,
   specialty VARCHAR(20) NOT NULL,
   phone_number VARCHAR(191) NOT NULL,
   salary DECIMAL(10,2),
   PRIMARY KEY(id)
);

CREATE TABLE patients (
    ID_P INT AUTO_INCREMENT,
    name VARCHAR(191) NOT NULL,
    age INT NOT NULL,
    ADDRESS VARCHAR(191) NOT NULL,
    phoneNumber CHAR(10) NOT NULL,
    gender ENUM('MALE', 'FEMALE') NOT NULL,
    PRIMARY KEY(ID_P)
);

CREATE TABLE appointment (
    id INT NOT NULL,
    reason VARCHAR(191) NOT NULL,
    appointmentTime DATETIME NOT NULL,
    status ENUM('SCHEDULED', 'STARTED', 'ENDED') NOT NULL,
    patient_id INT NOT NULL,
    FOREIGN KEY(patient_id) REFERENCES patients(ID_P),
    doctorId INT NOT NULL,
    FOREIGN KEY(doctorId) REFERENCES Doctor(id),
    PRIMARY KEY(id)
);

CREATE UNIQUE INDEX reason_index ON appointment (reason);
CREATE INDEX specialty_index ON Doctor (specialty);

Note that I have deliberately used different naming conventions such as camelCase, PascalCase and snake_case, etc. to see how bal persist can handle such scenarios and ensure that the generated Ballerina records conform to the Ballerina naming conventions.

Now open up a terminal and run the following command to spin up the database.

docker compose up

Now that we have a database running, we can start creating the Ballerina service.

Initializing bal persist

You can easily initialize a new Ballerina project with the following command.

bal new hospital

Now you can open up the project in VSCode with the following command.

code hospital

Now you can initialize bal persist and configure the data store and the module you are going to save the generated client APIs as follows. In my case, I named the module as db.

bal persist add --datastore mysql --module db

Upon initializing bal persist you will see a project structure resembling the one below.

hospital
|--- persist
     |--- model.bal
|--- Ballerina.toml
|--- main.bal

The persist/model.bal file will contain the initial code required to get you up and running with bal persist. However, we will be replacing that in the next step.

Introspecting the database

With the new bal persist pull command, you can introspect the database and generate the data model.

bal persist pull --datastore mysql --host localhost --port 3306 --user root --database hospital

The datastore and port number do not need to be set as they are set to mysql and 3306 respectively by default. Now, the CLI tool will prompt you to enter the password of the database, which will be read through the secure console. You may enter the password.

bal persist pull --datastore mysql --host localhost --user root --database hospital  --port 3300

WARNING The support for database introspection is currently an experimental feature, and its behavior may be subject to change in future releases.
Database Password: 
WARNING A model.bal file already exists. Continuing would overwrite it. Do you wish to continue? (y/n) y
Continuing...
Introspection complete! The model.bal file created successfully.

It may give you a warning saying a model.bal file already exists in the persist directory, and ask you to confirm overwriting it. You can enter y to confirm the action.

After the successful execution of the bal persist pull command, you will see the generated data model in the persist/model.bal file. It will look something like the one below.

import ballerina/persist as _;
import ballerina/time;
import ballerinax/persist.sql;

public enum AppointmentStatus {
    SCHEDULED = "SCHEDULED",
    STARTED = "STARTED",
    ENDED = "ENDED"
}

public enum PatientGender {
    MALE = "MALE",
    FEMALE = "FEMALE"
}

@sql:Name {value: "appointment"}
public type Appointment record {|
    readonly int id;
    @sql:UniqueIndex {name: "reason_index"}
    string reason;
    time:Civil appointmentTime;
    AppointmentStatus status;
    @sql:Name {value: "patient_id"}
    @sql:Index {name: "patient_id"}
    int patientId;
    @sql:Index {name: "doctorId"}
    int doctorId;
    @sql:Relation {keys: ["patientId"]}
    Patient patient;
    @sql:Relation {keys: ["doctorId"]}
    Doctor doctor;
|};

@sql:Name {value: "patients"}
public type Patient record {|
    @sql:Name {value: "ID_P"}
    @sql:Generated
    readonly int idP;
    string name;
    int age;
    @sql:Name {value: "ADDRESS"}
    string address;
    @sql:Char {length: 10}
    string phoneNumber;
    PatientGender gender;
    Appointment[] appointments;
|};

public type Doctor record {|
    readonly int id;
    string name;
    @sql:Varchar {length: 20}
    @sql:Index {name: "specialty_index"}
    string specialty;
    @sql:Name {value: "phone_number"}
    string phoneNumber;
    @sql:Decimal {precision: [10, 2]}
    decimal? salary;
    Appointment[] appointments;
|};

Notice that any table name or column name that goes against the Ballerina naming conventions is automatically converted to conforming cases through the use of SQL annotations. Moreover, you can also see the types that have varying lengths than what is used in bal persist also being annotated with appropriate SQL type annotations. These annotations were also introduced with Ballerina Swan Lake Update 9.

Generating the client API

Now that we have the generated model, we can simply run the bal build command to generate the client API as follows.

bal build

Upon successful build, you will see a file structure resembling this.

hospital
|--- generated
   |--- db
        |--- persist_client.bal
        |--- persist_types.bal
        |--- persist_configuration.bal
        |--- script.sql
|--- Config.toml
|--- persist
     |--- model.bal
|--- Ballerina.toml
|--- main.bal

The generated directory contains the client API and client types required to run queries against the hospital database. You can configure the database credentials in the Config.toml file as follows.

[hospital.db]
host = "localhost"
port = 3306
user = "root"
password = "Test123#"
database = "hospital"

Writing application code

Now that we are done with the introspection and client generation, we can write some application code to try it out a bit. The process is pretty much the same when writing a REST API as well. However, to clearly understand bal persist we will only be running the core business logic of the application.

First, you need to import the generated db module and initialize the client. I will also create two functions named insertRecords() and readAndPrintRecords() to save data in the database and to output the same respectively.

// Initialize client
db:Client dbClient = check new db:Client();

public function main() returns error? {

    // Insert records
    check insertRecords();

    // Read records
    check readAndPrintRecords();
}

Now let’s implement the above two methods. First, let’s go with insertRecords().

function insertRecords() returns persist:Error? {

    // Create insert records
    db:PatientInsert newPatient = {
        name: "John Doe",
        age: 30,
        address: "Colombo",
        gender: db:MALE,
        phoneNumber: "0771234567"
    };

    db:DoctorInsert newDoctor = {
        id: 1,
        name: "Dr. Jane Doe",
        specialty: "Cardiologist",
        phoneNumber: "0771234568",
        salary: 100000
    };

    db:AppointmentInsert newAppointment = {
        id: 1,
        patientId: 1,
        doctorId: 1,
        appointmentTime: {year: 2024, month: 4, day: 22, hour: 10, minute: 30},
        reason: "Heart pain",
        status: db:STARTED
    };

    // Insert records
    int[] result = check dbClient->/patients.post([newPatient]);
    io:println(string `Patient with ID:${result[0]} inserted successfully`);
    _ = check dbClient->/doctors.post([newDoctor]);
    io:println(string `Doctor with ID:${newDoctor.id} inserted successfully`);
    _ = check dbClient->/appointments.post([newAppointment]);
    io:println(string `Appointment with ID:${newAppointment.id} inserted successfully`);
}

What I have done is simply create three records for a patient, a doctor, and an appointment. Next, I just called the initialized dbClient to POST the data to the database. This will effectively save the three records in the database.

Note that in the Patient entity, the primary key, which is the idP field, is generated by the database as it is an auto-increment column. Hence, we are not providing that field. In MySQL databases, the generated idP field is returned as an array of integers, so that we can know which value was given to the generated idP field. More information about generated fields can be found here.

Now let’s write the readAndPrintRecords() function.

function readAndPrintRecords() returns persist:Error? {

    stream<db:Patient, persist:Error?> patientStream = dbClient->/patients();

    db:Patient[] patients = check from db:Patient patient in patientStream select patient;
    io:println(`Patients: ${patients}`);
    
    stream<db:Doctor, persist:Error?> doctorStream = dbClient->/doctors();

    db:Doctor[] doctors = check from db:Doctor doctor in doctorStream select doctor;
    io:println(`Doctors: ${doctors}`);

    stream<db:AppointmentWithRelations, persist:Error?> appointmentStream = dbClient->/appointments();
    
    db:AppointmentWithRelations[] appointments = check from db:AppointmentWithRelations appointment in appointmentStream select appointment;
    io:println(`Appointments: ${appointments}`);
}

Here, I am just calling the three resource functions to get the patients, doctors, and the appointments in the database into a stream and simply printing out the data.

You can run the project using the bal run command as follows.

bal run

You can also verify that the data is inserted into the existing database by logging in to the database through a client.

Conclusion

In this article, we discussed the new introspection feature in bal persist and built a small application that integrates with an existing MySQL database. I hope you found this article useful and thank you very much for making it to the end.