Building an Email Marketing Engine Part 4: API Endpoints & Database Enhancements
Tags: tutorial, productmarketing
Published: 2023-07-31 16:10:30

---
title: Building an Email Marketing Engine Part 4: API Endpoints & Database Enhancements
published: true
date: 2023-07-31 15:29:23 UTC
tags: Tutorial,productmarketing
canonical_url: https://codesphere.com/articles/building-an-email-marketing-engine-part-4-api-endpoints-database-enhancements
---

 ![Building an Email Marketing Engine Part 4: API Endpoints & Database Enhancements](https://codesphere.ghost.io/content/images/2023/07/email-marketing-engine-part4.jpg)

Welcome back to our series on building an email marketing engine using Node.js, Express.js, SQLite, and SendGrid. In the previous parts, [we set up our application, integrated it with SendGrid](https://codesphere.com/articles/building-email-marketing-engine-expressjs-sendgrid-part-1?ref=codesphere.ghost.io), and [created a simple SQLite database](https://codesphere.com/articles/building-an-email-marketing-engine-express-js-sendgrid-part-2?ref=codesphere.ghost.io) to store our contacts. In Part 3, we introduced a [custom login system with Auth0](https://codesphere.com/articles/building-an-email-marketing-engine-securing-your-frontend-with-auth0?ref=codesphere.ghost.io)

In this article, we will be extending our database and creating API endpoints to upload and delete contacts. These enhancements will allow us to manage our contacts more effectively and provide a foundation for more complex operations in the future.

## Extending our SQLite Database

SQLite is a great choice for our application due to its simplicity and ease of use. To start, we already created a `Contacts` table to store our contacts' information. Now, we're going to extend our database by introducing a `subgroups` table and a linking table called `contact_subgroup`.

### Creating the `subgroups` Table

We want to be able to categorize our contacts into different subgroups for targeted marketing. To do this, we need a new table called `subgroups`. This table will have two columns:

- `id`: A unique identifier for each subgroup.
- `name`: The name of the subgroup.

Here is the SQL statement to create the `subgroups` table:

```sql
CREATE TABLE subgroups (
   id INTEGER PRIMARY KEY AUTOINCREMENT,
   name TEXT
);

```

### Creating the `contact_subgroup` Linking Table

Since a contact can belong to multiple subgroups and a subgroup can have multiple contacts, we have a many-to-many relationship between contacts and subgroups. To represent this relationship in SQLite, we need a linking table.

The `ContactsSubgroups` table has two columns:

- `contact_id`: The ID of a contact, which is a foreign key referencing `contacts.id`.
- `subgroup_id`: The ID of a subgroup, which is a foreign key referencing `subgroups.id`.

Here's the SQL statement to create the `contact_subgroup` table:

```sql
CREATE TABLE contact_subgroup (
   contact_id INTEGER,
   subgroup_id INTEGER,
   PRIMARY KEY (contact_id, subgroup_id),
   FOREIGN KEY (contact_id) REFERENCES contacts (id),
   FOREIGN KEY (subgroup_id) REFERENCES subgroups (id)
);
```

Now, we have a much more flexible system for categorizing our contacts. We can assign a contact to multiple subgroups and select contacts based on their assigned subgroup.

## Implementing API Endpoints

With our database now ready, we need a way to interact with it. That's where our API endpoints come in. We will create two endpoints: one for adding a contact and one for deleting a contact.

### The `addContact` Endpoint

The `addContact` endpoint will receive a POST request with the contact's details in the request body. These details include the contact's email, first name, last name, and optionally, the ID of the subgroup that the contact belongs to.

For this, we create a new file called `dataToDb.js` where we define the `addContact` function:

```javascript
function addContact(req, res) {
    // Check if the contact object is provided in the request body
    if (!req.body || !req.body.contact) {
        return res.status(400).json({ error: 'Contact data is missing in the request body.' });
    }

    // Deconstruct the contact and subgroupIds from the request body
    const { contact, subgroupIds } = req.body;

    // Validate the contact object
    const { sendgrid_contact_id, email, first_name, last_name } = contact;
    if (!sendgrid_contact_id || !email || !first_name || !last_name) {
        return res.status(400).json({ error: 'Some contact properties are missing.' });
    }

    // Open a connection to the database
    let db = new sqlite3.Database('./server/data/contacts.db', sqlite3.OPEN_READWRITE);

    // Prepare the SQL for inserting the contact
    let sql = 'INSERT INTO contacts (sendgrid_contact_id, email, first_name, last_name) VALUES (?, ?, ?, ?)';

    // Run the SQL query
    db.run(sql, [sendgrid_contact_id, email, first_name, last_name], function (err) {
        if (err) {
            return res.status(500).json({ error: err.message });
        }

        const contactId = this.lastID;

        // If subgroupIds were provided, associate the contact with each subgroup
        if (subgroupIds && Array.isArray(subgroupIds)) {
            const subgroupSql = 'INSERT INTO contact_subgroup (contact_id, subgroup_id) VALUES (?, ?)';

            // Iterate over each subgroupId
            subgroupIds.forEach(subgroupId => {
                db.run(subgroupSql, [contactId, subgroupId], function (err) {
                    if (err) {
                        return res.status(500).json({ error: err.message });
                    }
                });
            });

            // Respond with a success message
            res.json({ message: `Contact and subgroup relationships have been created with contactId ${contactId}` });
        } else {
            // Respond with a success message
            res.json({ message: `Contact has been inserted with rowid ${contactId}` });
        }
    });

    // Close the database connection
    db.close((err) => {
        if (err) {
            console.error(err.message);
        }
    });
}
```

Upon receiving a request, the `addContact` function validates the provided data, extracts essential information, and connects to the database. It then prepares an SQL query to insert the contact details into the `contacts` table. If successful, it obtains the `contactId` of the new contact and, if applicable, associates the contact with provided subgroups in the `contact_subgroup` table. The function responds with appropriate success messages, and it closes the database connection after the operations are completed.

### The `deleteContact` Endpoint

The `deleteContact` endpoint receives a DELETE request with the ID of the contact to delete in the URL. It deletes the contact from the `Contacts` table and also removes any links between the contact and subgroups in the `ContactsSubgroups` table.

Here's how the `deleteContact` function looks in our `dataToDb.js` file:

```javascript
function deleteContact(req, res) {
    const contactId = req.params.contactId;

    if (!contactId) {
        return res.status(400).json({ error: 'Contact id is required.' });
    }

    let db = new sqlite3.Database('./server/data/contacts.db', sqlite3.OPEN_READWRITE);

    // Prepare the SQL for deleting the contact
    let sql = 'DELETE FROM contacts WHERE id = ?';

    // Run the SQL query
    db.run(sql, [contactId], function (err) {
        if (err) {
            return res.status(500).json({ error: err.message });
        }

        // Respond with a success message
        res.json({ message: `Contact with id ${contactId} has been deleted` });
    });

    // Close the database connection
    db.close((err) => {
        if (err) {
            console.error(err.message);
        }
    });
}
```

The `deleteContact` expects the `contactId` as a parameter in the request. If the `contactId` is missing, it responds with an error. Otherwise, it connects to the `contacts.db` database, prepares an SQL query to delete the specified contact from the `contacts` table, and executes the query. If successful, it responds with a success message.

## Protecting API Endpoints with an API Key

Security is a crucial aspect of any web application. For our API endpoints, we'll use a simple but effective method of protection: an API key. An API key is a secret token that a client must send in their request to access our endpoints.

We'll define a middleware function, `apiKeyMiddleware`, that checks if the incoming request includes a valid API key. This middleware will be used in our `addContact` and `deleteContact` routes.

Here's the middleware definition in our main server file:

```javascript
const API_KEY = 'api-key';
const apiKeyMiddleware = (req, res, next) => {
  const reqKey = req.get('x-api-key');
  if (reqKey && reqKey === API_KEY) {
    next();
  } else {
    res.status(403).json({error: "Invalid or missing API key"});
  }
};
```

We then use this middleware in our routes as follows:

```javascript
apiRouter.post('/addContact', apiKeyMiddleware, addContact);
apiRouter.delete('/deleteContact/:contactId', apiKeyMiddleware, deleteContact);
```

With this setup, only requests that include the correct API key in the `x-api-key` header will be able to access the `addContact` and `deleteContact` endpoints.

### Adjusting the Server Setup

In our previous setup, we had all our routes handled directly by our main `app` object. We also didn't differentiate between different types of routes, like those for our API versus our application.

However, as we move forward, our application grows and we need to separate these concerns. We don't want to apply the same set of rules to all routes. For example, we want to secure our API with an API key and our application with Auth0. To achieve this, we introduce two new routers, `apiRouter` and `appRouter`, using `express.Router()`.

```javascript
const apiRouter = express.Router();
const appRouter = express.Router();
```

Routers are like mini Express applications that only have middleware and routing methods. We use the `apiRouter` to handle all our API routes and `appRouter` to handle application routes.

We then apply the API key middleware only to the `apiRouter` and Auth0 middleware to the `appRouter`:

```javascript
apiRouter.post('/addContact', apiKeyMiddleware, addContact);
apiRouter.delete('/deleteContact/:contactId', apiKeyMiddleware, deleteContact);
...
appRouter.use(auth(auth0Config));
...
appRouter.post("/contacts", requiresAuth(), async (req, res) => {...});
appRouter.post('/upload', requiresAuth(), upload.single('contacts-upload'), (req, res) => {...});
appRouter.get('/profile', requiresAuth(), (req, res) => {...});

```

Now, the different routers are applied to different sets of routes. We use them in our main app like so:

```javascript
app.use('/api', apiRouter);
app.use('/', appRouter);

```

This way, any request to the `/api` endpoint will go through the `apiRouter` and will be checked for the API key. Any other request will be handled by the `appRouter`, which is secured by Auth0.

With these changes, our application is not only more organized, but it also handles different types of requests with different security requirements. This allows us to better control access to our resources and provides a more secure and efficient system.

## Wrapping Up

In this article, we extended our database and created API endpoints for adding and deleting contacts. We also went over how to handle many-to-many relationships in SQLite. With these enhancements, our email marketing engine is becoming more powerful and flexible.

In the next part of this series, we will be looking at how to send emails to specific subgroups and fixing up the frontend. Stay tuned!

As always, you can take a look at the latest status of the Email Marketing Engine in our [GitHub Repo](https://github.com/codesphere-cloud/sendgridAPI?ref=codesphere.ghost.io). To host the application yourself, just use  our [magic Codesphere link](https://codesphere.com/https://github.com/codesphere-cloud/sendgridAPI?ref=codesphere.ghost.io) to get you started in only a few clicks!

* * *