-1

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"
  },

1 Answer 1

0

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

  1. 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)
            }
        });
    }

});      
  1. 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]);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.