Retool is a low-code platform for building internal tools. It’s pretty fun to build internal admin UIs with little or no code.
One issue I ran into setting up Retool is that it’s a hosted product over the internet. This means to give Retool access to my database I’d either need to risk enabling public networking, which relies heavily on my trust in Postgres’ security and my database’s password security, or I need to find a way to poke a smaller networking hole to give Retool access to our database.
Enter: SSH Tunneling.
In this guide we’ll talk about how you can connect Retool to your Fly.io database using an SSH Tunnel so you don’t have to make your database directly available to the public internet. We’ll use a Fly.io Postgres Database for this guide, but similar steps can be used to connect Retool to any of their SSH Tunnel supported databases hosted on Fly.io.
Requirements
Make sure you have an account on Retool.
Make sure you’ve setup a Fly.io database and have your connection details.
Step 1: Get the Retool Public Key
Navigate to your Retool account and select “Create a Resource” in the “Resources” card.
Select PostgreSQL.
Set the host to your internal DNS name (example: if my app’s name is jphenow-db the internal DNS name would be jphenow-db.internal).
Set Port to 5433.
Enter your database username and password.
Leave SSL/TLS unchecked.
Enable SSH Tunnel.
You do not yet have the “Bastion” details, but you need to Download Retool’s public key - the link should be towards the bottom of the create resource form.
At this point your Retool resource settings look something like this:
Leave this form open, we’ll come back to it.
Step 2: Create an SSH Server on Fly.io
In order to safely expose your Fly.io database to Retool, we’ll create an SSH server on Fly.io that will act as a tunnel from Retool to Fly.io database.
git clone https://github.com/fly-apps/ssh-app
cd ssh-app
fly launch
When fly launch prompts you:
- Allow it to copy the configuration from this fly.toml
- Enter a name for your SSH Server app
- Select the organization that your Fly.io database is in
- Select the region that your Fly.io database is in
- Do not create a postgres or redis for this app
- Do not deploy
Then run:
fly ips allocate-v4
Copy the contents of the Public Key we downloaded earlier and setup the public key secret and deploy the SSH App:
fly secrets set PUBLIC_KEY="the contents of the public key file"
fly deploy
Step 3: Connect Retool to your Fly.io Database
Now that we have an SSH Server on Fly.io, configured with the retool public key, we can connect Retool to our Fly.io database.
Go back to the Retool form we left open earlier and enter your SSH Server as the Bastion host. It should look something like “yoursshserverapp.fly.dev”. yoursshserverapp is based on the app name you chose in step 2.
You can see we only the internal port 22, as port 1122 over the internet in the SSH app’s fly.toml.
Because of that we’ll set 1122 as the port in the Retool form like so:
Now you should be able to select “Test Connection” and see a green checkmark.
If that’s working, you can select “Create Resource.”
Step 4: Using your resource in Retool
Now that you’ve created your resource, you can use it in Retool.
Navigate to an app in Retool or create a new Retool “app.”
Switch to the “Edit” mode.
Step 4.1: Setup a query
Look to the bottom left, and select the “+” in the “Code” area.
Select “Resource query” and select the resource you created - it will add a query to the bottom of the Code list. Name it something useful like flydbpeople
In the bottom middle, now, you can see the query you just added.
In that same bottom middle pane, click the “Resource” dropdown and select the Fly database you added to Retool.
Enter a simple query like SELECT * FROM people LIMIT 10; (this assumes your database has a table named people, pick any table you want to try this with) and click “Save & Run.” Select “Preview” to see some of the data from your database.
Step 4.2: Connect query to UI
On the right-hand bar there’s you can select “Create” to add components to your app.
Drag a Table component onto your app.
With the table selected, edit the Data section to use your new query. If your query was named flydbpeople it would look something like:
Conclusion
You can now build internal tools with Retool and connect them to your Fly.io database 
Build anything fun with this? Drop a note here!