Connection to SQL DB with Cypress

Question

I'm trying to connect with SQL db using cypress followed the NPM guide . All the dependencies are exactly as mentioned but on running this

 cy.sqlServer('SELECT Email FROM [MyDB].[dbo].[User] WHERE Name ="test"')

I am getting error as below while running.

CypressError: cy.task('sqlServer:execute') failed with the following error:

TypeError: No connection configuration given.

Although my cypress.json file has got my database connection string.

Cypress.json

{
"baseUrl": "myurl",
"db": {
    "userName": "test",
    "password": "test",
    "server": "test\\test",
    "options": {
        "database": "test",
        "encrypt": true,
        "rowCollectionOnRequestCompletion" : true
    }
}    
}

The below is my plugins/index.js file

  const sqlServer = require('cypress-sql-server');
module.exports = (on, config) => {
  // `on` is used to hook into various events Cypress emits
  // `config` is the resolved Cypress config
  tasks = sqlServer.loadDBPlugin(config.db);
  on('task', tasks);
}

Answer 1

To anyone looking for how to solve this like I was:

For some reason Cypress(I am using 3.8.2 and I am not sure what version cypress-sql-server author was using) doesn't see the custom property of 'db'.

One method(you can do this many ways) is to just require the cypress config in the plugins file and reference your custom property from there.

To do this just change the plugins/index.js to look like this:

const sqlServer = require('cypress-sql-server');
const dbConfig = require('../../cypress.json');

module.exports = (on, config) => {
  tasks = sqlServer.loadDBPlugin(dbConfig.db);
  on('task', tasks);
}

Answer 2

alternative to cypress-sql-server

npm i tedious

cipress.json

    {
"db": {
  "userName": "xxxxx",
  "password": "xxx",
  "server": "xxxxxxxx",
  "options": {
    "database": "",
    "encrypt": true,
    "rowCollectionOnRequestCompletion": true
  }
}
  
}

plugins/index.js

const Tedious = require('tedious');
const dbConfigSQL = require('../../cypress.json');
module.exports = (on) => {
   on('task', {'sqlServer:execute' : (sql) => {
    const connection = new Tedious.Connection(dbConfigSQL.db);
      return new Promise((res, rej) => {
        connection.on('connect', err => {
          if (err) {
            rej(err);
          }

          const request = new Tedious.Request(sql, function (err, rowCount, rows) {
            return err ? rej(err) : res(rows);
          });

          connection.execSql(request);
        });
      });
    }
  }
  )};

support/command.js

Cypress.Commands.add('sqlServer', (query) => {
  if (!query) {
    throw new Error('Query must be set');
  }

  cy.task('sqlServer:execute', query).then(response => {
    let result = [];

    const flatten = r => Array.isArray(r) && r.length === 1 ? flatten(r[0]) : r;

    if (response) {
      for (let i in response) {
        result[i] = [];
        for (let c in response[i]) {
          result[i][c] = response[i][c].value;
        }
      }
      result = flatten(result);
    } else {
      result = response;
    }

    return result;
  });
});

integration/example/sqlServer.spec.js

/// <reference types="cypress" />

context('SQL SERVER', () => {
    
  it('SELECT', () => {

    const sql = `SELECT * FROM DATABASE..TABELA where CAMPO = 1`;

    cy.sqlServer(sql).should(res=>{console.log(res)})


  })

  })