ballerina/mysql module
Module overview
This module provides the functionality that is required to access and manipulate the data stored in a MySQL database.
Creating the client
You need to create a client
object to access a database. You can create a sample MySQL client as follows.
Handling the connection pool
There are 3 possible usage scenarios for a connection pool.
1. The globally shareable connection pool
If you do not provide the poolOptions
field, a globally shareable pool will be created for your database unless
a connection pool matching the properties you provided already exists.
Info: This is the connection pool that is used by default.
mysql:Client testDB = new({
host: "localhost",
port: 3306,
name: "testdb",
username: "root",
password: "root",
dbOptions: { "useSSL": false }
});
2. An unshareable connection pool owned by the client
If you define the poolOptions
field inline, an unshareable connection pool will be created.
mysql:Client testDB = new({
host: "localhost",
port: 3306,
name: "testdb",
username: "root",
password: "root",
poolOptions: { maximumPoolSize: 5 },
dbOptions: { "useSSL": false }
});
3. A locally shareable connection pool
If you create a record of the sql:PoolOptions
type and reuse that in the configuration of multiple clients, a shared
connection pool will be created, for each set of clients that connect to the same database instance with the same set of
properties.
mysql:Client testDB1;
mysql:Client testDB2;
mysql:Client testDB3;
sql:PoolOptions poolOptions1 = { maximumPoolSize: 5 };
testDB1 = new({
host: "localhost",
port: 3306,
name: "testdb",
username: "root",
password: "root",
poolOptions: poolOptions1,
dbOptions: { "useSSL": false }
});
testDB2 = new({
host: "localhost",
port: 3306,
name: "testdb",
username: "root",
password: "root",
poolOptions: poolOptions1,
dbOptions: { "useSSL": false }
});
testDB3 = new({
host: "localhost",
port: 3306,
name: "testdb",
username: "root",
password: "root",
poolOptions: poolOptions1,
dbOptions: { "useSSL": false }
});
For the default values of the connection pool properties, see the documentation of the sql:PoolOptions
type.
Database operations
Once the client is created, the database operations can be executed through that client. This module provides support for creating tables and executing stored procedures. It also supports selecting, inserting, deleting, updating a single data record, and updating data in batches.
For more details on the supported remote functions, and of the details of the SQL data types and query parameters relevant to these database operations,see the documentation of the sql
module.
Samples
Creating a client
mysql:Client testDB = new({
host: "localhost",
port: 3306,
name: "testdb",
username: "root",
password: "root",
dbOptions: { "useSSL": false }
});
For a complete list of client properties, see the documentation of the sql:PoolOptions
type.
Records Summary
Record | Description | ||
---|---|---|---|
ClientEndpointConfig | The Client endpoint configuration for mysql databases. |
Endpoints Summary
Endpoint | Description | ||
---|---|---|---|
Client | Represents an MySQL client endpoint. |
public type ClientEndpointConfig record
The Client endpoint configuration for mysql databases.
Field Name | Data Type | Default Value | Description |
---|---|---|---|
host | string | The host name of the database to connect |
|
port | int | 3306 | The port of the database to connect |
name | string | The name of the database to connect |
|
username | string | Username for the database connection |
|
password | string | Password for the database connection |
|
poolOptions | sql:PoolOptions | Properties for the connection pool configuration. Refer |
|
dbOptions | map | {} | A map of DB specific properties |
Endpoint Client
Represents an MySQL client endpoint.
-
<Client> call(string sqlQuery, typedesc[]? recordType, string|int|boolean|float|decimal|byte[][]|sql:Parameter? parameters) returns (table<$anonType$0>[]|null|error<>)
The call remote function implementation for MySQL Client to invoke stored procedures/functions.
Parameter Name Data Type Default Value Description sqlQuery string The SQL stored procedure to execute
recordType typedesc[]? Array of record types of the returned tables if there is any
parameters string|int|boolean|float|decimal|byte[][]|sql:Parameter? The parameters to be passed to the procedure/function call. The number of parameters is variable
Return Type Description table<$anonType$0>[]|null|error<> A
table[]
if there are tables returned by the call remote function and else nil,error
will be returned if there is any error -
<Client> select(string sqlQuery, typedesc? recordType, boolean loadToMemory, string|int|boolean|float|decimal|byte[][]|sql:Parameter? parameters) returns (table<$anonType$1>|error<>)
The select remote function implementation for MySQL Client to select data from tables.
Parameter Name Data Type Default Value Description sqlQuery string SQL query to execute
recordType typedesc? Type of the returned table
loadToMemory boolean false Indicates whether to load the retrieved data to memory or not
parameters string|int|boolean|float|decimal|byte[][]|sql:Parameter? The parameters to be passed to the select query. The number of parameters is variable
Return Type Description table<$anonType$1>|error<> A
table
returned by the sql query statement elseerror
will be returned if there is any error -
<Client> update(string sqlQuery, string[]? keyColumns, string|int|boolean|float|decimal|byte[][]|sql:Parameter? parameters) returns (sql:UpdateResult|error<>)
The update remote function implementation for MySQL Client to update data and schema of the database.
Parameter Name Data Type Default Value Description sqlQuery string SQL statement to execute
keyColumns string[]? () Names of auto generated columns for which the auto generated key values are returned
parameters string|int|boolean|float|decimal|byte[][]|sql:Parameter? The parameters to be passed to the update query. The number of parameters is variable
Return Type Description sql:UpdateResult|error<> A
sql:UpdateResult
with the updated row count and key column values, elseerror
will be returned if there is any error -
<Client> batchUpdate(string sqlQuery, string|int|boolean|float|decimal|byte[][][]|sql:Parameter? parameters) returns (int[]|error<>)
The batchUpdate remote function implementation for MySQL Client to batch data insert.
Parameter Name Data Type Default Value Description sqlQuery string SQL statement to execute
parameters string|int|boolean|float|decimal|byte[][][]|sql:Parameter? Variable number of parameter arrays each representing the set of parameters of belonging to each individual update
Return Type Description int[]|error<> An
int[]
- The elements in the array returned by the operation may be one of the following or else anerror
will be returned if there is any error. A number greater than or equal to zero - indicates that the command was processed successfully and is an update count giving the number of rows A value of -2 - Indicates that the command was processed successfully but that the number of rows affected is unknown A value of -3 - Indicates that the command failed to execute successfully and occurs only if a driver continues to process commands after a command fails