products

How To Verify Phone Number During Tests Part 2

How Cypress test can access the MySQL database during email verification step.

Let's continue looking at a web application that makes the users sign up using a phone number. We have covered the test phone prefix in the previous blog post How To Verify Phone Number During Tests Part 1. In this blog post, I intend to show how the test runner can directly access the MySQL database to look up the user's record, rather than relying on the application's API to expose it.

🎁 You can find the full source code in the repo bahmutov/verify-code-example.

The application

To install all NPM dependencies to run the application we execute the command

1
$ npm install

The above command also automatically installs the dependencies in the api subfolder via postinstall script in the package.json file

package.json
1
2
3
4
5
{
  "scripts": {
    "postinstall": "npm install --prefix api"
  }
}

The database connection is made from the api folder via mysql module as I will show in the later sections.

The user id

When a new user is added to the table, it gets assigned a user id. Later on, if we want to look up the user's record, we need to know this value in the test. We can see the user ID returned by the API call in the DevTools Network tab below

The Ajax call returns the user ID

Aside: the app is showing this user id as you can see below the input elements, but that's just to make the demo simpler to understand. Usually the item's ID is not exposed by the UI.

We can grab the returned user ID using the cy.intercept command by spying on the API call that creates the user. Here is the test that prints the returned ID to the console.

cypress/integration/user-id.js
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
/// <reference types="cypress" />

// Lodash library is bundled with Cypress
const { _ } = Cypress;

it('returns user id', () => {
  cy.visit('/');

  const username = `test-${_.random(1e4)}`;
  const email = `${username}@example.com`;

  cy.get('[name=username]').type(username);
  cy.get('[name=email]').type(email);
  cy.intercept('/signup.json').as('signup');
  cy.contains('button', 'Sign up').click();
  cy.wait('@signup')
    .its('response.body')
    // the assertion "have.property" yields its value
    .should('have.property', 'userId')
    .then((userId) => {
      console.log('New user id %s', userId);
    });
});

The API returns the created user ID

Now we need to use this ID to look up the user information in the database.

MySQL database connection

The Cypress test runs in the browser, but we want to connect to the MySQL database, which is only possible from outside the browser. Luckily, Cypress includes the plugin file that runs in its own Node process on the host machine. The plugin process can connect to the database, and communicate the results back to the browser when called using the cy.task command.

Here is our cypress/plugins/index.js file. It will read the database connection information from the process environment variables that Node applications automatically have in the process.env object, see Node API documentation.

Tip: read Working with Environment Variables in Node.js to learn how to work with the environment variables.

cypress/plugins/index.js
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
// @ts-check
/// <reference types="cypress" />
// mysql dependency is installed in the API subfolder
// thus Cypress plugin file has to reach into that folder
const mysql = require('../../api/node_modules/mysql');

// this function runs in Node at the start of the project
module.exports = function (on, config) {
  // read the environment variables defined on the host machine
  // Node.js allows one to look up all variables defined in the environment
  // and then access them using process.env.<VARIABLE_NAME>
  // https://nodejs.org/api/process.html#process_process_env

  // in our system we assume that the database is running
  // at the host URL "DB_HOST", and we can connect to it using
  // "DB_USER", "DB_PASSWORD" environment values
  // The database name comes from the "DB_NAME" variable.
  const host = process.env.DB_HOST;
  const user = process.env.DB_USER;
  const database = process.env.DB_NAME;
  const password = process.env.DB_PASSWORD;
  // if the config variables are not set
  // throw an error and stop the application
  if (!host || !user || !database || !password) {
    throw new Error('DB variables are not set');
  }

  on('task', {
    async getUser(id) {
      const connection = mysql.createConnection({
        host,
        user,
        password,
        database
      });

      connection.connect();

      const theUser = await new Promise((resolve, reject) => {
        connection.query(
          {
            sql: 'SELECT * FROM users WHERE user_id = ?',
            values: [id]
          },
          function (error, results, fields) {
            if (error) {
              console.error(error);
              return reject(error);
            }

            if (!results.length) {
              console.error('Could not find user with username %s', id);
              return reject(new Error(`Unknown user ${id}`));
            }

            console.log(results);
            // return all fields except for ID
            // also convert the isPhoneVerified to boolean
            resolve({
              ...results[0],
              isPhoneVerified: results[0].isPhoneVerified === 1,
              user_id: undefined
            });
          }
        );
      });

      connection.end();

      return theUser;
    }
  });
};

It looks so much like the regular Node code - because it is. The spec can now call cy.task('getUser', ...) to fetch the user information. We can print the yielded object in the console.

Getting the user record from the database via cy.task

Note: the mysql NPM module is used and installed by the package.json file inside the api folder. Thus our Cypress plugin Node file requires it using the relative path require('../../api/node_modules/mysql').

Gotcha: ES6 modules

By default, the web project uses ES6 modules by setting type: module in its package.json. If we simply try to load the Cypress plugin file, it fails:

Cypress fails to load its own plugin file

To solve this, I added a dummy package.json to the cypress subfolder. This file points back at the CommonJS resolution when Cypress loads its own files.

cypress/package.json
1
2
3
4
5
{
  "name": "cypress-tests",
  "private": true,
  "type": "commonjs"
}

Gotcha: Native database driver code

Sometimes when loading a database module, Cypress might need to load the native code. Since you might install dependencies using one version of Node (let's say Node 10), while Cypress comes with its own built-in Node used by default to load the plugin file, the native code install is incompatible. You can check the Node version bundled with Cypress using the cypress version command:

1
2
3
4
5
$ npx cypress version
Cypress package version: 8.3.1
Cypress binary version: 8.3.1
Electron version: 13.2.0
Bundled Node version: 14.16.0

Here is the tip if the native database driver does not load: tell Cypress to use the system Node version by using the cypress.json file.

cypress.json
1
2
3
{
  "nodeVersion": "system"
}

Re-using the API code

The above plugin file code looks very much like the API code used to access the database. I suggest re-using part of the production code to create the database connection from the plugin file.

cypress/plugins/index.js
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
// @ts-check
/// <reference types="cypress" />
const { makeConnection } = require('../../api/src/db.config');

module.exports = function (on, config) {
  on('task', {
    async getUser(id) {
      const connection = makeConnection();

      const theUser = await new Promise((resolve, reject) => {
        connection.query(
          {
            sql: 'SELECT * FROM users WHERE user_id = ?',
            values: [id]
          },
          function (error, results, fields) {
            if (error) {
              console.error(error);
              return reject(error);
            }

            if (!results.length) {
              console.error('Could not find user with username %s', id);
              return reject(new Error(`Unknown user ${id}`));
            }

            console.log(results);
            // return all fields except for ID
            // also convert the isPhoneVerified to boolean
            resolve({
              ...results[0],
              isPhoneVerified: results[0].isPhoneVerified === 1,
              user_id: undefined
            });
          }
        );
      });

      connection.end();

      return theUser;
    }
  });
};

Even better is to move the SQL query into the production code - it might be useful there, and just call the API code from the plugin file. Let's put the DB access into the api/src/db.user.js next to the other database queries.

api/src/db.user.js
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
// @ts-check
const { makeConnection } = require('./db.config');

module.exports = async function getUser(id) {
  const connection = makeConnection();

  const theUser = await new Promise((resolve, reject) => {
    connection.query(
      {
        sql: 'SELECT * FROM users WHERE user_id = ?',
        values: [id]
      },
      function (error, results) {
        if (error) {
          console.error(error);
          return reject(error);
        }

        if (!results.length) {
          console.error('Could not find user with username %s', id);
          return reject(new Error(`Unknown user ${id}`));
        }

        console.log(results);
        // return all fields except for ID
        // also convert the isPhoneVerified to boolean
        resolve({
          ...results[0],
          isPhoneVerified: results[0].isPhoneVerified === 1,
          user_id: undefined
        });
      }
    );
  });

  connection.end();

  return theUser;
};

From the Cypress plugin file, simply call the exported asynchronous function.

cypress/plugins/index.js
1
2
3
4
5
6
7
8
9
10
11
12
// @ts-check
/// <reference types="cypress" />
const getDbUser = require('../../api/src/db.user');

module.exports = function (on, config) {
  on('task', {
    async getUser(id) {
      const user = await getDbUser(id);
      return user;
    }
  });
};

Beautiful.

Looking up the phone verification code

Finally, if we can look up the user info via cy.task + DB query, we do not need to use much of the special test phone code we wrote before. We can still prevent the SMS send for the phones that start with the test prefix. But now we can use randomly generated codes for such numbers too!

1
2
3
4
5
6
7
8
let sendSms = true;
if (
  process.env.TEST_PHONE_NUMBER_PREFIX &&
  phoneNumber.startsWith(process.env.TEST_PHONE_NUMBER_PREFIX)
) {
  sendSms = false;
}
const phoneConfirmationCode = String(Math.random()).slice(2, 6);

After creating the user, we can look the code by querying the MySQL database using the task. Here is the complete test.

cypress/integration/query-db.js
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
/// <reference types="cypress" />

import { getTestPhoneNumber } from './utils';

// Lodash library is bundled with Cypress
const { _ } = Cypress;

it('queries code from DB', () => {
  cy.visit('/');

  const username = `test-${_.random(1e4)}`;
  const email = `${username}@example.com`;
  // we will find the user id later
  let userId;

  cy.get('[name=username]').type(username);
  cy.get('[name=email]').type(email);
  cy.intercept('/signup.json').as('signup');
  cy.contains('button', 'Sign up').click();
  cy.wait('@signup')
    .its('response.body')
    // the assertion "have.property" yields its value
    .should('have.property', 'userId')
    .then((id) => {
      userId = id;
    });

  const phoneNumber = getTestPhoneNumber();
  cy.get('[name=phone]').type(`${phoneNumber}{enter}`, { delay: 75 });
  cy.get('[name=code]')
    .should('be.visible')
    .then(() => {
      // now we can query the database to find out the confirmation code
      // need to use .then closure to make sure the userId is defined
      cy.task('getUser', userId)
        .should('have.property', 'phoneConfirmationCode')
        .then((code) => {
          cy.log(`code: **${code}**`);
          cy.get('[name=code]').type(code + '{enter}', { delay: 75 });
        });
    });

  // at the end of all the previous commands
  // the phone number should have been verified
  cy.get('[data-cy=PhoneVerified]').should('be.visible');
});

Verified the user by looking up the code from the database

Nice!

Final thoughts

I think the best practices for accessing the database from Cypress tests are:

  • try avoiding the code duplication by re-using as much of the existing production code as possible
  • limit the number of shortcuts taken via DB lookups or modifications; use them to bypass 3rd party services that cannot be automated otherwise (like receiving an SMS message).