Cypress 10 and connecting to an Oracle database-CodePudding

So I've got a new Cypress 10 project, and I'm trying to integrate some functionality to allow me to make some basic database calls to our Oracle database (which is on a server I have direct access to, not running locally).

I've been following this guide which shows how to add the oracledb package as a Cypress plugin, but the method used (using the /plugin directory) has been depreciated in Cypress 10 so I can't follow the example exactly.

I've instead tried applying this logic using the Cypress plugin documentation as a guide and I think I have something that almost works, but I can't seem to connect to any database, even if the location is in my tnsnames.ora file (although I'm providing the connection string directly for this particular project).

Here's what my cypress.config.ts file looks like, with the code I've created (I'm using Cucumber in my implementation too, thus why those references are present here):

import { defineConfig } from "cypress";
import createBundler from "@bahmutov/cypress-esbuild-preprocessor";
import { addCucumberPreprocessorPlugin } from "@badeball/cypress-cucumber-preprocessor";
import createEsbuildPlugin from "@badeball/cypress-cucumber-preprocessor/esbuild";
const oracledb = require("oracledb");

oracledb.initOracleClient({ libDir: "C:\\Users\\davethepunkyone\\instantclient_21_6" });

// This data is correct, I've obscured it for obvious reasons
const db_config = {
  "user": "<username>",
  "password": "<password>",
  "connectString": "jdbc:oracle:thin:@<hostname>:<port>:<sid>"  
}

const queryData = async(query, dbconfig) => {
  let conn;
  try{
    // It's failing on this getConnection line
    conn = await oracledb.getConnection(dbconfig);
    console.log("NOTE===>connect established")
    return await conn.execute(query);
  }catch(err){
    console.log("Error===>" err)
    return err
  } finally{
    if(conn){
      try{
        conn.close();
      }catch(err){
        console.log("Error===>" err)
      }
    }
  }
}

async function setupNodeEvents(
  on: Cypress.PluginEvents, config: Cypress.PluginConfigOptions ): Promise<Cypress.PluginConfigOptions> {
    await addCucumberPreprocessorPlugin(on, config);

    on("file:preprocessor", createBundler({
        plugins: [createEsbuildPlugin(config)],
      })
    );

    on("task", {
      sqlQuery: (query) => {
        return queryData(query, db_config);
      },
    });

    return config;
}

export default defineConfig({
  e2e: {
    specPattern: "**/*.feature",
    supportFile: false,
    setupNodeEvents,
  },
});

I've then got some Cucumber code to run a test query:

Then("I do a test database call", () => {
  // Again this is an example query for obvious reasons
  const query = "SELECT id FROM table_name FETCH NEXT 1 ROWS ONLY"
  cy.task("sqlQuery", query).then((resolvedValue: any) => {
    resolvedValue["rows"].forEach((item: any) => {
      console.log("result==>"   item);
    });
  })
})

And here are the dependencies from my package.json:

  "dependencies": {
    "@badeball/cypress-cucumber-preprocessor": "^12.0.0",
    "@bahmutov/cypress-esbuild-preprocessor": "^2.1.3",
    "cypress": "^10.4.0",
    "oracledb": "^5.4.0",
    "typescript": "^4.7.4"
  },

I feel like I'm somewhat on the right track as when I run the feature step above, the error I get back is:

Error===>Error: ORA-12154: TNS:could not resolve the connect identifier specified

This makes me think that it has at least called the node-oracledb package to generate the error but I can't really tell if I've made an obvious error or not (I'm pretty new to JS/TS). I know I've referenced the right path for the oracle instant client and it's been initialized correctly at least because Cypress points out a config error if the path is incorrect. I know the database paths work as well because we have an older Selenium implementation that can connect using the details I'm providing.

I think I'm just more curious to know if anyone has so far successfully implemented an oracledb connection with Cypress 10 or if someone who has a bit more Cypress experience can spot any obvious errors in my code as resources for this particular combination of packages seem to be non-existent (possibly because Cypress 10 is reasonably new).

NOTE: I am planning on switching to using environmental variables for the database connection information that will eventually be passed into the project - I just want to get a connection working first before I tackle that issue.

CodePudding user response：

Oracle's C stack drivers like node-oracledb are not using Java so the JDBC connection string needs changing from:

  "connectString": "jdbc:oracle:thin:@<hostname>:<port>:<sid>"

If you were using:

jdbc:oracle:thin:@mydbmachine.example.com:1521/orclpdb1

then your Node.js code should use:

connectString : "mydbmachine.example.com:1521/orclpdb1"

Since you're using the very obsolete SID syntax, check the node-oracledb manual for the solution if you can't use a service name: JDBC and Oracle SQL Developer Connection Strings.