Establishing mySql connection over SSH tunnel in Cypress

[pachkovska]

Hi there,
I was wondering if anyone has accomplished similar task.
I am using library mysql-ssh to connect to mysql DB over ssh tunnel.
Here is my connection code

const mysqlssh = require('mysql-ssh');

let conn = mysqlssh.connect(
    {
        host: Cypress.env('STAGING_SSH_HOST'),
        user: Cypress.env('STAGING_SSH_USER'),
        privateKey: cy.readFile(Cypress.env('home') + '/.ssh/id_rsa_old')
    },
    {
        host: Cypress.env('STAGING_MYSQL_HOST'),
        user: Cypress.env('STAGING_MYSQL_USER'),
        password: Cypress.env('STAGING_MYSQL_PASSWORD'),
        database: Cypress.env('STAGING_MYSQL_DB')
    }
);

conn.then(client => {
    client.query(`SELECT count(id) as cnt FROM table_name`, function (err, results) {
        if (err) throw err;
        console.log(results)
        mysqlssh.close();
    })
})
    .catch(err => {
        console.log(err)
});

I have successfully connected to the DB using same library in webdriver i/o in the past and had no issues. This also perfectly works in plane node.js if I clean out Cypress.env and cy.readFile obviously.

However, in Cypress, I get error: > process.binding is not supported

I have also tried establishing this connection in plugins/index.js file and called it in my test via cy.task() also without success.

From what I understand I am having this issue due to 'Cypress' running in the browser and in case on plugins/index.js - it communicates with Cypress' own background node.js process (from Electron), not one that would be supplied by host (my) environment.

Is there a workaround for this ?

From cypress DEBUG logs it seems like my error is thrown by "ssh2-streams" package.

Any help would be appreciated.

[bahmutov]

Cypress spec file runs in the browser, thus it cannot directly execute (in most cases) code that is meant to run in Node. To run in Node you should use the plugin file that runs in a separate process which can communicate with the spec via cy.task command. See a typical example in the blog post https://glebbahmutov.com/blog/verify-phone-part-two/

[pachkovska]

hey @bahmutov ,

It did work like a charm. I created the connection in plugins/index.js and returned it wrapped into a promise. and then used it via cy.task in the test and got it successfully working.

thank you!!!

[pachkovska]

If anyone comes across this and needs a code for this specific library, here is my code:
plugins/index.js file

const dotenvPlugin = require('cypress-dotenv');
const mysqlssh = require('mysql-ssh');
const fs = require('fs');

module.exports = (on, config) => {
  //  `config` is the resolved Cypress config
    config = dotenvPlugin(config);

    on('task', {
        executeSql (sql, ...args) {
            return new Promise(async (resolve, reject) => {
                try {
                    let connection = await mysqlssh.connect(  {
                            host: process.env.SSH_HOST,
                            user: process.env.SSH_USER,
                            privateKey: fs.readFileSync(process.env.HOME + '/.ssh/id_rsa_old')
                        },
                        {
                            host: process.env.MYSQL_HOST,
                            user: process.env.MYSQL_USER,
                            password: process.env.MYSQL_PASSWORD,
                            database: process.env.MYSQL_DB
                        });
                    let result = await connection.promise().query(sql, args);
                    mysqlssh.close();
                    resolve(result[0][0]); 
                } catch (err) {
                    reject(err);
                }
            });
        }
    })

    return config
}

and here is how I used it in a test example:

describe('Db Test',  () => {
    it('Query Test', () => {
        cy.task('executeSql', 'SELECT count(id) as cnt FROM table_name').then(result => {
            expect(result.cnt, 'Does not equal to 8').to.equal(2000);
        })
    })
})

[valk990]

I am trying to establish connection to Db as well. Could you please help me with the following ?:

• What should the host name be ?
• Who would be the user in this case ?
• How do you acquire the private key ?

[pachkovska]

hey @valk990 ,

I was connecting to company's staging db in this case.

• Host name was given to me by the company.
• User and password were also provided by the company. Some dbs have shared users, some have users created per employee, but in either case, company provides this info.
• Pair of ssh keys were generated using ssh-keygen, private key stays on your computer only, and public key is given to the company to allow access to db with your private key.

If your company allows ssh access to database, this cypress connection essentially would use same credentials as any sql client if you are using one.

Hope this helps,