Building an efficient email marketing application with ChatGPT, ToolJet, and PostgreSQL
Tags: webdev, javascript, chatgpt, lowcode
Published: 2023-06-06 11:44:47

> In this tutorial, you'll learn how to build an email marketing application using ToolJet, ChatGPT, and PostgreSQL.

The application allows you to add subscribers to your mailing list, group them into categories, and send them newsletters using [Brevo (formerly Sendinblue)](https://www.brevo.com/). We'll also use ChatGPT for generating email content. All these features are very easy to integrate with the help of ToolJet.

## 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 need your help? 😔**
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)

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

![01](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/isu5h2gyiw3gey077bvk.png)

Congratulations! You've successfully created the database needed for this application. Next, let's design the application interface and connect the database to the application.

## Building the application with ToolJet

Here, you'll learn how to create a ToolJet account and build a fully functional application with ToolJet.

If you are new to ToolJet, [create an account](https://app.tooljet.com/signup).

Create a Workspace and a new app called Newsletter Manager.


![02](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/i21wsyg9lfjf6sbzwtsl.png)

The application is divided into four pages - the home page, the `ViewNewsletter` page, the `CreateNewsletter` page, and the `SubscribersManager` page. Click on the pages icon on the top left corner to create the three additional pages.


![03](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/8o1bp1i08gt2qufzovoc.png)


![04](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/tzrwkuw5g0dspy5rcrv5.png)

On the home page, you can view all the available newsletters and navigate to the `SubscribersManager` and `CreateNewsletter` pages.

![05](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/aqvgvqzv40ppzhvo84le.png)

The `ViewNewsletter` page displays the details of a published newsletter.

![06](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/wrc1t5x6r6s6jkz0elpz.png)

The `CreateNewsletter` page enables us to create a newsletter as a draft or publish it by sending it to a group of users.

![07](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/odu566aeop9ja2gl9y8o.png)

The `SubscribersManager` page displays existing subscribers and allows you to add a new subscriber to the list.

![08](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/py6wvp407c80dz4n1x0d.png)

### The Home page

Here, you'll learn how to build the user interface below.

![09](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/0g2c4r8uzc9ugav2f7pm.png)

To create the UI above, you need to add a container element on the canvas that will house all the other UI elements of the application.
Next, drag the Text and Button component from the panel on the right into the container element to create the title and buttons.

![10](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/sgwytmhm53a8p179tlpt.png)

In the image above, I added a Tab component below the button and text components. Next, edit the Tab component to contain only the Published and Drafts tab menu by copying the code snippet below into the Tabs input at the right-hand side of the screen.

```jsx
{{[
		{ title: 'Published', id: '0' },
		{ title: 'Drafts', id: '1' }
 ]}}
```

Remove the ToolJet image at the centre of the list and insert the `ListView` component into the body of the Tab component. Adjust it until it becomes similar to this.

![11](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/833t7ucsm8y74602xqk3.png)

Next, add an `on click` event on the Subscribers button to navigate users to the `SubscribersManager` page.

![12](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/42m16sft3ssc0i526ni5.png)

Finally, add an `on click` event on the Create button that redirects users to the `Create Newsletter` page.

![13](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/luoc5ea7svdnh6l3a4jd.png)

### The View newsletter page

Change the current page to the `ViewNewsletter` page, place a large container on the canvas, and create a UI similar to the image below.

![14](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/7hq2kb6ophloro6fhtr4.png)

There are three text components - the application's name, title and content, and the Back button. The Title and content texts are placeholders for the title and content of each newsletter. This page allows us to view the contents of any published newsletter.

Finally, add an `on click` event on the Back button to redirect users to the Home page.

![15](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/atgs1umgnxlbr069trgj.png)

### The Create newsletter page

This page enables you to save and publish a newsletter. It accepts the newsletter's title, email group, and content. You can also use AI-generated content with ChatGPT by providing accurate prompts.

![16](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/j6tjj0v02pdbcwqbo7kl.png)

The `Save to Draft` and `Send` buttons allow us to save the newsletter or send it to the users' email.

Finally, redirect users to the home when they click the Back button.

### The Subscribers manager page

On this page, you can view all the existing subscribers and add a new subscriber to the list.

![addSubscriber](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/xpmu6tzrpebvukx7qkgc.gif)

Design the page as shown below. We have the Title and Button components at the top of the page and the Table component displaying the list of subscribers.

![17](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/d5mijukli9v31vz3zt00.png)

Then, add an `on click` event on the Add button such that it displays a Modal component that allows you to add the subscriber's details.

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

Congratulations! You've successfully built the user interface for the application. Next, let's add the required functionalities to the application.

## How to communicate with a PostgreSQL database 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.

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

Here, you'll learn how to communicate with a PostgreSQL database in ToolJet. First, let me walk you through connecting a PostgreSQL database to ToolJet.

### Connecting the PostgreSQL database to ToolJet

Select Global Datasources from the top-right menu bar on your ToolJet dashboard.

![20 description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/36fzgt6pb7q8i1cps65j.png)

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

![21 description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/prbrwk5kl17y8gyz901u.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.

![22 description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/91e7e2werb5n2ts8lo0a.jpeg)

Scroll down the page and click `Test Connection`.

![23-test-connection.png](Building%20an%20efficient%20email%20marketing%20application%20%20a002a57601224c77bb03e64110b0a58e/23-test-connection.png)

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

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

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

### Setting up the database structure

You need to create two database tables, one for the Subscribers and another for the Newsletters. 

Both the newsletter and subscriber table will have an `email_group` property to enable us to identify subscribers who belong to a group and send emails to only subscribers within a group.

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

Therefore, let's create the database tables and add some dummy data. Navigate to the browser section on your ElephantSQL database and execute the code snippet below.

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

```sql
CREATE TABLE Letters(
    id SERIAL PRIMARY KEY,
    title VARCHAR(255),
    content TEXT,
    email_group VARCHAR(255),
    published BOOLEAN, published_date VARCHAR(255));

INSERT INTO Letters(title, content, email_group, published, published_date) VALUES('ToolJet v3.0 is live', 'ToolJet is an open-source low-code framework that enables us to build full-stack web applications within a few minutes.',  'one', false, '');

INSERT INTO Letters(title, content, email_group, published, published_date) VALUES('Team Community Call is live', 'ToolJet is an open-source low-code framework that enables us to build full-stack web applications within a few minutes.',  'two', true,'01-05-2023');

INSERT INTO Letters(title, content, email_group, published, published_date) VALUES('Invitation to product launch', 'It is an excellent development tool helping individuals, developers, and businesses create and ship products faster.',  'three', true, '01-05-2023');
```

The code snippet above creates the `Letters` table on the database and adds three data entries: two are published, and the last one is a draft.

Finally, create the Subscribers table.

```sql
CREATE TABLE Subscribers (
    name VARCHAR(255),
    email VARCHAR(255),
    organisation VARCHAR(255),
    email_group VARCHAR(255),
);

INSERT INTO Subscribers(name, email, organisation, email_group) VALUES('Jack', 'jack@gmail.com', 'ToolJet',  'one');
INSERT INTO Subscribers(name, email, organisation, email_group) VALUES('Teja', 'teja@gmail.com', 'ToolJet',  'two');
INSERT INTO Subscribers(name, email, organisation, email_group) VALUES('Badri', 'badri@gmail.com', 'ToolJet',  'three');
```

## Querying the PostgreSQL database in ToolJet

To create the email marketing application, you need to create some database queries.

- Get all the subscribers.
- Add a new subscriber to the list.
- Get newsletters saved as drafts.
- Get published newsletters.
- Save a newsletter as draft.
- Save a published newsletter
- Get all the details of a published newsletter.

### Getting all available subscribers

On the `SubscriberManager` page we need to display the list of subscribers from the database on the table as shown below.

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

Therefore, create a new query on the Postgres database called `getSubscribers` and retrieve all the data from the Subscribers table. Ensure you set the `Run this query on application load` toggle to true.

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

Finally, update the the Table data with the data returned from the query.

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

### Adding a new subscriber to the list

On the `SubscribersManager` page, there is a `Add` button. When you click on the button, it displays a modal that enables you to add the various details related to a subscriber.

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

Therefore, you need to execute a `createSubscriber` query when you click the Add button.

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

```sql
INSERT INTO Subscribers(name, email, organisation, email_group) VALUES('{{components.subscriberName.value}}', '{{components.subscriberEmail.value}}', '{{components.subscriberOrg.value}}',  '{{components.subscriberGroup.value}}');
```

The code snippet above access the value of the input components on the modal. The `components` object allows you to access every component's properties.

Finally, let's run the query when a user clicks the Add button and closes the modal immediately.

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

### Getting newsletters saved as drafts

Create a PostgreSQL query that retrieves all the newsletters saved as drafts.

![33 description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/2eddjfwhxoegfz8f8vag.png)

Enable the Transformations below the SQL editor. It enables us to modify the data returned from the query.

```jsx
const imageURL = "https://www.svgrepo.com/show/34217/image.svg";

return data.map((obj) => {
	return { imageURL, text: obj.title, group: obj.email_group };
});
```

Update the Draft tab to display the data returned from the `getDrafts` query.

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

Each element on the List View displays the content using the `listItem` property. Therefore, to access each data property, use `listItem.<propertyName>` - `{{listItem.text}}` and `{{listItem.group}}`.

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

### Getting published newsletters

Create a similar query to the `getDrafts` query, which will return only newsletters whose published attribute is true.

```sql
SELECT *
FROM Letters
WHERE published = TRUE;
```

![36 description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/8xmtfcuh0j38r76hr9c7.png)

```jsx
const imageURL = "https://www.svgrepo.com/show/34217/image.svg";

return data.map((obj) => {
	return {
		imageURL,
		text: obj.title,
		date: obj.published_date,
		group: obj.email_group,
		id: obj.id,
	};
});
```

The code snippet above returns an image URL, text (title), date, group, and id of the published letters.

Display all the published newsletters in the list view. Recall you need to use the `listItem.<property>` method to access each attribute returned from the query.

### Saving newsletters as draft

Create another query that accepts the values from the `createNewsletter` page and saves the newsletter as a draft.

```sql
INSERT INTO Letters(title, content, email_group, published, published_date) VALUES('{{components.title.value}}', '{{components.content.value}}', '{{components.group.value}}', false, '');
```

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

The query is executed when a user clicks the `Save to Drafts` button in the image below. You can redirect the user to the home page after saving the newsletter.

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

### Saving published newsletters

Add another query called `savePublished` that accepts the values from the input field and saves it as a published newsletter.

```sql
INSERT INTO Letters(title, content, email_group, published, published_date) VALUES('{{components.title.value}}', '{{components.content.value}}', '{{components.group.value}}', true, CURRENT_DATE);
```

![39 description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/8i3zds8x1ptb6wi8jcnf.png)

Run the query when a user clicks on the Send button. In the upcoming section, after saving the published newsletter, we'll also send it to the subscribers in that email group.

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

### Getting the details of a published newsletter

We have a page called -`viewNewsletter` that is supposed to display the title and content of a published post when a user clicks on them.

![41 description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/0g5z9egtn9ljnc0hovtc.gif)

To do this, you need to get the id of the selected newsletter and query the `Letters` database using the id.

First, add the `set Variable` action on each row of the list view. When you click on each list item, it sets the variable's value to its index in the list view.

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

The variable - `selectedListviewId` contains the selected item's index. Therefore, you can get the item's ID from the data returned from its query.

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

```sql
SELECT *
FROM Letters
WHERE id = {{queries.getPublished.data[variables.selectedListviewId].id}}
```

The code snippet above accesses the `variables` object and gets the selected item's id using its index. Then, you can filter the Letters database using the id.

Since we only need the title and content attribute of the newsletter. Therefore, you can update the data returned with only the required data.

```jsx
return data.map((item) => {
	return { title: item.title, content: item.content };
});
```

Next, run the query when a user clicks on each item of the List View.

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

Finally, redirect the user to the `View Newsletter` page when they click on each item in the `List View` to enable them to view the title and content of the newsletter.

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

## How to communicate with ChatGPT in ToolJet

Here, you'll learn how to communicate with ChatGPT via OpenAI in ToolJet by accepting prompts and generating email contents.

### Setting up an OpenAI account

Log in or create an OpenAI account [here](https://openai.com/product).

Click `Personal` on the navigation bar and select `View API keys` from the menu bar to create a new secret key.

![05-openai-dashboard.png](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/1x5qracbk52q7ielfj1m.png)

Generate a new API key and copy it somewhere on your computer. We'll use it in the upcoming section.

### Communicating with the OpenAI API in ToolJet

Under the Global Datasources tab on your dashboard, click Plugins, and select OpenAI. Then, paste your API key and organization ID into the input fields and test the connection. 

You can now access the OpenAI data source from the query panel.

> If you don’t have an organization ID, use “Personal”.


![46 description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/6rj2w1iervux3pf3dadv.png)

Select OpenAI from the Query Panel and set its content to draft an email using ChatGPT.

```
Draft a newsletter about {{components.prompt.value}}
```

Run the query when a user clicks the Ask button on the `createNewsLetter` page.

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

## **How to send emails via the Sendinblue API in ToolJet**

[Sendinblue](https://www.sendinblue.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 integrate and send emails via Sendinblue in ToolJet. First, you need to log in or [create a SendinBlue account](https://www.sendinblue.com/).

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

![28 description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/kz6446lsul71iz4bod6m.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.

![29-smtp-datasource.png](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/91mmx1nk9jo466avm3p0.png)

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

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

Next, create the query for sending the emails. Provide your email, name, the recipient's email, subject, and title components.

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

From the image above, the email recipient is the data returned from another query - `{{queries.querySubscriber.data}}`. Since you need to send the newsletter to a particular group of subscribers when you select a group on the `createNewsLetter` page. Therefore, the `querySubscriber` query fetches the list of subscribers under a particular group.

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

The image above returns an array of the subscribers’ email related to the selected group. 

Congratulations! You’ve completed this project. Here is a working demo of the application:[Toolbus newsletter manager](https://app.tooljet.com/applications/toolbus-newsletter-manager-app)

You can also [download its JSON file](https://github.com/withshubh/toolbus-newsletter-manager) and import it into a ToolJet app, but you'll need to provide your Sendinblue credentials and OpenAI API key.

## Conclusion

So far, you've learnt how to

- add a PostgreSQL database to ToolJet
- send emails within a ToolJet application using Sendinblue,
- communicate with ChatGPT in ToolJet, and
- build full-stack applications in a few minutes with ToolJet.

[ToolJet](https://github.com/ToolJet/ToolJet) is both an excellent development tool and open-source - meaning its code is readily available for everyone to modify and improve. It has a [large community of developers](https://tooljet.com/slack) 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!