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

mysql package installed in package json
 
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)
      }
    })
  })
}

connecting to database in cypress using mysql plugin
 
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
}

using cypress task plugin event run 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"
    }
  }

cypress json with database details
 
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("")
        })
    })
})

cypress sql database testing test script

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’.

cypress database test execution report
 
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)
            })
    })

result object for database query in cypress

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/