Sequelize and UUID as primary key

By default sequelize models and migrations are generated with the column id as the primary key, the data type set to INTEGER and, with the option autoIncrement set to true. This means that each row will have a sequential id starting from 1.

User migration file

'use strict';
module.exports = {
  up: (queryInterface, Sequelize) => {
    return queryInterface.createTable('users', {
      id: {
        allowNull: false,
        autoIncrement: true,
        primaryKey: true,
        type: Sequelize.INTEGER
      },
      name: {
        type: Sequelize.STRING
      },
    });
  },
  down: (queryInterface, Sequelize) => {
    return queryInterface.dropTable('users');
  }
};

This is a classical approach and works perfectly, but when your application needs to expose data to the outside world, for instance, through a REST API, HTTP API or even GraphQL, special attention is required to the id column to avoid common situations like:

  • Users trying to guess the table records frrom the primary key value
  • A rare and extreme situation, your database may ran out of integers, just like it happend to Basecamp.

Read the article BIGINT v INT. Is there a big deal?

If you want to learn more about the pros and cons of using UUIDs as primary key, here are two blog posts that may enlight you:

UUIDs will consume extra space in your database due to the length of each UUID value. Also, keep in mind that indexing a column with a complex value like a UUID may cause performance issues.

Using UUIDs

The Sequelize module has already prebuilt properties that will help define the data type and generate UUIDs in execution time. Let’s procede by refactoring the migration file:

User migration

// Migration

'use strict';
module.exports = {
  up: (queryInterface, Sequelize) => {
    return queryInterface.createTable('users', {
      id: {
        allowNull: false,
        primaryKey: true,
        type: Sequelize.UUID,
        defaultValue: Sequelize.UUIDV4
      },
      name: {
        type: Sequelize.STRING
      },
    });
  },
  down: (queryInterface, Sequelize) => {
    return queryInterface.dropTable('users');
  }
};

In the code above the following changes were made:

  • The property autoIncrement was removed. There’s no point in having it since the UUID datatype is not incrementable.
  • The property type received the value Sequelize.UUID
  • The property defaultValue was added and assigned the value Sequelize.UUIDV4, so every time an INSERT operation is made, Sequelize will take care of the process of generating a new UUID value, on this case, it will use the version 4, which is purely random.

User model

To keep the database base definition consistent across the project codebase, the changes in the migration files need to reflect in the User model file too.

'use strict';
module.exports = (sequelize, DataTypes) => {
  const users = sequelize.define('users', {
      id: {
        allowNull: false,
        primaryKey: true,
        type: DataTypes.UUID,
        defaultValue: DataTypes.UUIDV4
    },
    name: DataTypes.STRING,
  }, {});
  users.associate = function(models) {
    // associations can be defined here
  };
  return users;
};

Just like in the migration file:

  • The property autoIncrement was removed
  • The type property was changed to UUID, but this time, using the object DataTypes instead of Sequelize
  • It was added the property defaultValue and it receives the value DataTypes.UUIDV4

That’s all you need to have UUIDs working with Sequelize.

Another use cases for UUIDs

I have other two use cases for UUIDs as the data type of columns:

  • Email account verification: when a user is created, a unique token is generated so the user can verify his account. In this case, a link with the same token is sent to the user’s mail address, once the user clicks on it, the column holding the token is updated to null.
  • Password reset: when the user resets his password, a mail with a token is sent to his email account. In this case, when the user clicks in the link, if the expiry date is less or equal than the current time, then the column holding the token is set to null.

For both cases I usually have the following columns:

  • mailConfirmationToken : UUID
  • passwordResetToken : UUID
  • passwordResetTokenExpiryDate : Date

User migration

'use strict';
module.exports = {
  up: (queryInterface, Sequelize) => {
    return queryInterface.createTable('users', {
      id: {
        allowNull: false,
        primaryKey: true,
        type: Sequelize.UUID,
        defaultValue: Sequelize.UUIDV4
      },
      name: {
        type: Sequelize.STRING
      },
      mailConfirmationToken: {
        type: Sequelize.UUID
      },
      passwordResetToken: {
        type: Sequelize.UUID
      },
      passwordResetTokenExpiryDate: {
        type: Sequelize.DATE
      }
    });
  },
  down: (queryInterface, Sequelize) => {
    return queryInterface.dropTable('users');
  }
};

User model

'use strict';
module.exports = (sequelize, DataTypes) => {
  const users = sequelize.define('users', {
      id: {
        allowNull: false,
        primaryKey: true,
        type: DataTypes.UUID,
        defaultValue: DataTypes.UUIDV4
    },
    name: DataTypes.STRING,
    mailConfirmationToken: DataTypes.UUID,
    passwordResetToken: DataTypes.UUID,
    passwordResetTokenExpiryDate: DataTypes.DATE
  }, {});
  users.associate = function(models) {
    // associations can be defined here
  };
  return users;
};

References

Firmino Changani

I'm Firmino, and here I write about web application development, focusing in Frontend, Backend and tooling tips that I learned and use.