Write unit test cases for sql clients generated using bal persist tools

Summary

In the current support, when we do bal persist generate, Ballerina client objects with required types are generated for the persist model definition. However, we don't have a recommended way of testing the project code with the generated clients. As our generated client tries to connect to a remote DB server, we need to mock the generated client to write unit tests for the application

Goals

This proposal is to evaluate different ways and come up with the best option to mock the generated client to write unit tests for the application.
Automatically generate the mocking client object along with the actual client object.
Help developer to set up test framework easily and write test cases.

Motivation

Ballerina developers are now adapting the bal persist feature to manage the data persistence of the application. This is one of the common questions have when we are trying to write test cases for the application code which uses generated clients to connect with SQL databases. This was also asked in the discord thread[1]

https://discord.com/channels/957996897782616114/1166333932736893028/1166333932736893028

Description

In order to come up with a feasible solution, we have evaluated the following options,

Mock the resource function of the generated client to set the return values, before calling the function.
Mock the generated SQL client object with the persistent in-memory client.
Mock the generated SQL client object with the H2 DB client.

From these three options, Mocking the generated SQL client object with an H2 DB client seems to be feasible. We discussed other approaches in the Alternatives section.

Mock the generated SQL client object with the H2 DB client

In this approach, we are going to generate a mock H2 DB client along with the actual client. The mock client can be used to write test cases. At the moment, we have to use function mocking to mock the client object.

For example, In the Ballerina project, we can define a global client object and have an initializeClient function which is used to initialize the MySQL client.

final db:Client dbClient = check initializeClient();

function initializeClient() returns db:Client|error {
   return new ();
}

In the test cases, we can mock the initializeClient function and mock the MySQL client with the H2 client like below,

@test:Mock {functionName: "initializeClient"}
isolated function getMockClient() returns db:Client|error {
    return test:mock(db:Client, check new db:MockClient("jdbc:h2:./test", "sa", "", options = {}));         
}

Here the MockClient is a JDBC client which generated along with the SQL client. We can set up H2 DB and a client by passing the URL, username, and password.

The next step is the table creation. We can use the BeforeSuite and AfterSuite functions to create and drop necessary tables like below,

isolated final MockClient h2Client = check new MockClient(url, username, pwd);

@test:BeforeSuite
isolated function beforeSuite() returns error? {
    _ = check h2Client->executeNativeSQL(`CREATE TABLE Doctor (id INT NOT NULL, name VARCHAR(191) NOT NULL, specialty VARCHAR(191) NOT NULL, phoneNumber VARCHAR(191) NOT NULL, PRIMARY KEY(id))`);
}

@test:AfterSuite
function afterSuite() returns error? {
    _ = check h2Client->executeNativeSQL(`DROP TABLE Doctor`);
}

We also can generate the above two functions, as we know the model definition and tables.

With these changes, the generated folder structure looks like follows,

Here, the mock_client.bal file contains the MockClient object, the mock_db_config.bal file contains the configurable variables, and the mock_init.bal file contains the BeforeSuite and AfterSuite functions.

Build options for mock client generations

We can provide this option in the bal persist init command and this will be recorded in the Ballerina.toml file as shown below.

bal persist init --module db --datastore mysql --mock-datastore h2

We can give a new option mock-datastore in the tool configuration, like below. This will apply to bal build.

[tool.persist]
id = "generate-db-client"
filePath = "persist/model.bal" // required field
targetModule = "db"
options.datastore = "mysql"
options.mock-datastore = "h2"

For one-time generation, we can give a new option will store like below,

[persist]
datastore = "mysql"
mock-datastore = "h2"
module = "hospitalsvc.db"

We only support h2 at the moment, the bal build command and bal persist generate command will fail for any other values.

Add support for seed data

[TBD]

Source Code: https://github.com/daneshk/persist-test-samples/tree/move_generated_dir/mock_with_h2

Alternatives

Mock the resource function of the generated client The mocking resource function is not supported in Ballerina. Related issue[4]. Once it is supported, the developer can use it, like below.

@test:Config {}
function testDoctorInsert() {
db:Client clientEndpoint = test:mock(db:Client);
test:prepare(clientEndpoint).when("/doctors.post").thenReturn([6]);
http:Response|http:ClientError unionResult = testClient->/hospital/doctors.post({
   "id": 6,
   "name": "Dr. House",
   "specialty": "Neurologist",
   "phoneNumber": "1234567890"
});
....
}

They need to set the return value in each test case before calling the function. From the bal persist side, we don't need to make any improvements to support this.

Mock the generated SQL client object with the persist in-memory client This is to mock the generated SQL client with the generated in-memory client.

Issues in this approach

The get all resource function in the generated SQL client has filter query parameters, but the in-memory client doesn’t have those parameters. Since we need to have the same signature for all functions, it gives an error in compilation.
The private fields in the in-memory client and SQL client are different. Which leads to a compilation error.
There are bad sad errors due to unsupported operations like accessing function pointers in client initialization. E.g: https://github.com/daneshk/persist-test-samples/blob/main/mock_with_inmemory/tests/persist_client.bal#L21

Source Code: https://github.com/daneshk/persist-test-samples/tree/main/mock_with_inmemory

Testing

We need to write test cases with the generated mock client in different scenarios with different model definitions.

Risks and Assumptions

No Breaking changes associated with this change.

Dependencies

@sameerajayasoma please check and share your thoughts

As we talked about offline, +1 from me to go ahead with the H2 DB approach. This approach is the best so far. We need to rethink the CLI design based on how we finalize the proposal described in https://github.com/ballerina-platform/ballerina-library/issues/5784.

I assume that this solution works only for the SQL databases (relational DBs that are accessed and managed using SQL).

Can we use the in-memory datastore for other kinds of datastores like Google sheets?

As we talked about offline, +1 from me to go ahead with the H2 DB approach. This approach is the best so far. We need to rethink the CLI design based on how we finalize the proposal described in #5784.

I assume that this solution works only for the SQL databases (relational DBs that are accessed and managed using SQL).

Can we use the in-memory datastore for other kinds of datastores like Google sheets?

Supporting in-memory datastore for other kinds of datastores is not working because internal persistClients types used in the generated clients are different. The Ballerina test framework doesn't support mocking clients if private field types differ.

Considering the limitations in the Ballerina language and test framework, the design changed slightly. The updated design is as follows,

we are going to generate a mock H2 DB client for SQL clients(MySQL, MSSQL, and Postgres) and in-memory clients for non-SQL clients(Google sheets and Redis). The mock client can be used to write test cases. At the moment, we have to use function mocking to mock the client object.

For example, In the Ballerina project, we have to define a global client object and have an initializeClient function which is used to initialize the client.

final db:Client dbClient = check initializeClient();

function initializeClient() returns db:Client|error {
   return new ();
}

In the test cases, we can mock the initializeClient function and mock the SQL client with the H2 client like below,

@test:Mock {functionName: "initializeClient"}
isolated function getMockClient() returns db:Client|error {
    return test:mock(db:Client, check new db:MockClient("jdbc:h2:./test", "sa", "", options = {}));         
}

The MockClient is a JDBC client which generated along with the SQL client. We can set up H2 DB and a client by passing the URL, username, and password.
We can't use configurable variables to pass H2 db configurations. So we have to hardcode them.

We can mock the non-SQL client with the in-memory client like below,

@test:Mock {functionName: "initializeClient"}
isolated function getMockClient() returns db:Client|error {
    return test:mock(db:Client, check new db:MockClient();         
}

The next step is the table creation. We can use the BeforeSuite and AfterSuite functions to create and drop necessary tables like below,

isolated final MockClient h2Client = check new MockClient("jdbc:h2:./test", "sa", "", options = {}));

@test:BeforeSuite
isolated function beforeSuite() returns error? {
    check entities:setupTestDB();
}

@test:AfterSuite
function afterSuite() returns error? {
    check entities:cleanupTestDB();
}

public isolated function setupTestDB() returns persist:Error? {
    _ = check h2Client->executeNativeSQL(`DROP TABLE IF EXISTS "Doctor";`);
    _ = check h2Client->executeNativeSQL(`CREATE TABLE "Doctor" ("id" INT NOT NULL, "name" VARCHAR(191) NOT NULL, "specialty" VARCHAR(191) NOT NULL, "phoneNumber" VARCHAR(191) NOT NULL, PRIMARY KEY("id"))`);
}

public isolated function cleanupTestDB() returns persist:Error? {
    _ = check h2Client->executeNativeSQL(`DROP TABLE IF EXISTS "Doctor";`);
}

We are generating the above two functions(setupTestDB and cleanupTestDB), as we know the model definition and tables. So the user can use these functions in their before and after suite functions in the tests as shown below.

With these changes, the generated folder structure looks like follows,

Here, the persist_mock_client.bal file contains the MockClient object, and the persist_test_init.bal file contains the setupTestDB and cleanupTestDB functions.

Build options for mock client generations We can provide this option in the bal persist add command and this will be recorded in the Ballerina.toml file as shown below.

bal persist add --module db --datastore mysql --with-mock-client

We can give a new option withMockClient in the tool configuration, like below. This will apply to bal build.

[tool.persist]
id = "generate-db-client"
filePath = "persist/model.bal" // required field
targetModule = "db"
options.datastore = "mysql"
options.withMockClient = "true"

For a one-time generation, we can give the option in the bal persist generate command like the below,

bal persist generate --module db --datastore mysql --with-mock-client

We will generate a mock H2 client for the SQL clients and generate a mock in-memory client for the other clients.

Once we successfully execute the command, the Following message will print with all the steps to use the generated mock client.

For SQL clients


Persist client and entity types generated successfully in the db directory.
Mock client and setup db scripts generated successfully in the db directory.

To use the generated mock client in your tests, please follow the steps below

Initialize the persist client in a function. final db:Client dbClient = check initializeClient();

function initializeClient() returns db:Client|error { return new (); }

Mock the client instance with the mock client instance using Ballerina function mocking @test:Mock {functionName: "initializeClient"} isolated function getMockClient() returns db:Client|error { return test:mock(db:Client, check new db:MockClient("jdbc:h2:./test", "sa", "")); }
Call the setup and cleanup DB scripts in tests before and after suites @test:BeforeSuite isolated function beforeSuite() returns error? { check db:setupTestDB(); }

@test:AfterSuite function afterSuite() returns error? { check db:cleanupTestDB(); }


* For other clients

Persist client and entity types generated successfully in the entities directory. Ballerina table based mock client is generated successfully in the entities directory.

To use the generated mock client in your tests, please follow the steps below

Initialize the persist client in a function. final entities:Client dbClient = check initializeClient();

function initializeClient() returns entities:Client|error { return new (); }

Mock the client instance with the mock client instance using Ballerina function mocking @test:Mock {functionName: "initializeClient"} isolated function getMockClient() returns entities:Client|error { return test:mock(entities:Client, check new entities:MockClient()); }

For in-memory/h2 client

Persist client and entity types generated successfully in the entities directory.
The mock client is not generated for h2 as it is not supported. Please use the generated client in your tests.

ballerina-platform / ballerina-library