Add A Local Database For Testing

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>2.4.2</version>
    <relativePath/>
  </parent>
  <groupId>gov.va.mobile.lib</groupId>
  <artifactId>starter-service-db</artifactId>
  <version>1.0.0</version>
  <packaging>jar</packaging>
</project>

  • 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.

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=v14.5

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 for postgres:

    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/dev/overrides.env for the datasource URL:

    DATASOURCE_URL=jdbc:postgresql://postgres:5432/postgres

  • 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:

    ...
  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 the port forward for postgres to skaffold.yaml so that you can connect to it:

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

  • Add the following properties to application.properties:

    # Postgres configuration
spring.datasource.url=${DATASOURCE_URL}
spring.datasource.username=${DATASOURCE_USER}
spring.datasource.password=${DATASOURCE_PASSWORD}
spring.jpa.database=postgresql

  • 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

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.

Testing the Database Endpoints

Now we can write some test code for our new database endpoints. This should test the resource, service and repository code that was added earlier, and utilize our test Postgres database.

  • First, in the test directory, 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 javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;

public abstract class DatabaseClient {

  protected final String schema;
  protected final String mode;
  protected DataSource dataSource;

  DatabaseClient(String schema, String mode) {
    this.schema = schema;
    this.mode = mode;
  }

  abstract void clearAll();

  abstract DataSource dataSource();

  void execute(String... sqlCommands) {
    Connection connection;
    try {
      connection = dataSource().getConnection();
    } catch (SQLException e) {
      throw new RuntimeException("Failed to connect to schema: " + schema, e);
    }
    for (String command : sqlCommands) {
      try {
        connection.prepareStatement(command).execute();
      } catch (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;


public class StarterDbClient extends DatabaseClient {

  public StarterDbClient(String schema, String mode) {
    super(schema, mode);
  }

  @Override
  public void clearAll() {
    execute(
            "truncate postgres.starter_service.STARTER_DB_TABLE;"
    );
  }

  @Override
  protected DataSource dataSource() {
    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.resource;

import java.nio.charset.StandardCharsets;
import java.util.Random;
import gov.va.mobile.service.test.JWTTestUtils;
import gov.va.mobile.starter.v1.service.BaseITCase;
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 gov.va.mobile.starter.v1.client.restclient.ApiClient;
import gov.va.mobile.starter.v1.client.restclient.api.DatabaseApi;
import gov.va.mobile.starter.v1.model.SetDatabaseKeyRequest;

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

public class DatabaseResourceITCase extends BaseITCase {

  private static final String value = "valueTest";
  private static final StarterDbClient dbClient = new StarterDbClient("STARTER_SERVICE", "POSTGRES");

  private String generateRandomKey(int size) {
    byte[] array = new byte[size];
    new Random().nextBytes(array);
    return new String(array, StandardCharsets.UTF_8);
  }

  private static DatabaseApi getApi() {
    final ApiClient apiClient = new ApiClient();
    apiClient.setBasePath(SERVICE_URL);
    apiClient.setApiKey(JWTTestUtils.createStaffJWT(STAFF_ICN));
    return new DatabaseApi(apiClient);
  }

  @BeforeEach
  public void cleanTable() {
    dbClient.clearAll();
  }

  @Test
  public void testEmptyKey() {
    final DatabaseApi api = getApi();
    SetDatabaseKeyRequest obj = new SetDatabaseKeyRequest();
    obj.setValue(value);
    HttpClientErrorException thrown = assertThrows(HttpClientErrorException.class, () -> {
      api.setDatabaseKey(obj);
    });
    assertThat(thrown.getStatusCode()).isEqualTo(HttpStatus.BAD_REQUEST);
    assertThat(thrown.getMessage()).contains("Empty Key");
  }

  @Test
  public void testRoundTrip() {
    // TODO: Implement
  }

  @Test
  public void testSettingKeyTwice() {
    // TODO: Implement
  }
}

  • 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.