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

![use retool and tidb cloud to build a kanban](https://www.pingcap.com/wp-content/uploads/2022/08/use-retool-and-tidb-cloud-to-build-a-kanban--1024x341.jpeg "use retool and tidb cloud to build a kanban")

**Author:** [Jinpeng Zhang](http://github.com/zhangjinpeng1987) (TiDB Cloud Engineer)
**Editors:** [Fendy Feng](http://github.com/septemberfd), 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](https://docs.pingcap.com/tidbcloud/) 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](https://docs.retool.com/docs) 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](https://news.ycombinator.com/), 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.

![Hacker News Insight](https://lh4.googleusercontent.com/jFINyjJnuk5orjb_TMnAz_VLI7VxWmcO2g2Am4B6ZGhiOu6rrJ6zOBm5f84v3tvsJcrEgbcJO1Lz6CXrdhAOaZfCDEneuU8y51va2CxYLMkl5Wic87OlCA_Mg-mvci81mC-e_fiEgzd5ThcGFOHWepQ)

## 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

1. [Sign up](https://tidbcloud.com/signup?__hstc=86493575.1ab658245a0f600a7fe1d5da32919bf3.1640069010269.1659334341751.1659343835027.112&__hssc=86493575.3.1659343835027&__hsfp=2474198974) for a free TiDB Cloud account and log in to it. If you already have one, you can [log in](https://tidb.auth0.com/login?state=hKFo2SBTemR5aTNBQTBPMkdCUDlqT2wzX1BZQXhJVVJNOW9CaaFupWxvZ2luo3RpZNkgTDlRWmdXNEItaXBVTEZVVG1IVkRON0l2aUZWWXI5WmSjY2lk2SA2SVp0aENmbVJLSVBFblFTVDhhRGJ0TTdTR2RNbmlSbA&client=6IZthCfmRKIPEnQST8aDbtM7SGdMniRl&protocol=oauth2&response_type=token%20id_token&redirect_uri=https%3A%2F%2Ftidbcloud.com%2Fauth_redirect%3Fprev%3D%2Fconsole%2Fclusters%3Futm_source%3Dossinsight%26utm_medium%3Dreferral&scope=openid%20email&nonce=loJ9qTOSY7MDGQKoQ3Kbh7lKi2DFZ-9s&auth0Client=eyJuYW1lIjoiYXV0aDAuanMiLCJ2ZXJzaW9uIjoiOS4xOS4wIn0%3D) to your account directly. ![image](https://en.pingcap.com/wp-content/uploads/2022/08/1280X1280.jpeg)
2. On the **Active Clusters** page, click **Create Cluster**.
3. Create your cluster. On the **Create Cluster** page, select the free **Developer Tier**. Set up your cluster name, cloud provider (only AWS is available for the Dev Tier), and region. (We recommend that you use the region where your application is deployed—especially if your application is sensitive to latency.) Then, click **Create**.  
    **Note**: You can also select the paid TiDB **Dedicated Tier** to get better performance. ![alt_text](https://en.pingcap.com/wp-content/uploads/2022/08/WX20220804-154152@2x.png)
4. After you create a cluster, you will be directed to the **Active Clusters** page. On the right side of this page, click **Connect**. ![alt_text](https://en.pingcap.com/wp-content/uploads/2022/08/WX20220804-160317@2x.png)
5. On the pop-up window, create a traffic filer and connect with a SQL client. ![alt_text](https://en.pingcap.com/wp-content/uploads/2022/08/iiauuaTink-scaled.jpg)

### 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](https://github.com/zhangjinpeng1987/hackernews-crawler) to grab the real-time data of newly-submitted articles and newly-given comments on [Hacker News](https://news.ycombinator.com/).

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](https://docs.pingcap.com/tidbcloud/import-sample-data).

[Subscribe to Blog](https://share.hsforms.com/1e2W03wLJQQKPd1d9rCbj_Q2npzm) [Request a Demo](/contact-us)

## Build your Kanban application with Retool

### Create a Retool account and connect it with TiDB Cloud

1. [Sign up](https://login.retool.com/auth/signup?plan=free) for a trial Retool account and sign in to it. If you already have one, you can [sign in](https://login.retool.com/auth/login) to your account directly. After you sign in, you can start to create your first application.
2. Create a new application. On the upper right side of the Onboard Page, click **Create new**. ![alt_text](https://en.pingcap.com/wp-content/uploads/2022/08/7khFNa45cc.jpg)
3. Select your resource type. On the **Select a resource type** window, select **MySQL** because TiDB is completely MySQL compatible. ![alt_text](https://en.pingcap.com/wp-content/uploads/2022/08/gsz4lX2Szv.jpg)
4. Create your MySQL resource. Fill in all the fields such as **Name, Folder, Host, Port, Database name, Database username,** and **Database password**. The default port for TiDB Cloud is 4000. When you’re done, click **Create resource**. ![alt_text](https://en.pingcap.com/wp-content/uploads/2022/08/RbRUlN3054.jpg)

### 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

1. Select a table. On the **Retool** page, in the **Components** section of the Kanban, drag a **Table** component to the center.
2. Open a new query. At the bottom of the **Query** editing section, click **New.** Write the following SQL commands to query the real-time data stored and processed in TiDB Cloud.

```{.sql .copyable}
    _SELECT * FROM items WHERE type='story' and title> '' ORDER BY time DESC LIMIT 500;_
```

1. Connect the query and the table you just added in the **Inspect** section.
2. Start the analytical query process. Click **Run**. Your Kanban board displays the latest 500 stories posted on Hacker News.  
    ![alt_text](https://en.pingcap.com/wp-content/uploads/2022/08/Gqpyl4tcTU-scaled.jpg)

#### 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.

```{.sql .copyable}
    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.

![20 most active post-submitters of Hacker News](https://lh3.googleusercontent.com/hSp0OUq7_j6PvJN4-6he16PS3SG-rvQ31YHYtst7bMOLocaRIlOuQzRqXGze35fxzTTg3quri3LCNnGoZ1n4zKAappOoG06BfHcT2naKB-abDqL5572m3ZqfPRpRCWirXuD4xjjTKRJwtZxVwieK7Uw)

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](https://docs.retool.com/docs). 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.

![SELECT clause](https://lh5.googleusercontent.com/RytUjBacDo7324TXfCAyCniUUi1sFATkoO3P02lbxZCAb5S4T4IcmzZ7uzQS19rZJtE2wlDmQBbvmHpGC4xeNr2ZUVHpefXH4G7ktsF3-F5UNBST9as10ltmwXIoSs8fnUyf29t7sLoqdLmg1GKDFk8)

## 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](https://twitter.com/PingCAP), our [Slack channel](https://slack.tidb.io/invite?team=tidb-community&channel=everyone&ref=pingcap-blog), or you can join [TiDB Internals](https://internals.tidb.io/) to share your thoughts and feedback with us.

**Keep reading:**  
[Build a Better Github Insight Tool in a Week? A True Story](https://en.pingcap.com/blog/build-a-better-github-insight-tool-in-a-week-a-true-story/)  
[The Beauty of HTAP: TiDB and AlloyDB as Examples](https://en.pingcap.com/blog/the-beauty-of-htap-tidb-and-alloydb-as-examples/)  
[The Long Expedition toward Making a Real-Time HTAP Database](https://en.pingcap.com/blog/the-long-expedition-toward-making-a-real-time-htap-database/)

### Original Blog Articles

[Using Retool and TiDB Cloud to Build a Real-Time Kanban in 30 Minutes](https://www.pingcap.com/blog/using-retool-and-tidb-cloud-to-build-a-real-time-kanban-in-30-minutes/).