Database Testing with Cypress (Part 8)

Hola,

In this Cypress framework, we have made certain useful additions that cypress.io doesn't provide by default. In this article, we will be looking at how we can leverage a connection to an SQL database and use this to validate values stored inside a database table.

Install pg

pg is a non-blocking PostgreSQL client for Node.js. Pure JavaScript and optional native libpq bindings. We will use this as a connector for our JavaScript code to the SQL database. We start by executing the following command

npm install --save-dev pg

Sample command line code for installing pg library

pg should get reflected in package.json file at the end of devDependencies.

after installing pg

Connecting to an SQL database

In order to connect to a database, we will need the following connection information for the DB. We will add this information towards the end in cypress.json file inside "env" object.

...  
"env":{
  "DB": {
      "user": "myuser",
      "host": "127.0.0.1",
      "database": "pokemonDB",
      "password": "pass",
      "port": 32763
  }
}
}

DB connection details inside cypress.json file

In this article, we are using a simple "pokemonDB" DB and will make our actions on a "pokemon" table that has the following information related to pokemon creatures:

pokemon Table data

We will start by adding a new Cypress Task called "DATABASE". For this, we will add a new task in the cypress/plugins/index.js file. This task accepts two main things:

i) dbConfig - (this the DB connection info retrieved from cypress.json file using Cypress.env()
ii) sql - (this is the SQL command which we need to execute)

/// <reference types="cypress" />
// ***********************************************************
// This example plugins/index.js can be used to load plugins
//
// You can change the location of this file or turn off loading
// the plugins file with the 'pluginsFile' configuration option.
//
// You can read more here:
// https://on.cypress.io/plugins-guide
// ***********************************************************

// This function is called when a project is opened or re-opened (e.g. due to
// the project's config changing)

/**
 * @type {Cypress.PluginConfig}
 */

 const fs = require('fs-extra');
 const pg = require("pg");
 const path = require('path');
 const cucumber = require('cypress-cucumber-preprocessor').default;

module.exports = (on, config) => {
  on('file:preprocessor', cucumber());
  // `on` is used to hook into various events Cypress emits
  // `config` is the resolved Cypress config

  
 //Connects to an env db and fetches query result
 on("task", {
  DATABASE ({ dbConfig, sql, values }) {
    // const pool = new pg.Pool(config.db);
    const pool = new pg.Pool(dbConfig);
    try {
        return pool.query(sql, values)
    } catch (e) {
    }
  }
});
    
  function getConfigurationByFile(env) {
      const pathToConfigFile = path.resolve("cypress/config", `${env}.config.json`);

      return fs.readJson(pathToConfigFile);
  }
  //if no environment is provided, then QA env will be default
  const env = config.env.configFile || "qa";  

  return getConfigurationByFile(env);
};

cypress/plugins/index.js after adding a new Task called DATABASE

Now in order to execute a simple "SELECT * FROM pokemon" SQL query, we will create a sample feature file called DB_test1.feature

@DB
Feature: Test DB connection
    @smoke @test
    Scenario: Pokemon Table SQL query executions
        Given I execute select all query on pokemon DB
        When I execute selet query on pokemon DB, where name equals "pikachu"

DB_test1.feature file

and the respective JavaScript code will become like the following. Here we provide the SQL query part under "sql" argument

Given('I execute selet all query on pokemon DB',() => {
    cy.task("DATABASE", {
      dbConfig: Cypress.env("DB"),
      sql: `
      select * from pokemon    
      `
    }).then((result) => {
      console.log(result.rows)
    });
  });

dbSteps.js file

Then on executing this test our code will yield all records of the table and printing them on the console.

Using Assertions

In order to make assertions on the values retrieved from DB, it will have to be done inside then block where we get the "result" object:

When('I execute selet query on pokemon DB, where name equals {string}',(pokemonName) => {
    cy.task("DATABASE", {
      dbConfig: Cypress.env("DB"),
      sql: `
      select * from pokemon where Poke_Name = '${pokemonName}'   
      `
    }).then((result) => {
      console.log(result.rows[0]);
        expect(result.rows[0].Poke_Name).to.have.string(`${pokemonName}`);
    });
  });

TestStep Code to make ASSERTION on DB query result inside dbSteps.js

Github Code:
https://github.com/far11ven/Cypress-TestFramework/tree/develop/Part 08