Postgres DB

In this example, we would see how to query a Postgres database table. Let’s create a postgres database called testdb. Let’s also create a table otp_details and populate it with few rows.

testdb=# SELECT otp FROM otp_details WHERE mobileNumber='9898989898';
  otp
--------
 321123
(1 row)

Provide database connection details#

Open resources/db/drivers.yml file

It has below template:

<KEY>:
  host: "<HOST>"
  port: "<PORT>"
  username: "${env:DB_USERNAME}"
  password: "${env:DB_PASSWORD}"

Now fill the template with connection details. Please note that Ekam would refer to the <KEY> for reading the connection details.

authentication:
  host: "localhost"
  port: "5432"
  username: "postgres"
  password: ""

Create query#

Create an interface with name Queries under db/clients and declare select query as shown

package ekam.example.db.clients;

import org.skife.jdbi.v2.sqlobject.Bind;
import org.skife.jdbi.v2.sqlobject.SqlQuery;

public interface Queries {

    @SqlQuery("SELECT otp FROM otp_details WHERE mobileNumber = :mobileNumber")
    String getOtp(@Bind("mobileNumber") String mobileNumber);
}

Create a Database Client#

Let us create a database client class that calls the query and returns otp for a given mobile number.

package ekam.example.db.clients;

import com.testvagrant.ekam.db.clients.PostgresDBClient;

public class AuthenticationDBClient extends PostgresDBClient {

    private final Queries queries;

    protected AuthenticationDBClient() {
        super("testdb", "authentication");
        queries = load(Queries.class);
    }

    public String getOTP(String mobileNumber) {
        return queries.getOtp(mobileNumber);
    }
}

Add & execute test#

1. Inject the AuthenticationDBClient
2. Use the injected instance to query

package ekam.example.db.clients;

import com.google.inject.Inject;
import com.testvagrant.ekam.testBases.testng.APITest;
import org.testng.Assert;
import org.testng.annotations.Test;

public class DatabaseQueryTest extends APITest {

    @Inject
    AuthenticationDBClient client;

    @Test(groups = "db")
    public void shouldGetOtp() {

        String otp = client.getOTP("9898989898");
        System.out.println(otp);
        Assert.assertNotNull(otp);

    }
}

Execute the test from IDE

How to avoid plain text of user credentials on the drivers.yml file?#

It is not a good practice to specify clear text passwords in any of the source files and configurations. Pass the username & password as a command-line argument.

Use this syntax "${env: <arg name>}" for the required fields in the drivers.yml file, provided an example below:

authentication:
  host: "localhost"
  port: "5432"
  username: "${env:DB_USERNAME}"
  password: "${env:DB_PASSWORD}"

Pass these as system properties to Gradle task:

    systemProperties = [
            config: System.getProperty("config"),
            DB_USERNAME: System.getProperty("DB_USERNAME"),
            DB_PASSWORD: System.getProperty("DB_PASSWORD")
    ] 

Complete Gradle Task:

task runDbTests(type: Test) {
    systemProperties = [
            config: System.getProperty("config"),
            DB_USERNAME: System.getProperty("DB_USERNAME"),
            DB_PASSWORD: System.getProperty("DB_PASSWORD")
    ]

    filter {
        excludeTestsMatching "*.mobile.*"
        excludeTestsMatching "*.api.*"
        excludeTestsMatching "*.web.*"
    }
    outputs.upToDateWhen { false }
    useTestNG {
        parallel = "methods"
        threadCount Integer.parseInt(System.getProperty("sessions", "2"))
        includeGroups System.getProperty("tags", "db")
        testLogging.showStandardStreams = true
        useDefaultListeners true
        outputDirectory = file("$buildDir/" + System.getProperty('tags', 'NONE'))
    }
}

Execute tests via gradle task#

./gradlew runDbTests -DDB_USERNAME=<username> -DDB_PASSWORD=<password>