Mastering TiDB Cloud Data Service–Your Ultimate Guide to Building a Data-Driven Backend

In our previous post, we introduced TiDB Cloud Data Service (TCDS), a fully managed, low-code backend-as-a-service solution. By seamlessly integrating your data with any application or service through HTTPS, TCDS enables developers to rapidly construct secure and scalable data-driven applications. 

This blog guides you through building REST endpoints with TCDS while constructing a Task Tracker app. Focused on backend development, the tutorial covers creating clusters and tables, inserting data, and crafting APIs. It also provides steps for integrating the React frontend hosted on Vercel with the backend. By the end of this tutorial, you will gain a comprehensive understanding of TCDS and acquire a fully functional backend solution, catering to both beginners and seasoned developers.

Set up a TiDB Serverless Cluster

Take the steps below to create a free TiDB Serverless cluster: 

1. Sign in to your TiDB Cloud. Register a TiDB Cloud account if you haven’t got one.
2. Follow the on-screen instructions to create a free Serverless Tier cluster within seconds.

Create a Data Model

1. Navigate to the overview page of your cluster, and enter the Chat2Query interface on the left.

   

2. Execute the SQL script provided below to create both the tasktracker database and the todos table within it. The script includes the insertion of sample data into the todos table. The Task Tracker application relies on this table to store individual tasks along with their related information, including title, description, status, and timestamps.

   
   CREATE DATABASE tasktracker;
   
   USE tasktracker;
   
   CREATE TABLE todos (
       `id`INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
       `task`VARCHAR(255) NOT NULL,
       `description` TEXT,
       `status`VARCHAR(50) NOT NULL,
       `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
       `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
   )ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;
   
   INSERT INTO todos (task, description, status) VALUES
   ('Grocery Shopping', 'Buy groceries for the week including fruits, vegetables, and dairy products', 'Incomplete'),
   ('Email Client', 'Send the revised project proposal to the client by EOD', 'Incomplete'),
   ('Gym Session', 'Attend the 6 PM cardio fitness class at the local gym', 'Completed'),
   ('Book Flight Tickets', 'Book tickets for the upcoming business trip to New York', 'Incomplete'),
   ('Annual Health Checkup', 'Schedule an annual health checkup appointment with Dr. Smith', 'Completed');
   
   SELECT * FROM todos;
   

3. Select all the statements in the script and execute them by clicking on the Run button.

Create a Data App with GitHub Integration

A Data App is a container that consists of a collection of objects like Endpoints. Creating a Data App is the initial phase for generating REST Endpoints. To create a Data App:

1. Select Data Service from the left navigation menu, and click the Create Data App button.

   

2. Create and configure a standard data app as below:
   • Name Your App: Enter “Todo App” in the Data App Name field.
   • Link Data Sources: Select a cluster to associate with your app. For this example, choose “Cluster0”.
   • Describe your App: In the ‘Description’ field, write “This is a Task Tracking Application designed to efficiently manage tasks.” This brief description outlines the app’s purpose.
   • App Type: Leave the ‘Data App Type’ field at its default setting.
3. Connect to GitHub (Optional):

   By integrating with GitHub, you can link a GitHub repository and a specific branch to your TCDS project. This allows for the automatic deployment of your Data App’s metadata and endpoints directly to the connected project. Here:

   

   1. Log into your GitHub account and create a todo-app for the task tracker project.
   2. In the Create Data App screen, follow the on-screen instructions to install Data Service as an application in your target repository.
   3. Click Authorize to authorize access to the task tracker application on GitHub.
   4. Specify the todo-app repository, branch, and directory as shown in the screenshot for storing configuration files. This enables TCDS to complete the backend API creation process.
4. Click the Create button to create your Data App.

After creating the Data App, you’ll land on the Data App Home page. For first-time users, a Getting Started with Data Service tutorial is available. For this tutorial, we’ll manually walk through each step of creating the endpoints.

Create Endpoints

This section guides you through the process of setting up four HTTPS endpoints (POST, GET, PUT, DELETE) that will enable Create, Read, Update, and Delete (CRUD) operations on the ‘todos’ table. For more detailed information, refer to our documentation on creating new endpoints.

POST /todos

The POST /todos endpoint allows you to create a new task to the todos table. Follow the steps below to create the endpoint:

1. Initiate the endpoint creation: On the left navigation bar of the Todo App, click the “+” symbol, and select the Create Endpoint option. 

   

2. Configure the endpoint properties. Navigate to the Properties tab:
   • Enter the Path as “/todos“. Set the Request Method to POST.
   The endpoint name is automatically generated from the path value entered by default.
    

   

3. Configure SQL. The SQL configuration defines the business logic for the endpoint. For this endpoint, use the following SQL script to insert a new task record: 

   
   USE tasktracker;
   INSERT INTO `todos` (`task`,`description`,`status`) 
   VALUES (${task},${description},${status});   
   

   Copy the SQL script into the natively provided SQL Editor window:

   

4. Configure endpoint parameters. Parameters are variables that add flexibility to your endpoint. They are automatically generated. You can make adjustments to suit your specific needs.
   Navigate to the Params tab to explore numerous configuration options:
   • Set Required to make a parameter mandatory.Align parameter types with your data model.
   Optional settings include enum value, default value, and description. For our todos endpoint, default parameter settings are typically adequate. Refer to our documentation for comprehensive details on configuring endpoint parameters.

   

Congratulations on successfully creating your first POST /todos endpoint! Now, let’s create the remaining endpoints with a similar procedure. 

GET /todos 

The GET /todos endpoint retrieves all tasks stored in our todos table. Follow these steps to effectively create and configure this endpoint: 

1. Navigate to the Properties tab to configure the endpoint properties:
   • Enter the Path as /todos. 
   • Set the Request Method to GET (default) . 
2. Copy the following SQL script into the SQL Editor window:

   
   USE tasktracker;
   SELECT * FROM `todos` ;

Note: This end point does not have any parameters.

PUT /todos

The PUT /todos endpoint updates a task, especially when the task status changes. Follow these steps to effectively create and configure this endpoint: 

1. Navigate to the Properties tab to configure endpoint properties:
   • Enter the Path as “/todos“. 
   • Set the Request Method to PUT . 
2. Copy the following SQL script into the SQL Editor window:

   
   USE tasktracker;
   UPDATE `todos` SET 
   `status` = IF(length(${status})>0,${status},`status`),
   `task` = IF(length(${task})>0,${task},`task`),
   `description` = IF(length(${description})>0,${description},`description`)
    WHERE `id` = ${id} ;
   

3. The above SQL script uses the id of each task for identification and updates. Therefore, it’s essential to correctly configure the ID parameters as below:
   • Set the id parameter as Required.
   • Change its type from the default string to Number. This aligns with the id field’s type in the todos table.

     

DEL /todos

The DELETE /todos endpoint deletes tasks. Follow these steps to effectively create and configure this endpoint:

1. Navigate to the Properties tab to configure endpoint properties:
   • Enter the Path as /todos. 
   • Set the Request Method to DELETE . 
2. Copy the following SQL script into the SQL Editor window:

   
   USE tasktracker;
   DELETE FROM `todos` WHERE `id` = ${id} ;
   

3. Use the same setting as the PUT /todos endpoint to configure endpoint parameters:
   • Set the id parameter as Required.
   • Change its type from the default string to Number. This aligns with the id field’s type in the todos table.

     

Testing the Endpoints

Before we integrate the new endpoints into the actual application, it’s important to test them locally to ensure that they function as expected. Let’s use the POST /todos endpoint as an example to guide you through the testing process:

1. In the Endpoints list, select POST /todos.
2. Under the Params tab, navigate to the Test Values section, and configure the test values as below:
   • Enter “Pay Internet Bill” for task. 
   • Input “May Internet Bill” for description. 
   • Enter “Incomplete” for status.
3. After configuring the test value, click the Test button on the upper right corner of the screen.

   

Upon executing the test, you should receive an HTTP response with a status code 200. This indicates a successful test of the POST /todos endpoint. In the same way, validate the remaining endpoints to ensure the overall robustness and reliability of your application.

Deploy the Endpoints

After thorough testing, it’s time to deploy your endpoints to make them accessible to external clients. Here’s how you can deploy each endpoint: 

1. On the top right corner, click the Deploy button. A Review Changes dialog will pop up.

   

2. Perform a final verification of your edits to ensure everything is configured correctly.
3. Click the Deploy and Push to GitHub button. This makes the endpoint publicly available and pushes the changes to your GitHub repository.

   

4. Repeat the same deployment process for the remaining endpoints.
5. Navigate to GitHub to verify if the backend/http_endpoints folder, which you configured earlier, contains configuration files for the deployed endpoint.

   

Create an API Key

Before exploring your new endpoints, you’ll need to set up an API Key for secure authentication. Here’s how to create one:

1. Click on the Todo App to navigate to your Data App’s home page.
2. Inside the Settings menu, find the authentication section and click the Create API Key button.

   

3. In the Create API Key popup window, specify the following:
   • Description: Input “TaskTrackerKey”.
   • Role: Set the role to “ReadAndWrite”.
4. Click Next to generate a Public Key and a Private Key. Copy and securely store both keys in a safe and easily accessible place.
5. Click Done to return to the Data App home screen.

Your API Key is now successfully created and ready for use in authentication with your newly created endpoints. 

Explore Your Endpoints

With your API Key in place, it’s time to explore the functionality of your newly created endpoints. There are two primary methods for this:

• API Docs: Utilize the built-in Swagger documentation in TCDS.
• Postman: Leverage the integrated Postman support within TCDS.

For this tutorial, we will use API Documentation via Swagger UI. For details on Postman integration, refer to our documentation.

1. On the Data App home page, click View API Docs. The API Docs window pops up and presents the API documentation generated through Swagger UI.

   

2. In the top right corner, click the Authorize button to authorize the endpoint access. You will be using the API keys you obtained from the previous step:
   • Enter the Public Key you as the username.
   • Enter the Private Key as the password.

   • 

This concludes the process of creating our backend application. 

You can now create, retrieve, update, and delete data in the todos table using the secure endpoints through the POST, GET, PUT, and DELETE methods.

Visualize Task Tracker with Vercel

Congratulations! You’re just a few steps away from completing your task tracker app. To finalize the frontend presentation, we’ll deploy using Vercel. Detailed deployment steps are available in the GitHub Readme and Vercel’s documentation.

1. Integrate Frontend Code. Follow these steps to copy the frontend code to your own repository.
2. Configure Vercel Project. After you’ve integrated the frontend code, it’s time to configure your Vercel project.
3. Deploy Vercel Project. With all configurations in place, you are now ready to deploy your project on Vercel. This final step will bring your task tracker app to life, making it accessible online.

Conclusion

TCDS offers a user-friendly, scalable, and reliable platform for application building and management. As showcased in this guide, TCDS enables swift creation of CRUD endpoints, facilitating rapid application development and deployment.

Ready to dive into the world of low-code development with TCDS? Getting started is easy and rewarding. 

Sign up for TiDB Serverless, follow our guides, and you’ll be on your way to mastering TCDS. Stay tuned for our upcoming how-to blogs, packed with guides and best practices to maximize your potential with TCDS.