Skip to content

Managing Database Transactions

Ballerina is an open-source programming language that empowers developers to integrate their system easily with the support of connectors. In this guide, we are mainly focusing on managing database transactions using Ballerina. Please note that Ballerina transactions is an experimental feature. Please use --experimental flag when compiling Ballerina modules that contain transaction-related constructs. You can find other integration modules from the wso2-ballerina GitHub repository.

A transaction is a small unit of a program that must maintain Atomicity, Consistency, Isolation, and Durability − commonly known as ACID properties − in order to ensure accuracy, completeness, and data integrity.

What you’ll build

To understand how you can manage database transactions using Ballerina, let’s consider a real-world use case of a simple banking application.

This banking application allows users to,

  • Create Accounts : Create a new account by providing the username.
  • Verify Accounts : Verify the existence of an account by providing the account Id.
  • Check Balance : Check the account balance.
  • Deposit Money : Deposit money into an account.
  • Withdraw Money : Withdraw money from an account.
  • Transfer Money : Transfer money from one account to another account.

Transferring money from one account to another account involves both operations withdrawal from the transferor and deposit to the transferee.

Let's assume the transaction fails during the deposit operation. Now the withdrawal operation carried out prior to deposit operation also needs to be rolled-back. Otherwise, we will end up in a state where the transferor loses money. Therefore, to ensure the atomicity (all or nothing property), we need to perform the money transfer operation as a transaction.

This example explains three different scenarios where one user tries to transfer money from his/her account to another user's account.

The first scenario shows a successful transaction whereas the other two fail due to different reasons.

Before you begin

Get the code

Pull the module from Ballerina Central using the following command.

ballerina pull wso2/managing_database_transactions

Alternately, you can download the ZIP file and extract the contents to get the code.

Download ZIP

Implementation

If you want to skip the basics, you can download the git repo and directly move to the Testing section by skipping Implementation section.

1. Create a new project.

$ ballerina new managing-database-transactions

2. Create a module.

Navigate into the project directory and add a new module.

$ ballerina add managing_database_transactions

The project structure is created as indicated below.

managing-database-transactions
├── Ballerina.toml
├── ballerina.conf
├── lib
│    └── mysql-connector-java-x.x.x.jar
└── src
    └── managing_database_transactions
        ├── resources
        ├── Module.md
        ├── account_manager.bal
        ├── application.bal
        └── tests
            ├── account_manager_test.bal
            └── resources

Create a folder called lib under the project root path. Copy the JDBC driver for MySQL into the lib folder.

3. Add project configurations file

Add a ballerina.conf file and create .bal files with meaningful names as shown in the project structure given above.

  • ballerina.conf file can be used to provide external configurations to Ballerina programs. Since this guide needs MySQL database integration, a Ballerina configuration file is used to provide the database connection properties to our Ballerina program. This configuration file has the following fields. Change these configurations with your connection properties accordingly.

    DATABASE_URL = "jdbc:mysql://127.0.0.1:3306/bankDB"
    DATABASE_USERNAME = "root"
    DATABASE_PASSWORD = "root"

4. Write the integration

Open the project with VS Code. The integration implementation is written in the src/managing_database_transaction/account_manager.bal and src/managing_database_transaction/application.bal files.

So, create a new file named application.bal under 'managing_database_transaction' with the following content.

import ballerina/log;

public function main () {
    log:printInfo("----------------------------------------------------------------------------------");
    // Create two new accounts
    log:printInfo("Create two new accounts for users 'Alice' and 'Bob'");
    var createAccRet = createAccounts();
    int accIdUser1;
    int accIdUser2;
    if (createAccRet is [int, int]) {
        [accIdUser1, accIdUser2] = createAccRet;

        // Deposit money to both new accounts
        log:printInfo("Deposit $500 to Alice's account initially");
        var depositRet1 = depositMoney(accIdUser1, 500);
        if (depositRet1 is error) {
            log:printError("Error occurred while depositing", err = depositRet1);
        }
        log:printInfo("Deposit $1000 to Bob's account initially");
        var depositRet2 = depositMoney(accIdUser2, 1000);
        if (depositRet2 is error) {
            log:printError("Error occurred while depositing", err = depositRet1);
        }

        // Scenario 1 - Transaction expected to be successful
        log:printInfo("\n\n--------------------------------------------------------------- Scenario 1"
                + "--------------------------------------------------------------");
        log:printInfo("Transfer $300 from Alice's account to Bob's account");
        log:printInfo("Expected: Transaction to be successful");
        _ = transferMoney(accIdUser1, accIdUser2, 300);

        // Scenario 2 - Transaction expected to fail
        log:printInfo("\n\n--------------------------------------------------------------- Scenario 2"
                + "--------------------------------------------------------------");
        log:printInfo("Again try to transfer $500 from Alice's account to Bob's account");
        log:printInfo("Expected: Transaction to fail as Alice now only has a balance of $200 in account");
        _ = transferMoney(accIdUser1, accIdUser2, 500);

        // Scenario 3 - Transaction expected to fail
        log:printInfo("\n\n--------------------------------------------------------------- Scenario 3"
                + "--------------------------------------------------------------");
        log:printInfo("Try to transfer $500 from Bob's account to a non existing account ID");
        log:printInfo("Expected: Transaction to fail as account ID of recipient is invalid");
        _ = transferMoney(accIdUser2, 1234, 500);

        log:printInfo("Check balance for Bob's account");
        var checkBalanceRet = checkBalance(accIdUser2);
        if (checkBalanceRet is error) {
            log:printError("Error occurred while checking balance", err = checkBalanceRet);
        }
        log:printInfo("You should see $1300 balance in Bob's account (NOT $800)");
        log:printInfo(
                "Explanation: When trying to transfer $500 from Bob's account to account ID 1234, \ninitially $500 " +
                "withdrew from Bob's account. But then the deposit operation failed due to an invalid recipient " +
                "account ID; Hence, \nthe TX failed and the withdraw operation rollbacked as it is in the same TX " +
                "\n");
    } else {
        log:printError("Account creation failed", err = createAccRet);
    }
}

function createAccounts() returns @tainted [int, int]|error {
    var createAccRet1 = createAccount("Alice");
    [int,int]|error retVal;
    if (createAccRet1 is int) {
        var createAccRet2 = createAccount("Bob");
        if (createAccRet2 is int) {
           return [createAccRet1, createAccRet2] ;
        } else {
            error err = error("Account creation failed");
            return err;
        }
    } else {
        error err = error("Account creation failed");
        return err;
    }
}

Similarly, create a new file named account_manager.bal under 'managing_database_transaction' with the following content.

import ballerina/config;
import ballerina/jsonutils;
import ballerina/lang.'int as ints;
import ballerina/log;
import ballerina/transactions;
import ballerinax/java.jdbc;

// 'jdbc:Client'.
jdbc:Client bankDB = new({
    url: config:getAsString("DATABASE_URL", "jdbc:mysql://127.0.0.1:3306/bankDB"),
    username: config:getAsString("DATABASE_USERNAME", "root"),
    password: config:getAsString("DATABASE_PASSWORD", "root"),
    dbOptions: { useSSL: false }
});

// Function to add users to 'ACCOUNT' table of 'bankDB' database.
public function createAccount(string name) returns @tainted int|error {
    // Insert query.
    var updateRet = bankDB->update("INSERT INTO ACCOUNT (USERNAME, BALANCE) VALUES (?, ?)", name, 0);
    if (updateRet is error) {
        log:printError("Error occurred during database operation", err = updateRet);
        return updateRet;
    }
    // Get the primary key of the last insertion (Auto incremented value).
    var selectRet = bankDB->select("SELECT LAST_INSERT_ID() AS ACCOUNT_ID from ACCOUNT where ?", (), 1);
    int|error retVal = -1;
    if (selectRet is table<record {}>) {
        // convert the table to json
        var jsonConvertRet = jsonutils:fromTable(selectRet);
        int|error returnVal;
        map<json>[] jsonConvert = <map<json>[]>jsonConvertRet;
        retVal = ints:fromString(jsonConvert[0]["ACCOUNT_ID"].toString());
        if (retVal is int) {
            log:printInfo("Account ID for user: '" + name.toString() + "': " + retVal.toString());
        } else {
            log:printError("Error occurred during JSON conversion", err = retVal);
        }
        // Since we are not fully iterating the json value converted from the table we need to explicitly close the table
        // to avoid connection leak. If the json was fully iterated, the table would also have been fully iterated and
        // streamed out and then the connection would have been automatically closed.
        selectRet.close();
    } else {
        retVal = selectRet;
        log:printError("Error occurred during database operation", err = selectRet);
    }
    // Return the primary key, which will be the account number of the account or an error in case of a failure
    return retVal;
}

// Function to verify an account whether it exists or not.
public function verifyAccount(int accId) returns @tainted boolean|error {
    log:printInfo("Verifying whether account ID " + accId.toString() + " exists");
    // SQL query parameters.
    // Select query to check whether account exists.
    var selectRet = bankDB->select("SELECT COUNT(*) AS COUNT FROM ACCOUNT WHERE ID = ?", (), accId);
    boolean|error retVal = false;
    if (selectRet is table<record {}>) {
        // convert the table to json.
        var jsonConvertRet = jsonutils:fromTable(selectRet);
        map<json>[] jsonConvert = <map<json>[]>jsonConvertRet;
        // convert the json to int.
        int|error count = ints:fromString(jsonConvert[0]["COUNT"].toString());
        // Return a boolean, which will be true if account exists; false otherwise.
        if (count is int) {
            if (count != 0) {
                retVal = true;
            } else {
                retVal = false;
            }  
        } else {
            retVal = count;
        }
        // Since we are not fully iterating the json value converted from the table we need to explicitly close the table
        // to avoid connection leak. If the json was fully iterated, the table would also have been fully iterated and
        // streamed out and then the connection would have been automatically closed.
        selectRet.close();
    } else {
        retVal = selectRet;
    }
    return retVal;
}

// Function to check balance in an account.
public function checkBalance(int accId) returns @tainted int|error {
    // Verify account whether it exists and return an error if not.
    var accountVerificationRet = verifyAccount(accId);
    if (accountVerificationRet is error) {
        return accountVerificationRet;
    } else {
        if (!accountVerificationRet) {
            error err = error("Error: Account does not exist");
            return err;
        }
    }
    int|error retVal = -1;
    // Select query to get balance.
    var selectRet = bankDB->select("SELECT BALANCE FROM ACCOUNT WHERE ID = ?", (), accId);
    if (selectRet is table<record {}>) {
        // convert the table to json.
        var jsonConvertRet = jsonutils:fromTable(selectRet);
        map<json>[] jsonConvert = <map<json>[]>jsonConvertRet;
        // convert the json to int.
        int|error balance = ints:fromString(jsonConvert[0]["BALANCE"].toString());
        if (balance is int) {
            log:printInfo("Available balance in account ID " + accId.toString() + ": " + balance.toString());
            retVal = balance;
        } else {
            retVal = balance;
        }
        // Since we are not fully iterating the json value converted from the table we need to explicitly close the table
        // to avoid connection leak. If the json was fully iterated, the table would also have been fully iterated and
        // streamed out and then the connection would have been automatically closed.
        selectRet.close();
    } else {
        retVal = selectRet;
    }
    // Return the balance or error in case of a failure.
    return retVal;
}

// Function to deposit money to an account.
public function depositMoney(int accId, int amount) returns @tainted error|() {
    // Check whether the amount specified is valid and return an error if not.
    if (amount <= 0) {
        error err = error("Error: Invalid amount");
        return err;
    }
    var accountVerificationRet = verifyAccount(accId);
    if (accountVerificationRet is error) {
        return accountVerificationRet;
    } else {
        if (!accountVerificationRet) {
            // Verify account whether it exists and return an error if not.
            error err = error("Error: Account does not exist");
            return err;
        }
    }
    log:printInfo("Depositing money to account ID: " + accId.toString());
    // Update query to increase the current balance.
    var updateRet = bankDB->update("UPDATE ACCOUNT SET BALANCE = (BALANCE + ?) WHERE ID = ?", amount, accId);
    if (updateRet is jdbc:UpdateResult) {
        log:printInfo("$" + amount.toString() + " has been deposited to account ID " + accId.toString());
    } else {
        return updateRet;
    }
    return;
}

// Function to withdraw money from an account.
public function withdrawMoney(int accId, int amount) returns @tainted error|() {
    // Check whether the amount specified is valid and return an error if not.
    if (amount <= 0) {
        error err = error("Error: Invalid amount");
        return err;
    }
    // Check current balance.
    var balanceVal = checkBalance(accId);
    if (balanceVal is int) {
        // Check whether the user has enough money to withdraw the requested amount and return an error if not.
        if (balanceVal < amount) {
            error err = error("Error: Not enough balance");
            return err;
        }
    } else {
        return balanceVal;
    }
    log:printInfo("Withdrawing money from account ID: " + accId.toString());
    // Update query to reduce the current balance.
    var updateRet = bankDB->update("UPDATE ACCOUNT SET BALANCE = (BALANCE - ?) WHERE ID = ?", amount, accId);
    if (updateRet is jdbc:UpdateResult) {
        log:printInfo("$" + amount.toString() + " has been withdrawn from account ID " + accId.toString());
    } else {
        return updateRet;
    }
    return;
}

// Function to transfer money from one account to another.
public function transferMoney(int fromAccId, int toAccId, int amount) returns boolean {
    boolean isSuccessful = false;
    log:printInfo("Initiating transaction");
    // Transaction block - Ensures the 'ACID' properties.
    // Withdraw and deposit should happen as a transaction when transferring money from one account to another.
    // Here, the reason for switching off the 'retries' option is, in failing scenarios almost all the time
    // transaction fails due to erroneous operations triggered by the users.
    transaction with retries = 0 {
        // Withdraw money from transferor's account.
        var withdrawRet = withdrawMoney(fromAccId, amount);
        if (withdrawRet is ()) {
            var depositRet = depositMoney(toAccId, amount);
            if (depositRet is ()) {
                isSuccessful = true;
            } else {
                log:printError("Error while depositing the money: " + depositRet.reason());
                // Abort transaction if deposit fails.
                log:printError("Failed to transfer money from account ID " + fromAccId.toString() + " to account ID " +
                    toAccId.toString());
                abort;
            }
        } else {
            log:printError("Error while withdrawing the money: " + withdrawRet.reason());
            // Abort transaction if withdrawal fails.
            log:printError("Failed to transfer money from account ID " + fromAccId.toString() + " to account ID " 
                + toAccId.toString());
            abort;
        }
    } committed {
        log:printInfo("Transaction: " + transactions:getCurrentTransactionId() + " committed");
        // If transaction successful.
        log:printInfo("Successfully transferred $" + amount.toString() + " from account ID " + fromAccId.toString() 
            + " to account ID " + toAccId.toString());
    } aborted {
        log:printInfo("Transaction: " + transactions:getCurrentTransactionId() + " aborted");
    }
    return isSuccessful;
}

The transferMoney function of account_manager.bal demonstrates how we can use transactions in Ballerina. It comprises of two different operations; withdrawal and deposit. To ensure that the transferring operation happens as a whole, it needs to reside in a database transaction block.

Transactions guarantee the 'ACID' properties. So if any of the withdrawal or deposit operations fail, the transaction will be aborted and all the operations carried out in the same transaction will be rolled back. The transaction will be successful only when both withdrawal from the transferor and deposit to the transferee are successful.

Testing

Before you begin

  • Run the SQL script database_initializer.sql provided in the resources directory, to initialize the database and to create the required table.
    $ mysql -u username -p <database_initializer.sql 

Let’s build the module. Navigate to the project directory and execute the following command.

$ ballerina build --experimental managing_database_transactions
 ```

The build command would create an executable .jar file. Now run the .jar file created in the above step. Path to the `ballerina.conf` could be provided using the `--b7a.config.file` option.

```bash
$ java -jar target/bin/managing_database_transactions.jar --b7a.config.file=path/to/ballerina.conf/file

Response you'll get

We created two user accounts for users 'Alice' and 'Bob'. Then initially we deposited $500 to Alice's account and $1000 to Bob's account. Later we had three different scenarios to check the money transfer operation, which includes a database transaction.

Let's now look at some important log statements we will get as the response for these three scenarios.

  • For Scenario 1 where 'Alice' transfers $300 to Bob's account, the transaction is expected to be successful
------------------------------------ Scenario 1---------------------------------------- 
INFO  [managing_database_transactions] - Transfer $300 from Alice's account to Bob's account
INFO  [managing_database_transactions] - Expected: Transaction to be successful
INFO  [managing_database_transactions] - Initiating transaction
INFO  [managing_database_transactions] - Verifying whether account ID 1 exists
INFO  [managing_database_transactions] - Available balance in account ID 1: 500
INFO  [managing_database_transactions] - Withdrawing money from account ID: 1
INFO  [managing_database_transactions] - $300 has been withdrawn from account ID 1
INFO  [managing_database_transactions] - Verifying whether account ID 2 exists
INFO  [managing_database_transactions] - Depositing money to account ID: 2
INFO  [managing_database_transactions] - $300 has been deposited to account ID 2
INFO  [managing_database_transactions] - Transaction committed
INFO  [managing_database_transactions] - Successfully transferred $300 from account ID 1 to account ID
  • For Scenario 2 where 'Alice' tries to transfer $500 to Bob's account, the transaction is expected to fail as 'Alice' has an insufficient balance
------------------------------------ Scenario 2---------------------------------------- 
INFO  [managing_database_transactions] - Again try to transfer $500 from Alice's account to Bob's account
INFO  [managing_database_transactions] - Expected: Transaction to fail as Alice now only has a balance of $200 in account
INFO  [managing_database_transactions] - Initiating transaction
INFO  [managing_database_transactions] - Verifying whether account ID 1 exists
INFO  [managing_database_transactions] - Available balance in account ID 1: 200
ERROR [managing_database_transactions] - Error while withdrawing the money: Error: Not enough balance
ERROR [managing_database_transactions] - Failed to transfer money from account ID 1 to account ID 2
INFO  [managing_database_transactions] - Transaction aborted
  • For Scenario 3 where 'Bob' tries to transfer $500 to account ID 1234, the transaction is expected to fail as account ID 1234 does not exist
------------------------------------ Scenario 3---------------------------------------- 
INFO  [managing_database_transactions] - Try to transfer $500 from Bob's account to a non existing account ID
INFO  [managing_database_transactions] - Expected: Transaction to fail as account ID of the recipient is invalid
INFO  [managing_database_transactions] - Initiating transaction
INFO  [managing_database_transactions] - Verifying whether account ID 2 exists
INFO  [managing_database_transactions] - Available balance in account ID 2: 1300
INFO  [managing_database_transactions] - Withdrawing money from account ID: 2
INFO  [managing_database_transactions] - $500 has been withdrawn from account ID 2
INFO  [managing_database_transactions] - Verifying whether account ID 1234 exists
ERROR [managing_database_transactions] - Error while depositing the money: Error: Account does not exist
ERROR [managing_database_transactions] - Failed to transfer money from account ID 2 to account ID 1234
INFO  [managing_database_transactions] - Check balance for Bob's account
INFO  [managing_database_transactions] - Verifying whether account ID 2 exists
INFO  [managing_database_transactions] - Available balance in account ID 2: 1300
INFO  [managing_database_transactions] - You should see $1300 balance in Bob's account (NOT $800)
INFO  [managing_database_transactions] - Explanation:
When trying to transfer $500 from Bob's account to account ID 1234, initially $500
was withdrawn from Bob's account. But then the deposit operation failed due to an invalid
recipient account ID; Hence, the transaction failed and withdraw operation rollbacked as 
it is in the same transaction
Top