ballerina/sql1.2.0
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.
-
Global, shareable, default connection pool
If you do not provide the
poolOptionsfield 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"); -
Client-owned, unsharable connection pool
If you define the
connectionPoolfield inline when creating the database client with thesql:ConnectionPooltype, 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 }); -
Local, shareable connection pool
If you create a record of the
sql:ConnectionPooltype 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};23jdbc: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 students2 WHERE id < 10 AND age > 12`;
Query with dynamic values
1int[] ids = [10, 50];2int age = 12;3sql:ParameterizedQuery query = `SELECT * FROM students4 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 DataTable3 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;34sql: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);34sql: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";34sql: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};78// Select the data from the database table. The query parameters are passed9// directly. Similar to the `execute` samples, parameters can be passed as10// sub types of `sql:TypedValue` as well.11int id = 10;12int age = 12;13sql:ParameterizedQuery query = `SELECT * FROM students14 WHERE id < ${id} AND age > ${age}`;15stream<Student, sql:Error?> resultStream = dbClient->query(query);1617// 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 passed2// directly. Similar to the `execute` samples, parameters can be passed as3// sub types of `sql:TypedValue` as well.4int id = 10;5int age = 12;6sql:ParameterizedQuery query = `SELECT * FROM students7 WHERE id < ${id} AND age > ${age}`;8stream<record{}, sql:Error?> resultStream = dbClient->query(query);910// 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];78// Do the batch update by passing the batches.9sql:ParameterizedQuery[] batch = from var row in data10 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;34sql: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 theBALLERINA_MAX_POOL_SIZEenvironment variable.
Functions
[4]
| arrayFlattenQuery | Joins the parameters in the array with the |
| generateApplicationErrorStream | Generates a stream consisting of |
| getGlobalConnectionPool | Returns the global connection pool. |
| queryConcat | Concatenates all provided |
Classes
[102]
| ArrayOutParameter | Represents Array Out Parameter in |
| ArrayValue | D Represents SQL ArrayValue type parameter in |
| BigIntArrayOutParameter | Represents BigInt Array Out Parameter in |
| BigIntArrayValue | Represents SQL BigInt array type parameter in |
| BigIntOutParameter | Represents BigInt Out Parameter in |
| BigIntValue | Represents SQL BigInt type parameter in |
| BinaryArrayOutParameter | Represents Binary Array Out Parameter in |
| BinaryArrayValue | Represents SQL Boolean array type parameter in |
| BinaryOutParameter | Represents Binary Out Parameter in |
| BinaryValue | Represents SQL Binary type parameter in |
| BitArrayOutParameter | Represents Bit Array Out Parameter in |
| BitArrayValue | Represents SQL Bit array type parameter in |
| BitOutParameter | Represents Bit Out Parameter in |
| BitValue | Represents SQL Bit type parameter in |
| BlobOutParameter | Represents Blob Out Parameter in |
| BlobValue | Represents SQL Blob type parameter in |
| BooleanArrayOutParameter | Represents Boolean Array Out Parameter in |
| BooleanArrayValue | Represents SQL Boolean array type parameter in |
| BooleanOutParameter | Represents Boolean Out Parameter in |
| BooleanValue | Represents SQL Boolean type parameter in |
| CharArrayOutParameter | Represents Char Array Out Parameter in |
| CharArrayValue | Represents SQL Char array type parameter in |
| CharOutParameter | Represents Char Out Parameter in |
| CharValue | Represents SQL Char type parameter in |
| ClobOutParameter | Represents Clob Out Parameter in |
| ClobValue | Represents SQL Clob type parameter in |
| DateArrayOutParameter | Represents Date Array Out Parameter in |
| DateArrayValue | Represents SQL Date array type parameter in |
| DateOutParameter | Represents Date Out Parameter in |
| DateTimeArrayOutParameter | Represents DateTime Array Out Parameter in |
| DateTimeArrayValue | Represents SQL DateTime array type parameter in |
| DateTimeOutParameter | Represents DateTime Out Parameter in |
| DateTimeValue | Represents SQL DateTime type parameter in |
| DateValue | Represents SQL Date type parameter in |
| DecimalArrayOutParameter | Represents Decimal Out Parameter in |
| DecimalArrayValue | Represents SQL Decimal array type parameter in |
| DecimalOutParameter | Represents Decimal Out Parameter in |
| DecimalValue | Represents SQL Decimal type parameter in |
| DoubleArrayOutParameter | Represents Double Array Out Parameter in |
| DoubleArrayValue | Represents SQL Double array type parameter in |
| DoubleOutParameter | Represents Double Out Parameter in |
| DoubleValue | Represents SQL Double type parameter in |
| FloatArrayOutParameter | Represents Float Array Out Parameter in |
| FloatArrayValue | Represents SQL Float array type parameter in |
| FloatOutParameter | Represents Float Out Parameter in |
| FloatValue | Represents SQL Float type parameter in |
| InOutParameter | Represents SQL InOutParameter in |
| IntegerArrayOutParameter | Represents Integer Array Out Parameter in |
| IntegerArrayValue | Represents SQL Integer array type parameter in |
| IntegerOutParameter | Represents Integer Out Parameter in |
| IntegerValue | Represents SQL Integer type parameter in |
| NCharOutParameter | Represents NChar Out Parameter in |
| NCharValue | Represents SQL NChar type parameter in |
| NClobOutParameter | Represents NClob Out Parameter in |
| NClobValue | Represents SQL NClob type parameter in |
| NumericArrayOutParameter | Represents Numeric Array Out Parameter in |
| NumericArrayValue | Represents SQL Numeric array type parameter in |
| NumericOutParameter | Represents Numeric Out Parameter in |
| NumericValue | Represents SQL Numeric type parameter in |
| NVarcharArrayOutParameter | Represents NVarchar Array Out Parameter in |
| NVarcharArrayValue | Represents SQL NVarchar type parameter in |
| NVarcharOutParameter | Represents NVarchar Out Parameter in |
| NVarcharValue | Represents SQL NVarchar type parameter in |
| ProcedureCallResult | Represents the results from |
| RealArrayOutParameter | Represents Real Array Out Parameter in |
| RealArrayValue | Represents SQL Real array type parameter in |
| RealOutParameter | Represents Real Out Parameter in |
| RealValue | Represents SQL Real type parameter in |
| RefOutParameter | Represents Ref Out Parameter in |
| RefValue | Represents SQL Ref type parameter in |
| ResultIterator | The result iterator used to iterate results in stream returned from |
| RowOutParameter | Represents Row Out Parameter in |
| RowValue | Represents SQL Row type parameter in |
| SmallIntArrayOutParameter | Represents SmallInt Array Out Parameter in |
| SmallIntArrayValue | Represents SQL SmallInt array type parameter in |
| SmallIntOutParameter | Represents SmallInt Out Parameter in |
| SmallIntValue | Represents SQL SmallInt type parameter in |
| StructOutParameter | Represents Struct Out Parameter in |
| StructValue | Represents SQL Struct type parameter in |
| TextOutParameter | Represents Text Out Parameter in |
| TextValue | Represents SQL Text type parameter in |
| TimeArrayOutParameter | Represents Time Array Out Parameter in |
| TimeArrayValue | Represents SQL Time array type parameter in |
| TimeOutParameter | Represents Time Out Parameter in |
| TimestampArrayOutParameter | Represents Timestamp Array Out Parameter in |
| TimestampArrayValue | Represents SQL Timestamp array type parameter in |
| TimestampOutParameter | Represents Timestamp Out Parameter in |
| TimestampValue | Represents SQL Timestamp type parameter in |
| TimestampWithTimezoneArrayOutParameter | Represents Timestamp with Timezone Array Out Parameter in |
| TimestampWithTimezoneOutParameter | Represents Timestamp with Timezone Out Parameter in |
| TimeValue | Represents SQL Time type parameter in |
| TimeWithTimezoneArrayOutParameter | Represents Time With Timezone Array Out Parameter in |
| TimeWithTimezoneOutParameter | Represents Time With Timezone Out Parameter in |
| VarBinaryArrayOutParameter | Represents VarBinary Array Out Parameter in |
| VarBinaryArrayValue | Represents SQL Boolean array type parameter in |
| VarBinaryOutParameter | Represents VarBinary Out Parameter in |
| VarBinaryValue | Represents SQL VarBinary type parameter in |
| VarcharArrayOutParameter | Represents Varchar Array Out Parameter in |
| VarcharArrayValue | Represents SQL Varchar array type parameter in |
| VarcharOutParameter | Represents Varchar Out Parameter in |
| VarcharValue | Represents SQL Varchar type parameter in |
| XMLOutParameter | Represents XML Out Parameter in |
Object Types
[6]
| Client | Represents a SQL client. |
| CustomResultIterator | The iterator for the stream returned in |
| OutParameter | Represents the generic OUT Parameters in |
| ParameterizedCallQuery | The object constructed through backtick surrounded strings. |
| ParameterizedQuery | The object constructed through backtick surrounded strings. |
| TypedValue | Generic type that can be passed to |
Records
[4]
| BatchExecuteErrorDetail | Represents the properties belonging to an |
| ConnectionPool | Represents the properties which are used to configure DB connection pool. |
| DatabaseErrorDetail | Represents the properties belonging to an |
| 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 |
| Value | Generic type of ballerina basic types that can be passed to |
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 |
| 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. |