ballerina/sql Ballerina library
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
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.
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
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.
Client-owned, unsharable connection pool
If you define the
connectionPoolfield inline when creating the database client with the
sql:ConnectionPooltype, an unsharable connection pool will be created. The JDBC module sample below shows how the global connection pool is used.
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.
Closing the Client
Once all the database operations are performed, you can close the database client you have created by invoking the
operation. This will close the corresponding connection pool if it is not shared by any other database clients.
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.
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
Query with dynamic values
Moreover, the SQL package has
sql:arrayFlattenQuery() util functions which make it easier
to create a dynamic/constant complex query.
sql:queryConcat() is used to create a parameterized query by concatenating a set of parameterized queries.
The sample below shows how to concatenate queries.
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.
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.
This sample creates a table with two columns. One column is of type
int and the other is of type
CREATE statement is executed via the
execute remote function of the client.
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
In this sample, the parameter values, which are in local variables are used to parameterize the SQL query in
execute remote function. This type of a parameterized SQL query can be used with any primitive Ballerina type
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.
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:IntegerValue, etc., when you need to
provide more details such as the exact SQL type of the parameter.
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
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
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
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.
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.
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.
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.
This sample demonstrates modifying data by executing an
UPDATE statement via the
execute remote function of
This sample demonstrates deleting data by executing a
DELETE statement via the
execute remote function of
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 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.
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