ballerina/sql0.6.0-alpha9
Package overview
This Package provides the common interface and functionality to interact with a database. The corresponding database
clients can be created by using specific database packages such as MySQL
or using the Java Database Connectivity
package JDBC
.
List of Database Packages
- JDBC (Java Database Connectivity) Package
This package can be used to connect with any database by simply providing the JDBC URL and the other related properties. For more details, see the JDBC package.
- MySQL Package
This package is specially designed to work with a MySQL database and allows to access the functionality provided by MySQL 8.0.x onwards. For more details, see the MySQL package.
Client
The database client should be created using any of the above-listed database packages and once it is created, the operations and functionality explained below can be used.
Connection pool handling
All database packages share the same connection pooling concept and there are 3 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
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 package example 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
connectionPool
field inline when creating the database client with thesql:ConnectionPool
type, an unsharable connection pool will be created. The JDBC package example 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 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. The JDBC package example 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();2if (e is error){3 io:println("Error occured:", e);4}5
Database operations
Once the client is created, database operations can be executed through that client. This package 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.2var ret = dbClient->execute("CREATE TABLE student(id INT AUTO_INCREMENT, " +3 "age INT, name VARCHAR(255), PRIMARY KEY (id))");4if (ret is sql:ExecutionResult) {5 io:println("Students table create status in DB: ", ret.affectedRowCount);6} else {7 error err = ret;8 io:println("Students table creation failed: ", err.message());9}
Inserting data
This sample shows three examples of data insertion by executing an INSERT statement using the execute
remote function
of the client.
In the first example, the query parameter values are passed directly into the query statement of the execute
remote function.
1var ret = dbClient->execute("INSERT INTO student(age, name) " +2 "values (23, 'john')");3if (ret is sql:ExecutionResult) {4 io:println("Inserted row count to Students table: ", ret.affectedRowCount);5} else {6 error err = ret;7 io:println("Insert to Students table failed: ", err.message());8}
In the second example, 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;34sql:ParameterizedQuery query = `INSERT INTO student(age, name)5 values (${age}, ${name})`;6var ret = dbClient->execute(query);7if (ret is sql:ExecutionResult) {8 io:println("Inserted row count to Students table: ", ret.affectedRowCount);9} else {10 error err = ret;11 io:println("Insert to Students table failed: ", err.message());12}
In the third example, 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);34sql:ParameterizedQuery query = `INSERT INTO student(age, name)5 values (${age}, ${name})`;6var ret = dbClient->execute(query);7f (ret is sql:ExecutionResult) {8 io:println("Inserted row count to Students table: ", ret.affectedRowCount);9} else {10 error err = ret;11 io:println("Insert to Students table failed: ", err.message());12}
Inserting data with auto-generated keys
This example 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})`;6var ret = dbClient->execute(query);7if (ret is sql:ExecutionResult) {8 int? count = ret.affectedRowCount;9 string|int? generatedKey = ret.lastInsertId;10 io:println("Inserted row count: ", count);11 io:println("Generated key: ", generatedKey);12} else {13 error err = ret;14 io:println("Insert to table failed: ", err.message());15}
Querying data
This sample shows three examples to demonstrate the different usages of the query
operation and query the
database table and obtain the results.
This example demonstrates querying data from a table in a database.
First, a type is created to represent the returned result set. Note the mapping of the database column
to the returned record's property is case insensitive (i.e., ID
column in the result can be mapped to the id
property in the record). Next, the SELECT query is executed via the query
remote function of the client by passing that
result set type. 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 a 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` examples, 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 =16 <stream<Student, sql:Error>> dbClient->query(query, Student);1718// Iterating the returned table.19error? e = resultStream.forEach(function(Student student) {20 io:println("Student Id: ", student.id);21 io:println("Student age: ", student.age);22 io:println("Student name: ", student.name);23});24if (e is error) {25 io:println("Query execution failed.", e);26}
Defining the return type is optional and you can query the database without providing the result type. Hence, the above example 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` examples, 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 io:println("Student Id: ", student["id"]);13 io:println("Student age: ", student["age"]);14 io:println("Student name: ", student["name"];15});16if (e is error) {17 io:println("Query execution failed.", e);18}
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");34record {|record {} value;|}|error? result = resultStream.next();56if (result is record {|record {} value;|}) {7 io:println("Total students : ", result.value["total"]);8} else if (result is error) {9 io:println("Error encoutered when executing query. ", result);10} else {11 io:println("Student table is empty");12}1314error? e = resultStream.close();15if(e is error){16 io:println("Error when closing the stream", e);17}
Updating data
This example 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}`;4var ret = dbClient->execute(query);5if (ret is sql:ExecutionResult) {6 io:println("Updated row count in Students table: ", ret.affectedRowCount);7} else {8 error err = ret;9 io:println("Update to students table failed: ", err.message());10}
Deleting data
This example 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}`;3var ret = dbClient->execute(query);4if (ret is sql:ExecutionResult) {5 io:println("Deleted student count: ", ret.affectedRowCount);6} else {7 error err = ret;8 io:println("Delete from students table failed: ", err.message());9}
Batch updating data
This example 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})`;12var ret = dbClient->batchExecute(batch);1314if (ret is error) {15 io:println("Error occurred:", err.message());16} else {17 io:println("Batch item 1 update count: ", ret[0].affectedRowCount);18 io:println("Batch item 2 update count: ", ret[1].affectedRowCount);19}
Execute SQL stored procedures
This example 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;34var ret = dbClient->call(`call InsertPerson(${uid}, ${insertId})`);5if (ret is error) {6 io:println("Error occurred:", err.message());7} else {8 io:println("Outparameter insert id: ", insertId.get(int));9 stream<record{}, sql:Error>? resultStr = ret.queryResult;10 if (!(resultStr is ())) {11 sql:Error? e = resultStr.forEach(function(record{} result) {12 io:println("Full Customer details: ", result);13 });14 } else {15 io:println("Stored procedure does not return anything.");16 }17 check ret.close();18}
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.
Functions
[2]
generateApplicationErrorStream | |
getGlobalConnectionPool |
Classes
[60]
ArrayOutParameter | Represents Array Out Parameter used in procedure calls |
ArrayValue | Represents ArrayValue SQL field. |
BigIntOutParameter | Represents BigInt Out Parameter used in procedure calls |
BigIntValue | Represents BigInt SQL field. |
BinaryOutParameter | Represents Binary Out Parameter used in procedure calls |
BinaryValue | Represents Binary 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. |
BooleanOutParameter | Represents Boolean Out Parameter used in procedure calls |
BooleanValue | Represents Boolean 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. |
DateOutParameter | Represents Date Out Parameter used in procedure calls |
DateTimeOutParameter | Represents DateTime Out Parameter used in procedure calls |
DateTimeValue | Represents DateTime SQL field. |
DateValue | Represents Date SQL field. |
DecimalOutParameter | Represents Decimal Out Parameter used in procedure calls |
DecimalValue | Represents Decimal SQL field. |
DoubleOutParameter | Represents Double Out Parameter used in procedure calls |
DoubleValue | Represents Double SQL field. |
FloatOutParameter | Represents Float Out Parameter used in procedure calls |
FloatValue | Represents Float SQL field. |
InOutParameter | Represents SQL InOutParameter used in procedure calls. |
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. |
NumericOutParameter | Represents Numeric Out Parameter used in procedure calls |
NumericValue | Represents Numeric 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. |
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. |
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. |
TimeOutParameter | Represents Time Out Parameter used in procedure calls |
TimestampOutParameter | Represents Timestamp Out Parameter used in procedure calls |
TimestampValue | Represents Timestamp SQL field. |
TimeValue | Represents Time SQL field. |
VarBinaryOutParameter | Represents VarBinary Out Parameter used in procedure calls |
VarBinaryValue | Represents VarBinary 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
[4]
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. |
TypedValue | Represents a parameter for the SQL Client remote functions when a variable needs to be passed to the remote function. |
Records
[4]
BatchExecuteErrorDetail | Represents the properties belonging to a |
ConnectionPool | Represents the properties which are used to configure DB connection pool. |
DatabaseErrorDetail | Represents the properties belonging to a |
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
[1]
Error | Defines the common error type for the module |