Express Sequelize ORM

Introduction

When building web applications with Express, managing database operations efficiently is crucial. While raw SQL queries or simple database drivers work, they often lead to repetitive code and potential security issues. This is where Object-Relational Mapping (ORM) tools like Sequelize come into play.

Sequelize is a promise-based Node.js ORM that supports PostgreSQL, MySQL, SQLite, and MSSQL databases. It provides an abstraction layer that maps JavaScript objects to database entities, making it easier to:

Define models and relationships
Perform CRUD operations
Manage migrations
Handle complex queries with a clean API

In this tutorial, we'll explore how to integrate Sequelize with Express to build robust database-driven applications.

Prerequisites

Before we begin, ensure you have:

Node.js installed (version 12 or higher)
Basic understanding of Express
Basic knowledge of SQL and databases
A MySQL, PostgreSQL, SQLite, or MSSQL database available

Setting Up Sequelize with Express

Step 1: Install Required Packages

First, let's install Sequelize and the database driver you'll be using. For this tutorial, we'll use MySQL:

bash
npm install sequelize mysql2
# Or for PostgreSQL
# npm install sequelize pg pg-hstore
# Or for SQLite
# npm install sequelize sqlite3
# Or for MSSQL
# npm install sequelize tedious

Step 2: Create Database Connection

Create a new file called database.js in your project:

javascript
const { Sequelize } = require('sequelize');

// Option 1: Passing connection parameters directly
const sequelize = new Sequelize('database_name', 'username', 'password', {
  host: 'localhost',
  dialect: 'mysql' // or 'postgres', 'sqlite', 'mssql'
});

// Test the connection
async function testConnection() {
  try {
    await sequelize.authenticate();
    console.log('Database connection established successfully.');
  } catch (error) {
    console.error('Unable to connect to the database:', error);
  }
}

testConnection();

module.exports = sequelize;

Step 3: Define Your First Model

Let's create a models folder and define a User model:

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

const User = sequelize.define('User', {
  // Model attributes are defined here
  firstName: {
    type: DataTypes.STRING,
    allowNull: false
  },
  lastName: {
    type: DataTypes.STRING
  },
  email: {
    type: DataTypes.STRING,
    allowNull: false,
    unique: true,
    validate: {
      isEmail: true
    }
  },
  password: {
    type: DataTypes.STRING,
    allowNull: false
  },
  isActive: {
    type: DataTypes.BOOLEAN,
    defaultValue: true
  }
}, {
  // Other model options go here
  timestamps: true // Adds createdAt and updatedAt fields
});

module.exports = User;

Step 4: Create a Models Index File

For better organization, create an index file to manage all your models:

javascript
// models/index.js
const sequelize = require('../database');
const User = require('./User');

// Define any model associations here
// User.hasMany(Post)

// Sync all models with database
async function syncModels() {
  try {
    await sequelize.sync({ alter: true });
    console.log('Models synchronized with database');
  } catch (error) {
    console.error('Error synchronizing models:', error);
  }
}

// Call this function to sync models when your app starts
syncModels();

module.exports = {
  sequelize,
  User
};

CRUD Operations with Sequelize in Express

Now, let's integrate Sequelize with Express routes to perform CRUD operations:

Create Express Routes for Users

javascript
// routes/users.js
const express = require('express');
const router = express.Router();
const { User } = require('../models');

// GET all users
router.get('/', async (req, res) => {
  try {
    const users = await User.findAll({
      attributes: ['id', 'firstName', 'lastName', 'email', 'isActive'] // Exclude password
    });
    res.json(users);
  } catch (error) {
    res.status(500).json({ message: error.message });
  }
});

// GET a single user
router.get('/:id', async (req, res) => {
  try {
    const user = await User.findByPk(req.params.id, {
      attributes: ['id', 'firstName', 'lastName', 'email', 'isActive']
    });
    
    if (!user) {
      return res.status(404).json({ message: 'User not found' });
    }
    
    res.json(user);
  } catch (error) {
    res.status(500).json({ message: error.message });
  }
});

// CREATE a user
router.post('/', async (req, res) => {
  try {
    const newUser = await User.create(req.body);
    // Exclude password from response
    const { password, ...userWithoutPassword } = newUser.toJSON();
    res.status(201).json(userWithoutPassword);
  } catch (error) {
    res.status(400).json({ 
      message: error.message,
      errors: error.errors?.map(e => e.message) 
    });
  }
});

// UPDATE a user
router.put('/:id', async (req, res) => {
  try {
    const user = await User.findByPk(req.params.id);
    
    if (!user) {
      return res.status(404).json({ message: 'User not found' });
    }
    
    await user.update(req.body);
    
    // Exclude password from response
    const { password, ...userWithoutPassword } = user.toJSON();
    res.json(userWithoutPassword);
  } catch (error) {
    res.status(400).json({ 
      message: error.message,
      errors: error.errors?.map(e => e.message) 
    });
  }
});

// DELETE a user
router.delete('/:id', async (req, res) => {
  try {
    const user = await User.findByPk(req.params.id);
    
    if (!user) {
      return res.status(404).json({ message: 'User not found' });
    }
    
    await user.destroy();
    res.json({ message: 'User deleted successfully' });
  } catch (error) {
    res.status(500).json({ message: error.message });
  }
});

module.exports = router;

Integrate Routes in the Main App

javascript
// app.js
const express = require('express');
const app = express();
const usersRoutes = require('./routes/users');

// Middleware
app.use(express.json());
app.use(express.urlencoded({ extended: true }));

// Routes
app.use('/api/users', usersRoutes);

// Start server
const PORT = process.env.PORT || 3000;
app.listen(PORT, () => {
  console.log(`Server running on port ${PORT}`);
});

Working with Model Relationships

One of Sequelize's strengths is its ability to handle relationships between models. Let's add a Post model and establish a relationship with User.

Define Post Model

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

const Post = sequelize.define('Post', {
  title: {
    type: DataTypes.STRING,
    allowNull: false
  },
  content: {
    type: DataTypes.TEXT,
    allowNull: false
  },
  published: {
    type: DataTypes.BOOLEAN,
    defaultValue: false
  }
});

module.exports = Post;

Setting up Relationships

Update your models/index.js file:

javascript
// models/index.js
const sequelize = require('../database');
const User = require('./User');
const Post = require('./Post');

// Define model associations
User.hasMany(Post);
Post.belongsTo(User);

// Sync all models with database
async function syncModels() {
  try {
    await sequelize.sync({ alter: true });
    console.log('Models synchronized with database');
  } catch (error) {
    console.error('Error synchronizing models:', error);
  }
}

syncModels();

module.exports = {
  sequelize,
  User,
  Post
};

Create Routes for Posts with User Associations

javascript
// routes/posts.js
const express = require('express');
const router = express.Router();
const { Post, User } = require('../models');

// GET all posts with user information
router.get('/', async (req, res) => {
  try {
    const posts = await Post.findAll({
      include: {
        model: User,
        attributes: ['id', 'firstName', 'lastName'] // Include only necessary user info
      }
    });
    res.json(posts);
  } catch (error) {
    res.status(500).json({ message: error.message });
  }
});

// GET posts by a specific user
router.get('/user/:userId', async (req, res) => {
  try {
    const posts = await Post.findAll({
      where: { UserId: req.params.userId },
      include: {
        model: User,
        attributes: ['id', 'firstName', 'lastName']
      }
    });
    res.json(posts);
  } catch (error) {
    res.status(500).json({ message: error.message });
  }
});

// CREATE a post for a user
router.post('/', async (req, res) => {
  try {
    const { userId, ...postData } = req.body;
    
    // Verify user exists
    const user = await User.findByPk(userId);
    if (!user) {
      return res.status(404).json({ message: 'User not found' });
    }
    
    // Create post associated with user
    const post = await Post.create({
      ...postData,
      UserId: userId
    });
    
    res.status(201).json(post);
  } catch (error) {
    res.status(400).json({ 
      message: error.message,
      errors: error.errors?.map(e => e.message) 
    });
  }
});

module.exports = router;

Update your app.js to include these routes:

javascript
// app.js
const postsRoutes = require('./routes/posts');

// Add this with other routes
app.use('/api/posts', postsRoutes);

Advanced Sequelize Features

Using Transactions

Transactions ensure database operations are performed atomically:

javascript
// Example transaction for transferring funds between accounts
const { sequelize } = require('./models');

async function transferFunds(fromAccountId, toAccountId, amount) {
  const transaction = await sequelize.transaction();
  
  try {
    // Get accounts (assuming an Account model exists)
    const fromAccount = await Account.findByPk(fromAccountId, { transaction });
    const toAccount = await Account.findByPk(toAccountId, { transaction });
    
    if (!fromAccount || !toAccount) {
      throw new Error('One or both accounts not found');
    }
    
    if (fromAccount.balance < amount) {
      throw new Error('Insufficient funds');
    }
    
    // Update balances
    await fromAccount.update(
      { balance: fromAccount.balance - amount },
      { transaction }
    );
    
    await toAccount.update(
      { balance: toAccount.balance + amount },
      { transaction }
    );
    
    // If all operations succeed, commit the transaction
    await transaction.commit();
    return true;
  } catch (error) {
    // If any operation fails, roll back the transaction
    await transaction.rollback();
    throw error;
  }
}

Complex Queries with Sequelize

Sequelize provides a rich API for complex queries:

javascript
// Example of complex querying
const { Op } = require('sequelize');
const { User, Post } = require('./models');

async function findActiveUsersWithPublishedPosts() {
  try {
    const users = await User.findAll({
      where: {
        isActive: true,
        createdAt: {
          [Op.gte]: new Date(new Date() - 30 * 24 * 60 * 60 * 1000) // Users created in last 30 days
        }
      },
      include: {
        model: Post,
        where: {
          published: true
        },
        required: true // Inner join - only users with published posts
      },
      order: [
        ['createdAt', 'DESC'],
        [Post, 'createdAt', 'DESC']
      ],
      limit: 10
    });
    
    return users;
  } catch (error) {
    console.error('Error in complex query:', error);
    throw error;
  }
}

Migrations with Sequelize CLI

For production applications, using migrations is recommended instead of syncing models directly:

First, install the Sequelize CLI:

bash
npm install --save-dev sequelize-cli

Initialize Sequelize in your project:

bash
npx sequelize-cli init

This creates several folders:

config: Database configuration
migrations: Migration files
models: Model definitions
seeders: Seed data files

Create a migration:

bash
npx sequelize-cli migration:generate --name create-users-table

Edit the generated migration file:

javascript
'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
      },
      email: {
        type: Sequelize.STRING,
        allowNull: false,
        unique: true
      },
      password: {
        type: Sequelize.STRING,
        allowNull: false
      },
      isActive: {
        type: Sequelize.BOOLEAN,
        defaultValue: true
      },
      createdAt: {
        allowNull: false,
        type: Sequelize.DATE
      },
      updatedAt: {
        allowNull: false,
        type: Sequelize.DATE
      }
    });
  },
  
  down: async (queryInterface, Sequelize) => {
    await queryInterface.dropTable('Users');
  }
};

Run the migration:

bash
npx sequelize-cli db:migrate

Real-World Example: Building a Blog API

Let's combine everything we've learned to build a simple blog API with users, posts, and comments:

Project Structure

📁 blog-api
  📁 config
    📄 database.js
  📁 models
    📄 index.js
    📄 User.js
    📄 Post.js
    📄 Comment.js
  📁 routes
    📄 users.js
    📄 posts.js
    📄 comments.js
  📄 app.js
  📄 package.json

Models

javascript
// models/User.js - as we defined earlier

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

const Post = sequelize.define('Post', {
  title: {
    type: DataTypes.STRING,
    allowNull: false
  },
  content: {
    type: DataTypes.TEXT,
    allowNull: false
  },
  published: {
    type: DataTypes.BOOLEAN,
    defaultValue: false
  }
});

module.exports = Post;

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

const Comment = sequelize.define('Comment', {
  content: {
    type: DataTypes.TEXT,
    allowNull: false
  }
});

module.exports = Comment;

Set up relationships

javascript
// models/index.js
const sequelize = require('../config/database');
const User = require('./User');
const Post = require('./Post');
const Comment = require('./Comment');

// One-to-Many: User has many Posts
User.hasMany(Post);
Post.belongsTo(User);

// One-to-Many: User has many Comments
User.hasMany(Comment);
Comment.belongsTo(User);

// One-to-Many: Post has many Comments
Post.hasMany(Comment);
Comment.belongsTo(Post);

// Sync all models with database
async function syncModels() {
  try {
    await sequelize.sync();
    console.log('Models synchronized with database');
  } catch (error) {
    console.error('Error synchronizing models:', error);
  }
}

module.exports = {
  sequelize,
  User,
  Post, 
  Comment,
  syncModels
};

Main App File

javascript
// app.js
const express = require('express');
const { syncModels } = require('./models');
const usersRoutes = require('./routes/users');
const postsRoutes = require('./routes/posts');
const commentsRoutes = require('./routes/comments');

const app = express();

// Middleware
app.use(express.json());
app.use(express.urlencoded({ extended: true }));

// Routes
app.use('/api/users', usersRoutes);
app.use('/api/posts', postsRoutes);
app.use('/api/comments', commentsRoutes);

// Sync models and start server
const PORT = process.env.PORT || 3000;

async function startServer() {
  try {
    await syncModels();
    app.listen(PORT, () => {
      console.log(`Server running on port ${PORT}`);
    });
  } catch (error) {
    console.error('Failed to start server:', error);
  }
}

startServer();

This structured approach creates a clean API with proper separation of concerns that can handle complex relationships and queries.

Best Practices

Separate concerns: Keep models, routes, and controllers in separate files/folders
Use environment variables: Store database credentials in environment variables
Implement proper error handling: Handle database errors gracefully
Use transactions for operations that require multiple changes
Optimize queries: Use appropriate Sequelize methods to minimize database load
Use migrations for database schema changes in production
Implement proper validation both at the model level and in route handlers
Consider connection pooling for production applications

Summary

In this tutorial, we covered:

Setting up Sequelize with Express
Defining models and their relationships
Performing CRUD operations
Working with associations
Using transactions and complex queries
Implementing migrations
Building a real-world API with proper structure

Sequelize provides a powerful abstraction over raw SQL queries while maintaining flexibility for complex operations. It helps maintain clean code, supports relationships between models, and offers built-in validation and security features.

By using Sequelize with Express, you can build robust, database-driven applications more efficiently, focusing on your application's business logic rather than writing repetitive database code.

Additional Resources

Exercises

Create a simple Express API with Sequelize for managing a book library (books, authors, genres)
Implement authentication using Sequelize and Express with JWT tokens
Create a RESTful API for a task management application with users, tasks, and categories
Implement data pagination for a list of records using Sequelize's limit and offset options
Build a simple e-commerce API with products, categories, and orders

If you spot any mistakes on this website, please let me know at [email protected]. I’d greatly appreciate your feedback! :)

Introduction​

Prerequisites​

Setting Up Sequelize with Express​

Step 1: Install Required Packages​

Step 2: Create Database Connection​

Step 3: Define Your First Model​

Step 4: Create a Models Index File​

CRUD Operations with Sequelize in Express​

Create Express Routes for Users​

Integrate Routes in the Main App​

Working with Model Relationships​

Define Post Model​

Setting up Relationships​

Create Routes for Posts with User Associations​

Advanced Sequelize Features​

Using Transactions​

Complex Queries with Sequelize​

Migrations with Sequelize CLI​

Real-World Example: Building a Blog API​

Project Structure​

Models​

Set up relationships​

Main App File​

Best Practices​

Summary​

Additional Resources​

Exercises​