Node.js MySQL Sequelize Complete Tutorial: From Basics to Master Level

Sequelize is the most popular ORM (Object-Relational Mapping) for Node.js, providing an easy way to interact with MySQL, PostgreSQL, SQLite, and MSSQL databases. This comprehensive tutorial will take you from a complete beginner to a master-level Sequelize developer, covering everything from basic setup to advanced techniques.

1. [Introduction to Sequelize](#introduction)

2. [Project Setup](#setup)

3. [Basic Configuration](#configuration)

4. [Models and Migrations](#models)

5. [CRUD Operations](#crud)

6. [Advanced Queries](#advanced-queries)

7. [Relations and Associations](#relations)

8. [Indexing and Performance](#indexing)

9. [Transactions](#transactions)

10. [Hooks and Lifecycle Events](#hooks)

11. [Scopes and Query Optimization](#scopes)

12. [Raw Queries](#raw-queries)

13. [Best Practices](#best-practices)

Introduction to Sequelize {#introduction}

Sequelize is a promise-based Node.js ORM that provides:

Database Abstraction: Write database-agnostic code

Type Safety: TypeScript support

Migrations: Version control for database schema

Relations: Easy handling of associations

Validation: Built-in data validation

Hooks: Lifecycle events for models

Why Use Sequelize?

✅ Security: Protection against SQL injection

✅ Productivity: Less boilerplate code

✅ Maintainability: Clean, organized code

✅ Relations: Easy handling of complex relationships

✅ Migrations: Version-controlled schema changes

Project Setup {#setup}

Step 1: Install Dependencies

bash

1# Create new project
2mkdir nodejs-sequelize-tutorial
3cd nodejs-sequelize-tutorial
4npm init -y
5 
6# Install Sequelize and MySQL driver
7npm install sequelize mysql2
8 
9# Install Sequelize CLI for migrations
10npm install --save-dev sequelize-cli
11 
12# Install dotenv for environment variables
13npm install dotenv

Step 2: Project Structure

bash

1nodejs-sequelize-tutorial/
2├── config/
3│   └── database.js
4├── models/
5│   ├── index.js
6│   └── User.js
7├── migrations/
8├── seeders/
9├── .env
10└── app.js

Step 3: Initialize Sequelize

bash

1# Initialize Sequelize (creates folders)
2npx sequelize-cli init

Basic Configuration {#configuration}

Environment Variables (.env)

env

1DB_HOST=localhost
2DB_PORT=3306
3DB_NAME=myapp_db
4DB_USER=root
5DB_PASSWORD=your_password
6DB_DIALECT=mysql

Database Configuration (config/database.js)

javascript(34 lines, showing 15)

1require('dotenv').config();
2 
3module.exports = {
4  development: {
5    username: process.env.DB_USER,
6    password: process.env.DB_PASSWORD,
7    database: process.env.DB_NAME,
8    host: process.env.DB_HOST,
9    port: process.env.DB_PORT,
10    dialect: 'mysql',
11    logging: console.log, // Show SQL queries
12    pool: {
13      max: 5,
14      min: 0,
15      acquire: 30000,

Initialize Sequelize Connection (models/index.js)

javascript(37 lines, showing 15)

1const { Sequelize } = require('sequelize');
2const config = require('../config/database');
3 
4const env = process.env.NODE_ENV || 'development';
5const dbConfig = config[env];
6 
7const sequelize = new Sequelize(
8  dbConfig.database,
9  dbConfig.username,
10  dbConfig.password,
11  {
12    host: dbConfig.host,
13    port: dbConfig.port,
14    dialect: dbConfig.dialect,
15    logging: dbConfig.logging,

Models and Migrations {#models}

Creating Your First Model

User Model (models/User.js)

javascript(74 lines, showing 15)

1const { DataTypes } = require('sequelize');
2const sequelize = require('./index');
3 
4const User = sequelize.define('User', {
5  id: {
6    type: DataTypes.INTEGER,
7    primaryKey: true,
8    autoIncrement: true
9  },
10  email: {
11    type: DataTypes.STRING(255),
12    allowNull: false,
13    unique: true,
14    validate: {
15      isEmail: true

Creating Migrations

bash

1# Create migration
2npx sequelize-cli migration:generate --name create-users-table

Migration File (migrations/XXXXXX-create-users-table.js)

javascript(76 lines, showing 15)

1'use strict';
2 
3module.exports = {
4  async up(queryInterface, Sequelize) {
5    await queryInterface.createTable('users', {
6      id: {
7        allowNull: false,
8        autoIncrement: true,
9        primaryKey: true,
10        type: Sequelize.INTEGER
11      },
12      email: {
13        type: Sequelize.STRING(255),
14        allowNull: false,
15        unique: true

Running Migrations

bash

1# Run all pending migrations
2npx sequelize-cli db:migrate
3 
4# Rollback last migration
5npx sequelize-cli db:migrate:undo
6 
7# Rollback all migrations
8npx sequelize-cli db:migrate:undo:all

CRUD Operations {#crud}

Create (INSERT)

javascript(37 lines, showing 15)

1const User = require('./models/User');
2 
3// Method 1: Using create()
4const user = await User.create({
5  email: 'john@example.com',
6  username: 'johndoe',
7  password: 'hashedpassword123',
8  firstName: 'John',
9  lastName: 'Doe',
10  age: 30,
11  role: 'user'
12});
13 
14// Method 2: Using build() then save()
15const newUser = User.build({

Read (SELECT)

javascript(43 lines, showing 15)

1// Find by primary key
2const user = await User.findByPk(1);
3 
4// Find one with conditions
5const user = await User.findOne({
6  where: {
7    email: 'john@example.com'
8  }
9});
10 
11// Find all
12const users = await User.findAll();
13 
14// Find all with conditions
15const activeUsers = await User.findAll({

Update (UPDATE)

javascript(26 lines, showing 15)

1// Method 1: Update instance then save
2const user = await User.findByPk(1);
3user.firstName = 'Jane';
4user.age = 25;
5await user.save();
6 
7// Method 2: Using update() method
8await user.update({
9  firstName: 'Jane',
10  age: 25
11});
12 
13// Method 3: Bulk update
14await User.update(
15  {

Delete (DELETE)

javascript(16 lines, showing 15)

1// Method 1: Delete instance
2const user = await User.findByPk(1);
3await user.destroy();
4 
5// Method 2: Bulk delete
6await User.destroy({
7  where: {
8    isActive: false,
9    createdAt: {
10      [Op.lt]: new Date('2020-01-01')
11    }
12  }
13});
14 
15// Soft delete (if paranoid is enabled)

Advanced Queries {#advanced-queries}

Operators

javascript(75 lines, showing 15)

1const { Op } = require('sequelize');
2 
3// AND conditions
4const users = await User.findAll({
5  where: {
6    [Op.and]: [
7      { isActive: true },
8      { role: 'user' }
9    ]
10  }
11});
12 
13// OR conditions
14const users = await User.findAll({
15  where: {

Complex Queries

javascript(29 lines, showing 15)

1// Group by with aggregation
2const users = await User.findAll({
3  attributes: [
4    'role',
5    [sequelize.fn('COUNT', sequelize.col('id')), 'count'],
6    [sequelize.fn('AVG', sequelize.col('age')), 'avgAge']
7  ],
8  group: ['role'],
9  having: sequelize.literal('COUNT(id) > 5')
10});
11 
12// Subqueries
13const users = await User.findAll({
14  where: {
15    id: {

Eager Loading (JOIN)

javascript(37 lines, showing 15)

1// Load user with posts (one-to-many)
2const user = await User.findByPk(1, {
3  include: [{
4    model: Post,
5    as: 'posts'
6  }]
7});
8 
9// Load with multiple associations
10const user = await User.findByPk(1, {
11  include: [
12    {
13      model: Post,
14      as: 'posts',
15      where: {

Relations and Associations {#relations}

One-to-One Relationship

User and Profile Models

javascript(62 lines, showing 15)

1// models/User.js
2const User = sequelize.define('User', {
3  // ... user fields
4});
5 
6// models/Profile.js
7const Profile = sequelize.define('Profile', {
8  id: {
9    type: DataTypes.INTEGER,
10    primaryKey: true,
11    autoIncrement: true
12  },
13  userId: {
14    type: DataTypes.INTEGER,
15    unique: true,

One-to-Many Relationship

User and Post Models

javascript(66 lines, showing 15)

1// models/Post.js
2const Post = sequelize.define('Post', {
3  id: {
4    type: DataTypes.INTEGER,
5    primaryKey: true,
6    autoIncrement: true
7  },
8  userId: {
9    type: DataTypes.INTEGER,
10    references: {
11      model: 'users',
12      key: 'id'
13    }
14  },
15  title: {

Many-to-Many Relationship

Post and Tag Models

javascript(76 lines, showing 15)

1// models/Tag.js
2const Tag = sequelize.define('Tag', {
3  id: {
4    type: DataTypes.INTEGER,
5    primaryKey: true,
6    autoIncrement: true
7  },
8  name: {
9    type: DataTypes.STRING(100),
10    allowNull: false,
11    unique: true
12  }
13});
14 
15// Junction table (PostTag)

Polymorphic Associations

javascript(44 lines, showing 15)

1// models/Comment.js
2const Comment = sequelize.define('Comment', {
3  id: {
4    type: DataTypes.INTEGER,
5    primaryKey: true,
6    autoIncrement: true
7  },
8  commentableType: {
9    type: DataTypes.STRING,
10    allowNull: false
11  },
12  commentableId: {
13    type: DataTypes.INTEGER,
14    allowNull: false
15  },

Indexing and Performance {#indexing}

Adding Indexes

javascript(51 lines, showing 15)

1// Single column index
2User.init({
3  // ... fields
4}, {
5  indexes: [
6    {
7      unique: true,
8      fields: ['email']
9    },
10    {
11      fields: ['username']
12    }
13  ]
14});
15

Performance Optimization

javascript(43 lines, showing 15)

1// 1. Use select specific columns
2const users = await User.findAll({
3  attributes: ['id', 'email', 'username'] // Only fetch needed columns
4});
5 
6// 2. Use limit and pagination
7const users = await User.findAll({
8  limit: 20,
9  offset: 0
10});
11 
12// 3. Use indexes for where clauses
13const users = await User.findAll({
14  where: {
15    email: 'john@example.com' // Uses email index

Transactions {#transactions}

Basic Transaction

javascript(40 lines, showing 15)

1const sequelize = require('./models/index');
2 
3// Method 1: Using transaction callback
4await sequelize.transaction(async (t) => {
5  const user = await User.create({
6    email: 'john@example.com',
7    username: 'johndoe',
8    password: 'password'
9  }, { transaction: t });
10 
11  await Post.create({
12    userId: user.id,
13    title: 'First Post',
14    content: 'Content here'
15  }, { transaction: t });

Advanced Transaction Usage

javascript(27 lines, showing 15)

1// Transaction with savepoints
2const t = await sequelize.transaction();
3 
4try {
5  const user = await User.create({...}, { transaction: t });
6 
7  // Create savepoint
8  const savepoint = await t.savepoint('sp1');
9 
10  try {
11    await Post.create({...}, { transaction: t });
12  } catch (error) {
13    // Rollback to savepoint
14    await t.rollback(savepoint);
15  }

Hooks and Lifecycle Events {#hooks}

Model Hooks

javascript(40 lines, showing 15)

1// Before create
2User.beforeCreate(async (user, options) => {
3  // Hash password before saving
4  user.password = await bcrypt.hash(user.password, 10);
5});
6 
7// After create
8User.afterCreate(async (user, options) => {
9  // Send welcome email
10  await sendWelcomeEmail(user.email);
11});
12 
13// Before update
14User.beforeUpdate(async (user, options) => {
15  // Hash password if it changed

Association Hooks

javascript(16 lines, showing 15)

1// After adding association
2User.hasMany(Post, {
3  foreignKey: 'userId',
4  as: 'posts',
5  hooks: true
6});
7 
8User.afterCreate(async (user, options) => {
9  // Create default post for new user
10  await Post.create({
11    userId: user.id,
12    title: 'Welcome Post',
13    content: 'Welcome to our platform!',
14    published: true
15  });

Scopes and Query Optimization {#scopes}

Defining Scopes

javascript(38 lines, showing 15)

1User.init({
2  // ... fields
3}, {
4  scopes: {
5    active: {
6      where: {
7        isActive: true
8      }
9    },
10    admins: {
11      where: {
12        role: 'admin'
13      }
14    },
15    olderThan: (age) => ({

Default Scope

javascript(28 lines, showing 15)

1User.init({
2  // ... fields
3}, {
4  defaultScope: {
5    where: {
6      isActive: true
7    },
8    attributes: {
9      exclude: ['password']
10    }
11  },
12  scopes: {
13    withPassword: {
14      attributes: {
15        include: ['password']

Raw Queries {#raw-queries}

Select Queries

javascript(38 lines, showing 15)

1const { QueryTypes } = require('sequelize');
2 
3// Simple select
4const users = await sequelize.query(
5  'SELECT * FROM users WHERE isActive = :isActive',
6  {
7    replacements: { isActive: true },
8    type: QueryTypes.SELECT
9  }
10);
11 
12// Select with model mapping
13const users = await sequelize.query(
14  'SELECT * FROM users',
15  {

Insert/Update/Delete Queries

javascript(30 lines, showing 15)

1// Insert
2await sequelize.query(
3  'INSERT INTO users (email, username, password) VALUES (:email, :username, :password)',
4  {
5    replacements: {
6      email: 'john@example.com',
7      username: 'johndoe',
8      password: 'hashedpassword'
9    },
10    type: QueryTypes.INSERT
11  }
12);
13 
14// Update
15await sequelize.query(

Best Practices {#best-practices}

1. Always Use Transactions for Multiple Operations

javascript

1// Good
2await sequelize.transaction(async (t) => {
3  const user = await User.create({...}, { transaction: t });
4  await Post.create({...}, { transaction: t });
5});

2. Use Parameterized Queries

javascript

1// Good: Prevents SQL injection
2await User.findAll({
3  where: {
4    email: userInput
5  }
6});
7 
8// Bad: Vulnerable to SQL injection
9await sequelize.query(`SELECT * FROM users WHERE email = '${userInput}'`);

3. Index Frequently Queried Columns

javascript

1// Add indexes for columns used in WHERE, JOIN, ORDER BY
2User.init({
3  // ... fields
4}, {
5  indexes: [
6    { fields: ['email'] },
7    { fields: ['isActive', 'role'] }
8  ]
9});

4. Use Eager Loading to Avoid N+1 Queries

javascript

1// Good: Single query with JOIN
2const users = await User.findAll({
3  include: [{ model: Post, as: 'posts' }]
4});
5 
6// Bad: N+1 queries
7const users = await User.findAll();
8for (const user of users) {
9  const posts = await user.getPosts();
10}

5. Validate Data at Model Level

javascript(16 lines, showing 15)

1User.init({
2  email: {
3    type: DataTypes.STRING,
4    validate: {
5      isEmail: true,
6      notEmpty: true
7    }
8  },
9  age: {
10    type: DataTypes.INTEGER,
11    validate: {
12      min: 0,
13      max: 150
14    }
15  }

6. Use Migrations for Schema Changes

bash

1# Always use migrations, never modify database directly
2npx sequelize-cli migration:generate --name add-column-to-users

7. Handle Errors Properly

javascript

1try {
2  const user = await User.create({...});
3} catch (error) {
4  if (error.name === 'SequelizeUniqueConstraintError') {
5    // Handle unique constraint violation
6  } else if (error.name === 'SequelizeValidationError') {
7    // Handle validation errors
8  } else {
9    // Handle other errors
10  }
11}

Complete Example: Blog Application

javascript(97 lines, showing 15)

1// app.js - Complete example
2const express = require('express');
3const sequelize = require('./models/index');
4const User = require('./models/User');
5const Post = require('./models/Post');
6const Tag = require('./models/Tag');
7 
8const app = express();
9app.use(express.json());
10 
11// Create user with posts
12app.post('/api/users', async (req, res) => {
13  try {
14    const user = await sequelize.transaction(async (t) => {
15      const newUser = await User.create(req.body, { transaction: t });

Conclusion

Sequelize is a powerful ORM that simplifies database operations in Node.js applications. This tutorial covered:

✅ Basic setup and configuration

✅ Models and migrations

✅ CRUD operations

✅ Advanced queries and operators

✅ All types of relationships

✅ Indexing and performance optimization

✅ Transactions

✅ Hooks and lifecycle events

✅ Scopes

✅ Raw queries

✅ Best practices

Master these concepts and you'll be able to build robust, scalable Node.js applications with MySQL and Sequelize. Remember to always use migrations, transactions for multiple operations, and eager loading to optimize performance. Happy coding!

Node.js MySQL Sequelize Complete Tutorial: From Basics to Master Level

Table of Contents

Introduction to Sequelize {#introduction}

Why Use Sequelize?

Project Setup {#setup}

Step 1: Install Dependencies

Step 2: Project Structure

Step 3: Initialize Sequelize

Basic Configuration {#configuration}

Environment Variables (.env)

Database Configuration (config/database.js)

Initialize Sequelize Connection (models/index.js)

Models and Migrations {#models}

Creating Your First Model

Creating Migrations

Running Migrations

CRUD Operations {#crud}

Create (INSERT)

Read (SELECT)

Update (UPDATE)

Delete (DELETE)

Advanced Queries {#advanced-queries}

Operators

Complex Queries

Eager Loading (JOIN)

Relations and Associations {#relations}

One-to-One Relationship

One-to-Many Relationship

Many-to-Many Relationship

Polymorphic Associations

Indexing and Performance {#indexing}

Adding Indexes

Performance Optimization

Transactions {#transactions}

Basic Transaction

Advanced Transaction Usage

Hooks and Lifecycle Events {#hooks}

Model Hooks

Association Hooks

Scopes and Query Optimization {#scopes}

Defining Scopes

Default Scope

Raw Queries {#raw-queries}

Select Queries

Insert/Update/Delete Queries

Best Practices {#best-practices}

1. Always Use Transactions for Multiple Operations

2. Use Parameterized Queries

3. Index Frequently Queried Columns

4. Use Eager Loading to Avoid N+1 Queries

5. Validate Data at Model Level

6. Use Migrations for Schema Changes

7. Handle Errors Properly

Complete Example: Blog Application

Conclusion

Enjoyed this article?

Stay Updated

Related Articles

Top 10 Must-Have Developer Tools in 2025

JSON Best Practices for Modern Applications