Creating a user database with Airtable part 1
Tags: airtable, node, express
Published: 2019-05-27 16:19:19

---
title: Creating a user database with Airtable part 1
published: true
description: A user database with encrypted passwords using Express, bcrypt and Airtable
tags: Airtable, NodeJs, ExpressJs
canonical_url: https://chinarajames.com/user-database-with-airtable-part-1/
series: Creating a user database with Airtable
---

I previously wrote an article on [how to get a record from Airtable by a unique field value](https://chinarajames.com/airtable-filterbyformula-to-get-a-record-by-a-unique-field-value/) such as an email or username. I'm going to expand on that using a practical example, a user database. If you are building apps using Airtable as the backend, this can come in handy. Let's look at an example user database base I have created.

[Example User Database](https://airtable.com/shrlcd86mTnOvSC1m)

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

The starter files will already have the bare bones of the app such as the login and register pages along with the routes for displaying these pages. This article will focus on building out the user controller for creating a user and handling the login.

## Creating a user

When the user submits their information on the registration page, it will send a post request to the /user/add route. This has been specified in registration form's action attribute. Let's first create a route in our index.js file for this post request.

```javascript
// index.js
router.post("/user/add", userController.addUser);
```

When a user posts a request to this route it will call the addUser function in userController.js. Let's create this function.

```javascript
// userController.js

exports.addUser = (req, res, next) => {
  const { fullname, email, username } = req.body;

  table.create(
    {
      email,
      username,
      display_name: fullname
    },
    function(err, record) {
      if (err) {
        console.error(err);
        return;
      }
      req.body.id = record.getId();
      // store password
    }
  );
};
```

We are using Airtable's create method to create the record. Notice I did not include the password field because we need an additional step to hash the password before saving it to the database. We will get to that later on.

## Add a constraint to the email and username fields

We have a small problem with adding users. Right now as it stands, we can add another user using email addresses and usernames that already exist in the database. Airtable currently does not have a feature to set constraints on fields. Lucky for us we can do it from our code through the API.

To do this we are going to create a helper function that will return true or false depending on if the user exists or not.

```javascript
// userController.js

const findUser = async (email, username) => {
  let recordExists = false;
  const options = {
    filterByFormula: `OR(email = '${email}', username = '${username}')`
  };

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

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

  return recordExists;
};
```

Then we need to call this function from our addUser function and only if it returns true we create the user, if not we render the login page with a message. The addUser function now becomes.

```javascript
// userController.js

exports.addUser = async (req, res, next) => {
  const { fullname, email, username } = req.body;

  const userExists = await findUser(email, username);

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

  table.create(
    {
      email,
      username,
      display_name: fullname
    },
    function(err, record) {
      if (err) {
        console.error(err);
        return;
      }
      req.body.id = record.getId();
      next();
    }
  );
};
```

## Storing the user's password

We are successfully creating a user record but we are not storing the user's password. We could store the plain text password entered but obviously that's not good. I'm going to use the bcrypt package to hash the user's plain text password and store that hashed password in the Airtable base.

First we need to install the bcrypt npm package and require it in our userController.js file. This has already been done for you if you are using the starter files.

We then create a function to create a hashed password and store it in the newly created user record. Since the user record is already create we need to _update_ the user record to add the password. We will use Airtable's update method for that.

```javascript
// userController.js

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

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

    table.update(
      id,
      {
        password: hash
      },
      function(err) {
        if (err) {
          console.error(err);
          return;
        }
        res.render("login", {
          message: "Your account has been created!"
        });
      }
    );
  });
};
```

We then need to modify our addUser function to call this function immediately after the record is created so that we can have access the user's email and password. To do this will modify the route to call the storePassword function after addUser and call next() when the record is created in the addUser function to call the next function in our route chain, the storePassword function.

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

```javascript
// userController.js
exports.addUser = (req, res, next) => {
  const { fullname, email, username } = req.body;

  const userExists = await findUser(email, username);

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

  table.create(
    {
      email,
      username,
      display_name: fullname
    },
    function(err, record) {
      if (err) {
        console.error(err);
        return;
      }
      req.body.id = record.getId();
      // The user has been successfully create, let's encrypt and store their password
      next();
    }
  );
};
```

## Logging the user in

Now let's create the flow for logging the user in. The login form sends a post request to this route /user/auth.

```javascript
// index.js
router.post("/user/auth", userController.authenticate);
```

We will create a function, called authenticate, to find the user by email or username and compare the passwords to decide whether to log in the user.

```javascript
// userController.js
exports.authenticate = (req, res) => {
  const { username, password } = req.body;
  const options = {
    filterByFormula: `OR(email = '${username}', username = '${username}')`
  };

  data
    .getAirtableRecords(table, options)
    .then(users => {
      users.forEach(function(user) {
        bcrypt.compare(password, user.get("password"), function(err, response) {
          if (response) {
            // Passwords match, response = true
            res.render("profile", {
              user: user.fields
            });
          } else {
            // Passwords don't match
            console.log(err);
          }
        });
      });
    })
    .catch(err => {
      console.log(Error(err));
    });
};
```

This completes part 1. You can get the full working code on [Github](https://github.com/cjwd/user-database-airtable-tut).
In part 2, we will implement a simple session to persist the user data when they are logged in. Keep an eye out for part 2 :eyes:.