Creating a user database with Airtable part 3
Tags: airtable, node, express, javascript
Published: 2019-06-11 19:34:30

---
title: Creating a user database with Airtable part 3
published: true
description: A user database with encrypted passwords using Airtable and Express with simple sessions and password reset flow
tags: Airtable, NodeJs, ExpressJs, javascript
canonical_url: https://chinarajames.com/user-database-with-airtable-part-3/
series: Creating a user database with Airtable
---

[Part 1](https://chinarajames.com/user-database-with-airtable-part-1/) we learned how to add users to an Airtable database with hashed passwords.
[Part 2](https://chinarajames.com/user-database-with-airtable-part-2/) we learned how to persist the user's data with sessions.

In part 3, our final tutorial in this series, we are going to create a password reset flow so let's dive in.

## Just want the code?

Get the full working demo on [Github](https://github.com/cjwd/user-database-airtable-tut). If you want to follow along, download the starter files folder and rename it to whatever you would like and run yarn to install the dependencies. Don't forget to check out [part 1](https://chinarajames.com/user-database-with-airtable-part-1/) and [part 2](https://chinarajames.com/user-database-with-airtable-part-2/) first.

## The Process

The password reset flow has the following general steps:

1. [User requests a password reset](#step-1)
2. [Send the user a password reset email](#step-2)
3. [User submits a new password](#step-3)
4. [Update the user record with the new password](#step-4)
5. [Send an email confirming the password change](#step-5)

## Step 1 User requests a password reset <a name="step-1"></a>

Create a view, _forget.pug_, with a form for the user to request a password reset.

```pug
<!-- templates/views/forget.pug -->

extends ../layouts/base

block content
  h1.title Password Reset

  if (message)
    .notification.is-success
      button.delete
      =message

  form(action="/user/forgot" method="POST")
    .field
      label.label(for="username") Email or Username
      .control.has-icons-left.has-icons-right
        input.input(type='text' name="username" id="username" placeholder='Email input' value='hello@')
        span.icon.is-small.is-left
          i.fas.fa-user

    .field
      .control
        button.button.is-link Reset Password
```

We will also add a forgot password link to our login form.

```pug
<!-- templates/views/index.pug -->
small
    a(href="/forgot-password") Lost your password?

```

Now let's create a route in index.js to display the forget password view and the associated middleware in our appController.js file. This will be a GET request.

```javascript
// index.js
router.get("/forgot-password", appController.getForgetPassword);

// appController.js
exports.getForgetPassword = (req, res) => {
  res.render("forgot");
};
```

## Step 2 Send the user a password reset email <a name="step-2"></a>

If the user exists, we are going to send them a password reset email that will contain a password reset link. This link will contain a token parameter as well as the email address as a query parameter. An example:

```
http://localhost:7777/login/resetlink/liEvWzccnrWnnhEzaNca@iafiazpXozJZXJa0zn?email=chinarakinda%40gmail.com
```

The format is

```
http://localhost:7777/[token]?user=[username]
```

The token will be used to confirm that the user indeed requested the reset and will be compared to the one store in the database. Therefore we need to add a token field to our base in Airtable. This will be a normal text field.

### Creating the password reset link

As explained above we will be using a token to confirm the reset so that persons can just enter any string with an email and be able to reset a user's password. This token will be a combination of the user's record ID and their email address.

Let's create two helper functions to handle generating the password reset URL string. The first function will be responsible for generating a token based on the user's record ID and email and the second function will use that token and generate the URL.

```javascript
// userController.js

// Built in node module provides utilities for parsing and formatting URL query strings
const querystring = require("querystring");

// The token will be using the user's ID and email address to generate a random string
const generateToken = (id, email) => {
  const source = `${id}${email}`;
  let token = "";
  for (let i = 0; i < source.length; i++) {
    token += source.charAt(Math.floor(Math.random() * source.length));
  }

  return token;
};

const generateResetUrl = (token, email) => {
  let url = "";
  url = `login/resetlink/${token}?${querystring.stringify({ email })}`;
  return url;
};
```

We want to add the token to the user's record when the user requests a password reset. Then we can confirm it later when they enter a new password.

```javascript
// userController.js
exports.addToken = async (req, res, next) => {
  const { username } = req.body;
  // Check that the user exists. We wrote this helper function already in Part 1 but we need to refactor as it requires two parameters and we are only including one here
  const userExists = await findUser(username);

  if (userExists) {
    res.render("login", {
      message: "Username or Email already exists!"
    });
    return;
  }

  const options = {
    filterByFormula: `OR(email = '${username}', username = '${username}')`
  };

  // Get the user
  const users = await data.getAirtableRecords(table, options);

  const user = users.map(record => ({
    id: record.getId(),
    email: record.get("email")
  }));

  const token = generateToken(user[0].id, user[0].email);

  table.update(
    user[0].id,
    {
      token
    },
    (err, record) => {
      if (err) {
        console.error(err);
      }

      req.body.url = generateResetUrl(token, user[0].email);
      req.body.to = user[0].email;
      next();
    }
  );
};
```

### Refactoring findUser

You will have noticed a comment before the code that checks if the user exists.

```javascript
// addToken function

// Check that the user exists. We wrote this helper function already in Part 1 but we need to refactor as it requires two parameters and we are only including one here
const userExists = await findUser(username);
```

Our function required a username and email but anyone or both can be supplied. Let's factor so that this is possible. We will set defaults for the function parameters and conditionally check for their presence.

```javascript
const findUser = async (email = undefined, username = undefined) => {
  let recordExists = false;
  let options = {};

  if (email && username) {
    options = {
      filterByFormula: `OR(email = '${email}', username = '${username}')`
    };
  } else {
    options = {
      filterByFormula: `OR(email = '${email}', username = '${email}')`
    };
  }

  const users = await data.getAirtableRecords(table, options);

  users.filter(user => {
    if (user.get("email") === email || user.get("username") === username) {
      return (recordExists = true);
    }
    if (user.get("email") === email || user.get("username") === email) {
      return (recordExists = true);
    } else {
      return false;
    }
  });

  return recordExists;
};
```

Now we can write the code to send our password reset email.

### Sending the password reset email

We are going to use the nodemailer package to send the email and the [Mailtrap](https://mailtrap.io/) web service to mock our transactional email provider.

Let's begin by installing and requiring nodemailer into our app.

```
yarn add nodemailer
```

```javascript
// userController.js
const nodemailer = require("nodemailer");
```

Let's now write the function to send our password reset link email.

```javascript
// userController.js

exports.sendEmail = async (req, res) => {
  const subject = "Password Reset link for My Sweet App";
  const { url, to } = req.body;
  const body = `Hello,
  You requested to have your password reset. Ignore if this is a mistake or you did not make this request. Otherwise, click the link below to reset your password.
  <a href="http://localhost:7777/${url}">Reset My Password</a>
  You can also copy and paste this link in your browser URL bar.
  <a href="http://localhost:7777/${url}">http://localhost:7777/${url}</a>`;

  const transporter = nodemailer.createTransport({
    host: process.env.SMTP_HOST,
    port: process.env.SMTP_PORT,
    // secure: true,
    auth: {
      user: process.env.SMTP_USERNAME,
      pass: process.env.SMTP_PASSWORD
    }
  });

  const mailOptions = {
    from: process.env.FROM_EMAIL,
    to,
    subject,
    html: body
  };

  transporter.sendMail(mailOptions, (err, info) => {
    if (err) {
      console.log(err);
    } else {
      // email sent
      res.render("forgot", {
        message: "Please check your email for your password reset link"
      });
    }
  });
};
```

You will notice in the createTransport function an object with several variables prefixed with process.env. These are the Mailtrap configuration details. We don't want these available in the codebase so we will store them in our _variables.env_ file.

If you have been following along you should have renamed the example variables file _example.variables.env_ to _variables.env_.

Create a free account at [Mailtrap](https://mailtrap.io/), get your details and enter save them to your environment variables file.

## Step 3 User submits a new password <a name="step-3"></a>

When the user clicks the password reset link in the email, it will display a form for them to enter their new password. Let's create this view, we will call it _reset.pug_.

```pug
<!-- templates/views/reset.pug -->

extends ../layouts/base
include ../mixins/_resetForm

block content
  h1.title Password Reset

  if (message)
    .notification.is-success
      button.delete
      =message

  form(action="/user/reset" method="POST")
    .field
      label.label(for="password") New Password
      .control.has-icons-left
        input.input(type='password' name="password" id="password" placeholder='Password')
        span.icon.is-small.is-left
          i.fas.fa-lock

      input(type="hidden" name="email" value=`${email}`)
      input(type="hidden" name="token" value=`${token}`)

    .field
      .control
        button.button.is-link Update Password

```

Let's create the route to display the above view and it's associated middleware to handle the request. Note the two hidden fields _email and token_ which will get from the password reset URL. Note the wild card parameter _:token_ in the route which will contain the query string of token and email.

```javascript
// index.js
router.get("/login/resetlink/:token", appController.getResetPassword);

// appController.js
exports.getResetPassword = (req, res) => {
  res.render("reset", {
    token: req.params.token,
    email: req.query.email
  });
};
```

## Step 4 Update the user record with the new password <a name="step-4"></a>

The form will send a POST request to _'/user/reset'_ so let's set up that route and the associated middleware to handle the request in our userController.js.

```javascript
// index.js
router.post(
  "/user/reset",
  userController.resetPassword,
  userController.storePassword
  userController.sendConfirmResetPasswordEmail
);

// userController.js
exports.confirmToken = async (req, res, next) => {
  // Get Form Variables
  const { email, token } = req.body;

  const options = {
    filterByFormula: `OR(email = '${email}', token = '${token}')`
  };

  // Get the user
  const users = await data.getAirtableRecords(table, options);

  const user = users.map(record => ({
    id: record.getId()
  }));

  // hash and the update the user's password
  req.body.id = user[0].id;
  next();
};
```

The first middleware, _confirmToken_, will handle getting the user record by confirming the email and token match. We then call next to call the second middleware, _storePassword_, which we created before but we will refactor a bit.

Since we are calling _storePassword_ from two different scenarios, let's make it more flexible. First, let's add the next parameter to the function definition and second let's create a general helper to update a record.

```javascript
// dataController.js

// Update any record in any table
exports.updateRecord = (table, recordId, fieldsToUpdate) => {
  table.update(recordId, fieldsToUpdate, (err, record) => {
    if (err) {
      console.log(Error(err));
      return;
    }

    return record;
  });
};

// userController

// Refactored storePassword middleware
exports.storePassword = (req, res, next) => {
  const { password, id } = req.body;

  bcrypt.hash(password, 10, function(err, hash) {
    if (err) {
      console.error(err);
      return;
    }

    req.body.hash = hash;

    data.updateRecord(table, id, {
      password: hash
    });

    next();
  });
};

// Since we are now using next, we need to update our previous routes to call the next middleware

// index.js
router.post(
  "/user/add",
  userController.addUser,
  userController.storePassword,
  appController.getLogin
);

// Step 4,5
router.post(
  "/user/reset",
  userController.confirmToken,
  userController.storePassword,
  userController.sendConfirmResetEmail
);
```

## Step 5 Send an email confirming the password change <a name="step-5"></a>

```javascript
// userController.js

exports.sendConfirmResetEmail = async (req, res) => {
  const subject = "Password successfully reset";
  const to = req.body.email;
  const body = `Hello, Your password was successfully reset.`;

  const transporter = nodemailer.createTransport({
    host: process.env.SMTP_HOST,
    port: process.env.SMTP_PORT,
    // secure: true,
    auth: {
      user: process.env.SMTP_USERNAME,
      pass: process.env.SMTP_PASSWORD
    }
  });

  const mailOptions = {
    from: process.env.FROM_EMAIL,
    to,
    subject,
    html: body
  };

  transporter.sendMail(mailOptions, (err, info) => {
    if (err) {
      console.log(err);
    } else {
      // email sent
      res.render("login");
    }
  });
};
```

And that's all folks. That concludes the series on creating a user database with Airtable. I should note there are some things that would need to get this production-ready but this is a good start to get you on your way. You can find the stepped and complete code on [Github](https://github.com/cjwd/user-database-airtable-tut).