Sequelize with PostgreSQL in Node.js: Part 2 – Models and Migrations

Now that you have a solid foundation with Sequelize connected to PostgreSQL, it’s time to define your data models and understand how migrations work. Models are the backbone of your application, defining how data is structured and how different entities relate to each other. In this part, we’ll create User and Post models, set up migrations, and establish relationships between them.

Understanding Models in Sequelize

A Sequelize model is a class that represents a database table. It defines the table’s structure (columns with their data types), validation rules, associations with other models, and custom methods for interacting with the data. Think of it as a blueprint for how your data is organized and behaves.

Creating Your First Model: User

Let’s generate a User model using Sequelize CLI. This command creates both the model file and a migration file:

npx sequelize-cli model:generate --name User --attributes firstName:string,lastName:string,email:string,password:string

This creates two files. First, let’s look at the migration file. Navigate to src/migrations and open the newly created file. Modify it to add constraints and proper data types:

'use strict';

module.exports = {
  up: async (queryInterface, Sequelize) => {
    await queryInterface.createTable('Users', {
      id: {
        allowNull: false,
        autoIncrement: true,
        primaryKey: true,
        type: Sequelize.INTEGER,
      },
      firstName: {
        type: Sequelize.STRING,
        allowNull: false,
      },
      lastName: {
        type: Sequelize.STRING,
        allowNull: false,
      },
      email: {
        type: Sequelize.STRING,
        allowNull: false,
        unique: true,
        validate: {
          isEmail: true,
        },
      },
      password: {
        type: Sequelize.STRING,
        allowNull: false,
      },
      createdAt: {
        allowNull: false,
        type: Sequelize.DATE,
      },
      updatedAt: {
        allowNull: false,
        type: Sequelize.DATE,
      },
    });
  },

  down: async (queryInterface, Sequelize) => {
    await queryInterface.dropTable('Users');
  },
};

Now, let’s update the User model file in src/models/user.js:

'use strict';
const { Model } = require('sequelize');

module.exports = (sequelize, DataTypes) => {
  class User extends Model {
    static associate(models) {
      User.hasMany(models.Post, {
        foreignKey: 'userId',
        as: 'posts',
        onDelete: 'CASCADE',
      });
    }
  }

  User.init(
    {
      firstName: {
        type: DataTypes.STRING,
        allowNull: false,
        validate: {
          len: [2, 50],
        },
      },
      lastName: {
        type: DataTypes.STRING,
        allowNull: false,
        validate: {
          len: [2, 50],
        },
      },
      email: {
        type: DataTypes.STRING,
        allowNull: false,
        unique: true,
        validate: {
          isEmail: true,
        },
      },
      password: {
        type: DataTypes.STRING,
        allowNull: false,
        validate: {
          len: [8, 255],
        },
      },
    },
    {
      sequelize,
      modelName: 'User',
      tableName: 'Users',
      timestamps: true,
      underscored: false,
    }
  );

  return User;
};

Creating the Post Model

Generate the Post model with its attributes:

npx sequelize-cli model:generate --name Post --attributes title:string,content:text,userId:integer

Update the migration file for Post:

'use strict';

module.exports = {
  up: async (queryInterface, Sequelize) => {
    await queryInterface.createTable('Posts', {
      id: {
        allowNull: false,
        autoIncrement: true,
        primaryKey: true,
        type: Sequelize.INTEGER,
      },
      title: {
        type: Sequelize.STRING,
        allowNull: false,
      },
      content: {
        type: Sequelize.TEXT,
        allowNull: false,
      },
      userId: {
        type: Sequelize.INTEGER,
        allowNull: false,
        references: {
          model: 'Users',
          key: 'id',
        },
        onDelete: 'CASCADE',
      },
      createdAt: {
        allowNull: false,
        type: Sequelize.DATE,
      },
      updatedAt: {
        allowNull: false,
        type: Sequelize.DATE,
      },
    });
  },

  down: async (queryInterface, Sequelize) => {
    await queryInterface.dropTable('Posts');
  },
};

Now update the Post model in src/models/post.js:

'use strict';
const { Model } = require('sequelize');

module.exports = (sequelize, DataTypes) => {
  class Post extends Model {
    static associate(models) {
      Post.belongsTo(models.User, {
        foreignKey: 'userId',
        as: 'author',
        onDelete: 'CASCADE',
      });
    }
  }

  Post.init(
    {
      title: {
        type: DataTypes.STRING,
        allowNull: false,
        validate: {
          len: [3, 200],
        },
      },
      content: {
        type: DataTypes.TEXT,
        allowNull: false,
        validate: {
          len: [10, 5000],
        },
      },
      userId: {
        type: DataTypes.INTEGER,
        allowNull: false,
      },
    },
    {
      sequelize,
      modelName: 'Post',
      tableName: 'Posts',
      timestamps: true,
      underscored: false,
    }
  );

  return Post;
};

Understanding Associations

Associations define relationships between models. We’ve used two types here:

• hasMany: One User can have multiple Posts. We use this on the User model and define it with foreignKey ‘userId’.
• belongsTo: Each Post belongs to one User. This is the inverse relationship.

The ‘as’ property creates aliases for easier querying. With User.hasMany(Post, {as: ‘posts’}), you can query user.getPosts() or include posts using {as: ‘posts’}.

Running Migrations

Migrations are files that modify your database schema. They allow you to version control your database structure. Run all pending migrations with:

npm run db:migrate

You should see output indicating that both Users and Posts tables were created. If you need to undo a migration:

npm run db:migrate:undo

Or undo all migrations:

npx sequelize-cli db:migrate:undo:all

Creating a Models Index File

Create src/models/index.js to centralize model exports and initialize associations:

const fs = require('fs');
const path = require('path');
const Sequelize = require('sequelize');
const sequelize = require('../config/sequelize');

const db = {};

const files = fs.readdirSync(__dirname).filter((file) => {
  return file.indexOf('.') !== 0 && file !== 'index.js' && file.slice(-3) === '.js';
});

files.forEach((file) => {
  const model = require(path.join(__dirname, file))(sequelize, Sequelize.DataTypes);
  db[model.name] = model;
});

Object.keys(db).forEach((modelName) => {
  if (db[modelName].associate) {
    db[modelName].associate(db);
  }
});

db.sequelize = sequelize;
db.Sequelize = Sequelize;

module.exports = db;

Data Model Relationships Visualization

erDiagram
    USERS ||--o{ POSTS : creates
    
    USERS {
        int id PK
        string firstName
        string lastName
        string email UK
        string password
        datetime createdAt
        datetime updatedAt
    }
    
    POSTS {
        int id PK
        string title
        text content
        int userId FK
        datetime createdAt
        datetime updatedAt
    }

Model Best Practices

• Validation: Always validate data at the model level. Use Sequelize validators to ensure data integrity.
• Naming Conventions: Use PascalCase for model names and camelCase for attributes. Keep table names plural and in English.
• Foreign Keys: Always define foreign key constraints in migrations to prevent orphaned records.
• Timestamps: Enable timestamps (createdAt, updatedAt) for audit trails and debugging.
• Associations: Define both sides of relationships to enable efficient querying in both directions.

Common Migration Patterns

Adding a Column to Existing Table:

module.exports = {
  up: async (queryInterface, Sequelize) => {
    await queryInterface.addColumn('Users', 'phone', {
      type: Sequelize.STRING,
      allowNull: true,
    });
  },
  down: async (queryInterface, Sequelize) => {
    await queryInterface.removeColumn('Users', 'phone');
  },
};

Creating an Index:

module.exports = {
  up: async (queryInterface, Sequelize) => {
    await queryInterface.addIndex('Posts', ['userId']);
  },
  down: async (queryInterface, Sequelize) => {
    await queryInterface.removeIndex('Posts', ['userId']);
  },
};

Troubleshooting Model Issues

Issue: “Foreign key constraint failed”
Solution: Ensure the referenced table exists and the parent record exists before creating child records.

Issue: “Unique constraint violated”
Solution: Check for duplicate values in unique columns like email. Use DATABASE to identify and resolve duplicates.

Issue: “Association errors when querying”
Solution: Ensure associations are properly defined in both models and that the models/index.js file is properly initialized.

What’s Next

In Part 3, we’ll implement actual CRUD operations using these models. We’ll create controllers and services to handle creating, reading, updating, and deleting users and posts. We’ll also explore eager loading to optimize queries and retrieve related data efficiently.

References

• Sequelize Model Definition: https://sequelize.org/docs/v6/core-concepts/model-basics/
• Sequelize Migrations: https://sequelize.org/docs/v6/other-topics/migrations/
• Sequelize Associations: https://sequelize.org/docs/v6/core-concepts/assocs/
• PostgreSQL Data Types: https://www.postgresql.org/docs/current/datatype.html