Can't connect to MySQL database when testing in Cypress (mysql2)

Question

I get an error when running the integration tests:

0 passing (17s)
   1 failure

   1) Registration page
        register new users allowed and update status in the database:
      TypeError: Net.connect is not a function
       at new Connection (webpack:///./node_modules/mysql2/lib/connection.js:50:0)
       at ./node_modules/mysql2/index.js.exports.createConnection (webpack:///./node_modules/mysql2/index.js:10:0)
       at Context.eval (webpack:///./cypress/integration/registration.spec.js:23:34)

Here is my environment:

MySQL Workbench
MySQL Server 8.0.29

I raised local backendless, I have access to the database. Here is my code:

const mysql2 = require('mysql2');

describe('Registration page', () => {
     beforeEach(() => {
         // visit the registration page
         cy.visit('http://localhost:3000/registration');
     });

     it('register new users allowed and update status in the database', () => {
         // fill out the registration form
         cy.get('input[name="fullName"]').type("Nazar Dmytryshyn")
         cy.get('input[type="email"]').type('testuser@example.com');
         cy.get('input[name="pwd"]').type('testpassword');
         cy.get('input[name="confirmPassword"]').type('testpassword');

         // submit the form
         cy.get('button[class="btn btn-success"]').click();

         // check that the user is redirected to the login page
         cy.url().should('include', '/login');

         // create a connection to the test database
         const connection = mysql2.createConnection({
             host: '127.0.0.1:3306',
             user: 'root',
             password: 'rootpassword',
             database: 'local1'
         });

         // open the connection
         connection.connect();

         // update the developer status in the database
         connection.query(
             'UPDATE `main_backendless`.`Developer` SET `developerStatusId` = "1" WHERE (`email` = "testuser@example.com")',
             (error, results) => {
                 if (error) throw error;
                 expect(results.affectedRows).to.equal(1);
             }
         );

         // close the connection
         connection.end();
     });
});

I checked this data 10 times, it is correct and I can connect to the database through MySQL WorkBench

host: '127.0.0.1:3306',
             user: 'root',
             password: 'rootpassword',
             database: 'main_backendless'

I will be grateful for any ideas that can be achieved!

Answer 1

If you want to use a task to call the mySql library, you must return a Promise from the task.

This is because the mysql calls are asynchronous, and the only way Cypress knows to wait for them is to get a promise returned from your code.

cypress.config.js

const { defineConfig } = require("cypress")
const mysql2 = require('mysql2')

const connection = mysql2.createConnection({
  host: '127.0.0.1:3306',
  user: 'root',
  password: 'rootpassword',
  database: 'local1'
})

module.exports = defineConfig({
  e2e: {
    setupNodeEvents(on, config) {
      on('task', {
        mySql: (sql) => {
          return new Promise((resolve, reject) => { 
            connection.query(sql, (error, results) => {
              if (error) {
                reject(error)
              } else {
                resolve(results.affectedRows)
              })
            })
          })
        }
      })
    },
})

it('tests with mysql', () => {
  cy.task('mySql', 'sql staement here')
    .then(result => {
      expect(result).to.equal(1);
    })
})

---

With Promise-wrapper

Alternatively, mysql2 provides a promise-wrapper that can simplify your code:

const { defineConfig } = require("cypress")
const mysql = require('mysql2/promise')          // different import here

const connection = mysql2.createConnection({
  host: '127.0.0.1:3306',
  user: 'root',
  password: 'rootpassword',
  database: 'local1'
})

module.exports = defineConfig({
  e2e: {
    setupNodeEvents(on, config) {
      on('task', {
        mySql: async (sql) => {                         // async here
          const result = await connection.execute(sql)  // await here
          return result;
        }
      })
    },
})

Answer 2

I recommend using the cypress-mysql, which hides a lot of the implementation details for you.

If you try to roll your own task, you may end up with an undefined return value.

Install

npm install cypress-mysql

//or

yarn add cypress-mysql 

Configure

The release notes are out of date, here is the configuration for Cypress 10+

// cypress.config.js

const { defineConfig } = require("cypress");
const mysql = require('cypress-mysql');

module.exports = defineConfig({
  // ...
  e2e: {
    setupNodeEvents(on, config) {
      mysql.configurePlugin(on);
    },
    "env": {
      "db": {
        "host": "localhost",
        "user": "user",
        "password": "password",
        "database": "database"
      }
    }
})

// cypress/support/e2e.js

const mysql = require('cypress-mysql');
mysql.addCommands();

Test

const sql = 'UPDATE "main_backendless.Developer" SET "developerStatusId" = "1" WHERE ("email" = "testuser@example.com")'

cy.query(sql).then(res => {
  expect(res.affectedRows).to.equal(1) 
});

Answer 3

The issue is that you're using a nodejs library with Cypress. Cypress tests execute inside a browser and cannot directly utilize nodejs libraries within a test.

To do what you want to need to create a cy.task() to be able to execute code in nodejs.

Assuming you're using js, create a file with a function to use the sql connector

// runSql.js
const mysql2 = require('mysql2');

function runSql(sql) {
  const connection = mysql2.createConnection({
      host: '127.0.0.1:3306',
      user: 'root',
      password: 'rootpassword',
      database: 'local1'
  });

  connection.connect();

  let rows;

  connection.query(sql, (error, results) => {
    if (error) throw error;
    rows = results.affectedRows
  });

  connection.end();

  return rows;
}

module.exports = runSql;

Then in the cypress.config.js file

const runSql = require('./runSql.js');

module.exports = defineConfig({
  // ...
  e2e: {
    setupNodeEvents(on, config) {
      on('task', {
        runSql
      });
    },
});

Now to call the task in a test

describe('Registration page', () => {
  it('test', () => {

    const sql = 'UPDATE `main_backendless`.`Developer` SET `developerStatusId` = "1" WHERE (`email` = "testuser@example.com")';

    cy.task('runSql', sql).then((rows) => {
      expect(rows).to.equal(1);
    });
  });
});