Cypress SQL Server Integration

Common errors

• cy.sqlServer is not a function

  cause The `sqlServer.loadDBCommands()` was not executed in your `cypress/support/index.js` file, or the support file itself is not being loaded by Cypress.
  fix Ensure `import sqlServer from 'cypress-sql-server'; sqlServer.loadDBCommands();` is correctly placed and loaded in your `cypress/support/index.js` (or equivalent support file in Cypress 10+).

• Error: Failed to connect to <server>:1433 - getaddrinfo ENOTFOUND <server>

  cause The database server address provided in your configuration (`cypress.json` or environment variables) is incorrect, or the server hostname cannot be resolved.
  fix Double-check the `server` property in your `db` configuration. Ensure the server name or IP address is correct and resolveable from the machine running Cypress tests.

• Login failed for user 'your_db_user'.

  cause The `userName` or `password` provided for the SQL Server connection is incorrect, or the specified user lacks the necessary database permissions.
  fix Verify your `userName` and `password` in your database configuration are accurate. Additionally, ensure the database user has appropriate permissions to perform the required SQL operations.

Warnings

• gotcha Storing database credentials directly in `cypress.json` is a significant security risk, especially in version-controlled repositories. It is strongly recommended to use environment variables (e.g., `CYPRESS_DB_USERNAME`) and pass them to your Cypress configuration or plugin file.
  Fix: Access `process.env` in `cypress/plugins/index.js` (or `cypress.config.js` for Cypress 10+) to construct your database configuration dynamically, then pass it to `sqlServer.loadDBPlugin`.
• gotcha The package currently exhibits different module import syntaxes (`require` for plugins, `import` for support files). Ensure you use the appropriate syntax based on the file's module context.
  Fix: For `cypress/plugins/index.js` (or CommonJS `cypress.config.js`), use `const sqlServer = require('cypress-sql-server');`. For `cypress/support/index.js` (or ES module `cypress.config.js` and e2e spec files), use `import sqlServer from 'cypress-sql-server';`.
• gotcha This package requires direct network connectivity to a SQL Server instance. Ensure your testing environment has the necessary network access and that firewall rules are configured to allow connections to the database server.
  Fix: Verify network connectivity using network utilities (e.g., `ping`, `telnet`) and ensure SQL Server is configured to accept remote connections on the specified port.

Install

• npm install cypress-sql-server npm
• yarn add cypress-sql-server yarn
• pnpm add cypress-sql-server pnpm

Imports

• sqlServer (for plugins)
  wrong:

  import sqlServer from 'cypress-sql-server';

  correct:

  const sqlServer = require('cypress-sql-server');

  Use CommonJS `require` in `cypress/plugins/index.js` (or a CJS `cypress.config.js`) for registering database tasks.
• sqlServer (for commands)
  wrong:

  const sqlServer = require('cypress-sql-server');

  correct:

  import sqlServer from 'cypress-sql-server';

  Use ES module `import` in `cypress/support/index.js` (or ESM `cypress.config.js` and e2e spec files) to load custom Cypress commands like `cy.sqlServer`.
• cy.sqlServer

  cy.sqlServer('SELECT GETDATE();')

  This custom command becomes available globally on the `cy` object after `sqlServer.loadDBCommands()` has been called and Cypress loads the support file.

Quickstart

Demonstrates setting up `cypress-sql-server` plugin tasks and custom commands, configuring database credentials (preferably via environment variables), and executing basic SQL operations within Cypress tests.

// cypress/plugins/index.js (for Cypress < 10)
// For Cypress 10+, use cypress.config.js and return tasks
const sqlServer = require('cypress-sql-server');

module.exports = (on, config) => {
  // It's highly recommended to use environment variables for sensitive data
  // For example, process.env.DB_USERNAME, process.env.DB_PASSWORD
  const dbConfig = config.db || {
    userName: process.env.CYPRESS_DB_USERNAME || 'SA',
    password: process.env.CYPRESS_DB_PASSWORD || 'YourStrong@Password',
    server: process.env.CYPRESS_DB_SERVER || 'localhost',
    options: {
      database: process.env.CYPRESS_DB_DATABASE || 'master',
      encrypt: process.env.CYPRESS_DB_ENCRYPT === 'true', // Use 'true' or 'false'
      rowCollectionOnRequestCompletion: true
    }
  };

  const tasks = sqlServer.loadDBPlugin(dbConfig);
  on('task', tasks);
  return config;
};

// cypress/support/index.js
import sqlServer from 'cypress-sql-server';
sqlServer.loadDBCommands();

// cypress.json (optional, can be passed via environment variables or plugins file)
// IMPORTANT: DO NOT hardcode sensitive credentials in version control.
// Use environment variables as shown in the plugins file example above.
// {
//   "db": {
//       "userName": "SA",
//       "password": "YourStrong@Password",
//       "server": "localhost",
//       "options": {
//           "database": "master",
//           "encrypt": false,
//           "rowCollectionOnRequestCompletion" : true
//       }
//   }
// }

// cypress/e2e/db.cy.js (example test file)
describe('SQL Server Database Operations', () => {
  const tableName = 'CypressTestTable';

  before(() => {
    // Ensure table exists and is clean for tests
    cy.sqlServer(`
      IF NOT EXISTS (SELECT * FROM sysobjects WHERE name='${tableName}' and xtype='U')
        CREATE TABLE ${tableName} (Id INT IDENTITY(1,1) PRIMARY KEY, Name NVARCHAR(255), Value INT);
      DELETE FROM ${tableName};
    `).then(console.log);
  });

  it('should insert and retrieve data', () => {
    const testName = 'TestUser_' + Date.now();
    const testValue = 123;

    cy.sqlServer(`INSERT INTO ${tableName} (Name, Value) VALUES ('${testName}', ${testValue});`);
    cy.sqlServer(`SELECT Name, Value FROM ${tableName} WHERE Name = '${testName}';`)
      .its('recordset')
      .should('have.length', 1)
      .and('deep.include', { Name: testName, Value: testValue });
  });

  it('should update and verify data', () => {
    const updatedValue = 456;
    cy.sqlServer(`UPDATE ${tableName} SET Value = ${updatedValue} WHERE Name LIKE 'TestUser_%';`);
    cy.sqlServer(`SELECT Value FROM ${tableName} WHERE Name LIKE 'TestUser_%';`)
      .its('recordset[0].Value')
      .should('eq', updatedValue);
  });

  after(() => {
    // Clean up specific test data after tests
    cy.sqlServer(`DELETE FROM ${tableName} WHERE Name LIKE 'TestUser_%';`);
  });
});