Skip to content
Colin Wren
Twitter

Making it easier to keep tabs on your projects using Retool

— Product Development, Technology, DevOps — 9 min read

After the first COVID lockdown hit the UK in 2020 I was bit by the entrepreneurial bug and since then I’ve built a number of products, namely JiffyCV, Reciprocal.dev and now I’m working on Clime.

One of the pain points I’ve had with the whole experience of building these products has been the non-software developmental parts of the process such as marketing, product development and operational support and the multitude of tools used to monitor the effectiveness of your efforts in these areas.

As I have three different products to monitor that means I have three different sets of accounts to login to each application and when combined with the fact that when I’m hyper-focused on development it means I often forget to check these important metrics, this all leads to me running a little blinder than I should be.

In order to regain oversight of my products I started looking at dashboard tools as all the services that make up my ‘product stack’ have APIs that allow for data to be read from them so it’s technically feasible to create a view across all of them. Two important criteria for these dashboard tools was that the tool could be self-hosted and that I could pull together the simple dashboard I needed for free.

After a bit of research I came across Retool, which markets itself as a framework for building internal tools, but for what I needed it worked really well for building a simple read-only dashboard. It can be self-hosted (although you still need to register for a license to do this) and it’s free to do pretty much anything apart from share the dashboard in a non-editable manner.

Running Retool

The first thing to do is getting Retool running. There are a couple of options here as Retool has their own cloud hosted solution, or you can use Docker to run it locally or on your own cloud provider. Both require you to sign up, but as I want to run everything locally I went with the self-hosted Docker based setup.

Once you’ve signed up for the Retool self-hosted portal you’ll be shown your license key and set of options on how to install Retool. If like me you want to run the Retool locally then you need to select the ‘Local machine’ and either run the bash command they provide or use the tryretool/backend docker image (more on how to do this later).

The bash command provided by Retool will download the retool-onpremise repo, configure the Docker Compose and environment variables needed to run the application and boots everything up for you. Once it’s running you can then access the application itself on port 3000.

When you first load up the Retool instance you’ll need to create an account for the instance and then you can start building up your Apps, Resources and Queries to build your dashboard.

Deploying Retool via Portainer

I use Portainer on my NAS to run different applications using Docker so I had to do a bit of reverse engineering of the bash installation script to get things running.

The bash installation script is lives in this repo: https://github.com/tryretool/retool-onpremise (on the ssop branch) and uses the docker-compose-local-trial.yml file and the docker.env.trial files (although it renames them during it’s execution).

The Docker Compose files in this repo point to building the Retool image as part of deployment so I had to amend them to instead use the tryretool/backend image from DockerHub.

This may not be required for your setup but in order to run everything on my NAS I needed to createssh and keys folders that could be mapped within the Docker Compose file.

Once I’d amended the docker-compose-local-trial.yml file all I had to do then was create a new Stack in Portainer, add the amended Docker Compose file and upload the docker.env.trial file, set the LICENSE_KEY env var and it deployed without a snag.

1version: '2'
2services:
3  api:
4    image: tryretool/backend:latest
5    env_file: ./stack.env
6    environment:
7      - SERVICE_TYPE=MAIN_BACKEND
8      - DB_CONNECTOR_HOST=http://db-connector
9      - DB_CONNECTOR_PORT=3002
10      - DB_SSH_CONNECTOR_HOST=http://db-ssh-connector
11      - DB_SSH_CONNECTOR_PORT=3002
12    networks:
13      - frontend-network
14      - backend-network
15      - db-connector-network
16      - db-ssh-connector-network
17    depends_on:
18      - postgres
19      - db-connector
20      - db-ssh-connector
21    command: bash -c "./docker_scripts/wait-for-it.sh postgres:5432; ./docker_scripts/start_api.sh"
22    links:
23      - postgres
24    ports:
25      - '3000:3000'
26    volumes:
27      - retool/keys:/root/.ssh
28      - retool/ssh:/retool_backend/autogen_ssh_keys
29

30  jobs-runner:
31    image: tryretool/backend:latest
32    env_file: ./stack.env
33    environment:
34      - SERVICE_TYPE=JOBS_RUNNER
35    networks:
36      - backend-network
37    depends_on:
38      - postgres
39    command: bash -c "chmod -R +x ./docker_scripts; sync; ./docker_scripts/wait-for-it.sh postgres:5432; ./docker_scripts/start_api.sh"
40    links:
41      - postgres
42

43  db-connector:
44    image: tryretool/backend:latest
45    command: bash -c "./retool_backend"
46    env_file: ./stack.env
47    environment:
48      - SERVICE_TYPE=DB_CONNECTOR_SERVICE
49    networks:
50      - db-connector-network
51    restart: on-failure
52

53  db-ssh-connector:
54    image: tryretool/backend:latest
55    command: bash -c "./docker_scripts/generate_key_pair.sh; ./retool_backend"
56    env_file: ./stack.env
57    environment:
58      - SERVICE_TYPE=DB_SSH_CONNECTOR_SERVICE
59    networks:
60      - db-ssh-connector-network
61    volumes:
62      - retool/ssh:/retool_backend/autogen_ssh_keys
63      - retool/keys:/retool_backend/keys
64    restart: on-failure
65

66  postgres:
67    image: 'postgres:9.6.5'
68    env_file: stack.env
69    networks:
70      - backend-network
71      - db-connector-network
72    volumes:
73      - data:/var/lib/postgresql/data
74

75  # Uncomment below to use nginx container to handle the frontend & SSL certification
76#  https-portal:
77#    image: tryretool/https-portal:latest
78#    ports:
79#      - '80:80'
80#      - '443:443'
81#    links:
82#      - api
83#    restart: always
84#    env_file: ./docker.env
85#    environment:
86#      STAGE: 'local' # <- Change 'local' to 'production' to use a LetsEncrypt signed SSL cert
87#    networks:
88#      - frontend-network
89networks:
90  frontend-network:
91  backend-network:
92  db-connector-network:
93  db-ssh-connector-network:
94

95volumes:
96  ssh:
97  data:
98  user-data:
Amended docker compose to run under Portainer, you may need to update the volume entries on api and db-ssh-container to fit with your set up

Adding data to your dashboard

Showing data from an external service in Retool requires four different entities:

  • Resource: This is the service you’re pulling data from, there are some predefined templates for major services as well as REST and GraphQL API options. You provide the authentication for the service at this level
  • Query (Query Library): This is the query you carry out against the Resource in order to get the data and is stored in a Query Library so you can share these across Apps. You define the parameters for the query at this level
  • Query (In App): This is the query you execute to get the data to show in the app and uses the Query from the Query Library as a template that you can change the parameters of and provide a transformer function to present the data in a means consumable by Components in the App
  • Component (In App): This visualisation of the data from the Query and Retool provides a whole range of different Components such as Tables, Charts, Lists as well as the means to show aggregated information to make important stats stand out

Once you’ve set up your Resources and added Queries to your Query Library then the workflow for building a dashboard mostly falls down to deciding how you want to represent the data, creating a new (In App) Query to pull the data and transforming it and adding a Component to render that data into a visual representation.

There are also a set of structural Components that can be used to create a visual hierarchy to make things easier to read and there are containers that provide tabs, steppers and other navigational techniques to keep the dashboard focused and clutter free.

How I built the Reciprocal.dev dashboard

The end result — an easy to read dashboard that contains all the important stats for my product
The end result — an easy to read dashboard that contains all the important stats for my product

In order to visualise the data I needed for monitoring Reciprocal.dev I had to pull information from the following services:

  • MailChimp: To keep track on the number of mailing list subscribers and unsubscribers as well as the open rate of the emails we send
  • PostHog: To see the number of people visiting the marketing site and the app itself as well as the fall off rates of different journeys key to determining how well the app is doing
  • Sentry: To see the issues created when people encounter errors using the app
  • Firebase: To see the number of documents created as people use the app. This required a custom Firebase function to be created that I could access via a REST API Resource but as it’s custom built I won’t explain how to add this data to Retool

I could then build a dashboard that would give me a quick glance view of if there are any errors in production, if there is a negative turn on users completing the tasks we hope they will show, as well as stats on how the userbase is growing.

In order to organise the analytics data from PostHog I made use of the tabbed container Component as this allowed me to group the conversion graphs and keep the dashboard’s length small enough that I didn’t need to scroll.

Adding data from MailChimp

The MailChimp API has a /lists/{list_id} endpoint that allows you to get data on the mailing lists that you have with them. In order to use the API you need to create an API key and you need to know the audience ID for the list and the region the list is in (visible in the MailChimp URL).

Once you have the information needed to use the MailChimp API you can create the following:

  • MailChimp REST API Resource: The base URL will be https://[REGION].api.mailchimp.com and you’ll need to provide the auth via Basic Auth
  • MailChimp List Info Query Library Query: Using the MailChimp REST API Resource you can then use the following endpoint 3.0/lists/{{ mailchimp_list_id }}?include_total_contacts=true where mailchimp_list_id is the audience ID
  • MailChimp List Stats Query in App: Using the MailChimp List Info Query from the Query Library you can grab the list information and apply the following transformer to create a key value pair to render in a table
1const keys = ['member_count', 'total_contacts', 'unsubscribe_count', 'open_rate']
2return Object.keys(data.stats).filter((key) => keys.includes(key)).map((key) => {
3  const value = data.stats[key]
4  return {
5    key,
6    value
7  }
8})
Update the keys array to include more data, or remove it and filter out unneeded keys in the UI

In order to use the data you can create a table Component on the canvas and set the data to your MailChimp List Stats Query.

Adding data from PostHog

The PostHog API has an insights endpoint that allows you to get the data that powers the charts shown for the different insights available in the insight list or on your dashboards.

In order to use the API you need to create an Access Token and you’ll need to know the Project ID and ID of the insight. You can find the insight ID in the URL when you view an insight but the project ID is a little harder to find, I ended up inspecting the network traffic in my browsers developer tools to find it as the URLs to the API the PostHog UI makes include it (e.g. api/projects/[PROJECT_ID]/insights/trend ).

Once you have the information needed to use the PostHog API you can create the following:

  • PostHog REST API Resource: The base URL will be https://app.posthog.com and you’ll need to provide your Access Token as an Authorization header in the format Bearer [ACCESS_TOKEN]
  • PostHog Insight Query Library Query: Using the PostHog REST API Resource you can then use the following endpoint api/projects/{{ project_id }}/insights/?short_id={{ insight_id }} replacing project_id with the Project ID and insight_id with the Insight ID
  • PostHog Insight Query in App: Using the PostHog Insight Query from the Query Library you can grab the insight data and apply one of the following transformation functions to show the data in different ways

If you want to show data over time such as daily active users then this transformer will turn the API response into a list of key value pairs for rendering as a line chart.

1return data.results[0].result[0].data.map((value, index) => {
2  const label = data.results[0].result[0].labels[index];
3  return {
4    label,
5    value
6  }
7})
The data property is an array of the values so you need to match them to the label using the index

If you want to show comparative data such as referrers to a page then this transformer will turn the API response into a list of key value pairs for rendering as a bar chart.

1return data.results[0].result.map((result) => {
2  return {
3    key: result.breakdown_value,
4    value: result.aggregated_value
5  }
6})
The referring domain insight returns a list of items so we just need to map the values

If you want to show a funnel such as visualisation of users completing subsequent actions then this transformer will turn the API response into a list of key value pairs for rendering as a bar chart

1return data.results[0].result.map((step) => {
2  return {
3    key: step.name,
4    value: step.count
5  }
6})
The funnel insight returns a list of items so we just need to map the values

In order to use the data you can create a chart Component and choose the appropriate chart type for displaying the information.

Adding data from Sentry

The Sentry API has a Project Issues endpoint that allows you to get a list of the issues (defaulted to only return unresolved issues). In order to use the API you need to create an ‘Internal Integration’ which will then give you the Access Token needed to make calls to the API with. You will also need to know the organisation slug and project slug (these can be found in the settings screens).

Once you have the information needed to use the Sentry API you can create the following:

  • Sentry REST API Resource: The base URL will be https://sentry.io and you’ll need to provide your Access Token as an Authorization header in the format Bearer [ACCESS_TOKEN]
  • Sentry Issues Query Library Query: Using the Sentry REST API Resource you can then use the following endpoint /api/0/projects/{{ organisation_slug }}/{{ project_slug }}/issues replacing organisation_slug with your Sentry organisation’s slug and project_slug with your Sentry project’s slug
  • Sentry Issues Query in App: Using the Sentry Issues Query from the Query Library you can grab the issues for the project and apply this transformation to create a dataset to show in a table
1return data.map((issue) => {
2    return {
3        title: issue.title,
4        count: issue.count,
5        culprit: issue.culprit,
6        url: issue.permalink,
7        level: issue.level,
8        ref: issue.shortId
9    }
10})
The Sentry event has a lot of data so I just return the ones I care about for the dashboard

In order to use the data you can create a table Component on the canvas and set the data to your Sentry Issues Query.

Gotchas and improvements

Retool for the most part is really good and easy to use but there were a few gotchas that I encountered, especially when building multiple dashboards that I thought were worth sharing.

  • You cannot parameterise REST API Resources so if you have multiple accounts for the same service but they use different subdomains or have different auth keys then you will need to create a Resource per account
  • You cannot override the Resource that a Query Library Query is run against so if you have multiple Resources you need to create a Query per Resource
  • Prefix your Resource names if you have multiple accounts for the same service as the dropdown in the Query Library only shows the title
  • Prefix your Query names in your Query Library if you have multiple Resources for the same service as the dropdown in the in-app Query tool only shows the title
  • If you have an expensive (time or money) Query on your dashboard then you may be better off configuring that Query to only run when manually triggered so it doesn’t execute on page load

There are a couple of ways I would improve Retool:

  • Allow for parameterised REST API Resources so parts of the URL that can change such as subdomain can be overridden from within the in-app Query screen
  • Abstract authentication credentials from the REST API Resource so that you can use different credentials against one Resource
  • Add a transformation function library so that Queries that consume data with the same shape can have a single transformation function that needs to be updated, should that shape change
  • If a transformation function library was added then having a dropdown list of those transformations so you could swap between them would also be great for making it easy to change the representation (e.g. from chart to key value pair in a table)

Summary

Retools is a really great tool for help you keep on top of what’s going on with your products and projects and it’s allowed me to build a quick glance view of all my products instead of having to log into six different accounts across different browser profiles.

Dashboarding is just one of it’s use cases and it may offer you even more value from it’s interactive forms and UI components so I think it’s a tool that I recommend that you try out if you’ve not already.

As Retool can be self-hosted it can be deployed into your own infrastructure which should make it viable for companies that want to pull data from internal services.