SQL query on remote MySQL

Make an SQL query on a remote database

Start to install dependencies

npm i -S sequelize
npm i -S mysql2

1 - Make an SQL query

// global sql
// hide
async function getSQL(sqlQuery, host, port, user, password, database) {
 const { Sequelize } = require('sequelize');
 const sequelize = new Sequelize(database, user, password, {
    dialect: "mysql"/* one of 'mysql' | 'mariadb' | 'postgres' | 'mssql' */,
    host: host,
    port: port
 });

 sequelize.authenticate();
 const [results, metadata] = await sequelize.query(sqlQuery);

 sequelize.close()
 return results;
}

2 - Make an SQL query with SSH

Start to create an SSH key

ssh-keygen -t rsa -N '' -f my-ssh-key

Copy your ssh key on your server

ssh-copy-id -i my-ssh-key user@XXX.ovh.net

Don't forget to change your credentials

//global sqlr
// hide
async function getRemoteSQL(sqlQuery) {
  const { spawn } = require('child_process');

  // connect to server (don't forget to copy your ssh-key on the remote server)
  const ssh = spawn('ssh', [
   '-o', 'StrictHostKeyChecking no',
   '-i', 'my-ssh-key',
   '-L', '3307:localhost:3306',
   'user@XXX.ovh.net',
  ]);
  await sleep(4000); // wait connection
  // make SQL query (replace with your credentials)
  const data = await getSQL(sqlQuery, "localhost", 3307, "USER", "PASSWORD", "DATABASE");
  // quit
  ssh.kill();
  return data;
}

You can now just do anywhere in my notes an SQL query against your remote database

const users = await getRemoteSQL("select email from app_users;");
printJSON(users)

Optionaly create a Job

Specify a job_name, the time to run your query (cron) and the key to use (files)

Example

const users = await getRemoteSQL("select email from app_users;");
printJSON(users)