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). 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 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 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
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.
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.
Create a Workspace and a new app called Newsletter Manager.
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.
On the home page, you can view all the available newsletters and navigate to the SubscribersManager and CreateNewsletter pages.
The ViewNewsletter page displays the details of a published newsletter.
The CreateNewsletter page enables us to create a newsletter as a draft or publish it by sending it to a group of users.
The SubscribersManager page displays existing subscribers and allows you to add a new subscriber to the list.
The Home page
Here, you'll learn how to build the user interface below.
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.
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.
{{[
{ 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.
Next, add an on click event on the Subscribers button to navigate users to the SubscribersManager page.
Finally, add an on click event on the Create button that redirects users to the Create Newsletter page.
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.
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.
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.
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.
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.
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.
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. In ToolJet, any function that interacts with a database, API, or cloud storage and runs a JavaScript or Python code is called a Query.
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.
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.
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.
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.
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.
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.
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.
Finally, update the the Table data with the data returned from the query.
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.
Therefore, you need to execute a createSubscriber query when you click the Add button.
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.
Getting newsletters saved as drafts
Create a PostgreSQL query that retrieves all the newsletters saved as drafts.
Enable the Transformations below the SQL editor. It enables us to modify the data returned from the query.
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.
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}}.
Getting published newsletters
Create a similar query to the getDrafts query, which will return only newsletters whose published attribute is true.
SELECT *
FROM Letters
WHERE published = TRUE;
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.
INSERT INTO Letters(title, content, email_group, published, published_date) VALUES('{{components.title.value}}', '{{components.content.value}}', '{{components.group.value}}', false, '');
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.
Saving published newsletters
Add another query called savePublished that accepts the values from the input field and saves it as a published newsletter.
INSERT INTO Letters(title, content, email_group, published, published_date) VALUES('{{components.title.value}}', '{{components.content.value}}', '{{components.group.value}}', true, CURRENT_DATE);
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.
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.
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.
The variable - selectedListviewId contains the selected item's index. Therefore, you can get the item's ID from the data returned from its query.
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.
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.
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.
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.
Click Personal on the navigation bar and select View API keys from the menu bar to create a new secret key.
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”.
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.
How to send emails via 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 integrate and 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, name, the recipient's email, subject, and title components.
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.
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
You can also download its JSON file 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 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 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 (0)