Cypress task undefined even though the promise is returned

[ashokkumarg]

I used TypeScript and Cypress framework for the automation purpose. To retrieve the data from SQL Server I have tried with Cypress sql server but that is available only in JavaScript, due to that I have used the JavaScript MSSQL library to retrieve the values under plugin/index.js then called that function in the required TypeScript file. But it throws the error as undefined value during retrieval process even though I handled with promise. But the query output is successfully printed in the console (attached the image below)

I have tried the same with MYSQL there it works and in MSSQL it fails. What would be the reason, do I miss anything here? I have shared the trials and information below:

plugin/index.js

var mssql = require('mssql');

module.exports = (on, config) => {      
  on('task', {
    'createMSSQLConnection'(query) {
      var res = odsQueryDB(query)
      console.log(res)
      return res
    }
  });

function odsQueryDB(query) {
    return new Promise((resolve, reject) => {
      mssql.connect(db.ods,function(err){       
      if(err){ console.log(err) }
      var sqlServerRequest = new mssql.Request();
      sqlServerRequest.query(query, (error, recordset) => {
        if(error)  
          return reject(error)
        mssql.close();      
        console.log(recordset[0])
        return resolve(recordset)
       })
      })
    })
  }   
};

database.ts

class DBConnectionManager {

  getODSValueFromDB(){
      const query = `SELECT TOP(1) FIELD FROM TABLE`;
      cy.task('createMSSQLConnection',query).then(function (recordset:any) {
        if(recordset.length<1){
          throw new Error("There are no values in the results. Check the database data!")
        }
        let rec:any = recordset
        const results:any = Object.values(rec[0])
        cy.log(''+results[0])
   })
}

Console Output:

In the browser console I can see the data is some how processed and displayed in the console. But why it is returning as undefined?

Expected behaviour:

The expected behaviour is the data should be successfully passed to the typescript class and it should be retrieved

Actual behaviour:

The actual behaviour is the data is returned as undefined in the index.js file even though it is handled with promise

This console output is displayed from the browser that Cypress invokes. I have went with some trials like giving wrong db credentials and query where that properly throws the exception. With that I assume there is some problem on the below part

1. JavaScript MSSQL Implementation

sqlServerRequest.query(query, (error, recordset) => {
        if(error)  
          return reject(error)
        mssql.close();      
        console.log(recordset[0])
        return resolve(recordset)
       })

I have tried to add the callback part as well but still it throws the undefined.

On the other hand to read the data from database.

Would it be possible to use cy.sqlserver with in Cypress Typescript Framework

2. Cy.SqlServer in index.js

For JavaScript I declared the cy.sqlserver inside a function under index.js as below But it throws exception as sqlServer not a function.

  function getODS(query){
    cy.sqlServer(query).then(function(result){
      return new Promise((resolve, reject) => {
        connection.query(query, (error, results) => {
          if (error) {
            return reject(error)
          }
          connection.end()
          return resolve(results) 
        })
      })
    })
  }

3. Make the Cy.sqlServer globally in TS files

For TypeScript I tried adding the custom commands via typescript. During the import it thrown error like types not available by author and I tried to add the index.d.ts file but still does not work.

const sqlServer  = require('cypress-sql-server');
const db = require('../../cypress.json')
declare namespace Cypress {
    interface Chainable<Subject> {
        sqlServer(value: any): Chainable<Subject>
    }
  }
  
Cypress.Commands.add(sqlServer, { prevSubject: 'optional'},sqlServer.loadDBPlugin(db.ods))

Invoke the sqlServer in the .ts file

The above two things were throwing cy.sqlServer is not a function() during the execution. Due to this I went with JS MSSQL implementation.

It would be great if there is a solution for any of the three

Software versions

Cypress: 6.2.1
NodeJS: 12.8.1
node-mssql: 6.1.0
SQL Server: 13.0.5830.85

[ashokkumarg]

@jennifer-shehane could you share your thoughts?

[ashokkumarg]

The issue was with the data retrieval process and the issue resolved with the below change in database.ts

Changes are

Javascript MSSQL implemention

class DBConnectionManager {

  getODSValueFromDB(){
      const query = `SELECT TOP(1) FIELD FROM TABLE`;
      cy.task('createMSSQLConnection',query).then(function (recordset:any) {
        if(recordset.length<1){
          throw new Error("There are no values in the results. Check the database data!")
        }
     else{
       cy.log(recordset['recordset'][0].FIELDNAME)  
     }
 
   })
}

But still if someone could solve completed through cy.sqlServer using cypress means share over here.