Migrating sequelize-imbi to new sequelize: unable to establish database connection

Question

I am tasked with migrating the legacy package sequelize-ibmi to the latest sequelize release as the side branch has been merged into the main lib.

PR can be found here: https://github.com/sequelize/sequelize/pull/14009

I am running into the following problem:

error: Unable to connect to the database: [odbc] Error connecting to the database

More specifically, the error object thrown in the sequelize connection-manager.connect:

{
  "state": "08004",
  "code": 10061,
  "message": "[IBM][System i Access ODBC Driver]Communication link failure. comm rc=10061 - CWBCO1049 - The IBM i server application  is not started, or the connection was blocked by a firewall"
}

My findings:

As this entails migrating a legacy package using sequelize-ibmi, I debugged the sequelize-ibmi/lib/dialects/imbi/connection-manager.js (for the legacy project), comparing it to sequelize/lib/dialects/imbi/connection-manager.js (for the 'new' project).

The sequelize-imbi package:

  connect(config) {
    return new Promise((resolve, reject) => {
      try {
        const connection = this.lib.connect(`${config.odbcConnectionString}`);
        debug('Connection successful.');
        resolve(connection);
      } catch (err) {
        debug('Failed to connect: Error in "odbc" package in "new Connection"');
        const error = err.toString();

        if (error.includes('IM002')) {
          throw new SequelizeErrors.ConnectionRefusedError(error);
        }
      }
    });
  }

The sequelize package:

  async connect(config) {
    const connectionKeywords = [];
    if (config.dialectOptions && config.dialectOptions.odbcConnectionString) {
      connectionKeywords.push(config.dialectOptions.odbcConnectionString);
    }
    if (config.database) {
      connectionKeywords.push(`DSN=${config.database}`);
    }
    if (config.username) {
      connectionKeywords.push(`UID=${config.username}`);
    }
    if (config.password) {
      connectionKeywords.push(`PWD=${config.password}`);
    }
    if (config.host) {
      connectionKeywords.push(`SYSTEM=${config.host}`);
    }
    const connectionString = connectionKeywords.join(";");
    if (connectionString.charAt(connectionString.length - 1) !== ";") {
      connectionString.concat(";");
    }
    let connection;
    try {
      connection = await this.lib.connect(connectionString);
    } catch (error) {
      if (error.toString().includes("Error connecting to the database")) {
        const err = new SequelizeErrors.ConnectionRefusedError(error);
        throw err;
      }
    }
    return connection;
  }

In essence, they both boil down to:

await this.lib.connect(connectionString);

The legacy implementation only used the raw connectionString (which includes db details, see below), while the new implementation uses config parameters being passed which are used to create the connectionString.

I debugged and made sure both resulted in the same connectionString.

In both cases the connectionString boils down to:

DSN=****;CurrentSchema=****;Persist Security Info=True;UID=****;PWD=****;DATABASE=****;ccsid=1208

I am unsure why certain parameters are added (Persistent Securiy Info, ccsid e.g.), but this is the currently used & working connectionString in production. (obviously censored)

However, I still receive the error stated above. The exact same config, same dependencies (odbc 4.2). I followed the provided documentation (https://github.com/sequelize/sequelize/blob/main/docs/manual/other-topics/dialect-specific-things.md#ibm-i).

The working code (legacy, sequelize-ibmi package):

const sequelize = new Sequelize({
    dialect: 'ibmi',
    dialectModule: require('odbc'),
    odbcConnectionString: odbcConnectionString,
    dialectOptions: {
        multipleStatements: true,
    },
    multipleStatements: true,
    logging: (msg) => Logger.debug(msg),
    isolationLevel: Transaction.ISOLATION_LEVELS.READ_COMMITTED,
    pool: {
        max: 100,
        min: 0,
        idle: 200000,
        // @note https://github.com/sequelize/sequelize/issues/8133#issuecomment-359993057
        acquire: 1000 * 200,
    },
});

Not working code, sequelize package:

const sequelize = new Sequelize(dbConfig.dbDatabaseName, dbConfig.dbUsername, dbConfig.dbPassword, {
    dialect: 'ibmi' as any, // have to `as any` here, imbi addition isn't fully typed in @Sequelize/core yet.
    dialectOptions: {
        odbcConnectionString: dbConfig.odbcConnectionString,
    },
    logging: (...msg: any) => Logger.info(msg),
});

Attempt to port over legacy settings..

const sequelize = new Sequelize({
    dialect: 'ibmi' as any, // have to `as any` here, imbi addition isn't fully typed in @Sequelize/core yet.
    dialectModule: require('odbc'),
    dialectOptions: {
        multipleStatements: true,
        odbcConnectionString: odbcConnectionString,
    },
    isolationLevel: Transaction.ISOLATION_LEVELS.READ_COMMITTED,
    pool: {
        max: 100,
        min: 0,
        idle: 200000,
        // @note https://github.com/sequelize/sequelize/issues/8133#issuecomment-359993057
        acquire: 1000 * 200,
    },
    logging: (...msg: any) => Logger.info(msg),
});

Is there something I am overlooking? The database I am trying to connect to is a remote one behind a firewall. However, the connection is established in the legacy package without any issues (working on a company vpn, so firewall shouldn't be an issue).

Answer 1

I managed to identify and resolve my issues. I had two things wrong:

• Wrong Database Name variable. DSN needs to be used, not DATABASE. I had these mixed up.
• The new Sequelize uses Hostname but is not documented. This one was important in my case.

The working code:

const sequelize = new Sequelize(dbConfig.dbDatabaseName, dbConfig.dbUsername, dbConfig.dbPassword, {
    dialect: 'ibmi' as any, // have to `as any` here, imbi addition isn't fully typed in @Sequelize/core yet.
    dialectOptions: {
        odbcConnectionString: dbConfig.odbcConnectionString,
    },
    host: dbConfig.dbHost,
    logging: (...msg: any) => Logger.info(msg),
});