Add A Local Database For Testing

In order to test our database code, we will add a dependency on postgres, one of our test database containers.

Create the Database Module

Our current standard is to create a database module within the service when we need to integrate with a database. This module will package all the necessary scripts needed to create and populate the database.

Create the new module for the database and name it starter-service-db.

Add/modify the module pom file to match the following:

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
  <modelVersion>4.0.0</modelVersion>
  <parent>
    <groupId>gov.va.mobile</groupId>
    <artifactId>mobile-java-parent</artifactId>
    <version>3.0.6</version>
    <relativePath/>
  </parent>
  <groupId>gov.va.mobile.lib</groupId>
  <artifactId>starter-service-db</artifactId>
  <version>1.0.0</version>
  <packaging>jar</packaging>
</project>

You can also delete any code that was generated when you created this module, so go ahead and delete any src/main/java and src/test/java directories that might have been created.

In the parent pom (starter-service-parent/pom.xml), add the new module, above the existing client and service modules:

<modules>
  <module>starter-service-db</module>
  <module>starter-service-client</module>
  <module>starter-service</module>
</modules>

Add the Database Script

In the new module, create the package src/main/resources/db/sql/ and create the script start-project-000.sql:

CREATE DATABASE STARTER_SERVICE
        WITH
        OWNER = 'postgres'
        ENCODING = 'UTF8'
        CONNECTION LIMIT = -1
        IS_TEMPLATE = False;

CREATE SCHEMA starter_service;

CREATE SEQUENCE STARTER_DB_TABLE_SQS
  INCREMENT BY 50
  START 1;

CREATE TABLE starter_service.STARTER_DB_TABLE
(
  ID  INTEGER PRIMARY KEY,
  KEY    VARCHAR NOT NULL unique,
  VALUE   VARCHAR
);

ALTER SEQUENCE STARTER_DB_TABLE_SQS owned by starter_service.STARTER_DB_TABLE.ID;

This is the postgres database creation script. It will be copied on build to the kubernetes/components/dev/db/sql directory, where it will be mounted to the postgres container and executed on startup to create the database and table that we will use for testing.

Add the Test Database Dependency

It’s time to set up the dependency on the postgres container for integration testing.

In kubernetes/dev-with-dependencies/kustomization.yaml, add the dependency for postgres to the top of your dependencies:
```
components:
- https://coderepo.mobilehealth.va.gov/scm/dhsss/postgres.git//kubernetes?ref=v18.1
```

Configure the Database

Next, make the following changes to configure postgres properly.

In kubernetes/components/dev/db, create a new directory named sql. This will remain blank for now, but it will be the location where your database script is copied to on build.

Edit kubernetes/components/dev/db/add-db-config.yaml to update the configuration to use a postgres database:

apiVersion: apps/v1
kind: Deployment
metadata:
  name: postgres
spec:
  template:
    spec:
      containers:
        - name: postgres
          volumeMounts:
            - name: starter-service-db-config
              mountPath: /tmp/sql
      volumes:
        - name: starter-service-db-config
          configMap:
            name: starter-service-db-config

Uncomment the following line in kubernetes/components/dev/kustomization.yaml to load the database configuration:

...
patches:
- path: deployment.yaml
- path: service.yaml
#- path: db/add-db-config.yaml # Uncomment if there are DB scripts to be run and managed by Maven DB module
...

Add the following to kubernetes/components/secrets/application-secrets.env for the database user and password:
```
DATASOURCE_USER=postgres
DATASOURCE_PASSWORD=password
```

Uncomment the following lines in skaffold.yaml to load the database scripts from the starter-service-db module into the proper directory for mounting to the postgres container on build:

...
  hooks:
    before:
    - host:
        command: [ "bash", "-c", "./scripts/setKustomizeValues.sh" ]
#    - host: # Uncomment if DB scripts are managed in a Maven DB module
#        command: [ "bash", "-c", "./scripts/add-configmap-files.sh starter-service-db/src/main/resources/db/sql kubernetes/components/dev db/sql starter-service-db-config" ]
...

Add a port forward for postgres to skaffold.yaml so that you can connect to it:

...
portForward:
- resourceType: service
  resourceName: postgres
  port: 5432
...

Add the following database configuration properties to application.properties:

# Postgres configuration
spring.datasource.url=jdbc:postgresql://postgres:5432/postgres
spring.datasource.username=${DATASOURCE_USER:}
spring.datasource.password=${DATASOURCE_PASSWORD:}
spring.jpa.database=postgresql

Additionally, add the database url to application-dev.properties to connect to the database during the testing phase:

spring.datasource.url=jdbc:postgresql://postgres:5432/postgres

Finally, add the following to the Documentation section of metadata.yaml:

documentation:
  consul:
    required:
      - name: DATASOURCE_URL
        value: URL for the postgres datasource
  vault:
    - name: DATASOURCE_USER
      value: User name for postgres database
    - name: DATASOURCE_PASSWORD
      value: password for postgres database

That probably seems like a lot of changes, but they are mostly just adding the database as a dependency and configuring it properly in kustomize and skaffold for the service to connect to it. The more you work with these tools, the more familiar these configurations will become.

At this point, execute a skaffold dev command to verify that your new database code builds and your postgres dependency starts up properly during deployment. If you encounter any issues, review the above steps to ensure all necessary changes were made and that the database is properly configured.

Testing the Database Endpoints

Now we can write some test code for our new database endpoints. These tests should test the controller, service and repository code that was added earlier, and they will utilize our test Postgres database.

First, in the root of your test directory (gov.va.mobile.starter.v1.service), create the database/client package. In there, create an abstract database client and extend it for our starter database to help us maintain our table data while testing:

package gov.va.mobile.starter.v1.service.database.client;

import java.sql.Connection;
import java.sql.SQLException;
import javax.sql.DataSource;

/**
 * Abstract base class for database clients.
 * <p>
 * This class provides a framework for interacting with a database schema, including
 * methods for clearing all entries and executing SQL commands. Subclasses must
 * implement the {@link #clearAll()} and {@link #dataSource()} methods.
 */
public abstract class DatabaseClient {

    /**
     * The database schema to use.
     */
    protected final String schema;

    /**
     * The mode of operation for the client.
     */
    protected final String mode;

    /**
     * The data source for database connections.
     */
    protected DataSource dataSource;

    /**
     * Constructs a new {@code DatabaseClient} with the specified schema and mode.
     */
    DatabaseClient(final String schema, final String mode) {
        this.schema = schema;
        this.mode = mode;
    }

    abstract void clearAll();

    abstract DataSource dataSource();

    /**
     * Executes the specified SQL commands.
     */
    void execute(final String... sqlCommands) {
        final Connection connection;
        try {
            connection = dataSource().getConnection();
        } catch (final SQLException e) {
            throw new RuntimeException("Failed to connect to schema: " + schema, e);
        }
        for (final String command : sqlCommands) {
            try {
                connection.prepareStatement(command).execute();
            } catch (final SQLException e) {
                throw new RuntimeException("Failed to execute command: " + command, e);
            }
        }
    }
}

package gov.va.mobile.starter.v1.service.database.client;

import org.springframework.jdbc.datasource.DriverManagerDataSource;

import javax.sql.DataSource;

/**
 * Client class for interacting with the "STARTER_DB_TABLE" table in the database.
 */
public class StarterDbClient extends DatabaseClient {

    /**
     * Constructs a new {@code StarterDbClient} with the specified schema and mode.
     */
    public StarterDbClient(final String schema, final String mode) {
        super(schema, mode);
    }

    /**
     * Clears all entries from the "STARTER_DB_TABLE" table.
     */
    @Override
    public void clearAll() {
        execute(
                "truncate postgres.starter_service.STARTER_DB_TABLE;"
        );
    }

    /**
     * Configures the data source for database connections.
     *
     * @return the configured {@link DataSource} instance.
     */
    @Override
    protected DataSource dataSource() {
        final DriverManagerDataSource dataSource = new DriverManagerDataSource();
        dataSource.setDriverClassName("org.postgresql.Driver");
        dataSource.setUrl("jdbc:postgresql://localhost:5432/postgres");
        dataSource.setUsername("postgres");
        dataSource.setPassword("password");

        return dataSource;
    }
}

Now we can create our integration test class like we have done previously:

package gov.va.mobile.starter.v1.service;

import gov.va.mobile.security.jwt.test.JwtTestUtils;
import gov.va.mobile.security.jwt.v2.JwtUserV2;
import gov.va.mobile.starter.v1.model.SetDatabaseKeyRequest;
import gov.va.mobile.starter.v1.service.database.client.StarterDbClient;
import org.junit.jupiter.api.BeforeEach;
import org.junit.jupiter.api.Test;
import org.springframework.http.HttpStatus;
import org.springframework.web.client.HttpClientErrorException;
import org.springframework.web.client.RestClient;

import java.util.UUID;

import static org.assertj.core.api.Assertions.assertThat;
import static org.junit.jupiter.api.Assertions.assertThrows;

class DatabaseControllerITCase extends BaseITCase {

    private static final String VALUE = "valueTest";
    private static final StarterDbClient DB_CLIENT = new StarterDbClient("STARTER_SERVICE", "POSTGRES");

    private RestClient restClient;
    private JwtUserV2 user;

    @BeforeEach
    void setUp() {
        user = JwtTestUtils.V2.createStaffUserBuilder().build();
        restClient = buildRestClient(user);
        DB_CLIENT.clearAll();
    }

    @Test
    void testEmptyKey() {
        final SetDatabaseKeyRequest request = new SetDatabaseKeyRequest();
        request.setValue(VALUE);
        final HttpClientErrorException thrown = assertThrows(HttpClientErrorException.class, () -> {
            restSetDatabaseKey(restClient, request);
        });
        assertThat(thrown.getStatusCode()).isEqualTo(HttpStatus.BAD_REQUEST);
        assertThat(thrown.getMessage()).contains("Empty Key");
    }

    @Test
    void testRoundTrip() {
        //TODO: implement
    }

    @Test
    void testSettingKeyTwice() {
        //TODO: implement
    }

    private static String generateRandomKey() {
        return "key-" + UUID.randomUUID();
    }

    private String restGetDatabaseKey(final RestClient restClient, final String key) {
        //TODO: implement
    }

    private void restSetDatabaseKey(final RestClient restClient, final SetDatabaseKeyRequest databaseKeyRequest) {
        //TODO: implement
    }
}

To start, implement the restSetDatabaseKey and restGetDatabaseKey methods to make the appropriate REST calls to your service’s endpoints for setting and getting database keys. The setup for the test uses the existing buildRestClient method from BaseITCase to create a RestClient with the appropriate authentication for your test user. Refer to the UserSessionControllerITCase for examples of how to implement these methods.
The provided test case utilizes the restSetDatabaseKey method, so make sure to implement that one first to get the test compiling and running before implementing the restGetDatabaseKey method.
To finish things up, implement the two stubbed integration tests for a successful round trip to/from the database and for attempting to set the same key twice in the database.