Most developers will at some point run across ngrok to expose their local applications to the internet. Did you know that instead of exposing your application, you can expose a local Postgres database as well? Here's how.
For this example, we'll use SQLBot, our humble little tool to get SQL reports into Slack and Basecamp.
How to Setup ngrok With A Local Postgres Database
This is step 1. If you're not familiar with ngrok, I recommend starting with their docs. Once familiar, you can run the following command to set up a TCP tunnel on the right port:
$ ngrok tcp 5432
You'll see a response something like this:
ngrok by @inconshreveable (Ctrl+C to quit) Session Status online Account doug breaker (Plan: Free) Update update available (version 2.3.40, Ctrl-U to update) Version 2.3.35 Region United States (us) Web Interface http://127.0.0.1:4040 Forwarding tcp://0.tcp.ngrok.io:15077 -> localhost:5432 Connections ttl opn rt1 rt5 p50 p90 0 0 0.00 0.00 0.00 0.00
That's it! That's now you can connect to your local database from outside of your own machine. Let's give it a try.
Helpful hint: make sure you keep your ngrok running in order to connect. :)
Connecting SQLBot to Your Local Machine
Now that ngrok is up and running, creating a connection to your local machine is really easy. Let's assume we have a Postgres database named "sample_db_development", a username of "admin", and a password of "a(*^yyhkadf^&ouh". To connect to that local database, you would create a new connection in SQLBot and specify the following:
After the connection completes, you should see it listed in your connections, like this.
To test the connection, write a simple report to see if it completes. In this case, we'll do a count on the users table, here's how to define the report in SQLBot.
All that's left is to run the report from Slack (make sure you connect your Slack team first). Here's an example.
Connecting SQLBot to a local database can be a very helpful way to develop and refine your reports. Hopefully this little tutorial helps.
If you're looking for a dead-easy way to get SQL reports into Slack, check SQLBot out!