Using Retool and TiDB Cloud to Build a Real-Time Kanban in 30 Minutes

Author: Jinpeng Zhang (TiDB Cloud Engineer) 
Editors: Fendy Feng, Tom Dewan 

A visualized board, or “Kanban,” can really help us manage our workflows, track the progress of our projects, or general visualized insights. Ideally, this should all be in real time. However, building a real-time Kanban application can be a tedious task and time-consuming. 

Now, we can make things much simpler with two remarkable tools: TiDB Cloud and Retool. TiDB Cloud is a fully-managed Database-as-a-Service (DBaaS) that brings TiDB, an open source Hybrid Transactional and Analytical Processing (HTAP) database, to your cloud. Retool is a low-code development platform that helps you build internal tools fast. By leveraging the two tools, you can build a real-time Kanban application in half an hour, and create a Kanban and extract insights from it by just writing simple SQL statements. 

In this post, I’ll demonstrate an application I built with TiDB Cloud and Retool, and then walk you through how to build such an application step by step. 

A Kanban demo: Hacker News Insight

Before we go into detail on how to build a real-time Kanban application, let me show you a demo called Hacker News Insight I built with TiDB Cloud and Retool. 

Using this Kanban, you can quickly get historical and real-time insights about Hacker News, a popular news service platform that focuses on computer science and entrepreneurship. For example, you can identify the most active post submitter and commenters, the number of new posts, and comments submitted in a certain period of time or in real time. 

Getting the source data and preparing the database

Before we start building the real-time Kanban, we need to get the source data and the database ready first. 

Create your TiDB Cloud cluster 

Prepare the data 

After you create a TiDB Cloud cluster, you can start to prepare the data source for your application. If you have applications that can produce real-time data, shadow your application’s traffic to the TiDB Cloud cluster you just created.

In this post, I will use a Hacker News Crawler to grab the real-time data of newly-submitted articles and newly-given comments on Hacker News. 

Note: If you want to use your historical data as the data source, you can refer to How to Import Sample Data into TiDB Cloud. 

Build your Kanban application with Retool

Create a Retool account and connect it with TiDB Cloud 

Create your real-time Kanban 

After you create your Retool account and connect it with TiDB Cloud, you can use Retool’s components to create your customized and visualized Kanban, and make analytical queries by writing simple SQL statements. 

Create a table to show the latest posted 500 Hacker News stories 

Create a chart to show the top 20 most active post-submitters

Creating a chart is similar to creating a table. From the Components section of the Kanban on the right side, drag a Chart component to the center. Then, write the following SQL commands to query the top 20 most active post-submitters on Hacker News. 

SELECT who AS author, COUNT(*) as number_of_articles FROM items 
    WHERE time > UNIX_TIMESTAMP('2022-07-01') and type="story" and who <> '' 
    GROUP BY author ORDER BY number_of_articles DESC LIMIT 20;

Then, your Kanban board displays the top 20 most active post-submitters of Hacker News.  

This sample application is only a small part of what you can do with Retool and TiDB Cloud. To learn how to use other Retool components and add them to your application, you can refer to the Retool documentation. If you want to share your Kanban application with others, click Share on the upper right of your Kanban board. Select the access level, and complete the appropriate fields. 

Summary

In this tutorial, you’ve learned how to leverage TiDB Cloud and Retool to build a Kanban application that can handle real-time analytics. I hope I’ve inspired you to build your own application. 

If you encounter any problems, you can contact us through Twitter, our Slack channel, or you can join TiDB Internals to share your thoughts and feedback with us.

Keep reading:
Build a Better Github Insight Tool in a Week? A True Story
The Beauty of HTAP: TiDB and AlloyDB as Examples
The Long Expedition toward Making a Real-Time HTAP Database