Cypress Database Testing (with Best Practices)

Cypress is an end-to-end testing framework that is primarily used for testing web applications. However, it is also possible to use Cypress for database testing. Database testing is an important aspect of testing web applications as it ensures that the data stored in the database is accurate and consistent.

Can Cypress be used for Database Testing?

• Yes. Using Cypress for database testing can provide developers with fast and reliable tests for their database operations. 
• In this process, developers can set up a test database, connect to the database, write tests to interact with the database and run the tests using the Cypress test runner. 
• With Cypress database testing, developers can ensure that their application and database perform well under real-world conditions and that their data is accurate and consistent.

Setting up Cypress for Database Testing

Installing Cypress

To install Cypress for database testing, follow these steps:

Step 1: Install the mysql plugin. Once it is installed, it should reflect under your package.json.

Step 2: To connect to a database, we will need the following connection information for the DB. We will add this information towards the end in the cypress.json file inside the “env” object.

"env":{
"DB": {
"user": "myuser",
"host": "127.0.0.1",
"database": "testDB",
"password": "pass",
"port": 32763
}
}

Start by adding a new Cypress Task called “DATABASE”. For this, we will add a  new task in the cypress/plugins/index.js file. This task accepts two main things:

• dbConfig – (this the DB connection info retrieved from cypress.json file using Cypress.env()
• sql – (this is the SQL command which we need to execute)

Setting up a Test Database

To set up a test database using Cypress, you can follow these general steps:

1. Install a local MySQL server: To create a test database, you need to install a local MySQL server on your machine. You can download MySQL from the official website.

2. Create a new database: Once you have installed MySQL, you can create a new database that will be used for testing. You can do this using the MySQL command line interface or GUI. 

For example, to create a new database called “testdb”, you can run the following command in the MySQL command line interface:

CREATE DATABASE testdb;

3. Configure your application: You need to configure your application to use the test database instead of the production database. You can do this in the application code or through environment variables. 

For example, you can set the DATABASE_URL environment variable to the connection string for the test database:

export DATABASE_URL=mysql://testuser:testpass@localhost/testdb

4. Connect to the test database: In your Cypress tests, you need to connect to the test database using the mysql2 package. Depending on your needs, you can create a connection pool or a single connection.

For example, you can create a connection pool in a db.js file:

const mysql = require('mysql2/promise');
const pool = mysql.createPool(process.env.DATABASE_URL);
module.exports = pool;

5. Write your tests: Once connected to the test database, you can write your tests. Your tests can include inserting data into the database, updating existing data, deleting data, or running queries to retrieve data from the database. 

For example, you can write a test that inserts a new user into the database

const pool = require('./db');
describe('User management', () => {
it('should insert a new user', async () => {
const result = await pool.execute('INSERT INTO users (name, email) VALUES (?, ?)', ['John Doe', 'johndoe@example.com']);
expect(result[0].affectedRows).to.equal(1);
});
});

6. Clean up the test database: After running your tests, you need to clean up the test database to ensure that the database is in a consistent state for the next test run. You can do this by deleting all the test data or by restoring the database to its original state. 

For example, you can write a test that deletes all the users from the database after each test

const pool = require('./db');
describe('User management', () => {
beforeEach(async () => {
await pool.execute('DELETE FROM users');
});
it('should insert a new user', async () => {
const result = await pool.execute('INSERT INTO users (name, email) VALUES (?, ?)', ['John Doe', 'johndoe@example.com']);
expect(result[0].affectedRows).to.equal(1);
});
});

By following these steps, you can set up a test database using Cypress for your Node.js application and write tests to ensure that your application and database function correctly. It is important to note that you should always use a separate test database when running tests and never use the production database.

Connecting Cypress to the Test Database

To connect Cypress to a test database, you can use a database driver to establish a connection and execute queries against the database.

1. Install the mysql2 package: You need to install the mysql2 package as a dev dependency in your Cypress project. You can do this by running the following command in your terminal:

npm install --save-dev mysql2

2. Create a connection pool: You can create a connection pool in a db.js file that exports a pool object. The pool object will be used to manage connections to the database.

const mysql = require('mysql2/promise');
const pool = mysql.createPool({
host: 'localhost',
user: 'testuser',
password: 'testpass',
database: 'testdb',
connectionLimit: 10 // maximum number of connections in the pool
});
module.exports = pool;

The above code creates a connection pool with a maximum of 10 connections to the test database.

3. Use the connection pool in your tests: In your Cypress tests, you can import the db.js file and use the connection pool to execute queries against the database. For example, you can write a test that inserts a new user into the users table:

const pool = require('./db');
describe('User management', () => {
it('should insert a new user', async () => {
const result = await pool.execute('INSERT INTO users (name, email) VALUES (?, ?)', ['John Doe', 'johndoe@example.com']);
expect(result[0].affectedRows).to.equal(1);
});
});

The above code executes an INSERT query to insert a new user with the name “John Doe” and email “johndoe@example.com” into the user’s table.

Writing Database Tests with Cypress

Writing Test Cases for CRUD operations

When testing CRUD (Create, Read, Update, Delete) operations for database testing in Cypress, it is important to understand the database schema and operations to be performed clearly. 

The test cases should cover all the possible scenarios and edge cases to ensure the system’s proper functioning.

Test cases for CRUD Operations:

• Create a user record: This test case verifies that the CREATE operation works as expected. Using the INSERT INTO SQL statement, we insert a new record into the “users” table. We then read the record using the SELECT statement and verify that the record was created successfully.

describe('Create', () => {
it('should add a new record to the "users" table', () => {
const newRecord = { name: 'Alice', email: 'alice@example.com' }
cy.task('insertRecord', { table: 'users', record: newRecord })
.then(() => {
cy.task('queryDatabase', 'SELECT * FROM users WHERE email = "alice@example.com"')
.then((result) => {
expect(result).to.have.lengthOf(1)
expect(result[0]).to.deep.include({ id: 4, name: 'Alice', email: 'alice@example.com' })
})
})
})
})

In this test case, we use the cy.task() command to call the insertRecord task with the table name and the new record to be inserted. We then use the then() method to handle the result of the query and use another cy.task() command to fetch the newly inserted record from the “users” table. We then use the expect() assertion to verify that the result contains the expected data, including the newly inserted record.

• Read a user record: This test case verifies that the READ operation works as expected. We insert a new record into the “users” table and then read the same record using the SELECT statement. We verify that the record was read successfully by checking that the values of the columns match the expected values.

describe('Read', () => {
it('should retrieve all records from the "users" table', () => {
cy.task('queryDatabase', 'SELECT * FROM users')
.then((result) => {
expect(result).to.have.lengthOf(4)
expect(result[0]).to.deep.include({ id: 1, name: 'John Doe', email: 'johndoe@example.com' })
expect(result[1]).to.deep.include({ id: 2, name: 'Jane Doe', email: 'janedoe@example.com' })
expect(result[2]).to.deep.include({ id: 3, name: 'Bob Smith', email: 'bobsmith@example.com' })
expect(result[3]).to.deep.include({ id: 4, name: 'Alice', email: 'alice@example.com' })
})
})
})

In this test case, we use the cy.task() command to call the queryDatabase task with the SQL query to fetch all records from the “users” table. We then use the then() method to handle the query’s result and use the expect() assertion to verify that the result contains the expected data.

• Update a user record: This test case verifies that the UPDATE operation works as expected. We insert a new record into the “users” table and then update the record using the UPDATE SQL statement. 

We verify that the record was updated successfully by checking that the updated value of the column matches the expected value.

describe('Update', () => {
it('should update the email of the user with ID 1', () => {
const updates = { email: 'johndoe_updated@example.com' }
cy.task('updateRecord', { table: 'users', id: 1, updates })
.then(() => {
cy.task('queryDatabase', 'SELECT * FROM users WHERE id = 1')
.then((result) => {
expect(result).to.have.lengthOf(1)
expect(result[0]).to.deep.include({ id: 1, name: 'John Doe', email: 'johndoe_updated@example.com' })
})
})
})
})

In this test case, define the updates to the record with ID 1. Then use the cy.task() command to call the updateRecord task with the table name, the record ID to be updated, and the updates to be made. 

Use the then() method to handle the result of the query and use another cy.task() command to fetch the updated record from the “users” table. Use the expect() assertion to verify that the result contains the expected data, including the updated email address.

• Delete a user record: This test case verifies that the DELETE operation works as expected. We insert a new record into the “users” table and then delete the record using the DELETE SQL statement. We verify that the record was deleted successfully by checking that the record is no longer present in the table.

describe('Delete', () => {
it('should delete the user with ID 2', () => {
cy.task('deleteRecord', { table: 'users', id: 2 })
.then(() => {
cy.task('queryDatabase', 'SELECT * FROM users')
.then((result) => {
expect(result).to.have.lengthOf(3)
expect(result).to.not.deep.include({ id: 2, name: 'Jane Doe', email: 'janedoe@example.com' })
})
})
})
})

In this test case, we use the cy.task() command to call the deleteRecord task with the table name and the ID of the record to be deleted. We use the then() method to handle the result of the query and use another cy.task() command to fetch all records from the “users” table. In each test case, we use the cy.task() command to call the corresponding SQL statement to perform the database operation. 

Use the cy.task() command again to read the data from the database and verify that the operation was successful.It is important to note that the cy.task() command is asynchronous, so we need to use the then() method to handle the result of the database operation.

Verifying Data Integrity

To verify the data integrity of the database in Cypress, you can use SQL queries to check if the data in the database match the expected values. 

Here’s how you can do it:

• Create a test case to verify data integrity:

describe('Verifying data integrity of the database', () => {
it('should have the expected data in the database', () => {
// use cy.task() command to call the SQL query
cy.task('queryDatabase', 'SELECT * FROM users')
.then((result) => {
// verify that the data in the database matches the expected data
expect(result).to.deep.equal([
{ id: 1, name: 'John Doe', email: 'johndoe@example.com' },
{ id: 2, name: 'Jane Doe', email: 'janedoe@example.com' },
{ id: 3, name: 'Bob Smith', email: 'bobsmith@example.com' },
// add more expected data as needed
])
})
})
})

In this test case, we use the cy.task() command to call the SQL query to fetch all the records from the “users” table. We then use the then() method to handle the query’s result and compare it with the expected data using the expect() assertion.

• Implement the queryDatabase task in the plugins file:

const { Pool } = require('pg')


module.exports = (on, config) => {
// configure the database connection
const pool = new Pool({
host: 'localhost',
user: 'postgres',
password: 'password',
database: 'mydatabase'
})


// define the queryDatabase task
on('task', {
queryDatabase: (query) => {
return pool.query(query)
.then((result) => {
return result.rows
})
}
})
}

In this code, we configure the PostgreSQL database connection using the pg module and define the queryDatabase task that takes a SQL query as a parameter and returns the result of the query as an array of objects.

Testing Database Transactions

Transactions provide a way to group a set of database operations into a single unit of work that either succeeds or fails as a whole. Testing database transactions is an important aspect of testing web applications, ensuring the application can handle database operations correctly and consistently. 

It involves testing that the transactions are executed correctly and provide the expected results regarding the database state. To test database transactions, we can use Cypress to execute database queries and transactions.

• Assuming you’re using a Node.js backend and a database that supports transactions (e.g. PostgreSQL), you can use the cy.task command in Cypress to execute database queries and transactions.
• First, you’ll need to create a database connection in your Node.js backend that Cypress can use. 

Here’s an example of how to do this using the pg library

const { Pool } = require('pg');


const pool = new Pool({
user: 'your-db-user',
host: 'your-db-host',
database: 'your-db-name',
password: 'your-db-password',
port: 5432, // or your database port
});
module.exports = pool;

• Next, you can create a Cypress command that executes a database transaction using the connection created above.
• This command should start a transaction, execute a callback function that can contain any database queries or transactions, and either commit or roll back the transaction based on the success of the callback function

const pool = require('../path/to/your/database/connection');

Cypress.Commands.add('runTransaction', async (transactionCallback) => {
const client = await pool.connect();

try {
await client.query('BEGIN');
await transactionCallback(client);
await client.query('COMMIT');
} catch (error) {
await client.query('ROLLBACK');
throw error;
} finally {
client.release();
}
});

This command starts a database transaction, executes a callback function that can contain any database queries or transactions, and either commits or rolls back the transaction based on the success of the callback function.

Here’s an example test that uses the runTransaction command:

describe('Database Transactions', () => {
it('should execute a database transaction', () => {
cy.runTransaction(async (client) => {
// Insert a new user into the database
await client.query('INSERT INTO users (name, email) VALUES ($1, $2)', ['John Doe', 'john.doe@example.com']);


// Query the database to check if the user was inserted
const { rows } = await client.query('SELECT * FROM users WHERE name = $1', ['John Doe']);
expect(rows.length).to.equal(1);
});
});
});

In this example, we’re inserting a new user into the database within the transaction and then querying the database to check if the user was inserted successfully. If the callback function completes successfully, the transaction will be committed and the test will pass.

Testing Database Migrations

Database Migrations provide a way to incrementally update the database schema, and ensure that the database schema is consistent across different environments (e.g. development, staging, production).

To test database migrations, we typically need to perform the following

Create a migration script that Cypress can execute. Assuming you’re using a Node.js backend and a database that supports migrations (e.g. PostgreSQL with the knex library), you can use the cy.task command in Cypress to execute migration scripts.

First, you’ll need to create a migration script that Cypress can execute. 

Here’s an example of how to create a migration using knex:

exports.up = function(knex) {
return knex.schema.createTable('users', function (table) {
table.increments('id');
table.string('name');
table.string('email').unique();
table.timestamps(true, true);
});
};


exports.down = function(knex) {
return knex.schema.dropTable('users');
};

This migration script creates a new users table with an id, name, email, and created_at and updated_at timestamps.

1. Create a Cypress command that executes the migration script using a database connection. create a Cypress command that executes migration scripts using knex: This command starts a knex database connection, runs the latest migrations, and either commits or rolls back the transaction based on the success of the migration script.

const knex = require('knex');
const config = require('../path/to/your/knexfile');


Cypress.Commands.add('runMigrations', async () => {
const db = knex(config);


try {
await db.migrate.latest();
} catch (err) {
console.error(err);
await db.rollback();
} finally {
await db.destroy();
}
});

2. Write tests that use the Cypress command to execute migration scripts and make assertions about the expected database schema changes.

Test that uses the runMigrations command:

describe('Database Migrations', () => {
it('should run database migrations', () => {
cy.runMigrations();


// Assert that the users table was created
cy.task('knex', { sql: 'SELECT * FROM users' }).then((result) => {
expect(result.rows.length).to.equal(0);
});
});


it('should roll back failed migrations', () => {
// Create a migration script that fails
const migration = `
CREATE TABLE users (id INTEGER PRIMARY KEY);
INSERT INTO users (id) VALUES (1);
INSERT INTO users (id) VALUES (1);
`;


// Execute the migration script
cy.task('knex', { sql: migration })
.should('throw')
.runMigrations();


// Assert that the users table was not created
cy.task('knex', { sql: 'SELECT * FROM users' }).then((result) => {
expect(result.rows.length).to.equal(0);
});
});
});

In this example, we have two tests: one that tests successful migrations and one that tests failed migrations. The first test runs the runMigrations command and verifies that the users table was created. 

The second test executes a migration script that intentionally fails, and verifies that the migration is rolled back and that the users table was not created. When testing database migrations, it’s important to test both successful and failed migrations. Successful migrations should update the database schema correctly, while failed migrations should roll back any changes made to the database schema.

Four Best Practices for Cypress DB Testing

1. Keeping the Test Data separate from Production Data

Keeping the test data separate from the production data in Cypress tests is essential. If your tests are writing or modifying data in your production database, it can lead to inconsistencies, corruption, or even loss.To keep test data separate from production data, here are some best practices:

• Use a separate test database: Use a separate database for your tests to keep your production data safe. This ensures that the data used in your tests will not affect your production data. You can also use the same database server but a different name for testing purposes.
• Use database migration scripts: Use database migration scripts to set up the test database schema and seed the initial data. This ensures that your test data is always in a known state and consistent across test runs.
• Use test-specific seed data: Use seed data specific to the test being run to ensure that the test is isolated and reproducible. This also makes it easier to troubleshoot issues that may arise during testing.
• Use transactional testing: Use transactional testing to ensure that each test runs in a database transaction and rollback the transaction after the test completes. This ensures that your test data remains separate from your production data and that your production data is unaffected by your tests.
• Clean up test data: After running tests, clean up any test data created in the test database. This can be done using database migration scripts or cleanup functions in your tests.

2. Testing with Realistic Data

Testing with realistic data in Cypress can help you catch bugs and issues that may not appear with synthetic test data. 

• Use real-world scenarios: Develop test cases that simulate real-world scenarios. For example, test scenarios include multiple users, different user roles, or different data sets that your customers commonly use.
• Use production-like data: Use production-like data for testing to ensure your tests are as realistic as possible. This means using data representative of what your customers use in production, including data volume, data types, and data distribution.
• Use anonymized data: Anonymize data for testing to protect sensitive customer data. This ensures that your tests do not contain any identifiable data that could be used to breach your customers’ privacy.
• Use test data generators: Use test data generators to automate the creation of realistic data. This helps you generate large amounts of realistic data quickly, which is especially useful when testing with large datasets.
• Use randomization: Use randomization techniques to generate test data that covers a wide range of values and edge cases. This helps you uncover potential bugs or issues that may not appear with a small subset of data.
• Use visualisations: Use data visualization techniques to make it easier to understand and analyse test results. This can help you identify patterns or trends that may indicate issues or areas for improvement.

3. Minimising test data duplication

Minimizing test data duplication in Cypress can help you avoid unnecessary maintenance and improve the readability and reliability of your tests. 

• Use fixtures: Use fixtures to load test data into your tests. Fixtures are external data files that contain test data in a format that Cypress can use, which helps you avoid duplicating test data within your test files and allows you to reuse data across multiple tests.
• Use data factories: Use data factories to generate test data dynamically. Data factories can help you create test data that is unique to each test, which reduces the need for duplicate data.
• Use database migrations: Use database migrations to seed the test database with initial data. This can help you avoid duplicating test data in your test files and allows you to manage your test data separately from your test code.
• Use test-specific data: Use test-specific data to keep your tests independent and avoid duplicating data across multiple tests. Each test should have its unique data set specific to the test being executed.
• Use shared data: Use shared data when it makes sense. Shared data can be used across multiple tests to improve readability and maintainability. However, be careful not to overuse shared data, as it can make it difficult to understand the dependencies between tests.

4. Using Cypress Plugins for database testing

Cypress plugins are a powerful feature that allows you to extend the functionality of Cypress with custom code. Regarding Cypress database testing, plugins can be used to simplify and automate the testing process. 

• Database plugins: Cypress supports plugins that interact with databases. These plugins can help you simplify database interactions, such as creating, reading, updating, and deleting data. For example, you can use the cypress-firebase plugin to interact with a Firebase database or the cypress-mongo-seed plugin to seed a MongoDB database.
• Test data generation plugins: Plugins can also generate test data automatically. These plugins can help you create large sets of realistic data quickly, which is especially useful when testing with large datasets. For example, you can use the cypress-faker plugin to generate realistic test data or the cypress-data-session plugin to generate unique test data for each test run.
• Cypress tasks: Cypress tasks are another way to use plugins for database testing. Tasks allow you to execute custom Node.js code during your Cypress tests, which can be used to interact with databases or perform other operations. For example, you can use a task to run a database migration script before your tests or to seed your test database with initial data.
• Assertion plugins: Assertion plugins can add custom assertions to your tests. These plugins can help you validate data retrieved from the database and ensure it meets certain criteria. For example, you can use the chai-firebase plugin to add Firebase-specific assertions to your tests.