While loading Oracle db within Cypress (plugins/index.js), getting error - NJS-045: cannot load the oracledb add-on binary for Node.js 8.2.1 (win32, x64)

[Sanj2005]

Current behavior:

I am trying to load oracledb using cy.task.
For that I am trying to load oracledb inside the plugins/index.js.
But whenever I am adding statement -

const oracledb = require('oracledb')

I am getting error as shown in below screenshot.

Desired behavior:

I would like to load the oracle db using node processor (nodejs)

Steps to reproduce: (app code and test code)

1. Complete all the prerequisite as per https://oracle.github.io/node-oracledb/INSTALL.html#prerequisites
   a) Install Python 2.7.14
   b) Install Visual Studio 2013
   c) Download Oracle Instant Client on local machine & give that to PATH
2. Install oracledb on node using npm install github:oracle/node-oracledb#v3.0.1
3. After successful installation on Windows 7 OS, execute below code outside of Cypress folder in the same project. It should run successfully without any error.

let dbQuery = '**********************************';
const oracledb = require('oracledb');
oracledb.createPool(dbConfig)
  .then(function() {
    return verifyDbPromise(dbQuery);
  })
  .then(function(result) {
    console.log(result);
  })
  .catch(function(err) {
    console.log(err);
  });


verifyDbPromise(dbQuery) {
    const globalConfig = require('../cypress/fixtures/config/globalConfig.json');
   // let connectionString = `${globalConfig.connectionString}`;

    let oracledb = require('oracledb');

    return new Promise(function(resolve, reject) {
      let conn; // Declared here for scoping purposes.

      oracledb.getConnection()
        .then(function(connection) {
          console.log('Connected to database');
          conn = connection;
          return conn.execute(
            dbQuery, [],
            {
              outFormat: oracledb.OBJECT
            }
          );
        })
        .then(
          function(result) {
            console.log('Query executed');
            //console.log(result.rows);
            resolve(result.rows);
          },
          function(err) {
            console.log('Error occurred', err);
            reject(err);
          }
        )
        .then(function() {
          if (conn) {
            // If conn assignment worked, need to close.
            return conn.close();
          }
        })
        .then(function() {
          console.log('Connection closed');
        })
        .catch(function(err) {
          // If error during close, just log.
          console.log('Error closing connection', err);
        });
    });
  }

4. Now inside plugins/index.js add statement - const oracledb = require('oracledb'); and try launching Cypress. User is getting above error (shown in above screenshot) in the Cypress Window and user is blocked from performing any actions.

Note :

1. I tried to perform postgres db validation using plugin/index.js and found that its working fine but only for oracle its not working
2. I have performed above steps on another Windows 8.1 machine with the same steps and found that this is reproducible always.

Versions

Cypress 3.1.3
nodejs - 8.2.1
Os - Windows 7 or Windows 8.1
oracledb - "github:oracle/node-oracledb#v3.0.1",

========================

I am feeling that most likely, it's issue from Cypress only, as we are able to load & make a successful connection to Oracle DB outside the Cypress. Kindly fix this so that we can perform the Oracle DB validations as part of our test execution

[Sanj2005]

Any update on this defect ?
I am working on the application where I need to support Oracle Database validations with Cypress tool.
Please update me on this defect.

[jennifer-shehane]

Hey @Sanj2005, have you tried troubleshooting the installation via oracledb's github issues? I do see quite a few people have encountered this error.

When you use OracleDB outside of Cypress, are you using the same const oracledb = require('oracledb'); to use it?

[bahmutov]

also a suggestion - if oracle native extensions cannot be loaded inside the Electron environment (which seems to be the case here when using cy.task), you could try using cy.exec to just run nodejs script that connects to Oracle DB, checks its and the exits. If you need to pass some results back to your tests, you could save the results from the nodejs script as a JSON file and read it from the test using cy.readFile after cy.exec

[shi745]

Hi @jennifer-shehane
I do not have any issues using Oracle DB outside of Cypress and while I am using OracleDB outside of Cypress, I am using the same statement - const oracledb = require('oracledb');

Since this is working fine outside of Cypress, I don't think this is related to OracleDB side.

@bahmutov - Are you saying that Cypress has known issue that we can't load oracleDB using cy.task ?
And for that only alternative is cy.exec ?

[roger-gl]

Any news on that since we are having the same problem here ?! BTW: on Mac it works on Windows it doesn't.

[drumbeg]

I've discussed this issue with the developers of the Oracle NodeJS plug-in. The issue sounds like incompatibility due to Cypress using quite an old version of Electron. Cypress are targeting an Electron upgrade for their next major release, so fingers crossed.

[bahmutov]

Even better - when this PR lands #4436 and is released, Cypress will be able to use your system Node version, ensuring that you no longer rely on the Electron inside Cypress to match what you need.

[drumbeg]

Awesome! Look forward to testing this one. Will it land in a forthcoming minor release?

[bahmutov]

yes, it probably will be released in 3.5.0

[arun290636]

Any update on this issue?

[drumbeg]

Any update on this issue?

I believe this is fixed from 3.5.0 onwards with the ability to run tasks using NodeJS present on the host machine rather than the one bundled with Cypress. See Cypress config docs for details.

[jennifer-shehane]

Closing as resolved.

If you're experiencing a bug similar to this in Cypress, please open a new issue with a fully reproducible example that we can run. There may be a specific edge case with the issue that we need more detail to fix.

[bahunov]

@Sanj2005 did it work for you?

[ice1080]

Since people are still commenting on this and I ran across this in my own struggles, here is the solution I came up with for this.
cypress doesn't have node running in all code contexts, only certain ones. The oracledb package needs node to be running to be usable. Even more so, you can't import/require the package on v5 because it immediately checks for node and blows up.
In order to get oracledb to work in cypress for testing or using an oracle database, I had to writ a cypress plugin for it:
src/plugins/dbPlugin.js:

let _connection;

// https://www.sitepoint.com/javascript-design-patterns-singleton/
const DbPluginUtil = {

    async setupDb(config) {
        // has to be in this method or even importing this file will break stuff in cypress
        const oracledb = require('oracledb');
        oracledb.outFormat = oracledb.OUT_FORMAT_OBJECT;
        oracledb.autoCommit = true;

        oracledb.initOracleClient({configDir: config['TNS_ADMIN_LOCATION']});
        _connection = await oracledb.getConnection({
            user: config['DB_USER'],
            password: config['DB_PASSWORD'],
            connectString: config['DB_CONNECT_STRING'],
        });

        return null;
    },

    async testConnection(config) {
        if (!_connection) {
            await this.setupDb(config);
        }
    },

    async executeDbStatement(statement, config) {
        await this.testConnection(config);

        try {
            return await _connection.execute(statement);
        } catch (e) {
            throw new Error('failed to execute: ' + statement + '\n' + e.message);
        }
    },
}

Object.freeze(DbPluginUtil);

function loadDBPlugin(config) {
    return {
        async executeDbStatement({statement}) {
            return await DbPluginUtil.executeDbStatement(statement, config);
        },
    }
}

module.exports = {
    loadDBPlugin
}

Then in cypress/plugins/index.js:

const {loadDBPlugin} = require("../../src/plugins/dbPlugin");
...
    // in the module.exports function:
    on('task', loadDBPlugin(config));

Then usage in your cypress tests looks like this:

cy.task('executeDbStatement', {
    statement: 'select user from dual',
})
    .then((response) => {
        expect(response['rows'][0]['USER']).to.equal('bla');
    });

Hope this helps someone in the future. I sure could have used it before going through all the trouble of figuring this out.

[kbaala88]

@ice1080 : Thanks for the details. However, using the above it is throwing error as below.

Message: The plugins file is missing or invalid.

Your pluginsFile is set to C:\CYPRESS_AUTOMATION\cypress\plugins\index.js, but either the file is missing, it contains a syntax error, or threw an error when required. The pluginsFile must be a .js, .ts, or .coffee file.

Or you might have renamed the extension of your pluginsFile. If that's the case, restart the test runner.

Please fix this, or set pluginsFile to false if a plugins file is not necessary for your project.

[bahunov]

Since people are still commenting on this and I ran across this in my own struggles, here is the solution I came up with for this.
cypress doesn't have node running in all code contexts, only certain ones. The oracledb package needs node to be running to be usable. Even more so, you can't import/require the package on v5 because it immediately checks for node and blows up.
In order to get oracledb to work in cypress for testing or using an oracle database, I had to writ a cypress plugin for it:
src/plugins/dbPlugin.js:

let _connection;

// https://www.sitepoint.com/javascript-design-patterns-singleton/
const DbPluginUtil = {

    async setupDb(config) {
        // has to be in this method or even importing this file will break stuff in cypress
        const oracledb = require('oracledb');
        oracledb.outFormat = oracledb.OUT_FORMAT_OBJECT;
        oracledb.autoCommit = true;

        oracledb.initOracleClient({configDir: config['TNS_ADMIN_LOCATION']});
        _connection = await oracledb.getConnection({
            user: config['DB_USER'],
            password: config['DB_PASSWORD'],
            connectString: config['DB_CONNECT_STRING'],
        });

        return null;
    },

    async testConnection(config) {
        if (!_connection) {
            await this.setupDb(config);
        }
    },

    async executeDbStatement(statement, config) {
        await this.testConnection(config);

        try {
            return await _connection.execute(statement);
        } catch (e) {
            throw new Error('failed to execute: ' + statement + '\n' + e.message);
        }
    },
}

Object.freeze(DbPluginUtil);

function loadDBPlugin(config) {
    return {
        async executeDbStatement({statement}) {
            return await DbPluginUtil.executeDbStatement(statement, config);
        },
    }
}

module.exports = {
    loadDBPlugin
}

Then in cypress/plugins/index.js:

const {loadDBPlugin} = require("../../src/plugins/dbPlugin");
...
    // in the module.exports function:
    on('task', loadDBPlugin(config));

Then usage in your cypress tests looks like this:

cy.task('executeDbStatement', {
    statement: 'select user from dual',
})
    .then((response) => {
        expect(response['rows'][0]['USER']).to.equal('bla');
    });

Hope this helps someone in the future. I sure could have used it before going through all the trouble of figuring this out.

How does your package.json look like? It seems that you can't just do 'npm install oracledb' with latest on Mac, you need to manual configure something to get it working.

[ice1080]

@bahunov

  "devDependencies": {
    "@babel/plugin-transform-async-to-generator": "^7.12.13",
    "@babel/plugin-transform-modules-commonjs": "^7.12.13",
    "@cypress/webpack-preprocessor": "^5.5.0",
    "cypress": "^6.3.0",
    "cypress-sql-server": "^1.0.0",
    "jest": "^26.6.3",
    "jest-junit": "^12.0.0",
    "mockdate": "^3.0.2",
    "webpack": "^5.21.0"
  },
  "dependencies": {
    "@babel/core": "^7.12.13",
    "@babel/preset-env": "^7.12.13",
    "@babel/register": "^7.12.13",
    "dateformat": "^4.5.1",
    "node": "^15.7.0",
    "oracledb": "^5.1.0"
  },

@kbaala88 I did have to add some babel stuff to get some of my unit tests to work, but I didn't think it was needed to get it to work. Looks like I was wrong. Here is my .babelrc file at the top level next to package.json

{
  "presets": [
    [
      "@babel/preset-env",
      {
        "targets": {
          "esmodules": true
        }
      }
    ]
  ],
  "plugins": [
    "@babel/transform-async-to-generator",
    "@babel/transform-modules-commonjs"
  ]
}

There is probably also a way to get it to work without needing babel, but I would guess it's the async/await stuff that is causing the plugins file to not load properly without babel.