'Connection to SQL DB with Cypress

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);
}
javascriptsqlnpmdatabase-connectioncypress


Solution 1:[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);
}

Solution 2:[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)})


  })

  })

Solution 3:[3]

I guess that the problem is in "server": "test\\test" in your Cypress.json. It should probably something like "server": "localhost","server": "localhost\\SQLExpress" or something along those lines. The value should be the same value that you use in "Connect to Server" dialog in Microsoft SQL Server Management Studio.

Solution 4:[4]

I fixed it by moving the configuration into env in cypress.json:

{
  "env": {
    "db": {
      "host": "localhost",
      "user": "sa",
      "password": "xxxx",
      "database": "CollateralSystem",
      "port": 1433
    }
  }
}

Then in plugins\index.js:

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

Solution 5:[5]

You have missed the return statement which you should include as below

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

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 aar0n
Solution 2 Fernando Filizola
Solution 3 Arnon Axelrod
Solution 4 rhurford
Solution 5 David Buck

Related Questions