Node.js & Express

Database Relationships & Queries

30 min Lesson 18 of 40

Understanding Database Relationships

Database relationships define how tables relate to each other. Proper relationship design is crucial for data integrity, query performance, and application architecture. In this lesson, we'll explore the main types of relationships and how to implement them in both Sequelize (SQL) and Mongoose (MongoDB).

Types of Relationships:

One-to-One: Each record in Table A relates to exactly one record in Table B
One-to-Many: Each record in Table A can relate to multiple records in Table B
Many-to-Many: Multiple records in Table A can relate to multiple records in Table B

One-to-Many Relationships

The most common relationship type. For example, one user can have many posts, but each post belongs to one user.

Sequelize Implementation

// models/User.js
const { DataTypes } = require('sequelize');
const sequelize = require('../config/database');

const User = sequelize.define('User', {
  id: {
    type: DataTypes.INTEGER,
    primaryKey: true,
    autoIncrement: true
  },
  username: {
    type: DataTypes.STRING(50),
    allowNull: false,
    unique: true
  },
  email: {
    type: DataTypes.STRING(100),
    allowNull: false,
    unique: true
  }
}, {
  tableName: 'users',
  timestamps: true
});

module.exports = User;

// models/Post.js
const { DataTypes } = require('sequelize');
const sequelize = require('../config/database');

const Post = sequelize.define('Post', {
  id: {
    type: DataTypes.INTEGER,
    primaryKey: true,
    autoIncrement: true
  },
  title: {
    type: DataTypes.STRING(200),
    allowNull: false
  },
  content: {
    type: DataTypes.TEXT,
    allowNull: false
  },
  userId: {
    type: DataTypes.INTEGER,
    allowNull: false,
    field: 'user_id',
    references: {
      model: 'users',
      key: 'id'
    },
    onUpdate: 'CASCADE',
    onDelete: 'CASCADE'
  }
}, {
  tableName: 'posts',
  timestamps: true
});

module.exports = Post;

// models/index.js - Define associations
const User = require('./User');
const Post = require('./Post');

// One user has many posts
User.hasMany(Post, {
  foreignKey: 'userId',
  as: 'posts',
  onDelete: 'CASCADE'
});

// Each post belongs to one user
Post.belongsTo(User, {
  foreignKey: 'userId',
  as: 'author'
});

module.exports = { User, Post };

Querying with associations:

// Get user with all posts
const getUserWithPosts = async (req, res) => {
  try {
    const user = await User.findByPk(req.params.id, {
      include: [{
        model: Post,
        as: 'posts'
      }]
    });

    if (!user) {
      return res.status(404).json({
        success: false,
        error: 'User not found'
      });
    }

    res.status(200).json({
      success: true,
      data: user
    });
  } catch (error) {
    res.status(500).json({
      success: false,
      error: error.message
    });
  }
};

// Get post with author
const getPostWithAuthor = async (req, res) => {
  try {
    const post = await Post.findByPk(req.params.id, {
      include: [{
        model: User,
        as: 'author',
        attributes: ['id', 'username', 'email']
      }]
    });

    if (!post) {
      return res.status(404).json({
        success: false,
        error: 'Post not found'
      });
    }

    res.status(200).json({
      success: true,
      data: post
    });
  } catch (error) {
    res.status(500).json({
      success: false,
      error: error.message
    });
  }
};

Mongoose Implementation

// models/User.js
const mongoose = require('mongoose');

const userSchema = new mongoose.Schema({
  username: {
    type: String,
    required: true,
    unique: true
  },
  email: {
    type: String,
    required: true,
    unique: true
  }
}, {
  timestamps: true,
  toJSON: { virtuals: true },
  toObject: { virtuals: true }
});

// Virtual populate
userSchema.virtual('posts', {
  ref: 'Post',
  localField: '_id',
  foreignField: 'author'
});

const User = mongoose.model('User', userSchema);
module.exports = User;

// models/Post.js
const mongoose = require('mongoose');

const postSchema = new mongoose.Schema({
  title: {
    type: String,
    required: true
  },
  content: {
    type: String,
    required: true
  },
  author: {
    type: mongoose.Schema.Types.ObjectId,
    ref: 'User',
    required: true
  }
}, {
  timestamps: true
});

const Post = mongoose.model('Post', postSchema);
module.exports = Post;

// Querying with population
const getUserWithPosts = async (req, res) => {
  try {
    const user = await User
      .findById(req.params.id)
      .populate('posts');

    if (!user) {
      return res.status(404).json({
        success: false,
        error: 'User not found'
      });
    }

    res.status(200).json({
      success: true,
      data: user
    });
  } catch (error) {
    res.status(500).json({
      success: false,
      error: error.message
    });
  }
};

const getPostWithAuthor = async (req, res) => {
  try {
    const post = await Post
      .findById(req.params.id)
      .populate('author', 'username email');

    if (!post) {
      return res.status(404).json({
        success: false,
        error: 'Post not found'
      });
    }

    res.status(200).json({
      success: true,
      data: post
    });
  } catch (error) {
    res.status(500).json({
      success: false,
      error: error.message
    });
  }
};

Many-to-Many Relationships

When multiple records in Table A can relate to multiple records in Table B, you need a junction (pivot) table. Example: students and courses.

Sequelize Implementation

// models/Student.js
const { DataTypes } = require('sequelize');
const sequelize = require('../config/database');

const Student = sequelize.define('Student', {
  id: {
    type: DataTypes.INTEGER,
    primaryKey: true,
    autoIncrement: true
  },
  name: {
    type: DataTypes.STRING(100),
    allowNull: false
  },
  email: {
    type: DataTypes.STRING(100),
    allowNull: false,
    unique: true
  }
}, {
  tableName: 'students',
  timestamps: true
});

module.exports = Student;

// models/Course.js
const { DataTypes } = require('sequelize');
const sequelize = require('../config/database');

const Course = sequelize.define('Course', {
  id: {
    type: DataTypes.INTEGER,
    primaryKey: true,
    autoIncrement: true
  },
  name: {
    type: DataTypes.STRING(100),
    allowNull: false
  },
  code: {
    type: DataTypes.STRING(20),
    allowNull: false,
    unique: true
  }
}, {
  tableName: 'courses',
  timestamps: true
});

module.exports = Course;

// models/Enrollment.js - Junction table
const { DataTypes } = require('sequelize');
const sequelize = require('../config/database');

const Enrollment = sequelize.define('Enrollment', {
  id: {
    type: DataTypes.INTEGER,
    primaryKey: true,
    autoIncrement: true
  },
  studentId: {
    type: DataTypes.INTEGER,
    allowNull: false,
    field: 'student_id'
  },
  courseId: {
    type: DataTypes.INTEGER,
    allowNull: false,
    field: 'course_id'
  },
  enrollmentDate: {
    type: DataTypes.DATE,
    defaultValue: DataTypes.NOW,
    field: 'enrollment_date'
  },
  grade: {
    type: DataTypes.STRING(2),
    allowNull: true
  }
}, {
  tableName: 'enrollments',
  timestamps: true
});

module.exports = Enrollment;

// models/index.js - Define associations
const Student = require('./Student');
const Course = require('./Course');
const Enrollment = require('./Enrollment');

// Many-to-Many relationship
Student.belongsToMany(Course, {
  through: Enrollment,
  foreignKey: 'studentId',
  otherKey: 'courseId',
  as: 'courses'
});

Course.belongsToMany(Student, {
  through: Enrollment,
  foreignKey: 'courseId',
  otherKey: 'studentId',
  as: 'students'
});

// Access junction table
Student.hasMany(Enrollment, { foreignKey: 'studentId' });
Enrollment.belongsTo(Student, { foreignKey: 'studentId' });

Course.hasMany(Enrollment, { foreignKey: 'courseId' });
Enrollment.belongsTo(Course, { foreignKey: 'courseId' });

module.exports = { Student, Course, Enrollment };

// Querying many-to-many
const getStudentWithCourses = async (req, res) => {
  try {
    const student = await Student.findByPk(req.params.id, {
      include: [{
        model: Course,
        as: 'courses',
        through: {
          attributes: ['enrollmentDate', 'grade']
        }
      }]
    });

    res.status(200).json({
      success: true,
      data: student
    });
  } catch (error) {
    res.status(500).json({
      success: false,
      error: error.message
    });
  }
};

// Enroll student in course
const enrollStudent = async (req, res) => {
  try {
    const { studentId, courseId, grade } = req.body;

    const enrollment = await Enrollment.create({
      studentId,
      courseId,
      grade
    });

    res.status(201).json({
      success: true,
      data: enrollment
    });
  } catch (error) {
    res.status(400).json({
      success: false,
      error: error.message
    });
  }
};

Mongoose Implementation

// models/Student.js
const mongoose = require('mongoose');

const studentSchema = new mongoose.Schema({
  name: {
    type: String,
    required: true
  },
  email: {
    type: String,
    required: true,
    unique: true
  },
  courses: [{
    course: {
      type: mongoose.Schema.Types.ObjectId,
      ref: 'Course'
    },
    enrollmentDate: {
      type: Date,
      default: Date.now
    },
    grade: String
  }]
}, {
  timestamps: true
});

const Student = mongoose.model('Student', studentSchema);
module.exports = Student;

// models/Course.js
const mongoose = require('mongoose');

const courseSchema = new mongoose.Schema({
  name: {
    type: String,
    required: true
  },
  code: {
    type: String,
    required: true,
    unique: true
  },
  students: [{
    type: mongoose.Schema.Types.ObjectId,
    ref: 'Student'
  }]
}, {
  timestamps: true
});

const Course = mongoose.model('Course', courseSchema);
module.exports = Course;

// Querying many-to-many
const getStudentWithCourses = async (req, res) => {
  try {
    const student = await Student
      .findById(req.params.id)
      .populate('courses.course', 'name code');

    res.status(200).json({
      success: true,
      data: student
    });
  } catch (error) {
    res.status(500).json({
      success: false,
      error: error.message
    });
  }
};

// Enroll student in course
const enrollStudent = async (req, res) => {
  try {
    const { studentId, courseId, grade } = req.body;

    const student = await Student.findById(studentId);
    const course = await Course.findById(courseId);

    if (!student || !course) {
      return res.status(404).json({
        success: false,
        error: 'Student or Course not found'
      });
    }

    // Add to student's courses
    student.courses.push({
      course: courseId,
      grade
    });
    await student.save();

    // Add to course's students
    course.students.push(studentId);
    await course.save();

    res.status(201).json({
      success: true,
      data: student
    });
  } catch (error) {
    res.status(400).json({
      success: false,
      error: error.message
    });
  }
};

MongoDB Many-to-Many: In MongoDB, you can implement many-to-many relationships using arrays of references in both documents, or create a separate collection for the relationship (similar to SQL junction tables).

Aggregation Queries

Aggregation allows you to process data and return computed results.

Sequelize Aggregation

const { fn, col, literal } = require('sequelize');

// Count posts per user
const countPostsPerUser = async (req, res) => {
  try {
    const users = await User.findAll({
      attributes: [
        'id',
        'username',
        [fn('COUNT', col('posts.id')), 'postCount']
      ],
      include: [{
        model: Post,
        as: 'posts',
        attributes: []
      }],
      group: ['User.id'],
      raw: true
    });

    res.status(200).json({
      success: true,
      data: users
    });
  } catch (error) {
    res.status(500).json({
      success: false,
      error: error.message
    });
  }
};

// Average, sum, min, max
const getStatistics = async (req, res) => {
  try {
    const stats = await Post.findAll({
      attributes: [
        [fn('COUNT', col('id')), 'totalPosts'],
        [fn('AVG', col('viewCount')), 'averageViews'],
        [fn('SUM', col('viewCount')), 'totalViews'],
        [fn('MIN', col('createdAt')), 'oldestPost'],
        [fn('MAX', col('createdAt')), 'newestPost']
      ],
      raw: true
    });

    res.status(200).json({
      success: true,
      data: stats[0]
    });
  } catch (error) {
    res.status(500).json({
      success: false,
      error: error.message
    });
  }
};

Mongoose Aggregation

// Count posts per user
const countPostsPerUser = async (req, res) => {
  try {
    const result = await Post.aggregate([
      {
        $group: {
          _id: '$author',
          postCount: { $sum: 1 }
        }
      },
      {
        $lookup: {
          from: 'users',
          localField: '_id',
          foreignField: '_id',
          as: 'userInfo'
        }
      },
      {
        $unwind: '$userInfo'
      },
      {
        $project: {
          _id: 1,
          username: '$userInfo.username',
          email: '$userInfo.email',
          postCount: 1
        }
      },
      {
        $sort: { postCount: -1 }
      }
    ]);

    res.status(200).json({
      success: true,
      data: result
    });
  } catch (error) {
    res.status(500).json({
      success: false,
      error: error.message
    });
  }
};

// Statistics with aggregation
const getStatistics = async (req, res) => {
  try {
    const stats = await Post.aggregate([
      {
        $group: {
          _id: null,
          totalPosts: { $sum: 1 },
          averageViews: { $avg: '$viewCount' },
          totalViews: { $sum: '$viewCount' },
          minViews: { $min: '$viewCount' },
          maxViews: { $max: '$viewCount' }
        }
      }
    ]);

    res.status(200).json({
      success: true,
      data: stats[0]
    });
  } catch (error) {
    res.status(500).json({
      success: false,
      error: error.message
    });
  }
};

Database Indexing

Indexes improve query performance by allowing the database to find data without scanning every row.

Sequelize Indexes

// In model definition
const User = sequelize.define('User', {
  // ... fields
}, {
  indexes: [
    {
      unique: true,
      fields: ['email']
    },
    {
      fields: ['username']
    },
    {
      fields: ['createdAt', 'isActive']
    },
    {
      type: 'FULLTEXT',
      fields: ['bio']
    }
  ]
});

Mongoose Indexes

const userSchema = new mongoose.Schema({
  email: {
    type: String,
    required: true,
    unique: true,
    index: true
  },
  username: {
    type: String,
    index: true
  },
  bio: {
    type: String
  }
});

// Compound index
userSchema.index({ createdAt: -1, isActive: 1 });

// Text index for searching
userSchema.index({ bio: 'text' });

Transactions

Transactions ensure that a series of database operations either all succeed or all fail, maintaining data consistency.

Sequelize Transactions

const transferFunds = async (req, res) => {
  const t = await sequelize.transaction();

  try {
    const { fromUserId, toUserId, amount } = req.body;

    // Deduct from sender
    await Account.decrement('balance', {
      by: amount,
      where: { userId: fromUserId },
      transaction: t
    });

    // Add to receiver
    await Account.increment('balance', {
      by: amount,
      where: { userId: toUserId },
      transaction: t
    });

    // Create transaction record
    await Transaction.create({
      fromUserId,
      toUserId,
      amount,
      type: 'transfer'
    }, { transaction: t });

    await t.commit();

    res.status(200).json({
      success: true,
      message: 'Transfer completed successfully'
    });
  } catch (error) {
    await t.rollback();

    res.status(400).json({
      success: false,
      error: error.message
    });
  }
};

Mongoose Transactions

const transferFunds = async (req, res) => {
  const session = await mongoose.startSession();
  session.startTransaction();

  try {
    const { fromUserId, toUserId, amount } = req.body;

    // Deduct from sender
    await Account.findOneAndUpdate(
      { userId: fromUserId },
      { $inc: { balance: -amount } },
      { session }
    );

    // Add to receiver
    await Account.findOneAndUpdate(
      { userId: toUserId },
      { $inc: { balance: amount } },
      { session }
    );

    // Create transaction record
    await Transaction.create([{
      fromUserId,
      toUserId,
      amount,
      type: 'transfer'
    }], { session });

    await session.commitTransaction();
    session.endSession();

    res.status(200).json({
      success: true,
      message: 'Transfer completed successfully'
    });
  } catch (error) {
    await session.abortTransaction();
    session.endSession();

    res.status(400).json({
      success: false,
      error: error.message
    });
  }
};

MongoDB Transactions: Transactions in MongoDB require a replica set or sharded cluster. They are not available in standalone MongoDB instances.

Practice Exercise:

Create a blog system with Users, Posts, and Comments (one-to-many relationships)
Implement a tagging system with Posts and Tags (many-to-many)
Create aggregation queries to count posts per user and comments per post
Add indexes to frequently queried fields
Implement a transaction for creating a post with multiple tags atomically
Create nested population queries (posts with author and comments with authors)
Implement pagination with relationship data

Best Practices:

Use eager loading (include/populate) judiciously to avoid performance issues
Add indexes to foreign key columns
Use transactions for operations affecting multiple tables
Consider denormalization for read-heavy applications
Use aggregation pipelines for complex data analysis
Monitor query performance and optimize slow queries
Implement proper cascade delete strategies