[Solved] Help, I need mysql-ssh

Hello All,

Does anyone know how to swap in mysql-ssh for mysql ?

My codebase needs to tunnel thru another server, I’ve tested w/mysql-ssh and it works – but I can’t figure out where in Adonis to plug it in.

@virk

If there is support for this in A5 I’d skip ahead!

Is there some way to use Knex directly for this?

Al;

1 Like

I would just make permanent SSH tunnel between two servers and then use mysql to connect to localhost:PORT, which actually references to other server thro tunnel.

Here’s some first tutorial that found from Google: https://www.brandonchecketts.com/archives/creating-a-permanent-ssh-tunnel-between-linux-servers

1 Like

Yes, I’d love to use the solution of a permanent tunnel, and it was easy to setup for the test… But the permanent tunnel won’t pass traffic, because the hosting provider doesn’t turn on AllowTcpForwarding in the sshd_config on sharedHosting servers.

Using the cheapest Ubuntu DigitalOcean droplet as-delivered I can create a Jump server that has a static ip address. No additional software is required to use it to forward the mysql-ssh connection (working code sample appended.)

And if I understand everything correctly, I should therefore takeover the connection pooling so that I am opening and closing the connections with my own code using mysql-ssh.

But I am finding it difficult to understand as I drill down into Adonis just how/what to specify/override.

mysqlssh.connect(
  {
    host: Env.get('JUMP_HOST', 'digital.ocean.droplet.ipAddr'),
    user: Env.get('JUMP_USER', 'root'),
    privateKey: Env.get('JUMP_KEY', keyVal),
    port: Env.get('JUMP_PORT', 22),
  },
  {
    host: Env.get('DATABASE_HOST', 'db_host'),
    user: Env.get('DATABASE_USER', 'db_user'),
    password: Env.get('DATABASE_PASSWORD', 'db_password'),
    database: Env.get('DATABASE_DATABASE', 'db_database'),
  }
)
  .then(client => {
    console.log('attempting db access');
    client.query('SELECT * FROM `wpjt_app_configs`', function (err, results, fields) {
      if (err) throw err
      console.log(results);
      mysqlssh.close()
    })
  })
  .catch(err => {
    console.log(err)
  })

From what I can see I am going to have to create a new dialect for Knex – I found this by setting up a new client name in my config/database.js file

mysqlssh: {
client: ‘mysqlssh’,

Error: Cannot find module ‘./dialects/mysqlssh/index.js’

The Knex group seems to be really aware of people wanting to make different connections: https://github.com/knex/knex/blob/master/CONTRIBUTING.md

I would like to add support for new dialect to knex, is it possible?

Currently there are already way too many dialects supported in knex and instead of adding new dialect to central codebase, all the dialects should be moved to separate npm packages out from knex core library with their respective maintainers and test suites.

So if you like to write your own dialect, you can just inherit own dialect from knex base classes and use it by passing dialect to knex in knex configuration (https://runkit.com/embed/90b3cpyr4jh2):

Certainly there are a lot:

$ ls ./node_modules/knex/lib/dialects/
mssql mysql mysql2 oracle oracledb postgres redshift sqlite3

All of these would likely work with Adonis, which is cool…

Anyway, that is where I am headed – if anyone (@virk) knows better and could head me off at the pass with a shortcut, I’d be grateful for a loooooooooong time !!!

Cheers!

Ok, so there are a few new dialects out there to look at

But more interesting is this fellow’s work: https://wildwolf.name/mariadb-driver-for-knex/

He’s figured out the minimal work required to inject a new dialect directly at config time

So I can use his method to re-implement the connect() with mysql-ssh

But I can’t do a require(‘knex/src/dialects/mysql’) without getting

SyntaxError: Unexpected identifier

Wrapped in a Provider or calling the require() in the config/database.js…

I can’t seem to require(‘knex/src/dialects/mysql’) !!

Drat my low understandings…

OK!

Now I have a hacked up dialect using the mysql-ssh client and working thru my DigitalOcean droplet

Now I have to figure out how to add this dialect so that Knex will use it via Adonis

Then I should go back and figure out if it’s pooling and how many connections it’s making – it doesn’t seem to tear down the one, so maybe it’s only one per heroku instance which would be Fabulous Dahling

If anyone knows how to add a knex dialect – that would help me get to sleep before tomorrow :slight_smile:

All Right!

So now I can add the new dialect from the config/database.sh

More tomorrow…

Zzzzzz

Al;

I’ve created a GitHub repo with my solution for using a tunneled ssh connection to access a remote MySQL server

1 Like

NOTE: If you are using this dialect (or trying to use it) refresh your copy as I have pushed a change for a problem where the tunneled connections were not being closed.

Please make sure you are using version 1.0.2 or greater !!

1 Like

Now version 1.0.3 is the latest & greatest. Very important fix!

1 Like

Updated again.

The new solution is rewritten to use connection counting and ssh-tunnel to create/destroy one tunnel as the connection count approached/leaves one.

This solution is cleaner, and abides by Knex’s usage of pools.

woo-hoo

npm i knex-mysqlssh-dialect !!

:slight_smile:

1 Like