Skip to content

Instantly share code, notes, and snippets.

@fityanos
Last active February 5, 2024 20:53
Show Gist options
  • Save fityanos/0a345e9e9de498b6c629f78e6b2835f5 to your computer and use it in GitHub Desktop.
Save fityanos/0a345e9e9de498b6c629f78e6b2835f5 to your computer and use it in GitHub Desktop.
connecting mysql to your cypress tests

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);
    });
@YagoIzidio
Copy link

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

@fityanos
Copy link
Author

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

@YagoIzidio
Copy link

YagoIzidio commented Dec 16, 2021

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:

image

mySQL Dump:

image

error when running:

image

my plugin/index.js file

image

image

@fityanos
Copy link
Author

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` )

@YagoIzidio
Copy link

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!

image

@rakeshcdndeveloper
Copy link

rakeshcdndeveloper commented Jun 27, 2022

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

@fityanos
Copy link
Author

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

@Karthikvt143
Copy link

Karthikvt143 commented Jul 7, 2022

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

@anamp-collab
Copy link

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 :)

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment