ballerina/h2 module

Module overview

This module provides the functionality that is required to access and manipulate the data stored in an H2 database.

Creating the client

You need to create a client object to access a database. You can create a sample H2 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.

h2:Client testDB = new({
    path: "/home/ballerina/test/",
    name: "testdb",
    username: "SA",
    password: ""
});

2. An unshareable connection pool owned by the client

If you define the poolOptions field inline, an unshareable connection pool will be created.

h2:Client testDB = new({
    path: "/home/ballerina/test/",
    name: "testdb",
    username: "SA",
    password: "",
    poolOptions: { maximumPoolSize: 5 }
});

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.

h2:Client testDB1;
h2:Client testDB2;
h2:Client testDB3;

sql:PoolOptions poolOptions1 = { maximumPoolSize: 5 };

testDB1 = new({
    path: "/home/ballerina/test",
    name: "testdb1",
    username: "SA",
    password: "",
    poolOptions: poolOptions1
});

testDB2 = new({
    path: "/home/ballerina/test",
    name: "testdb2",
    username: "SA",
    password: "",
    poolOptions: poolOptions1
});

testDB3 = new({
    path: "/home/ballerina/test",
    name: "testdb1",
    username: "SA",
    password: "",
    poolOptions: poolOptions1
});

For the default values of the connection pool properties, see the documentation of the sql:PoolOptions type.

Handling 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 SQL data types and query parameters relevant to these database operations, see the documentation of the sql module.

Samples

Creating a client in the embedded mode of H2

h2:Client testDB = new({
    path: "/home/ballerina/test/",
    name: "testdb",
    username: "SA",
    password: ""
});

Creating a client in the server mode of H2

h2:Client testDB = new({
    host: "localhost",
    port: 9092,
    name: "testdb",
    username: "SA",
    password: ""
});

Creating a client in the in-memory mode of H2

h2:Client testDB = new({
    name: "testdb",
    username: "SA",
    password: ""
});

For a complete list of client properties, see the documentation of the sql:PoolOptions type.

Module Detail

Records

Record Description
EmbeddedModeConfig The Client endpoint configuration for the embedded mode of h2 databases.
InMemoryConfig The Client endpoint configuration for the in-memory mode of h2 databases.
ServerModeConfig The Client endpoint configuration for the server mode of h2 databases.

Endpoints

Endpoint Description
Client

Represents an H2 client endpoint.

public type EmbeddedModeConfig

The Client endpoint configuration for the embedded mode of h2 databases.

Field Name Data Type Default Value Description
path string

The path of the database connection (in case of file based DB)

public type InMemoryConfig

The Client endpoint configuration for the in-memory mode of h2 databases.

Field Name Data Type Default Value Description
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

public type ServerModeConfig

The Client endpoint configuration for the server mode of h2 databases.

Field Name Data Type Default Value Description
host string

The host name of the database to connect (in case of server based DB)

port int 9092

The port of the database to connect (in case of server based DB)

Endpoint Client

Represents an H2 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 H2 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 H2 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 else error 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 H2 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<>

    sql:UpdateResult with the updated row count and key column values, else error 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 H2 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