In this article, we will explore in detail how we can perform common database actions on any SQL database using cypress.
Step 1: Install the mysql plugin. Once it is installed it should reflect under your package.json.
1 | npm i mysql -D |
Step 2: We will be using the mysql plugin to connect to our database. Go to cypress/plugins/index.js and write:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | //For connecting to SQL Server 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() return resolve(results) } }) }) } |
Step 3: Now, we will be using cy.task() to enable cypress to run SQL queries. For this again go to cypress/plugins/index.js and write:
1 2 3 | module.exports = (on, config) => { on('task', { queryDb: query => { return queryTestDb(query, config) }, }); //For running sql query } |
Step 4: Go to cypress.json and write the following. These details would be used to connect to our db.
1 2 3 4 5 6 7 8 | "env": { "db": { "host": "db4free.net", "user": "admin", "password": "password", "database": "db_name" } } |
Step 5: Now lets write our test to perform some database actions.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 | describe('Example to Demonstrate SQL Database Testing in Cypress', () => { it('Create a Table', function () { cy.task('queryDb', "CREATE TABLE Persons (PersonID int, FirstName varchar(255), Address varchar(255), City varchar(255))") }) it('Input Entries into the table', function () { cy.task('queryDb', `INSERT INTO Persons (PersonID, FirstName, Address, City) VALUES (001, "John", "House No. 01", "Helsinki"), (002, "Pam", "House No. 02", "Espoo"), (003, "Dwight", "House No. 03", "Lapland"), (004, "Michael", "House No. 04", "Vantaa");`).then((result) => { expect(result.affectedRows).to.equal(4) }) }) it('Update an Entry into the table and verify', function () { cy.task('queryDb', `UPDATE Persons SET FirstName = "Kevin" WHERE City="Vantaa"`).then((result) => { expect(result.changedRows).to.equal(1) }) cy.task('queryDb', `SELECT FirstName FROM Persons WHERE City="Vantaa"`).then((result) => { expect(result[0].FirstName).to.equal('Kevin') }) }) it('Verify that there is only one row where the city is Espoo', function () { cy.task('queryDb', `SELECT COUNT(*) as "rowCount" FROM Persons WHERE City="Espoo"`).then((result) => { expect(result[0].rowCount).to.equal(1) }) }) it('Delete a Table and Verify', function () { cy.task('queryDb', `DROP TABLE Persons`).then((result) => { expect(result.message).to.equal("") }) }) }) |
1 2 3 | it('Create a Table', function () { cy.task('queryDb', "CREATE TABLE Persons (PersonID int, FirstName varchar(255), Address varchar(255), City varchar(255))") }) |
Here we are using the CREATE TABLE SQL statement to create a ‘Persons’ table with the following fields – PersonID, FirstName, Address, City
1 2 3 4 5 6 7 8 9 | it('Input Entries into the table', function () { cy.task('queryDb', `INSERT INTO Persons (PersonID, FirstName, Address, City) VALUES (001, "John", "House No. 01", "Helsinki"), (002, "Pam", "House No. 02", "Espoo"), (003, "Dwight", "House No. 03", "Lapland"), (004, "Michael", "House No. 04", "Vantaa");`).then((result) => { expect(result.affectedRows).to.equal(4) }) }) |
Here we are using the INSERT INTO SQL statement to insert new records in the ‘Persons’ table. And after that, we are asserting that ‘result.affectedRows’ is equal to 4.
1 2 3 4 5 6 7 8 | it('Update an Entry into the table and verify', function () { cy.task('queryDb', `UPDATE Persons SET FirstName = "Kevin" WHERE City="Vantaa"`).then((result) => { expect(result.changedRows).to.equal(1) }) cy.task('queryDb', `SELECT FirstName FROM Persons WHERE City="Vantaa"`).then((result) => { expect(result[0].FirstName).to.equal('Kevin') }) }) |
Here we are using the UPDATE SQL statement to update an entry into the table. Then we are validating ‘result.changedRows’ to be equal to 1. Then using the SELECT SQL statement we are retrieving the changed value and validating it using ‘result[0].FirstName’.
1 2 3 4 5 6 | it('Verify that there is only one row where the city is Espoo', function () { cy.task('queryDb', `SELECT COUNT(*) as "rowCount" FROM Persons WHERE City="Espoo"`).then((result) => { expect(result[0].rowCount).to.equal(1) }) }) |
Here we are retrieving the row count using the SELECT COUNT(*) SQL statement. And after that, we are asserting that ‘result[0].rowCount’ is equal to 1.
1 2 3 4 5 | it('Delete a Table and Verify', function () { cy.task('queryDb', `DROP TABLE Persons`).then((result) => { expect(result.message).to.equal("") }) }) |
Here we are using the DROP SQL statement to delete the table completely and validating that ‘result.message’ is empty.
Step 6: Finally after execution, we will get a ‘PASS’.
Note: For assertions, I first printed the result object after the query execution and then applied the assertion based on the output. For example, let’s execute the statement for inserting new records into the table and print the result object.
1 2 3 4 5 6 7 8 9 10 | it('Input Entries into the table', function () { cy.task('queryDb', `INSERT INTO Persons (PersonID, FirstName, Address, City) VALUES (001, "John", "House No. 01", "Helsinki"), (002, "Pam", "House No. 02", "Espoo"), (003, "Dwight", "House No. 03", "Lapland"), (004, "Michael", "House No. 04", "Vantaa");`).then((result) => { cy.log(JSON.stringify(result)) //expect(result.affectedRows).to.equal(4) }) }) |
Now, we can choose any key-value pair(s) for assertion. In my case, I chose the key affectedRows and applied the assertion using expect(result.affectedRows).to.equal(4).
Do check out 🙂
Github:Â https://github.com/alapanme/Cypress-Automation
All Cypress Articles: https://testersdock.com/cypress-tutorial/
It a a great documentation. Helped me a lot.
Thank You for your kind words 🙂
I am getting an auth error even though I am using the same version of mysql as you suggested in your code. Has the DB been changed to require a higher version of mysql since you wrote this article?
“ER_NOT_SUPPORTED_AUTH_MODE: Client does not support authentication protocol requested by server; consider upgrading MySQL client”
Hi,
Thanks for the documentation.
You have mentioned in the beginning it is for any database connection and hope it works for MS SQL Server connection as well.
I followed your steps and fails with this…
cy.task(‘queryDb’) failed with the following error:
> getaddrinfo ENOTFOUND XYZ.tc.com,1470
Any idea on this please?
Thanks
When I use the same code then the ‘result’ object is giving me an error so where do we need to declare it?
Hi,
Does any body help me with cypress script for cosmos db testing
Hello, Great tutorial i just don’t know how to set it up in cypress 10 and newer as there are no longer plugin/index files
Hello, amazing tutorial, helps out a lot. I need to know how to setup the same with Cypress 10, it does not have plugins/index and cypress.json files.
Please help out.