ballerina/sql

Overview

This module provides the generic interface and functionality to interact with a SQL 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 generic 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 parameterization can be performed 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 parameters 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 in sql:ParameterizedCallQuery.

ArrayValue
D

Represents SQL ArrayValue type parameter in sql:ParameterizedQuery.

BigIntArrayOutParameter

Represents BigInt Array Out Parameter in sql:ParameterizedCallQuery.

BigIntArrayValue

Represents SQL BigInt array type parameter in sql:ParameterizedQuery.

BigIntOutParameter

Represents BigInt Out Parameter in sql:ParameterizedCallQuery.

BigIntValue

Represents SQL BigInt type parameter in sql:ParameterizedQuery.

BinaryArrayOutParameter

Represents Binary Array Out Parameter in sql:ParameterizedCallQuery.

BinaryArrayValue

Represents SQL Boolean array type parameter in sql:ParameterizedQuery.

BinaryOutParameter

Represents Binary Out Parameter in sql:ParameterizedCallQuery.

BinaryValue

Represents SQL Binary type parameter in sql:ParameterizedQuery.

BitArrayOutParameter

Represents Bit Array Out Parameter in sql:ParameterizedCallQuery.

BitArrayValue

Represents SQL Bit array type parameter in sql:ParameterizedQuery.

BitOutParameter

Represents Bit Out Parameter in sql:ParameterizedCallQuery.

BitValue

Represents SQL Bit type parameter in sql:ParameterizedQuery.

BlobOutParameter

Represents Blob Out Parameter in sql:ParameterizedCallQuery.

BlobValue

Represents SQL Blob type parameter in sql:ParameterizedQuery.

BooleanArrayOutParameter

Represents Boolean Array Out Parameter in sql:ParameterizedCallQuery.

BooleanArrayValue

Represents SQL Boolean array type parameter in sql:ParameterizedQuery.

BooleanOutParameter

Represents Boolean Out Parameter in sql:ParameterizedCallQuery.

BooleanValue

Represents SQL Boolean type parameter in sql:ParameterizedQuery.

CharArrayOutParameter

Represents Char Array Out Parameter in sql:ParameterizedCallQuery.

CharArrayValue

Represents SQL Char array type parameter in sql:ParameterizedQuery.

CharOutParameter

Represents Char Out Parameter in sql:ParameterizedCallQuery.

CharValue

Represents SQL Char type parameter in sql:ParameterizedQuery.

ClobOutParameter

Represents Clob Out Parameter in sql:ParameterizedCallQuery.

ClobValue

Represents SQL Clob type parameter in sql:ParameterizedQuery.

DateArrayOutParameter

Represents Date Array Out Parameter in sql:ParameterizedCallQuery.

DateArrayValue

Represents SQL Date array type parameter in sql:ParameterizedQuery.

DateOutParameter

Represents Date Out Parameter in sql:ParameterizedCallQuery.

DateTimeArrayOutParameter

Represents DateTime Array Out Parameter in sql:ParameterizedCallQuery.

DateTimeArrayValue

Represents SQL DateTime array type parameter in sql:ParameterizedQuery.

DateTimeOutParameter

Represents DateTime Out Parameter in sql:ParameterizedCallQuery.

DateTimeValue

Represents SQL DateTime type parameter in sql:ParameterizedQuery.

DateValue

Represents SQL Date type parameter in sql:ParameterizedQuery.

DecimalArrayOutParameter

Represents Decimal Out Parameter in sql:ParameterizedCallQuery.

DecimalArrayValue

Represents SQL Decimal array type parameter in sql:ParameterizedQuery.

DecimalOutParameter

Represents Decimal Out Parameter in sql:ParameterizedCallQuery.

DecimalValue

Represents SQL Decimal type parameter in sql:ParameterizedQuery.

DoubleArrayOutParameter

Represents Double Array Out Parameter in sql:ParameterizedCallQuery.

DoubleArrayValue

Represents SQL Double array type parameter in sql:ParameterizedQuery.

DoubleOutParameter

Represents Double Out Parameter in sql:ParameterizedCallQuery.

DoubleValue

Represents SQL Double type parameter in sql:ParameterizedQuery.

FloatArrayOutParameter

Represents Float Array Out Parameter in sql:ParameterizedCallQuery.

FloatArrayValue

Represents SQL Float array type parameter in sql:ParameterizedQuery.

FloatOutParameter

Represents Float Out Parameter in sql:ParameterizedCallQuery.

FloatValue

Represents SQL Float type parameter in sql:ParameterizedQuery.

InOutParameter

Represents SQL InOutParameter in sql:ParameterizedCallQuery.

IntegerArrayOutParameter

Represents Integer Array Out Parameter in sql:ParameterizedCallQuery.

IntegerArrayValue

Represents SQL Integer array type parameter in sql:ParameterizedQuery.

IntegerOutParameter

Represents Integer Out Parameter in sql:ParameterizedCallQuery.

IntegerValue

Represents SQL Integer type parameter in sql:ParameterizedQuery.

NCharOutParameter

Represents NChar Out Parameter in sql:ParameterizedCallQuery.

NCharValue

Represents SQL NChar type parameter in sql:ParameterizedQuery.

NClobOutParameter

Represents NClob Out Parameter in sql:ParameterizedCallQuery.

NClobValue

Represents SQL NClob type parameter in sql:ParameterizedQuery.

NumericArrayOutParameter

Represents Numeric Array Out Parameter in sql:ParameterizedCallQuery.

NumericArrayValue

Represents SQL Numeric array type parameter in sql:ParameterizedQuery.

NumericOutParameter

Represents Numeric Out Parameter in sql:ParameterizedCallQuery.

NumericValue

Represents SQL Numeric type parameter in sql:ParameterizedQuery.

NVarcharArrayOutParameter

Represents NVarchar Array Out Parameter in sql:ParameterizedCallQuery.

NVarcharArrayValue

Represents SQL NVarchar type parameter in sql:ParameterizedQuery.

NVarcharOutParameter

Represents NVarchar Out Parameter in sql:ParameterizedCallQuery.

NVarcharValue

Represents SQL NVarchar type parameter in sql:ParameterizedQuery.

ProcedureCallResult

Represents the results from call method holding returned results or metadata of query execution.

RealArrayOutParameter

Represents Real Array Out Parameter in sql:ParameterizedCallQuery.

RealArrayValue

Represents SQL Real array type parameter in sql:ParameterizedQuery.

RealOutParameter

Represents Real Out Parameter in sql:ParameterizedCallQuery.

RealValue

Represents SQL Real type parameter in sql:ParameterizedQuery.

RefOutParameter

Represents Ref Out Parameter in sql:ParameterizedCallQuery.

RefValue

Represents SQL Ref type parameter in sql:ParameterizedQuery.

ResultIterator

The result iterator used to iterate results in stream returned from query function.

RowOutParameter

Represents Row Out Parameter in sql:ParameterizedCallQuery.

RowValue

Represents SQL Row type parameter in sql:ParameterizedQuery.

SmallIntArrayOutParameter

Represents SmallInt Array Out Parameter in sql:ParameterizedCallQuery.

SmallIntArrayValue

Represents SQL SmallInt array type parameter in sql:ParameterizedQuery.

SmallIntOutParameter

Represents SmallInt Out Parameter in sql:ParameterizedCallQuery.

SmallIntValue

Represents SQL SmallInt type parameter in sql:ParameterizedQuery.

StructOutParameter

Represents Struct Out Parameter in sql:ParameterizedCallQuery.

StructValue

Represents SQL Struct type parameter in sql:ParameterizedQuery.

TextOutParameter

Represents Text Out Parameter in sql:ParameterizedCallQuery.

TextValue

Represents SQL Text type parameter in sql:ParameterizedQuery.

TimeArrayOutParameter

Represents Time Array Out Parameter in sql:ParameterizedCallQuery.

TimeArrayValue

Represents SQL Time array type parameter in sql:ParameterizedQuery.

TimeOutParameter

Represents Time Out Parameter in sql:ParameterizedCallQuery.

TimestampArrayOutParameter

Represents Timestamp Array Out Parameter in sql:ParameterizedCallQuery.

TimestampArrayValue

Represents SQL Timestamp array type parameter in sql:ParameterizedQuery.

TimestampOutParameter

Represents Timestamp Out Parameter in sql:ParameterizedCallQuery.

TimestampValue

Represents SQL Timestamp type parameter in sql:ParameterizedQuery.

TimestampWithTimezoneArrayOutParameter

Represents Timestamp with Timezone Array Out Parameter in sql:ParameterizedCallQuery.

TimestampWithTimezoneOutParameter

Represents Timestamp with Timezone Out Parameter in sql:ParameterizedCallQuery.

TimeValue

Represents SQL Time type parameter in sql:ParameterizedQuery.

TimeWithTimezoneArrayOutParameter

Represents Time With Timezone Array Out Parameter in sql:ParameterizedCallQuery.

TimeWithTimezoneOutParameter

Represents Time With Timezone Out Parameter in sql:ParameterizedCallQuery.

VarBinaryArrayOutParameter

Represents VarBinary Array Out Parameter in sql:ParameterizedCallQuery.

VarBinaryArrayValue

Represents SQL Boolean array type parameter in sql:ParameterizedQuery.

VarBinaryOutParameter

Represents VarBinary Out Parameter in sql:ParameterizedCallQuery.

VarBinaryValue

Represents SQL VarBinary type parameter in sql:ParameterizedQuery.

VarcharArrayOutParameter

Represents Varchar Array Out Parameter in sql:ParameterizedCallQuery.

VarcharArrayValue

Represents SQL Varchar array type parameter in sql:ParameterizedQuery.

VarcharOutParameter

Represents Varchar Out Parameter in sql:ParameterizedCallQuery.

VarcharValue

Represents SQL Varchar type parameter in sql:ParameterizedQuery.

XMLOutParameter

Represents XML Out Parameter in sql:ParameterizedCallQuery.

Object types

[6]

Client

Represents a SQL client.

CustomResultIterator

The iterator for the stream returned in query function to be used overriding the default behaviour of sql:ResultIterator.

OutParameter

Represents the generic OUT Parameters in sql:ParameterizedCallQuery.

ParameterizedCallQuery

The object constructed through backtick surrounded strings.

ParameterizedQuery

The object constructed through backtick surrounded strings.

TypedValue

Generic type that can be passed to sql:ParameterizedQuery to represent parameters in the SQL query.

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

Metadata of the query execution.

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 the count of affected rows is unavailable.

Types

[2]

Parameter

Generic type that can be passed to sql:ParameterizedCallQuery to indicate procedure/function parameters.

Value

Generic type of ballerina basic types that can be passed to sql:ParameterizedQuery to represent parameters in the SQL query.

Errors

[10]

ApplicationError

Represents an error originating from application-level configurations.

BatchExecuteError

Represents an error occurred during execution of batch queries.

ConversionError

Represents an error that occurs when a query retrieves a result that is corrupted and cannot be converted to the 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 parameters or returned results.

Error

Defines the generic error type for the sql module.

FieldMismatchError

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

NoRowsError

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

TypeMismatchError

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

UnsupportedTypeError

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