Interacting with a RDBMS using Ballerina

Photo by Sunder Muthukumaran on Unsplash

Ballerina is an open-source programming language. It’s a language that was designed with cloud in mind and has superior support for network interactions that’s essential for modern application design. It also got an extensive standard library for common operations that’s needed for application programming. In this short post I’ll walk you through how ballerina enables a developer to interact with a RDBMS.

Let’s first begin by installing the ballerina.

Installing Ballerina

As the first step, you need to get ballerina installed. The latest version of ballerina can be downloaded from here for your OS. In Windows and MAC OS you can double click on the downloaded executable and follow the onscreen instructions. In Linux execute the relevant commands to install .deb or .rpm files.

Once successfully installed and binaries are in the system executable path, you should have no trouble with below commands.

bal

bal version

It’s also recommended to install ballerina VSCode extension for development. It provides syntax coloring, intellisense and more for developers to easily get into ballerina programming.

Creating the database

For demonstration purpose I’m going to use a database to store simple product information. For the purpose of this post I’m going to use MySQL. Hope you have MySQL setup in your OS. If not get it installed for your system.

Ballerina doesn’t support ORM yet. But this is in the roadmap. We’ll have to wait and see what functionality it might bring in. Typically it should bring in capability to define the underlying model in the language it self and make DDL executed automatically to create the schema.

In the meantime, we’ll have to setup the tables and execute SQL to interact with the database from the language which we’ll see later.

$ mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.25 Homebrew

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create database productapi;
Query OK, 1 row affected (0.00 sec)

mysql> use productapi;
Database changed

mysql> CREATE TABLE product (
    -> name VARCHAR(30) NOT NULL,
    -> product_category VARCHAR(30),
    -> created_date DATETIME,
    -> available_items INT
    -> );
Query OK, 0 rows affected (0.01 sec)

Starting a ballerina project

Create a specific folder for ballerina projects. Navigate into that and issue the below command to start a project. This will simply create boilerplate code that gets you started quickly.

bal new mysql_test
cd mysql_test

The productapi directory will have two files

• Ballerina.toml — this file contains the metadata that describes the package. And ballerina tool uses this file to identify the root package.
• main.bal — A boilerplate source file with a main function that prints “Hello, World!”

Code to INSERT and SELECT records

Add below to the Ballerina.toml file as we’ll be accessing the MySQL database from within our code.

[[platform.java11.dependency]]
groupId = "mysql"
artifactId = "mysql-connector-java"
version = "8.0.20"

Below is the full code that I used to insert two records and query the inserted records to print. Note, the variables names are not chosen with best practices in normal sense. you should use relatable variable names that make sense for your program. Also there are better ways than printing to console to see outputs. This is just chosen for demonstration purpose.

[embed]main.bal — Inserting to and querying data from MySQL database

The first three lines imports necessary packages. ballerinax/mysql package provides functionality to access and manipulate data of MySQL databases.

Lines 5–9 provides what ballerina calls a configurable variables. It can occur at any file in a ballerina program. Compiler generated binary will automatically know about such variables that doesn’t have a value and will not start the program until you provide values for these variables. These are used in the line 19. In this program however, I have specified the values in this file it self.

Line 11–16 defines a record. A record is a type that defines fields of that given type name. In this case I have defined an ‘open’ record type named ‘Product’ with the given fields. An ‘open’ record means it can have extra fields other than what’s specified.

The entry point of the program is the main() function declared as public. It has been specified as ‘return error?’. This means the program could return an error. This is specified mainly because our database operations could result in an error and I wanted that error to be propagated to calling entity.

Line 19, creates the dbClient. Note we are using the configurable variables and the statement is preceded with ‘check’ keyword. This is ballerina’s way to tell that this instantiation could result in error and if it results in error propagate up to the calling entity stopping further operations.

Lines 21–23 execute an INSERT query and expect a ‘sql:ExecutionResult’. Line 24 get the affected number of rows and line 25 prints it. Lines 27–31 do the same.

Line 33 creates a ‘sql:ParameterizedQuery’ with the required SELECT query statement. These statements can have parameters ( such as ${age} )as well even though in this example we didn’t have any.

Line 36 executes the query and return a ‘stream<Product, sql:Error?> resultStream’. Note the stream is expected a ‘Product’ type or an sql:Error. This stream is iterable and that exactly is what happening in line 38. Note the ’check’ expression, which is to indicate return in case of an error.

OK. That explains the code.

Run the code

Run the below command to execute the code. Note how the tool is downloading the dependent mysql-connector based on the dependency we added previously in the Ballerina.toml file.

$ bal run
Resolving Maven dependencies
        Downloading dependencies into /Users/developer/ProjectFiles/codes/Git/Developer/sample-tests/Ballerina/RDBMS/mysql_test/target/platform-libs
mysql-connector-java-8.0.20.jar 100% [=============================================================================================================] 2330/2330 KB (0:02:45 / 0:00:00)

Compiling source
        developer/mysql_test:0.1.0

Running executable

Affected row count - 1
Affected row count - 1
Added Product ==> orange juice - beverage - 2022-09-12 18:30:00.0 - 53
Added Product ==> apple juice - beverage - 2022-09-16 18:30:00.0 - 49

As you can see the added entries are successfully retrieved and printed in the code output.

Remove “productapi” entry and replace with a question mar (?) as in below and try to run the code as stated in “Run the code” section.

configurable string DB_SCHEMA = ?;

Now try to run the above and you should see the program doesn’t start indicating below.

error: value not provided for required configurable variable 'DB_SCHEMA'
        at developer/mysql_test:0.1.0(main.bal:8)

You can provide that configurable value when you run the program as in below.

bal run -- -CDB_SCHEMA="productapi"

OK. That’s it. We looked at how easy it is to write a program that interacts with an RDBMS using ballerina. Happy coding.

PS: Originally posted at [1]

[1] https://stackoverflow.com/collectives/wso2/articles/73795707/interacting-with-a-rdbms-using-ballerina