In this tutorial, you'll learn how to build a customer support desk with ToolJet and a PostgreSQL database. The application enables you to track and respond to customers' queries and save their contact details on the application.
What is ToolJet?
ToolJet is an open-source low-code framework that enables us to build full-stack web applications within a few minutes. With ToolJet, you can create standalone fully-functional full-stack applications or embed applications into other websites.
ToolJet allows you to build applications that use relational and non-relational databases, REST APIs, OpenAI technologies, and cloud storage like Google Cloud Storage, AWS S3, and Minio. It is an excellent development tool helping individuals, developers, and businesses create and ship products faster.
Before we continue
I would be super happy if you could give us a star! And let me also know in the comments section. ❤️
https://github.com/ToolJet/ToolJet
Building the application user interface
Here, you'll learn how to use the various UI widgets provided by ToolJet to build user-friendly interfaces. Before, then you need to create an account.
If you are new to ToolJet, create an account.
Create a Workspace and a new app called Customer Support Desk.
After creating the application, a new canvas will be displayed where you can build any user interface using the UI widgets on the right-hand side panel.
In this article, I'll guide you through building the user interface for the customer support application.
The application is divided into three pages - the Dashboard page, the Ticket Details page, and the Customers' Information page.
Click on the pages icon on the top left corner to create the additional pages.
The Dashboard Page
Here, you'll learn how to work with various UI widgets by building the dashboard page of the application.
To replicate this design, let's divide the page into sections.
From the image above, the dashboard is divided into three sections - the left-hand sidebar, the top menu (with a red border), and the main section.
For the left-hand side icons bar, ToolJet has a collection of icons you can pick from, and you can also change the background colour of the container when you select it.
The top menu bar comprises the Text and Container components provided by ToolJet. You can change their sizes and colour to match the image below.
The main section of the application contains the Tab and Table components provided by ToolJet. The Table component is the child element of the Tab menu.
The Ticket Details page
The Ticket Details page displays a ticket's content and enables you to respond to them.
To recreate the header component, use ToolJet's DropDown component, and the main body of the page contains the TextInput, Text Editor, Text, and Button components provided by ToolJet.
The Customers' Information Page
This page displays the information about your customers. You can recreate the design using the Text and Table components.
Congratulations! You've successfully built the user interface for the application. In the upcoming section, you'll learn how to make the application functional by connecting it to a PostgreSQL database.
Setting up an ElephantSQL (PostgreSQL) database
ElephantSQL enables us to create a PostgreSQL database on the cloud instead of your local machine. Follow the steps below to create a PostgreSQL database:
Create an ElephantSQL account here.
Add a new database instance. No credit card or billing information is required.
Once you've created the database instance, your database information is displayed.
Next, let's connect your ToolJet app to the database.
Connecting a PostgreSQL database to ToolJet
Select Global Datasources from the top-right menu bar on your ToolJet dashboard.
Select PostgreSQL from the list of databases under the Global Datasources panel, and provide the required information as shown below.
From the image above, the host is the same as the server name on ElephantSQL (excluding the brackets). The username and database name are the same, and copy and paste the password into its field.
Scroll down the page and click Test Connection.
If the connection is verified, we can start making queries to the database.
Congratulations, you've successfully created and connected your database to the application. You can now start making queries to the database.
How to communicate with a PostgreSQL database in ToolJet
In this section, you'll learn how to communicate with the PostgreSQL database and how to make database queries in ToolJet.
ToolJet allows us to communicate with external resources or create custom functions via a panel known as Query Panel. In ToolJet, any function that interacts with a database, API, or cloud storage and runs a JavaScript or Python code is called a Query.
Next, create the database tables and add some dummy data before querying the database. You need to create two tables, one for the contacts and the other for the support tickets.
Navigate to the browser section on your ElephantSQL database and execute the code snippet below.
CREATE TABLE tickets (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255),
email VARCHAR(255),
product_version VARCHAR(10),
issue_type VARCHAR(50),
description TEXT,
status VARCHAR(50),
priority VARCHAR(50),
assigned_to VARCHAR(100)
);
INSERT INTO tickets (name, email, product_version, issue_type, description, status, priority, assigned_to)
VALUES ('Chris Doe', 'john@example.com', '1.0', 'Bug', 'Encountered an error message when trying to save a file.', 'Open', 'High', 'Support Team');
INSERT INTO tickets (name, email, product_version, issue_type, description, status, priority, assigned_to)
VALUES ('John Doe', 'john@example.com', '1.0', 'Bug', 'Encountered an error message when trying to save a file.', 'Open', 'High', 'Support Team');
Finally, create the Contacts table and add some dummy data to the table.
CREATE TABLE contacts (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255),
email VARCHAR(255),
title VARCHAR(100),
organization VARCHAR(100),
country VARCHAR(100),
language VARCHAR(100)
);
INSERT INTO employees (name, email, title, organization, country, language)
VALUES ('John Doe', 'john@example.com', 'Manager', 'ToolJet', 'United States', 'English');
INSERT INTO employees (name, email, title, organization, country, language)
VALUES ('Sean Doe', 'sean@example.com', 'Manager', 'ToolJet', 'United States', 'English');
Querying the PostgreSQL database in ToolJet
To make the customer support application fully functional, you need to be able to fetch the data within the application. Therefore, create a query called getTickets that retrieves all the support tickets from the database.
Add the SQL query into the SQL mode editor, set the Run the query on application load? option to true, and save the query.
Next, set the value of the ticket's table to the result returned from the query.
You need to display the details of a support ticket when you click on each data row on the table.
To do this, you have to add a Switch Page event on the table and save the selected data row into a variable (to be accessed on the Ticket Details page).
Retrieve the selected row details using the {{variables.<variable_name>.values}} method provided by ToolJet. Therefore, when you click on any data on the table, it redirects you to the Ticket Details page, where you can see all the ticket information.
Next, create another PostgreSQL query that shows the number of tickets available according to their status.
Copy the code below into the SQL editor. It returns the status and number of tickets for each of them.
SELECT
status,
COUNT(*) AS ticket_count
FROM
tickets
WHERE
status IN ('Open', 'On Hold', 'Resolved')
GROUP BY
status;
Display the number of tickets for each status, as done below.
{{queries.ticketStatusCount.data[<index>].ticket_count}}
Lastly, create another query that retrieves the contacts from the database and displays them on the table within the Customers page.
Update the table's content as shown below.
Congratulations on making it thus far! In the upcoming section, you'll learn how to respond to the customer's queries via email using the SendInBlue API in ToolJet.
How to send emails with the SendInBlue API in ToolJet
Sendinblue is a digital marketing tool that provides Email, SMS, Facebook, Chat, and more, via one platform to help grow businesses by building stronger customer relationships.
In this section, you'll learn how to send emails via Sendinblue in ToolJet. First, you need to log in or create a Sendinblue account.
Select SMTP and API on your dashboard, generate an SMTP key, and copy it somewhere on your computer. You'll need it shortly.
Return to your ToolJet app, add a new SMTP data source, and fill in the required credentials. Your password is the generated SMTP key.
If successfully connected, it will display "Connection Verified". Then you can start sending emails.
Next, create the query for sending the emails. Provide your email and name, and retrieve the recipient's email, subject, and response from the Ticket Details page.
The query retrieves the data automatically from the components. Finally, execute the query when you click the Send button.
Congratulations! You've completed the application. Here is a working demo of the application: Customer Support Desk
You can also download its JSON file and import it into a ToolJet app, but you'll need to provide your Sendinblue and database credentials.
Conclusion
So far, you've learnt how to
- add a PostgreSQL database to ToolJet
- send emails within a ToolJet application using Sendinblue, and
- build full-stack applications in a few minutes with ToolJet.
ToolJet is both an intutive development tool and open-source - meaning its code is readily available for everyone to improve and contribute. It has a large community of developers and talented contributors constantly maintaining and improving the software. As a user, you can be sure of getting the best performance when you use ToolJet.
Are you interested in contributing to ToolJet? Feel free to check out our GitHub repo- https://github.com/ToolJet/ToolJet to contribute and raise issues about ToolJet.
Thank you for reading!
Top comments (2)
Can we connect ToolJet to other databases?
Yes, you can find more at: tooljet.com/integrations