ballerinax/mysql

Overview

This module provides the functionality required to access and manipulate data stored in a MySQL database.

Prerequisite

Add the MySQL driver JAR as a native library dependency in your Ballerina project's Ballerina.toml file. It is recommended to use a MySQL driver version greater than 8.0.13 as this module uses the database properties from the MySQL driver version 8.0.13 onwards.

Follow one of the following ways to add the JAR in the file:

  • Download the JAR and update the path

    1[[platform.java11.dependency]]
    2path = "PATH"
  • Add JAR with a maven dependency params

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

Client

To access a database, you must first create a mysql:Client object. The samples for creating a MySQL client can be found below.

Creating a Client

This sample shows the different ways of creating the mysql:Client.

The client can be created with an empty constructor, and thereby, the client will be initialized with the default properties.

1mysql:Client|sql:Error dbClient = new ();

The dbClient receives the host, username, and password. Since the properties are passed in the same order as they are defined in the mysql:Client, you can pass them without named params.

1mysql:Client|sql:Error dbClient = new ("localhost", "rootUser", "rooPass",
2 "information_schema", 3306);

The dbClient uses the named params to pass the attributes since it is skipping some params in the constructor. Further, the mysql:Options property is passed to configure the SSL and connection timeout in the MySQL client.

1mysql:Options mysqlOptions = {
2 ssl: {
3 mode: mysql:SSL_PREFERRED
4 },
5 connectTimeout: 10
6};
7mysql:Client|sql:Error dbClient = new (user = "rootUser", password = "rootPass",
8 options = mysqlOptions);

Similarly, the dbClient uses the named params and it provides an unshared connection pool of the type of sql:ConnectionPool to be used within the client. For more details about connection pooling, see the sql Module.

1mysql:Client|sql:Error dbClient = new (user = "rootUser", password = "rootPass",
2 connectionPool = {maxOpenConnections: 5});

Using SSL

To connect the MySQL database using an SSL connection, you must add the SSL configurations to the mysql:Options when creating the dbClient. For the SSL Mode, you can select one of the modes: mysql:SSL_PREFERRED, mysql:SSL_REQUIRED, mysql:SSL_VERIFY_CA, or mysql:SSL_VERIFY_IDENTITY according to the requirement. For the key and cert files, you must provide the files in the .p12 format.

1string clientStorePath = "/path/to/keystore.p12";
2string turstStorePath = "/path/to/truststore.p12";
3
4mysql:Options mysqlOptions = {
5 ssl: {
6 mode: mysql:SSL_PREFERRED,
7 key: {
8 path: clientStorePath,
9 password: "password"
10 },
11 cert: {
12 path: turstStorePath,
13 password: "password"
14 }
15 }
16};

Connection Pool Handling

All ballerina database modules share the same connection pooling concept and there are three possible scenarios for connection pool handling. For its properties and possible values, see the sql:ConnectionPool.

  1. Global shareable default connection pool

    If you do not provide the poolOptions field when creating the database client, a globally-shareable pool will be created for your database unless a connection pool matching with the properties you provided already exists.

    1mysql:Client|sql:Error dbClient =
    2 new ("jdbc:mysql://localhost:3306/testdb",
    3 "root", "root");
  2. Client owned, unsharable connection pool

    If you define the connectionPool field inline when creating the database client with the sql:ConnectionPool type, an unsharable connection pool will be created.

    1mysql:Client|sql:Error dbClient =
    2 new (url = "jdbc:mysql://localhost:3306/testdb",
    3 connectionPool = { maxOpenConnections: 5 });
  3. Local, shareable connection pool

    If you create a record of type sql:ConnectionPool and reuse that in the configuration of multiple clients, for each set of clients that connects to the same database instance with the same set of properties, a shared connection pool will be created.

    1sql:ConnectionPool connPool = {maxOpenConnections: 5};
    2
    3mysql:Client|sql:Error dbClient1 =
    4 new (url = "jdbc:mysql://localhost:3306/testdb",
    5 connectionPool = connPool);
    6mysql:Client|sql:Error dbClient2 =
    7 new (url = "jdbc:mysql://localhost:3306/testdb",
    8 connectionPool = connPool);
    9mysql:Client|sql:Error dbClient3 =
    10 new (url = "jdbc:mysql://localhost:3306/testdb",
    11 connectionPool = connPool);

For more details about each property, see the mysql:Client constructor.

The mysql:Client references sql:Client and all the operations defined by the sql:Client will be supported by the mysql:Client as well.

Closing the Client

Once all the database operations are performed, you can close the database client you have created by invoking the close() operation. This will close the corresponding connection pool if it is not shared by any other database clients.

1error? e = dbClient.close();

Or

1check dbClient.close();

Database Operations

Once the client is created, database operations can be executed through that client. This module defines the interface and common properties that are shared among multiple database clients. It also supports querying, inserting, deleting, updating, and batch updating data.

Creating Tables

This sample creates a table with two columns. One column is of type int and the other is of type varchar. The CREATE statement is executed via the execute remote function of the client.

1// Create the ‘Students’ table with the ‘id’, 'name', and ‘age’ fields.
2sql:ExecutionResult result = check dbClient->execute("CREATE TABLE student(id INT AUTO_INCREMENT, " +
3 "age INT, name VARCHAR(255), PRIMARY KEY (id))");
4//A value of the`sql:ExecutionResult` type is returned for 'result'.

Inserting Data

These samples show the data insertion by executing an INSERT statement using the execute remote function of the client.

In this sample, the query parameter values are passed directly into the query statement of the execute remote function.

1sql:ExecutionResult result = check dbClient->execute("INSERT INTO student(age, name) " +
2 "values (23, 'john')");

In this sample, the parameter values, which are in local variables are used to parameterize the SQL query in the execute remote function. This type of parameterized SQL query can be used with any primitive Ballerina type like string, int, float, or boolean and in that case, the corresponding SQL type of the parameter is derived from the type of the Ballerina variable that is passed in.

1string name = "Anne";
2int age = 8;
3
4sql:ParameterizedQuery query = `INSERT INTO student(age, name)
5 values (${age}, ${name})`;
6sql:ExecutionResult result = check dbClient->execute(query);

In this sample, the parameter values are passed as a sql:TypedValue to the execute remote function. Use the corresponding subtype of the sql:TypedValue such as sql:Varchar, sql:Char, sql:Integer, etc., when you need to provide more details such as the exact SQL type of the parameter.

1sql:VarcharValue name = new ("James");
2sql:IntegerValue age = new (10);
3
4sql:ParameterizedQuery query = `INSERT INTO student(age, name)
5 values (${age}, ${name})`;
6sql:ExecutionResult result = check dbClient->execute(query);

Inserting Data With Auto-generated Keys

This sample demonstrates inserting data while returning the auto-generated keys. It achieves this by using the execute remote function to execute the INSERT statement.

1int age = 31;
2string name = "Kate";
3
4sql:ParameterizedQuery query = `INSERT INTO student(age, name)
5 values (${age}, ${name})`;
6sql:ExecutionResult result = check dbClient->execute(query);
7//Number of rows affected by the execution of the query.
8int? count = result.affectedRowCount;
9//The integer or string generated by the database in response to a query execution.
10string|int? generatedKey = result.lastInsertId;
11}

Querying Data

These samples show how to demonstrate the different usages of the query operation and query the database table and obtain the results.

This sample demonstrates querying data from a table in a database. First, a type is created to represent the returned result set. This record can be defined as an open or a closed record according to the requirement. If an open record is defined, the returned stream type will include both defined fields in the record and additional database columns fetched by the SQL query which are not defined in the record. Note the mapping of the database column to the returned record's property is case-insensitive if it is defined in the record(i.e., the ID column in the result can be mapped to the id property in the record). Additional Column names added to the returned record as in the SQL query. If the record is defined as a close record, only defined fields in the record are returned or gives an error when additional columns present in the SQL query. Next, the SELECT query is executed via the query remote function of the client. Once the query is executed, each data record can be retrieved by looping the result set. The stream returned by the select operation holds a pointer to the actual data in the database and it loads data from the table only when it is accessed. This stream can be iterated only once.

1// Define an open record type to represent the results.
2type Student record {
3 int id;
4 int age;
5 string name;
6};
7
8// Select the data from the database table. The query parameters are passed
9// directly. Similar to the `execute` samples, parameters can be passed as
10// sub types of `sql:TypedValue` as well.
11int id = 10;
12int age = 12;
13sql:ParameterizedQuery query = `SELECT * FROM students
14 WHERE id < ${id} AND age > ${age}`;
15stream<Student, sql:Error> resultStream = dbClient->query(query);
16
17// Iterating the returned table.
18error? e = resultStream.forEach(function(Student student) {
19 //Can perform any operations using 'student' and can access any fields in the returned record of type Student.
20});

Defining the return type is optional and you can query the database without providing the result type. Hence, the above sample can be modified as follows with an open record type as the return type. The property name in the open record type will be the same as how the column is defined in the database.

1// Select the data from the database table. The query parameters are passed
2// directly. Similar to the `execute` samples, parameters can be passed as
3// sub types of `sql:TypedValue` as well.
4int id = 10;
5int age = 12;
6sql:ParameterizedQuery query = `SELECT * FROM students
7 WHERE id < ${id} AND age > ${age}`;
8stream<record{}, sql:Error> resultStream = dbClient->query(query);
9
10// Iterating the returned table.
11error? e = resultStream.forEach(function(record{} student) {
12 //Can perform any operations using the 'student' and can access any fields in the returned record.
13 io:println("Student name: ", student.value["name"]);
14});

There are situations in which you may not want to iterate through the database and in that case, you may decide to only use the next() operation in the result stream and retrieve the first record. In such cases, the returned result stream will not be closed and you have to explicitly invoke the close operation on the sql:Client to release the connection resources and avoid a connection leak as shown below.

1stream<record{}, sql:Error> resultStream =
2 dbClient->query("SELECT count(*) as total FROM students");
3
4record {|record {} value;|}? result = check resultStream.next();
5
6if result is record {|record {} value;|} {
7 // A valid result is returned.
8 io:println("total students: ", result.value["total"]);
9} else {
10 // The `Student` table must be empty.
11}
12
13error? e = resultStream.close();

Updating Data

This sample demonstrates modifying data by executing an UPDATE statement via the execute remote function of the client.

1int age = 23;
2sql:ParameterizedQuery query = `UPDATE students SET name = 'John'
3 WHERE age = ${age}`;
4sql:ExecutionResult result = check dbClient->execute(query);

Deleting Data

This sample demonstrates deleting data by executing a DELETE statement via the execute remote function of the client.

1string name = "John";
2sql:ParameterizedQuery query = `DELETE from students WHERE name = ${name}`;
3sql:ExecutionResult result = check dbClient->execute(query);

Batch Updating Data

This sample demonstrates how to insert multiple records with a single INSERT statement that is executed via the batchExecute remote function of the client. This is done by creating a table with multiple records and parameterized SQL query as same as the above execute operations.

1// Create the table with the records that need to be inserted.
2var data = [
3 { name: "John", age: 25 },
4 { name: "Peter", age: 24 },
5 { name: "jane", age: 22 }
6];
7
8// Do the batch update by passing the batches.
9sql:ParameterizedQuery[] batch = from var row in data
10 select `INSERT INTO students ('name', 'age')
11 VALUES (${row.name}, ${row.age})`;
12sql:ExecutionResult[] result = check dbClient->batchExecute(batch);

Execute SQL Stored Procedures

This sample demonstrates how to execute a stored procedure with a single INSERT statement that is executed via the call remote function of the client.

1int uid = 10;
2sql:IntegerOutParameter insertId = new;
3
4sql:ProcedureCallResult|sql:Error result = dbClient->call(`call InsertPerson(${uid}, ${insertId})`);
5if result is error {
6 //An error returned
7} else {
8 stream<record{}, sql:Error>? resultStr = result.queryResult;
9 if resultStr is stream<record{}, sql:Error> {
10 sql:Error? e = resultStr.forEach(function(record{} result) {
11 //can perform operations using 'result'.
12 });
13 }
14 check result.close();
15}

Note that you have to explicitly invoke the close operation on the sql:ProcedureCallResult to release the connection resources and avoid a connection leak as shown above.

Note: The default thread pool size used in Ballerina is: the number of processors available * 2. You can configure the thread pool size by using the BALLERINA_MAX_POOL_SIZE environment variable.

Clients

[1]

Client

Represents a MySQL database client.

Classes

[1]

CustomResultIterator

The object type that is used as a structure to define a custom class with custom implementations for nextResult and getNextQueryResult in the MySQL module.

Records

[2]

Options

MySQL database options.

SecureSocket

SSL Configuration to be used when connecting to mysql server.

Constants

[4]

SSL_PREFERRED

Establish an encrypted connection if the server supports encrypted connections falling back to an unencrypted connection if an encrypted connection cannot be established.

SSL_REQUIRED

Establish an encrypted connection if the server supports encrypted connections.

SSL_VERIFY_CA

Establish an encrypted connection if the server supports encrypted connections.

SSL_VERIFY_IDENTITY

Establish an encrypted connection if the server supports encrypted connections and verify the server Certificate Authority (CA) certificate against the configured CA certificates.

Types

[1]

SSLMode

SSLMode as a union of available SSL modes.