Introduction

Cloud SQL

Cloud SQL is a cloud-based alternative to local MySQL, PostgreSQL, and SQL server databases that is a part of the Google Cloud Platform Suite. You can follow this guide to create your own Cloud SQL instance in 5 minutes.

Ballerina

Ballerina is an open-source programming language for the cloud that makes it easier to use, combine, and create network services. To learn more about Ballerina, visit the official website.

Configuring your Ballerina Project and Connecting to a MySQL Database

Installing Ballerina and Creating a New Project

A detailed guide on downloading, installing, and updating Ballerina can be found on the official website. To create your very first program, you can follow this guide. After installation, create a new project using the bal new <project_name> command. Navigate into the newly created directory and follow the steps below to configure your project.

Setting Up the MySQL Driver

It is required to add the official JDBC driver for MySQL to the “Ballerina.toml” in your project directory. This can be done in one of two ways.

Use the Ballerina MySQL Driver

This package bundles the latest MySQL driver so that the MySQL connector can be used in Ballerina projects easily. To use this, simply import the module as follows.

import ballerinax/mysql.driver as _;

Add the Dependency With Maven Dependency Params

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

Note: If you are connecting to a PostgreSQL, SQL server, or OracleDB database, replace the above with the relevant driver parameters.

Store Connection Details Securely

To set up the connection parameters for connecting to the SQL database, it is recommended to use the config module which provides the functionality to feed configuration data to Ballerina programs. Create a file named “Config.toml” in your project directory and define the connection configurations as below. Ballerina will automatically use these values on program execution. Instructions on obtaining the configuration values are detailed in the guide on creating your Cloud SQL instance.

dbUrl = "jdbc:mysql://<SERVER_IP>/<DATABASE_NAME>"
dbUsername = "<DB_USERNAME>"
dbPassword = "<DB_PASSWORD>"

This is just one of several methods of setting up configuration variables in Ballerina. To learn more, check out the guide on defining configuration variables.

Connecting to Your Cloud SQL Database

Connecting to your database is a very straightforward task in Ballerina, requiring only a few lines of code. Type the following in your “main.bal” file and run the program using bal run.

import ballerinax/java.jdbc;

configurable string dbUrl = ?;
configurable string dbUsername = ?;
configurable string dbPassword = ?;

public function main() returns error? {
    jdbc:Client dbClient = check new(url = dbUrl, user = dbUsername, password = dbPassword);
       _ = check dbClient.close();
}

Ballerina will automatically fetch and download both the MySQL driver as well as the JDBC connector from Ballerina Central. Ballerina Central is a globally hosted package management system for Ballerina that includes but is not limited to connectors for popular SQL databases such as MySQL, PostgreSQL, SQL Server, and Oracle DB.

If the above program successfully executes without an error, the connection to the database has been established successfully.

Executing Simple SQL Queries

For this example, we will be simulating the common use case of managing a table of employees working in an organization. To represent a single row of the table (i.e. an employee), we will be using Ballerina’s closed record type.

public type Employee record {|
    int id?;
    string first_name;
    string last_name;
    int dept_id;
    string designation;
    int supervisor_id;
|};

The jdbc:Clientclass offers several useful methods for interacting with your database. The execute() method allows you to execute a query, while the query() and queryRow() methods allow you to retrieve rows from the database.

Creating a table:

jdbc:Client dbClient = check new(url = dbUrl, user = dbUsername, password = dbPassword);

_ = check dbClient->execute(`
        CREATE TABLE Employees (
            id INT(6) AUTO_INCREMENT PRIMARY KEY,
            first_name VARCHAR(30) NOT NULL,
            last_name VARCHAR(30) NOT NULL,
            dept_id INT(3) NOT NULL,
            designation VARCHAR(30) NOT NULL,
            supervisor_id INT(6) NOT NULL
        )
    `);

Adding a new employee:

isolated function addEmployee(Employee employee) returns error? {
    jdbc:Client dbClient = check new(url = dbUrl, user = dbUsername, password = dbPassword);

    _ = check dbClient->execute(`
    	INSERT INTO Employees (
        	first_name, last_name, dept_id, designation, supervisor_id
      	)
        VALUES (
          ${employee.first_name},
          ${employee.last_name},
          ${employee.dept_id},
          ${employee.designation},
          ${employee.supervisor_id}
        )
    `);
}

Retrieving an employee:

isolated function getEmployee(int id) returns Employee|error {
    jdbc:Client dbClient = check new(url = dbUrl, user = dbUsername, password = dbPassword);
    
    Employee employee = check dbClient->queryRow(`
        SELECT * FROM Employees WHERE id = ${id}
    `);
    
    return employee;
}

The two above methods can be tested by creating a new employee record and inserting it into the database. Since these two methods are isolated, they can be executed concurrently.

Employee employee = {
        first_name: "Tom",
        last_name: "Scott",
        dept_id: 1,
        designation: "Executive",
        supervisor_id: 1
    };
_ = check addEmployee(employee);

Employee retrieved Employee = check getEmployee(1);
io:println(retrieved Employee);

After executing the above program using bal run, the following output should be expected

Parameterized Queries

A key feature of working with Ballerina and SQL is “parameterized queries,” which is similar to Java’s implementation of “prepared statements.” Parameterized queries allow the user to write dynamic queries with the added advantage of being able to protect against SQL injection attacks.

int id = 5;
sql:ParameterizedQuery query = ‘SELECT * FROM Employees WHERE id = ${id}`;
Employee employee = check dbClient->queryRow(query);

In addition to the above, Ballerina’s JDBC module also provides methods for making procedure calls and executing batches of queries, as well as handling connection pools, SSL configurations, and much more. More details on using this package effectively can be found here.

Notes

• The methodology outlined above can be replicated with any SQL database, both remote (Google Cloud SQL, Microsoft Azure, Amazon AWS, etc.) and local.
• This article covers only a small fraction of the capabilities of Ballerina’s JDBC module. For more in-depth examples and documentation on its functionality, please refer to the documentation.
• Ballerina includes specific modules for connecting to and working with MySQL, PostgreSQL, SQL server, and OracleDB databases with their individual database-specific functionalities.

Resources

If you would like to learn more about Ballerina, check out these resources:

If you are looking for information on creating an HTTP RESTful API or an API using GraphQL for interacting with your SQL databases, look no further than Ballerina — HTTP and Ballerina — GraphQL.