ballerina/mysql module

Module overview

This module provides the functionality required to access and manipulate data stored in a MySQL database.

Endpoint

To access a database, you must first create a client object. Create a client of the MySQL Client type (i.e., mysql:Client) and provide the necessary connection parameters. This will create a pool of connections to the given MySQL database. A sample for creating a MySQL client can be found below.

Database operations

Once the client is created, 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, and batch updating data. For more details on the supported remote functions refer to the sql module. Also the details of the SQL data types and query parameters relevant to these database operations can be found in the documentation of the sql module.

Samples

Creating an endpoint

mysql:Client testDB = new({
    host: "localhost",
    port: 3306,
    name: "testdb",
    username: "root",
    password: "root",
    poolOptions: { maximumPoolSize: 5 },
    dbOptions: { "useSSL": false }
});

The full list of client properties can be found in 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 sql:PoolOptions for more details

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|byte[][]|sql:Parameter parameters) returns (table<$anonType$0>[] | null | error<>)

    The call operation 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|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|byte[][]|sql:Parameter parameters) returns (table<$anonType$1> | error<>)

    The select operation 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|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 else error will be returned if there is any error

  • <Client> update(string sqlQuery, string|int|boolean|float|byte[][]|sql:Parameter parameters) returns (int | error<>)

    The update operation 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

    parameters string|int|boolean|float|byte[][]|sql:Parameter

    The parameters to be passed to the update query. The number of parameters is variable

    Return Type Description
    int | error<>

    int number of rows updated by the statement and else error will be returned if there is any error

  • <Client> batchUpdate(string sqlQuery, string|int|boolean|float|byte[][][]|sql:Parameter parameters) returns (int[] | error<>)

    The batchUpdate operation 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|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

  • <Client> updateWithGeneratedKeys(string sqlQuery, string[]? keyColumns, string|int|boolean|float|byte[][]|sql:Parameter parameters) returns ((int,string[]) | error<>)

    The updateWithGeneratedKeys operation implementation for MySQL Client which returns the auto generated keys during the update remote function.

    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|byte[][]|sql:Parameter

    The parameters to be passed to the update query. The number of parameters is variable

    Return Type Description
    (int,string[]) | error<>

    A Tuple will be returned and would represent updated row count during the query exectuion, aray of auto generated key values during the query execution, in order. Else error will be returned if there is any error.