Talking to a database with Ballerina

To start with, Ballerina is a programming language optimized for integration, developed by the folks at WSO2. Integration is about connecting systems. WSO2 has been developing awesome middle-ware for integration for 10+ years. Ballerina is our new kid in town. However it is not some typical middle-ware. Its a language that has first-class support to easily implement concepts in integration! Cool, right?

So, lets get back to the subject! The purpose of this post to help you write some code real quick, and demonstrate how to talk to a database with ballerina. I’m going with mysql. Please install mysql if you don’t have it. Also please note that you need java 8 installed to run ballerina.

I know many of you like to first just copy paste something and see it working ;) So lets run such a sample and later I’ll briefly explain the code to you.

Please follow the steps below.

1. Download Ballerina and install.
2. Create a mysql database “testdb”.
3. Copy paste mysql java connector to BALLERINA_HOME/bre/libs.
4. Copy paste following code in to a file, say dbsample.bal and run the given command given below.

Once run, hope you got the following output.

========================================

Now I’m going to explain the code we just wrote.

Here we are importing some packages needed to write our code. io package is required because we are printing some sentences using println function which comes from io package.

I don’t think I need to explain you why mysql package is needed.

========================================

Here we are creating a custom type called Employee here. It has the fields id of type int and name of type string. We’ll see where we use this later.

========================================

Here we are creating a mysql client endpoint. This is what exposes the mysql database to us. Yes, host, port, name, username and password are what they appear to be. poolOptions and dbOptions are some optional parameters we could pass when creating the endpoint.

poolOptions: These are properties used to configure database connection pool. Here I have used one of these properties maximumPoolSize.

dbOptions: These are properties specific to database.

========================================

Here we are just printing some stuff.

========================================

Now, we are creating a table employee by calling the update action on the endpoint we created above.

What is tableCreationRet? It is the return value when we call the update action. It can either be an error or an int. error will be returned when something goes wrong when performing the update operation. 0 (int) will be returned if operation was executed successfully. In ballerina we need to “match” the return value with each of these types (error and int) and tell what to do in each case (obviously only if you need something done). Check below.

There are “check” and “but” expressions which can be used when handling return types. I am not going to explain them here.

=======================================

Here we are inserting some data to the table we just created.

========================================

Now we use select action to retrieve data from the table we created.

Again, we are using the match expression. Look below.

If the type of the returned value is table we assign the returned value to a reference of table type. It is constrained by Employee type (Remember we created this at the beginning. It should have fields matching to the columns of the table)

Here we are iterating the table returned from the select action.

That’s it! We connected to a mySQL database from Ballerina and performed some operations. Hope you learned something from this.