Make SQL query on a remote MySQL
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)
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)