Firstly, most people know that the Node.js community (and the JavaScript community at large) is extremely active, and that the technology is growing by leaps and bounds. There are things like SQL Server drivers and integrations with nearly any platform/library you can think of. I found myself needing to interface with a MySQL database (MySQL is actually pretty great, despite the fact that the evil empire now owns it) that was hosted on an environment restricted to SSH traffic. Even connecting with a tool like Sequel Pro is a bit convoluted. So imagine my horror when I was faced with doing this programmatically. Making things even more difficult was the fact that I was using "serverless" technology that could use a massive range of IP addresses, so simply opening the database port was not an option.
The way to achieve this was with a few absolutely awesome Node libraries and a bit of network-thinking. First of all, if you aren't familiar with them, you should definitely checkout tunnel-ssh and ssh2. The fact that some wonderful individuals crafted these libraries leaves me forever in their debt. And then there's mysql, a great driver for Node.
So without further ado, here's how you do it:
const tunnel = require('tunnel-ssh');
const mysql = require('mysql')
const tunnelConfig = {
username: 'user', // SSH User
password: 'foobar', // SSH Password
host: 'my.remotedbhost.net', // The SSH host, can be the same or different than the DB host
port: 22, // SSH PORT
dstHost: 'my.remotedbhost.net',
dstPort: 3306, // MySQL port
// This is where you connect to the database
localHost: '127.0.0.1',
localPort: 3307,
privateKey: fs.readFileSync(path.join(process.cwd(), '/Users/foo/.ssh/mykey')),
keepAlive: true
};
const val = 'foobar';
tunnel(tunnelConfig, (err, tunn) => {
const conn = mysql.createConnection({host: '127.0.0.1', port: 3307});
conn.connect(e => {
conn.query(`SELECT * FROM foo WHERE bar = '${val}', (e, result) => {
console.log(`The value is ${result[0].foobar});
conn.destroy();
tunn.close();
});
});
}
This may seem rather simple at first, but there are a handful of gotchas here that I had to wade through before I found the promised land. First off, the configuration for tunnel-ssh seems relatively finnicky. The more information you provide about your connection, the better off you're going to be. Secondly, what you're seeing here is a much-simplified version of the actual program that I ended up writing. There is a promisified version of the MySQL library out there, but it's much less popular and much less supported than the linked library, which relies upon callbacks. I ended up in what I call "callback hell," where my functionality was borne out by a seemingly endless string of callbacks. The more callbacks you have, the harder your code is going to be to read. This is the reason that I prefer promises, especially via async/await. Ultimately, you end up with code that performs in a similar fashion, but it's a lot more clear at a glance what it's doing:
const foo = async () => {
const f = foo();
const b = bar(f);
await foobar(b);
}
Or...
function foo() {
return foo().then(r => {
return bar(r);
}).then)(r => {
return foobar(r);
});
}
But I digress. There's another gotcha to consider. When you're dealing with non-linear execution, you really have to bear in mind what the event loop has in its backlog and how it will behave as a result. It is imperative that you properly handle any errors that arise. Note that I didn't do that above, in hopes that it would make the code a bit easier to read. Handling errors properly looks like so:
conn.query(`SELECT * FROM foo WHERE bar = '${val}', (e, result) => {
if (e) {
console.error(e);
conn.destroy();
tunn.close();
} else {
console.log(`The value is ${result[0].foobar});
conn.destroy();
tunn.close();
}
});
Obviously there is some refactoring that should be done here, but you get the idea. What you want to keep in mind is that your leftover event loop activity needs to be wrapped up or your program won't complete execution. This was quite important for me, as the cost of serverless architecture is predicated upon how much time it takes to run your code. If your code never exists, you can end up with an eye-popping bill at the end of the month. With that being said, I highly recommend checking out Apex. It makes managing your remote code and doing test execution runs all the more easy.
And since it's been so long since I've dropped a post here, I want to reiterate a crucial point vis-a-vis Node.js. It's a great platform, but you need to make sure that you're using the right tool for the job. I've seen too many situations where Node is seen as a hammer and everything is a nail. Most of the time, if you're doing something database-related (like here), you'd be better-served using something like the JVM, where you've got a large pool of worker threads that can bombard the database simultaneously. You'll really start to feel the constraints of single-threading in situations like this.
