ngrok and Postgres - How To Connect To Local Postgres With ngrok

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.

Conclusion

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!