Building an Email Marketing Engine: Express.js & SendGrid Part 2
Published: 2023-07-17 16:37:51

---
title: Building an Email Marketing Engine: Express.js & SendGrid Part 2
published: true
date: 2023-07-17 13:41:12 UTC
tags: 
canonical_url: 
---

##   
 ![Building an Email Marketing Engine: Express.js & SendGrid Part 2](https://codesphere.ghost.io/content/images/2023/07/Frame-657.jpg)

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

1. Upload SendGrid CSV exports to our application
2. Store this data in a SQLite database
3. 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.

![Building an Email Marketing Engine: Express.js & SendGrid Part 2](https://codesphere.ghost.io/content/images/2023/07/image-3.png)

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

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

![Building an Email Marketing Engine: Express.js & SendGrid Part 2](https://codesphere.ghost.io/content/images/2023/07/image-5.png)

The JS will be stored in a file called `handleCsvUpload.js`. Which will be implemented to out HTML via a simple script tag:

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

```javascript
// 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.

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

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

![Building an Email Marketing Engine: Express.js & SendGrid Part 2](https://codesphere.ghost.io/content/images/2023/07/image-6.png)
_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.

```javascript
// 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:

![Building an Email Marketing Engine: Express.js & SendGrid Part 2](https://codesphere.ghost.io/content/images/2023/07/image-11.png)

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.

![Building an Email Marketing Engine: Express.js & SendGrid Part 2](https://codesphere.ghost.io/content/images/2023/07/image-16.png)

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.

![Building an Email Marketing Engine: Express.js & SendGrid Part 2](https://codesphere.ghost.io/content/images/2023/07/image-14.png)

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:

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

```javascript
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:

```javascript
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:

```javascript
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](https://github.com/codesphere-cloud/sendgridAPI?ref=codesphere.ghost.io) on Github or just host it for free on Codesphere by clicking [here](https://codesphere.com/https://github.com/codesphere-cloud/sendgridAPI?ref=codesphere.ghost.io). 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!