Building a Customer Support Desk App with ToolJet and PostgreSQL
Tags: javascript, webdev, beginners, tutorial
Published: 2023-06-26 10:52:58

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](https://github.com/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](https://cloud.google.com/storage), [AWS S3](https://aws.amazon.com/pm/serv-s3), and [Minio](https://min.io/). 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](https://github.com/ToolJet/ToolJet)

![https://media.giphy.com/media/v1.Y2lkPTc5MGI3NjExZjlhNzRjMWJlYzgyNWZjYjcyOGMxYjJiYjQxOTQzYTU1NTg5YzAzNiZjdD1n/dfbMVqwq8GrC19xSEF/giphy.gif](https://media.giphy.com/media/v1.Y2lkPTc5MGI3NjExZjlhNzRjMWJlYzgyNWZjYjcyOGMxYjJiYjQxOTQzYTU1NTg5YzAzNiZjdD1n/dfbMVqwq8GrC19xSEF/giphy.gif)

## 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](https://app.tooljet.com/signup).

Create a Workspace and a new app called Customer Support Desk.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/kapz2hdfe0x9pbfwyu9i.png)


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.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/gpamqsntpaaiye6hdnib.png)

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.


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/c7lbo39kdr1kemmkrl16.png)


### The Dashboard Page

Here, you'll learn how to work with various UI widgets by building the dashboard page of the application.


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/k3ril2ebmrzjbe6iamm3.png)


To replicate this design, let's divide the page into sections.


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/orsen1feolvqn77k5sj7.png)



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.


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/37stzexlk1aus3hxgs1y.png)


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.


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/j9cvl1j3wbybaigessgv.png)


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.


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/iz2vcehv42s1t86vru9x.png)



### The Ticket Details page

The Ticket Details page displays a ticket's content and enables you to respond to them.


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/iy0l7tmh0k56ib79nucs.png)


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.


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/tnxdnxi8tsotii3p46f9.png)


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](https://www.elephantsql.com/) 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](https://customer.elephantsql.com/login).

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.


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/cin4ixt1cryn45ldja5h.png)

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.


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/xt4f1m2p6h57bxef1rs9.png)


Select PostgreSQL from the list of databases under the Global Datasources panel, and provide the required information as shown below.


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/u2h24hdejmdbr2lz2pa7.png)


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.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/d5y0cukj9sssnkf8irlm.jpeg)

Scroll down the page and click `Test Connection`.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/butd23yivp1z0muqvpk0.png)

If the connection is verified, we can start making queries to the database.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/p7wkpzi7nbkeqj8ah5cd.png)

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](https://docs.tooljet.com/docs/app-builder/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.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/vww8zc5cm5va9lfml6vf.png)

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.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/hqpplglemlfbndihukob.jpeg)

```sql
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.

```sql
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.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/gue4yysv5lhda02bxten.png)

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.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/m4gvvdhfaaapg5ljp7ea.png)


You need to display the details of a support ticket when you click on each data row on the table.


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/h30fbcki5uei5ce8bkwf.gif)


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

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/mkkksav52wmhmjw7jdqb.png)

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/m15mbzrj03grhrfj9djy.png)

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.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/mnuqen1fa5d6z7tpmn61.png)

Next, create another PostgreSQL query that shows the number of tickets available according to their status.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/5tyceeh91mq7z5763225.png)

Copy the code below into the SQL editor. It returns the status and number of tickets for each of them.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/tyz50lksse7ojtjo10n8.png)

```sql
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.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/zi6rgxuilvvn9hxmvg6d.png)

```
{{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.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/o8yrlmj5tnw8cckbs63t.png)

Update the table's content as shown below.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ajw4wboxlzuoni7lfk3q.png)

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

Select SMTP and API on your dashboard, generate an SMTP key, and copy it somewhere on your computer. You'll need it shortly.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/hwunzhymvszu5dovntem.png)

Return to your ToolJet app, add a new SMTP data source, and fill in the required credentials. Your password is the generated SMTP key.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/cv0yzl8i2pbkol4w2z16.png)

If successfully connected, it will display "Connection Verified". Then you can start sending emails.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/bu4vnvkcxylb39ujbdyj.png)

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.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/0oi8li77mj54gibrjhvg.png)

The query retrieves the data automatically from the components. Finally, execute the query when you click the Send button.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/fr7o0fcd11ec2etcjnij.png)

Congratulations! You've completed the application. Here is a working demo of the application: [Customer Support Desk](https://app.tooljet.com/applications/867240f3-eb26-45c8-ba1d-d94f004925e9)

You can also download its [JSON file](https://tooljet.com) 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](https://github.com/ToolJet/ToolJet) to contribute and raise issues about ToolJet.

Thank you for reading!