ballerina/sql

Overview

This module provides the common interface and functionality to interact with a database. The corresponding database clients can be created by using specific database modules such as mysql or using the Java Database Connectivity module jdbc.

List of Database Modules

Ballerina now has the jdbc module as the generic DB connector module to connect to any relational database by simply providing the JDBC URL and the other related properties. Ballerina also provides specially designed various database-specific DB connectors so that you can work with different databases and you can access their DB-specific functionalities.

Client

The database client should be created using any of the above-listed database modules and once it is created, the operations and functionality explained below can be used.

Connection Pool Handling

All 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. The JDBC module sample below shows how the global connection pool is used.

    1jdbc: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. The JDBC module sample below shows how the global connection pool is used.

    1jdbc: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 the sql:ConnectionPool type 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. The JDBC module sample below shows how the global connection pool is used.

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

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.

Parameterized Query

The sql:ParameterizedQuery is used to construct the SQL query to be executed by the client. You can create a query with constant or dynamic input data as follows.

Query with constant values

1sql:ParameterizedQuery query = `SELECT * FROM students
2 WHERE id < 10 AND age > 12`;

Query with dynamic values

1int[] ids = [10, 50];
2int age = 12;
3sql:ParameterizedQuery query = `SELECT * FROM students
4 WHERE id < ${ids[0]} AND age > ${age}`;

Moreover, the SQL package has sql:queryConcat() and sql:arrayFlattenQuery() util functions which make it easier to create a dynamic/constant complex query.

The sql:queryConcat() is used to create a parameterized query by concatenating a set of parameterized queries. The sample below shows how to concatenate queries.

1int id = 10;
2int age = 12;
3sql:ParameterizedQuery query = `SELECT * FROM students`;
4sql:ParameterizedQuery query1 = ` WHERE id < ${id} AND age > ${age}`;
5sql:ParameterizedQuery sqlQuery = sql:queryConcat(query, query1);

The query with the IN operator can be created using the sql:ParameterizedQuery like below. Here you need to flatten the array and pass each element separated by a comma.

1int[] ids = [1, 2, 3];
2sql:ParameterizedQuery query = `SELECT count(*) as total FROM DataTable
3 WHERE row_id in (${ids[0]}, ${ids[1]}, ${ids[2]})`;

The util function sql:arrayFlattenQuery() is introduced to make the array flatten easier. It makes the inclusion of varying array elements into the query easier by flattening the array to return a parameterized query. You can construct the complex dynamic query with the IN operator by using both functions like below.

1int[] ids = [1, 2];
2sql:ParameterizedQuery sqlQuery =
3 sql:queryConcat(`SELECT * FROM DataTable WHERE id IN (`,
4 sql:arrayFlattenQuery(ids), `)`);

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 =
3 check dbClient->execute(`CREATE TABLE student (
4 id INT AUTO_INCREMENT,
5 age INT,
6 name VARCHAR(255),
7 PRIMARY KEY (id)
8 )`);
9//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 a 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:VarcharValue, sql:CharValue, sql:IntegerValue, 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;

Querying Data

These samples show how to demonstrate the different usages of the query operation to 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 operations using the record 'student' 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 operations using the record 'student'.
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 use the sql:queryRow() operation. If the provided return type is a record, this method returns only the first row retrieved by the query as a record.

1int id = 10;
2sql:ParameterizedQuery query = `SELECT * FROM students WHERE id = ${id}`;
3Student retrievedStudent = check dbClient->queryRow(query);

The sql:queryRow() operation can also be used to retrieve a single value from the database (e.g., when querying using COUNT() and other SQL aggregation functions). If the provided return type is not a record (i.e., a primitive data type) , this operation will return the value of the first column of the first row retrieved by the query.

1int age = 12;
2sql:ParameterizedQuery query = `SELECT COUNT(*) FROM students WHERE age < ${age}`;
3int youngStudents = check dbClient->queryRow(query);

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' WHERE age = ${age}`;
3sql: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 result =
5 check dbClient->call(`call InsertPerson(${uid}, ${insertId})`);
6stream<record{}, sql:Error?>? resultStr = result.queryResult;
7if resultStr is stream<record{}, sql:Error?> {
8 sql:Error? e = resultStr.forEach(function(record{} result) {
9 // Can perform operations using the record 'result'.
10 });
11}
12check result.close();

Note that you have to invoke the close operation explicitly 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.

Functions

[4]

arrayFlattenQuery

Joins the elements in the array with the , delimiter into an sql:ParameterizedQuery.

generateApplicationErrorStream

Generates a stream consisting of sql:Error elements.

getGlobalConnectionPool

Returns the global connection pool.

queryConcat

Concatenates all provided sql:ParameterizedQuerys into a single sql:ParameterizedQuery.

Classes

[102]

ArrayOutParameter

Represents Array Out Parameter used in procedure calls.

ArrayValue
D

Represents ArrayValue SQL field.

BigIntArrayOutParameter

Represents BigInt Array Out Parameter used in procedure calls

BigIntArrayValue

Represents BigInt array SQL field.

BigIntOutParameter

Represents BigInt Out Parameter used in procedure calls.

BigIntValue

Represents BigInt SQL field.

BinaryArrayOutParameter

Represents Binary Array Out Parameter used in procedure calls.

BinaryArrayValue

Represents Boolean array SQL field.

BinaryOutParameter

Represents Binary Out Parameter used in procedure calls.

BinaryValue

Represents Binary SQL field.

BitArrayOutParameter

Represents Bit Array Out Parameter used in procedure calls.

BitArrayValue

Represents Bit array SQL field.

BitOutParameter

Represents Bit Out Parameter used in procedure calls.

BitValue

Represents Bit SQL field.

BlobOutParameter

Represents Blob Out Parameter used in procedure calls.

BlobValue

Represents Blob SQL field.

BooleanArrayOutParameter

Represents Boolean Array Out Parameter used in procedure calls.

BooleanArrayValue

Represents Boolean array SQL field.

BooleanOutParameter

Represents Boolean Out Parameter used in procedure calls.

BooleanValue

Represents Boolean SQL field.

CharArrayOutParameter

Represents Char Array Out Parameter used in procedure calls

CharArrayValue

Represents Char array SQL field.

CharOutParameter

Represents Char Out Parameter used in procedure calls.

CharValue

Represents Char SQL field.

ClobOutParameter

Represents Clob Out Parameter used in procedure calls.

ClobValue

Represents Clob SQL field.

DateArrayOutParameter

Represents Date Array Out Parameter used in procedure calls.

DateArrayValue

Represents Date array SQL field.

DateOutParameter

Represents Date Out Parameter used in procedure calls.

DateTimeArrayOutParameter

Represents DateTime Array Out Parameter used in procedure calls.

DateTimeArrayValue

Represents DateTime array SQL field.

DateTimeOutParameter

Represents DateTime Out Parameter used in procedure calls.

DateTimeValue

Represents DateTime SQL field.

DateValue

Represents Date SQL field.

DecimalArrayOutParameter

Represents Decimal Out Parameter used in procedure calls.

DecimalArrayValue

Represents Decimal array SQL field.

DecimalOutParameter

Represents Decimal Out Parameter used in procedure calls.

DecimalValue

Represents Decimal SQL field.

DoubleArrayOutParameter

Represents Double Array Out Parameter used in procedure calls.

DoubleArrayValue

Represents Double array SQL field.

DoubleOutParameter

Represents Double Out Parameter used in procedure calls.

DoubleValue

Represents Double SQL field.

FloatArrayOutParameter

Represents Float Array Out Parameter used in procedure calls.

FloatArrayValue

Represents Float array SQL field.

FloatOutParameter

Represents Float Out Parameter used in procedure calls.

FloatValue

Represents Float SQL field.

InOutParameter

Represents SQL InOutParameter used in procedure calls.

IntegerArrayOutParameter

Represents Integer Array Out Parameter used in procedure calls.

IntegerArrayValue

Represents Integer array SQL field.

IntegerOutParameter

Represents Integer Out Parameter used in procedure calls.

IntegerValue

Represents Integer SQL field.

NCharOutParameter

Represents NChar Out Parameter used in procedure calls.

NCharValue

Represents NChar SQL field.

NClobOutParameter

Represents NClob Out Parameter used in procedure calls.

NClobValue

Represents NClob SQL field.

NumericArrayOutParameter

Represents Numeric Array Out Parameter used in procedure calls.

NumericArrayValue

Represents Numeric array SQL field.

NumericOutParameter

Represents Numeric Out Parameter used in procedure calls.

NumericValue

Represents Numeric SQL field.

NVarcharArrayOutParameter

Represents NVarchar Array Out Parameter used in procedure calls.

NVarcharArrayValue

Represents Varchar NVarchar SQL field.

NVarcharOutParameter

Represents NVarchar Out Parameter used in procedure calls.

NVarcharValue

Represents NVarchar SQL field.

ProcedureCallResult

Object that is used to return stored procedure call results.

RealArrayOutParameter

Represents Real Array Out Parameter used in procedure calls.

RealArrayValue

Represents Real array SQL field.

RealOutParameter

Represents Real Out Parameter used in procedure calls.

RealValue

Represents Real SQL field.

RefOutParameter

Represents Ref Out Parameter used in procedure calls.

RefValue

Represents Ref SQL field.

ResultIterator

The result iterator object that is used to iterate through the results in the event stream.

RowOutParameter

Represents Row Out Parameter used in procedure calls.

RowValue

Represents Row SQL field.

SmallIntArrayOutParameter

Represents SmallInt Array Out Parameter used in procedure calls.

SmallIntArrayValue

Represents SmallInt array SQL field.

SmallIntOutParameter

Represents SmallInt Out Parameter used in procedure calls.

SmallIntValue

Represents SmallInt SQL field.

StructOutParameter

Represents Struct Out Parameter used in procedure calls.

StructValue

Represents Struct SQL field.

TextOutParameter

Represents Text Out Parameter used in procedure calls.

TextValue

Represents Text SQL field.

TimeArrayOutParameter

Represents Time Array Out Parameter used in procedure calls.

TimeArrayValue

Represents Time array SQL field.

TimeOutParameter

Represents Time Out Parameter used in procedure calls.

TimestampArrayOutParameter

Represents Timestamp Array Out Parameter used in procedure calls.

TimestampArrayValue

Represents Timestamp array SQL field.

TimestampOutParameter

Represents Timestamp Out Parameter used in procedure calls.

TimestampValue

Represents Timestamp SQL field.

TimestampWithTimezoneArrayOutParameter

Represents Timestamp with Timezone Array Out Parameter used in procedure calls.

TimestampWithTimezoneOutParameter

Represents Timestamp with Timezone Out Parameter used in procedure calls.

TimeValue

Represents Time SQL field.

TimeWithTimezoneArrayOutParameter

Represents Time With Timezone Array Out Parameter used in procedure calls.

TimeWithTimezoneOutParameter

Represents Time With Timezone Out Parameter used in procedure calls.

VarBinaryArrayOutParameter

Represents VarBinary Array Out Parameter used in procedure calls.

VarBinaryArrayValue

Represents Boolean array SQL field.

VarBinaryOutParameter

Represents VarBinary Out Parameter used in procedure calls.

VarBinaryValue

Represents VarBinary SQL field.

VarcharArrayOutParameter

Represents Varchar Array Out Parameter used in procedure calls.

VarcharArrayValue

Represents Varchar array SQL field.

VarcharOutParameter

Represents Varchar Out Parameter used in procedure calls.

VarcharValue

Represents Varchar SQL field.

XMLOutParameter

Represents XML Out Parameter used in procedure calls.

Object Types

[6]

Client

Represents a SQL client.

CustomResultIterator

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

OutParameter

Represents all OUT parameters used in SQL stored procedure call.

ParameterizedCallQuery

Represents Parameterized Call SQL Statement.

ParameterizedQuery

Represents Parameterized SQL query.

TypedValue

Represents a parameter for the SQL Client remote functions when a variable needs to be passed.

Records

[4]

BatchExecuteErrorDetail

Represents the properties belonging to an sql:BatchExecuteError.

ConnectionPool

Represents the properties which are used to configure DB connection pool.

DatabaseErrorDetail

Represents the properties belonging to an sql:DatabaseError.

ExecutionResult

The result of the query without returning the rows.

Constants

[2]

EXECUTION_FAILED

Constant indicating that the specific batch statement failed.

SUCCESS_NO_INFO

Constant indicating that the specific batch statement executed successfully but that no count of the number of rows it affected is available.

Types

[2]

Parameter

Represents all parameters used in SQL stored procedure call.

Value

Possible type of parameters that can be passed into the SQL query.

Errors

[10]

ApplicationError

Represents an error originating from application-level causes.

BatchExecuteError

Represents an error occurred when a batch execution is running.

ConversionError

Represents an error that occurs when a query retrieves a result that is corrupted and cannot be converted to expected type.

DatabaseError

Represents an error caused by an issue related to database accessibility, erroneous queries, constraint violations, database resource clean-up, and other similar scenarios.

DataError

Represents an error during the processing of the returned data or parameters.

Error

Defines the common error type for the module.

FieldMismatchError

Represents an error that occurs when a query retrieves a result that cannot be matched to the expected record type.

NoRowsError

Represents an error that occurs when a query retrieves no rows when at least one row was expected.

TypeMismatchError

Represents an error that occurs when a query retrieves a result that differs from the expected result type.

UnsupportedTypeError

Represents an error that occurs when an unsupported parameter type is added to the query.