How to connect with Mysql db using Cypress 12?

Question

I want to connect to Mysql DB in the server to delete several records. I have tried using some of the tutorials and answers available. Since all of them are regarding older versions of Cypress, I can't figure out what is the mistake. Here is the code. I'm happy to know if there is any easy method with less code or easy configurations. Thanks

cypress.config.js

const mysql = require("mysql")

function queryTestDb(query, config) {
  // creates a new mysql connection using credentials from cypress.json env's
  const connection = mysql.createConnection(config.env.db);
  // start connection to db
  connection.connect();
  // exec query + disconnect to db as a Promise
  return new Promise((resolve, reject) => {
    connection.query(query, (error, results) => {
      if (error) reject(error);
      else {
        connection.end();
        // console.log(results)
        return resolve(results);
      }
    });
  });
}

module.exports = defineConfig({

    e2e: {
    
        db: {
          host: "10.100.111.25",
          user: "xxxx",
          password: "xxxxx",
          database: "testDB"
      },
      async setupNodeEvents(on, config) {
      // implement node event listeners here

        const bundler = createBundler({
          plugins: [createEsbuildPlugin(config)],
        });

        on("file:preprocessor", bundler);
        await addCucumberPreprocessorPlugin(on, config);
      
        on("task", {
          queryDb: query => {
            return queryTestDb(query, config);
          }
        });

        return config;
      
      },
    }
})          

Test.spec.js

cy.task("queryDb","'select * from data_table where CREATEDDATE like '2022-12-23'")
        .then(count => {
            expect(count);
            cy.log('DB records count is => '+ count);
          });

Package.json

"devDependencies": {
    "@badeball/cypress-cucumber-preprocessor": "^15.1.2",
    "@bahmutov/cypress-esbuild-preprocessor": "^2.1.5",
    "cypress": "^12.4.0",
    "cypress-cucumber-preprocessor": "^4.3.1",
    "cypress-file-upload": "^5.0.8",
    "cypress-mochawesome-reporter": "^3.2.3",
    "cypress-multi-reporters": "^1.6.2",
    "cypress-xpath": "^2.0.1",
    "mocha": "^10.2.0",
    "mochawesome": "^7.1.3",
    "mochawesome-merge": "^4.2.2",
    "mysql": "github:mysqljs/mysql"
  },
  "dependencies": {
    "cypress-email-results": "^1.8.0",
    "cypress-mysql": "^1.0.0",
    "dayjs": "^1.11.5"
  },

Answer 1

Since I had to spend a lot of time to figure out a solutions I thought of answering myself just in case if someone got the same problem.

1. First of all I had to reinstall mysql2. If you have installed Mysql uninstall it first

npm i mysql2 -D

2. Configure cypress.config.js
   const mysql = require("mysql2")

Add below function in the same file

  // creates a new mysql connection using credentials from cypress.json env's
  const connection = mysql.createConnection(config.env.db);
  // start connection to db
  connection.connect();
  // exec query + disconnect to db as a Promise
  return new Promise((resolve, reject) => {
    connection.query(query, (error, results) => {
      if (error) reject(error);
      else {
        connection.end();
        // console.log(results)
        return resolve(results);
      }
    });
  });
}

module.exports = defineConfig({

    "env":{
      "db": {
        "host": "11.11.11.11",
        "user": "username",
        "password": "password",
        "database": "databaseName",
        "port": 08,
        //"key": "cypress/pemFiles/testPemFile.pem",
        //"http": true
      },
    },

    async setupNodeEvents(on, config) {
        on("task", {
            queryDb: query => {
               return queryTestDb(query, config)
            }
        });
    }

});      

3. Now inside youtest.spec.js
   I have assigned result object into a var record and read zeroth row if the object and print to the log.

cy.task('queryDb',"select * from tableName where columnname = '2050-11-29'")
        .then(function(returnObject) {
            //expect(result);
            var record = returnObject;
            const results = Object.values(record[0])
            cy.log('DB records count is => '+ results);
}); 

Also you can get column value by adding its index number as result[0]
cy.log('DB records count is => '+ results[0]);