So I wanted to make a small project with a DB, Backend and mobile frontend. I have a mariadb database on a raspberry pi and have everything setup for connections from anywhere. I created my backend server with TypeORM and hosted it on heroku. The problem is that my heroku server has a dynamic ip and I want to only have a small amount of whitelisted IP's. So I added quotaguard to my heroku app. The problem is the only way to setup that proxy connection (from the quotaguard documentation) is through socksjs (once again from the documentation on quotaguard) which creates a SockConnection object. I know if I use mysql.createConnection() there's a stream option that allows me to pass in that object, but I don't see it in the createConnection function from TypeORM. I have a variable called sockConn and I have verified that the connection is made on the quotaguard dashboard, but I don't know how to add it as an option to the TypeORM createConnection function.
Here is the index.ts file from my project:
import "reflect-metadata";
import {createConnection, getConnectionManager} from "typeorm";
import express from "express";
import {Request, Response} from "express";
import {Routes} from "./routes";
import { DB_CONNECTION } from "./database";
import { MysqlConnectionOptions } from "typeorm/driver/mysql/MysqlConnectionOptions";
import { config } from 'dotenv';
import { parse } from 'url';
var SocksConnection = require('socksjs');
config().parsed
const setupConnection = () => {
DB_CONNECTION.username = process.env.DB_USER;
DB_CONNECTION.password = process.env.DB_PASS;
DB_CONNECTION.host = process.env.DB_HOST;
DB_CONNECTION.port = parseInt(process.env.DB_PORT);
DB_CONNECTION.debug = true;
// ---- this section from quotaguard documentation ---- //
var proxy = parse(process.env.QUOTAGUARDSTATIC_URL),
auth = proxy.auth,
username = auth.split(':')[0],
pass = auth.split(':')[1];
var sock_options = {
host: proxy.hostname,
port: 1080,
user: username,
pass: pass
};
var sockConn = new SocksConnection({host: DB_CONNECTION.host, port: DB_CONNECTION.port}, sock_options);
// ---- this section above from quotaguard documentation ---- //
}
setupConnection();
createConnection(DB_CONNECTION as MysqlConnectionOptions).then(async connection => {
// create express app
const app = express();
app.use(express.json());
// register express routes from defined application routes
Routes.forEach(route => {
(app as any)[route.method](route.route, (req: Request, res: Response, next: Function) => {
const result = (new (route.controller as any)())[route.action](req, res, next);
if (result instanceof Promise) {
result.then(result => result !== null && result !== undefined ? res.send(result) : undefined);
} else if (result !== null && result !== undefined) {
res.json(result);
}
});
});
// setup express app here
// start express server
app.listen(3000);
console.log("Express server has started on port 3000.
Open http://localhost:3000 to see results");
}).catch(error => console.log(error));
There might also just be a better package, but as I've never worked with this type of thing, I only went off of what the documentation had on it.
So I reached out to QuotaGuard and they gave me an answer that works. The answer is below:
However we usually recommend that you use our QGTunnel software for database connections.
The QGTunnel software is a wrapper program that presents a socket to your application from the localhost. Then you connect to that socket as if it were your database. Below are some setup instructions for the QGTunnel.
Download QGTunnel into the root of your project
Log in to our dashboard and setup the tunnel
Using the Heroku CLI you can log into our dashboard with the following command: heroku addons:open quotaguardstatic
Or if you prefer, you can login from the Heroku dashboard by clicking on QuotaGuard Static on the resources tab of your application.
Once you are logged into our dashboard, in the top right menu, go to Setup. On the left, click Tunnel, then Create Tunnel.
Remote Destination: tcp://hostname.for.your.server.com:3306 Local Port: 3306 Transparent: true Encrypted: false
This setup assumes that the remote database server is located at hostname.for.your.server.com and is listening on port 3306. This is usually the default port.
The Local Port is the port number that QGTunnel will listen on. In this example we set it to 3306, but if you have another process using 3306, you may have to change it (ie: 3307).
Transparent mode allows QGTunnel to override the DNS for hostname.for.your.server.com to 127.0.0.1, which redirects traffic to the QGTunnel software. This means you can connect to either hostname.for.your.server.com or 127.0.0.1 to connect through the tunnel.
Encrypted mode can be used to encrypt data end-to-end, but if your protocol is already encrypted then you don't need to spend time setting it up.
Without transparent mode, you will want to connect to 127.0.0.1:3306.
So for a Procfile that was previously: web: your-application your arguments you would now want: web: bin/qgtunnel your-application your arguments
If you do not have a Procfile, then heroku is using a default setup in place of the Procfile based on the framework or language you are using. You can usually find this information on the Overview tab of the application in Heroku's dashboard. It is usually under the heading "Dyno information".
If you are using transparent mode, be sure that vendor/nss_wrapper/libnss_wrapper.so is also added to your repository.
VERY IMPORTANT 7. After you get everything working, I suggest you download your QGTunnel configuration from our dashboard as a .qgtunnel file and put that in the root of your project. This keeps your project from not relying on our website during startup.
This did work for me, and I was able to make a connection to my database.