connecting mysql to your cypress tests

mysql-cypress-connection-mac.md

Verify if you have MYSQL

1- Through your terminal, make sure that you have mysql by running:

which mysql

2- If you got the path means you have mysql and you can verify mysql status by running:

brew services list

You will find mysql as stopped status

3- If you don't have mysql path, then you need to install:

brew install mysql

4- Start mysql if you just install or incase you have stopped status

brew services start mysql

5- Connect to mysql via:

mysql -h localhost -uUSER_NAME -p;

and on password, click enter (without password), however, you can setup a password...

6- Create your db:

create database NAME_OF_YOUR_DB;

7- If you have a DB dump on your local, then import by running from CLI, or simply import from mysqlWorkbench GUI:

mysql -h YOUR_HOST -uYOUR_USERNAME -pYOUR_PASSWORD SCHEMA_NAME < ~/PATH_TO_YOUR_DUMP_FILE/DUMP_NAME.sql

7- Now you have you tables, you can query or work through GUI (MYSQLWorkbench)

Setup cypress to integrate your DB:

1- On your cypress root, run the below command to have node.js Client for MySQL protocol:

npm install mysqljs/mysql

2- In yopur cypress project, you need to add the following in plugins/index.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 = (on, config) => {
  // Usage: cy.task('queryDb', query)
  on("task", {
    queryDb: query => {
      return queryTestDb(query, config);
    }
  });
};

keeping in mind that const connection is loading db from env object

3- Add the follwoing to cypress.json

"db": {
      "host": "127.0.0.1",
      "user": "YOUR_USERNAME",
      "password": "YOUR_PASSWORD"
    }

4- Now, in your spec.js file you can call cy.task as following:

cy.task("queryDb",`SELECT * FROM SCHEMA_NAME.TABLE_NAME WHERE COLUMNS_NAME='VALUE'`);

5- Incase you need to run any assertion:

cy.task(
      "queryDb",
      `SELECT * FROM SCHEMA_NAME.TABLE_NAME WHERE COLUMNS_NAME='VALUE'`
    ).then(count => {
      expect(count).to.have.lengthOf(1);
    });

Hi, could you help me? How can I remove a creation of a data directly from mySQL database for the test not to break in the next run. Would leave this automation in before to always remove before creating again

Hi, if I understand you correctly, you are trying to clear a database record before your next test run.

In this case, you can SQL statement as a before() or beforeEach() hook in your test

e.g.

context("Something something", function () {
  before(function () {
    cy.queryDb(`DELETE FROM table_name WHERE condition;`);
  });

  describe("Something something", function () {
    it("Should do something", function () {
      // test goes here
    });
  });
});

Hi, thanks for the help!
I tried to run as below, but with no success.
I'm doing something wrong.
The connection to the bank is via a local dump

- Below is my cucumber spec code

before(() => {
	cy.queryDb(`DELETE FROM simulator.vehicle_color WHERE code='AE3';`); 
})

Given(/^que acesso a pagina de login com minhas credenciais validas$/, () => {
	cy.login('userGMF')
});

illustration:

mySQL Dump:

error when running:

my plugin/index.js file

I think you are missing something, queryDb command should be added to your commands.js file under the support directory

Add this in your command.js file

Cypress.Commands.add("queryDb", (query) => {
  cy.task("queryDb", query);
});

then in the test file call it under the before

cy.queryDb(`your_data_base_query` )

Perfect ! helped me a lot, I managed to make it work!
The only problem is that when I run the tools in the continuous integration GIT ACTION. It ends up breaking!
I want to thank you for the help! thank you so much!

Hi I am facing this error

cy.task('queryDb') failed with the following error:

The 'task' event has not been registered in the setupNodeEvents method. You must register it before using cy.task()

Fix this in your setupNodeEvents method here:
/Users/samkitjain/Downloads/OfloadProject/cypress.config.jsLearn more

This gist need an update as per the latest release by Cypress.io 10.x.x

for the time being, you need to follow this to fix it properly. Later I will update the Gist. Thanks for raising this

Hi how to connect to a postgresql db instance in cypress using IAM auth configuration

Hi @fityanos ,

did you update the Gist with this approach in setupNodeEvents for Cypress.i 10.x.x ? I'm currently facing this issue and can't solve yet ...
Thanks :)