Sequelize database migrations and models with Nodejs

After using Sequelize in my Express applications for the past 6 years, this is how I write my migrations and models for all my Nodejs applications.

Install sequelize with npm first and initialize the project with "sequelize init". That should create the "models", "migrations" and "seeders" folders as well as create the "config/config.json" file.

In our application, we'll have users and cards. Each user can have multiple cards.

We need a users table and a cards table in our database and users will have a one-to-many relationship to cards. You can find how those two models are associated using the Sequelize way here:Sequelize database connection with Nodejs

Create the users table migration by running:

create users table migration

./node_modules/.bin/sequelize migration:create --name create-users

You should now see the "migrations/20190610033956-create-users.js"

migrations/20190610033956-create-users.js

"use strict";

module.exports = {
  up: function(queryInterface, DataTypes) {
    return queryInterface.createTable("users", {
      id: {
        allowNull: false,
        autoIncrement: true,
        primaryKey: true,
        type: DataTypes.INTEGER,
      },
      email: {
        type: DataTypes.STRING,
        allowNull: false,
        unique: true,
      },
      password: {
        type: DataTypes.STRING,
        allowNull: false,
      },
      auth_token: {
        type: DataTypes.STRING,
        defaultValue: "",
      },
      email_verification_token: {
        type: DataTypes.STRING,
        allowNull: false,
      },
      is_email_verified: {
        type: DataTypes.BOOLEAN,
        defaultValue: false,
      },
      created_at: {
        allowNull: false,
        type: DataTypes.DATE,
      },
      updated_at: {
        allowNull: false,
        type: DataTypes.DATE,
      },
      deleted_at: {
        type: DataTypes.DATE,
      },
    });
  },

  down: function(queryInterface, DataTypes) {
    return queryInterface.dropTable("users");
  },
};

Also, create the model file under "models/User.js"

models/User.js

"use strict";

const secrets = [
  "password",
  "emailVerificationToken",
  "status",
  "createdAt",
  "updatedAt",
  "deletedAt",
];

module.exports = function(sequelize, DataTypes) {
  const User = sequelize.define(
    "user",
    {
      id: {
        allowNull: false,
        autoIncrement: true,
        primaryKey: true,
        type: DataTypes.INTEGER,
      },
      email: {
        type: DataTypes.STRING,
        allowNull: false,
        unique: true,
      },
      password: {
        type: DataTypes.STRING,
        allowNull: false,
      },
      authToken: {
        type: DataTypes.STRING,
        defaultValue: "",
      },
      emailVerificationToken: {
        type: DataTypes.STRING,
        allowNull: false,
      },
      isEmailVerified: {
        type: DataTypes.BOOLEAN,
        defaultValue: false,
      },
      createdAt: {
        allowNull: false,
        type: DataTypes.DATE,
      },
      updatedAt: {
        allowNull: false,
        type: DataTypes.DATE,
      },
      deletedAt: {
        type: DataTypes.DATE,
      },
    },
    {
      timestamps: true,
      paranoid: true,
    }
  );

  User.prototype.purge = function() {
    const clean = {};
    for (const key of Object.keys(this.dataValues)) {
      if (!secrets.includes(key)) {
        clean[key] = this.dataValues[key];
      }
    }
    return clean;
  };

  return User;
};

I usually add the purge function on the model itself, since we can then call purge on a created user instance before sending it back to the frontend. Otherwise, we might send back user data that should have not been sent back such as the password hash.

create cards table migration

./node_modules/.bin/sequelize migration:create --name create-cards

You should now see the "migrations/20190610033980-create-cards.js"

migrations/20190610033980-create-cards.js

"use strict";

module.exports = {
  up: function(queryInterface, DataTypes) {
    return queryInterface.createTable("cards", {
      id: {
        allowNull: false,
        autoIncrement: true,
        primaryKey: true,
        type: DataTypes.INTEGER,
      },
      stripe_id: {
        type: DataTypes.STRING,
        required: true,
      },
      user_id: {
        type: DataTypes.INTEGER,
        allowNull: false,
      },
      last4: {
        type: DataTypes.STRING,
        required: true,
      },
      created_at: {
        allowNull: false,
        type: DataTypes.DATE,
      },
      updated_at: {
        allowNull: false,
        type: DataTypes.DATE,
      },
      deleted_at: {
        type: DataTypes.DATE,
      },
    });
  },

  down: function(queryInterface, DataTypes) {
    return queryInterface.dropTable("cards");
  },
};

Also, create the model file under "models/Card.js"

models/Card.js

"use strict";

const secrets = ["stripeId"];

module.exports = function(sequelize, DataTypes) {
  const Card = sequelize.define(
    "card",
    {
      id: {
        allowNull: false,
        autoIncrement: true,
        primaryKey: true,
        type: DataTypes.INTEGER,
      },
      stripeId: {
        type: DataTypes.STRING,
        required: true,
      },
      userId: {
        type: DataTypes.INTEGER,
        allowNull: false,
      },
      last4: {
        type: DataTypes.STRING,
        required: true,
      },
      createdAt: {
        allowNull: false,
        type: DataTypes.DATE,
      },
      updatedAt: {
        allowNull: false,
        type: DataTypes.DATE,
      },
      deletedAt: {
        type: DataTypes.DATE,
      },
    },
    {
      timestamps: true,
      paranoid: true,
    }
  );

  Card.prototype.purge = function() {
    const clean = {};
    for (const key of Object.keys(this.dataValues)) {
      if (!secrets.includes(key)) {
        clean[key] = this.dataValues[key];
      }
    }
    return clean;
  };

  return Card;
};

Create the users and cards tables and then the model files. We create the association in our "lib/database.js" file. You can find that here:Sequelize database connection with Nodejs

That's pretty much it. The above code is actually from Violetnorth's API. If you need to secure your website and your corporate email, you can check out Violetnorth.

Smart web and email firewall to secure your entire infrastructure:Violetnorth

Thanks for stopping by!