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

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