Introduction
As we continue our journey to build a robust email marketing engine, we will focus in Part 2 on integrating SQLite as a database. This powerful addition will allow us to import and manage contact data directly from CSV files exported from SendGrid. Consequently, we'll be able to streamline our operations and bypass the cost of SendGrid's storage fees.
This tutorial will also demonstrate how to leverage SendGrid's dynamic templates. These templates enhance our capability to create highly personalized and sophisticated emails for our marketing campaigns. We won't need to grapple with HTML ourselves; SendGrid has it covered.
By the end of this segment, you'll be able to perform the following:
- Upload SendGrid CSV exports to our application
- Store this data in a SQLite database
- Send dynamic email templates using SendGrid's template ID to all the contacts in the database
Let's not forget that our project is hosted and deployed via Codesphere. And the cherry on the top? It's a quick 3-minute process from local setup to live deployment. However, if you prefer to skip the coding part and explore the completed version of our Email Marketing Engine, click here to get started. You'll need to set your API key in an environment variable (i.e., SENDGRID_API_KEY="your_key"), install the necessary dependencies with npm install, and get the engine running with npm start. No other configuration needed!
Ready to dive in? Let's build!
Creating the CSV Upload Form on the Frontend
SendGrid allows exporting contact lists as .csv files. We'll start by integrating an upload form on our website that accepts .csv files and saves them to our server. This form is distinct from the one we use to send emails, given their disparate functionalities.
The following is the code of our new form. Keep in mind the name property of our form input, which will be used to handle the writing of the file to our server using multer.
<div class="container mx-auto mt-8">
<form id="dbForm" enctype="multipart/form-data" method="post">
<div class="space-y-12">
<div class="border-b border-gray-900/10 pb-12">
<h1 class="mb-4 text-4xl font-extrabold text-gray-900">Add Contacts to Database</h1>
<div class="col-span-full">
<label for="contacts-upload" class="block text-sm font-medium leading-6 text-gray-900">Upload
Contacts</label>
<div id="drag-drop-area"
class="mt-2 flex justify-center rounded-lg border border-dashed border-gray-900/25 px-6 py-10">
<div class="text-center">
<svg class="mx-auto h-12 w-12 text-gray-300" viewBox="0 0 24 24" fill="currentColor" aria-hidden="true">
<path fill-rule="evenodd"
d="M1.5 6a2.25 2.25 0 012.25-2.25h16.5A2.25 2.25 0 0122.5 6v12a2.25 2.25 0 01-2.25 2.25H3.75A2.25 2.25 0 011.5 18V6zM3 16.06V18c0 .414.336.75.75.75h16.5A.75.75 0 0021 18v-1.94l-2.69-2.689a1.5 1.5 0 00-2.12 0l-.88.879.97.97a.75.75 0 11-1.06 1.06l-5.16-5.159a1.5 1.5 0 00-2.12 0L3 16.061zm10.125-7.81a1.125 1.125 0 112.25 0 1.125 1.125 0 01-2.25 0z"
clip-rule="evenodd" />
</svg>
<div id="selected-file" class="mt-4 flex text-sm leading-6 text-gray-600">
<label for="contacts-upload"
class="relative cursor-pointer rounded-md bg-white font-semibold text-indigo-600 focus-within:outline-none focus-within:ring-2 focus-within:ring-indigo-600 focus-within:ring-offset-2 hover:text-indigo-500">
<span>Upload a file</span>
<input id="contacts-upload" name="contacts-upload" type="file" class="sr-only">
</label>
<p class="pl-1">or drag and drop</p>
</div>
<p class="text-xs leading-5 text-gray-600">.CSV up to 10MB</p>
</div>
</div>
</div>
<div class="container mx-auto mt-6 flex items-center justify-start">
<button type="submit"
class="rounded-md bg-indigo-600 px-3 py-2 text-sm font-semibold text-white shadow-sm hover:bg-indigo-500 focus-visible:outline focus-visible:outline-2 focus-visible:outline-offset-2 focus-visible:outline-indigo-600">Upload
Contacts</button>
</div>
</div>
</div>
</form>
</div>
This is pretty straight forward form, created with TailwindCSS.
We then need some JS to handle our upload and form submit, show the user once a file has been added or if the selected file doesn't match the csv format.
The JS will be stored in a file called handleCsvUpload.js. Which will be implemented to out HTML via a simple script tag:
<script src="./handleCsvUpload.js" type="module"></script>
The form submit will trigger the upload by sending a fetch request with the formData that includes the csv to a /upload server endpoint we will be creating in the next step.
// Get the necessary elements
const dragDropArea = document.getElementById('drag-drop-area');
const selectedFile = document.getElementById('selected-file');
const fileInput = document.getElementById('contacts-upload');
// Handle drag and drop events
dragDropArea.addEventListener('dragover', (e) => {
e.preventDefault();
dragDropArea.classList.add('border-blue-500');
});
dragDropArea.addEventListener('dragleave', () => {
dragDropArea.classList.remove('border-blue-500');
});
dragDropArea.addEventListener('drop', (e) => {
e.preventDefault();
dragDropArea.classList.remove('border-blue-500');
const file = e.dataTransfer.files[0];
displaySelectedFile(file);
});
// Handle file input change event
fileInput.addEventListener('change', () => {
const file = fileInput.files[0];
displaySelectedFile(file);
});
// Display the selected file name
function displaySelectedFile(file) {
if (file) {
selectedFile.innerText = file.name;
} else {
selectedFile.innerText = 'Upload a file';
}
}
// Handle form submit event
const form = document.getElementById('dbForm');
form.addEventListener('submit', (e) => {
e.preventDefault();
const file = fileInput.files[0];
if (file) {
uploadFile(file);
} else {
alert('Please select a file.');
}
});
// Upload the file
function uploadFile(file) {
// Check if the file is a CSV
if (file.type !== 'text/csv') {
alert('Please select a CSV file.');
return;
}
const formData = new FormData();
formData.append('contacts-upload', file);
// Make a POST request to the server/upload endpoint
fetch('/upload', {
method: 'POST',
body: formData
})
.then(response => {
if (response.ok) {
// File uploaded successfully
// Perform any additional actions if needed
console.log('File uploaded successfully.');
} else {
// Error uploading file
console.log("formData",formData);
console.error('Failed to upload file.');
}
})
.catch(error => {
console.error('An error occurred while uploading the file:', error);
});
}
Creating a new endpoint for uploading files
As stated earlier, we handle the upload through the new /upload endpoint we create in the main server file index.js which we created in part 1. With Express, this setup is super easy and straight forward.
Our new endpoint will essentially only do one thing - call the uploadToDb() function and pass it the path to the file sent on form submit.
const { uploadToDb } = require('./server/uploadToDb');
app.post('/upload', upload.single('contacts-upload'), (req, res) => {
uploadToDb(req.file.path);
res.send('File uploaded successfully');
});
Creating a database
To create a database, we can either install one of the many SQL/SQLite extensions available for VSCode (our your IDE of choice) which give you the possibility to create databases in a more visually appealing and user-friendly way. Alternatively, you could create a database by creating a table from your index.js.
What's important to note here is that for our primary key, we create the id column which has the format of INTEGER. We do this so that when we add data to our database, we do not have to fill the id using JS but rather it counts up automatically.
Note that this way of assigning an ID does not define actually unique IDs. Once you remove an entry, the ID can be redistributed to another row.
sql = "CREATE TABLE contacts(id INTEGER PRIMARY KEY, sendgrid_contact_id, email TEXT, first_name TEXT, last_name TEXT)"
db.run(sql, (err) => {
if (err) {
return console.error(err.message);
}
console.log('Table created.');
});
In this case, we create a table with the fields id, sendgrid_contact_id, email, first_name and last_name. Those are the fields we track for our SendGrid contacts. You might have to adjust this to fit your exported SendGrid data.
SendGrid csv export example
Processing Data and Saving it to the Database
Our uploadToDb.js file, where the uploadToDb() function is found does the heavy lifting of processing and storing our data. To accomplish this task, we are using the fs (filesystem), csv-parser, and sqlite3 modules.
Let's have a closer look.
The function begins by opening a connection to an SQLite database located at ./server/data/contacts.db. Next, it reads the CSV file located at the provided filePath, parsing each row of data using the csv-parser module. For each row, the function extracts the EMAIL, FIRST_NAME, LAST_NAME, and CONTACT_ID fields that are exported from SendGrid and inserts them into the contacts table in the SQLite database. The function executes an SQL insert statement for each row of data, logging any errors encountered along the way. Once all rows have been processed, the function closes the database connection and removes the CSV file from the filesystem. Thus, this code automates the process of loading contact information from a CSV file into an SQLite database.
// Import necessary modules
const fs = require('fs'); // filesystem module for handling file operations
const csv = require('csv-parser'); // csv-parser module to parse csv files
const sqlite3 = require('sqlite3').verbose(); // SQLite database driver with verbose logging
// Define the function uploadToDb to upload csv data to the SQLite database
function uploadToDb(filePath) {
// Open the SQLite database
let db = new sqlite3.Database('./server/data/contacts.db', sqlite3.OPEN_READWRITE, (err) => {
if (err) {
console.error(err.message); // Log error message if any
}
console.log('Connected to the database.'); // Log success message
});
// Prepare the SQL insert statement
let sql = "INSERT INTO contacts (sendgrid_contact_id, email, first_name, last_name) VALUES (?, ?, ?, ?)";
// Create a readable stream from the csv file
fs.createReadStream(filePath)
.pipe(csv()) // Parse the csv data
.on('data', (row) => { // Handle each row of data
// Access the fields by their names in the CSV
let email = row['EMAIL'];
let firstName = row['FIRST_NAME'];
let lastName = row['LAST_NAME'];
let contactID = row['CONTACT_ID'];
// Run the SQL insert statement for each row of data
db.run(sql, [contactID, email, firstName, lastName], function (err) {
if (err) {
return console.log(err.message); // Log error message if any
}
console.log(`A row has been inserted with rowid ${this.lastID}`); // Log success message
});
})
.on('end', () => { // Handle the end of the csv file
console.log('CSV file successfully processed');
// Close the SQLite database
db.close((err) => {
if (err) {
console.error(err.message); // Log error message if any
}
console.log('Close the database connection.'); // Log success message
});
// Remove the csv file after processing
fs.unlink(filePath, (err) => {
if (err) {
console.error(err); // Log error message if any
return;
}
console.log('File removed'); // Log success message
});
});
}
// Export the function for use in other modules
module.exports = { uploadToDb }
That's it!
Once you upload your file, your contacts.db should look similar to this:
As you can see, the id filled in automatically without having the need to handle this in our JS code.
The only thing left to do is to add the possibility to upload templates!
Sending SendGrid templates
We wanted to add the possibility to send SendGrid templates so we could leverage the visual SendGrid template builder without having to worry about writing our email HTML by hand. Luckily, each Dynamic Template in SendGrid has its own unique ID which can be used as the content for an email sent by the API.
The implementation of this couldn't be easier. The only thing needing adjustments compared to the initial version would be the html form and the sendEmails() we created in part 1.
The new form will have only one input for the template ID and will automatically send the prefilled template to all contacts in our database.
To adjust the HTML, all we need to do is remove the text field we previously had in our email form and rename the list-id input to template-id, leaving us with this code for the first email form:
<div class="container mx-auto mt-8">
<form id="emailForm">
<div class="space-y-12">
<div class="border-b border-gray-900/10 pb-12">
<h1 class="mb-4 text-4xl font-extrabold text-gray-900">Configure your Email</h1>
<div class="sm:col-span-4">
<label for="username" class="block text-sm font-medium leading-6 text-gray-900">Template ID</label>
<div class="mt-2">
<div
class="flex rounded-md shadow-sm ring-1 ring-inset ring-gray-300 focus-within:ring-2 focus-within:ring-inset focus-within:ring-indigo-600 sm:max-w-md">
<input type="text" name="username" id="template-id" autocomplete="username"
class="block flex-1 border-0 bg-transparent py-1.5 pl-2 text-gray-900 placeholder:text-gray-400 focus:ring-0 sm:text-sm sm:leading-6"
placeholder="d-584d994488ff4e19988efd313bebfed9">
</div>
</div>
</div>
<div class="container mx-auto mt-6 flex items-center justify-start">
<button type="submit"
class="rounded-md bg-indigo-600 px-3 py-2 text-sm font-semibold text-white shadow-sm hover:bg-indigo-500 focus-visible:outline focus-visible:outline-2 focus-visible:outline-offset-2 focus-visible:outline-indigo-600">Send
Emails</button>
<p id="error-message" class="mt-3 text-sm leading-6 text-red-600 hidden">Request failed.</p>
<div role="status">
<svg aria-hidden="true" id='spinner'
class="hidden w-8 h-8 mr-2 text-gray-200 animate-spin dark:text-gray-600 fill-blue-600"
viewBox="0 0 100 101" fill="none" xmlns="http://www.w3.org/2000/svg">
<path
d="M100 50.5908C100 78.2051 77.6142 100.591 50 100.591C22.3858 100.591 0 78.2051 0 50.5908C0 22.9766 22.3858 0.59082 50 0.59082C77.6142 0.59082 100 22.9766 100 50.5908ZM9.08144 50.5908C9.08144 73.1895 27.4013 91.5094 50 91.5094C72.5987 91.5094 90.9186 73.1895 90.9186 50.5908C90.9186 27.9921 72.5987 9.67226 50 9.67226C27.4013 9.67226 9.08144 27.9921 9.08144 50.5908Z"
fill="currentColor" />
<path
d="M93.9676 39.0409C96.393 38.4038 97.8624 35.9116 97.0079 33.5539C95.2932 28.8227 92.871 24.3692 89.8167 20.348C85.8452 15.1192 80.8826 10.7238 75.2124 7.41289C69.5422 4.10194 63.2754 1.94025 56.7698 1.05124C51.7666 0.367541 46.6976 0.446843 41.7345 1.27873C39.2613 1.69328 37.813 4.19778 38.4501 6.62326C39.0873 9.04874 41.5694 10.4717 44.0505 10.1071C47.8511 9.54855 51.7191 9.52689 55.5402 10.0491C60.8642 10.7766 65.9928 12.5457 70.6331 15.2552C75.2735 17.9648 79.3347 21.5619 82.5849 25.841C84.9175 28.9121 86.7997 32.2913 88.1811 35.8758C89.083 38.2158 91.5421 39.6781 93.9676 39.0409Z"
fill="currentFill" />
</svg>
<span class="sr-only">Loading...</span>
</div>
</div>
</div>
</form>
</div>
Since we don't have to fetch the contact data from the SendGrid server ourselves now, we can get rid of all the API handling in our api.js except for the sendEmails() function where all we need to do is take the templateId as an argument and pass it to our msg object.
const sendEmails = async (emailAddresses, templateId) => {
let personalizationsArray = emailAddresses.map(email => {
return { to: [{ email: email }] };
});
const msg = {
from: 'support@codesphere.com',
subject: 'Hello from Codesphere',
template_id: templateId,
// html: `<p>${emailText}</p>`,
personalizations: personalizationsArray,
};
try {
await sgMail.send(msg);
console.log('Emails sent successfully');
} catch (error) {
console.error('Error sending emails:', error);
throw error;
}
};
Then, we adjust our /contacts endpoint only call sendEmails() using the template id instead of handling all the API requests to the SendGrid server:
app.post("/contacts", async (req, res) => {
console.log(req.body);
const { templateId } = req.body;
try {
const emailGroups = await processContacts(99);
// Send emails to each group
for (let group of emailGroups) {
await sendEmails(group, templateId);
}
res.json(emailGroups);
} catch (error) {
res.status(500).json({ error: "An error occurred while fetching contacts" });
}
});
Lastly, we adjust our main.js which is inserted to our html via a script tag to handle the form submission and use the template id so send in the formData object to the server:
document.getElementById("emailForm").addEventListener("submit", function (event) {
event.preventDefault();
console.log("submit logged");
// const listId = document.getElementById('list-id').value
const templateId = document.getElementById('template-id').value
const formData = {
// listId: listId,
templateId: templateId
}
document.getElementById('spinner').style.display = 'block';
fetch('/contacts', {
method: "POST",
headers: {
'Content-Type': 'application/json',
},
body: JSON.stringify(formData)
})
.then(response => {
document.getElementById('spinner').style.display = 'none';
if (response.ok) {
window.location.href = "/success.html";
}
})
.catch(error => {
console.error('Error:', error);
const errorMessageElement = document.getElementById('error-message');
errorMessageElement.innerText = 'Sending failed: ' + error.message;
errorMessageElement.style.display = 'block';
document.getElementById('spinner').style.display = 'none';
});
})
Conclusion
In this part, we've substantially improved our email marketing engine with a SQLite database and SendGrid's dynamic templates. These enhancements make our system more potent and efficient. With these foundational elements in place, we're eager to move forward with additional improvements in our following tutorials. Check out the current status of our Email Marketing Engine on GitHub or host it for free on Codesphere.
You can check out the latest status of our Email Marketing Engine here on Github or just host it for free on Codesphere by clicking here. Just define your CI pipeline (npm install, npm start) and env variables ( SENDGRID_API_KEY="your_key")
Stay tuned for Part 3 of our series and happy coding!
Top comments (0)