Sequelize ORM with Express.js

Sequelize is a powerful and widely-used Object-Relational Mapping (ORM) library for Node.js that supports various SQL databases, including MySQL, PostgreSQL, SQLite, and MariaDB. It provides an abstraction layer over SQL queries, allowing developers to interact with their database using JavaScript instead of writing raw SQL queries. This makes it easier to manage and manipulate data, especially when working with complex data models.

What is Sequelize?

Sequelize is an ORM that helps in managing databases by abstracting complex SQL queries into JavaScript functions. It supports various relational databases and provides features like data validation, associations, and query building.

Why Use Sequelize?

Abstraction: Interact with the database using JavaScript instead of raw SQL queries.
Cross-Database Compatibility: Supports multiple SQL databases.
Data Validation: Automatically validates data according to defined models.
Associations: Easily define relationships between different models (tables).
Seamless Integration: Works smoothly with Express.js and other Node.js applications.

Setting Up Sequelize with Express.js

Installing Dependencies

To start using Sequelize with Express.js, you need to install the required packages.

Installation:

				
					npm install express sequelize sequelize-cli mysql2

express: The Express.js framework.
sequelize: The Sequelize ORM library.
sequelize-cli: Command-line interface for Sequelize, useful for generating models, migrations, and more.
mysql2: A MySQL client for Node.js, required for connecting to a MySQL database.

Initializing Sequelize

Sequelize provides a command-line tool to initialize the project structure.

Initialize Sequelize:

				
					npx sequelize-cli init

This command will create the following structure:

config/: Configuration files for different environments (development, production).
models/: This folder contains all the database models (tables).
migrations/: Migrations are used to modify the database schema over time.
seeders/: Seed files are used to populate the database with initial data.

Configuring the Database

After initializing Sequelize, you need to configure the database connection.

File: `config/config.json`

				
					{
  "development": {
    "username": "root",
    "password": "password",
    "database": "mydatabase",
    "host": "127.0.0.1",
    "dialect": "mysql"
  },
  "test": {
    "username": "root",
    "password": "password",
    "database": "database_test",
    "host": "127.0.0.1",
    "dialect": "mysql"
  },
  "production": {
    "username": "root",
    "password": "password",
    "database": "database_production",
    "host": "127.0.0.1",
    "dialect": "mysql"
  }
}

username: The database username.
password: The database password.
database: The database name.
host: The database host.
dialect: The type of SQL database (e.g., mysql, postgres, sqlite).

Defining Models

What are Models in Sequelize?

In Sequelize, a model represents a table in the database. Each model is a JavaScript class that defines the structure of the table, including its columns and data types.

Creating a Model

You can create a model using Sequelize’s command-line tool.

Create a User Model:

				
					npx sequelize-cli model:generate --name User --attributes name:string,email:string,age:integer

This command generates a model file in the models/ directory and a migration file in the migrations/ directory.

File: `models/user.js`

				
					'use strict';
const { Model } = require('sequelize');
module.exports = (sequelize, DataTypes) => {
  class User extends Model {
    static associate(models) {
      // define association here
    }
  }
  User.init({
    name: DataTypes.STRING,
    email: DataTypes.STRING,
    age: DataTypes.INTEGER
  }, {
    sequelize,
    modelName: 'User',
  });
  return User;
};

Explanation:

DataTypes.STRING: Defines a string column.
DataTypes.INTEGER: Defines an integer column.
User.init(): Initializes the model with the specified attributes.

Running Migrations

Migrations are used to apply changes to the database schema, such as creating or altering tables.

Run Migration:

				
					npx sequelize-cli db:migrate

This command will create the Users table in the database with the columns name, email, and age.

Performing CRUD Operations

Create Operation

To create a new record in the database, you can use the create() method provided by Sequelize.

File: `app.js`

				
					const express = require('express');
const { User } = require('./models');
const app = express();

app.use(express.json());

app.post('/users', async (req, res) => {
  try {
    const user = await User.create(req.body);
    res.status(201).json(user);
  } catch (err) {
    res.status(400).json({ error: err.message });
  }
});

app.listen(3000, () => {
  console.log('Server is running on port 3000');
});

Explanation:

User.create(): Creates a new user record in the Users table using the data provided in the request body.
req.body: Contains the user data (e.g., { name: 'John', email: 'john@example.com', age: 25 }).

Output:

When you send a POST request to /users with the user data, a new record is inserted into the Users table, and the created user is returned in the response.

Read Operation

To read data from the database, you can use the findAll() and findOne() methods.

File: `app.js` (continued)

				
					app.get('/users', async (req, res) => {
  try {
    const users = await User.findAll();
    res.json(users);
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

app.get('/users/:id', async (req, res) => {
  try {
    const user = await User.findOne({ where: { id: req.params.id } });
    if (user) {
      res.json(user);
    } else {
      res.status(404).json({ error: 'User not found' });
    }
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

Explanation:

User.findAll(): Retrieves all user records from the Users table.
User.findOne(): Retrieves a single user record based on the provided criteria (e.g., id).

Output:

When you send a GET request to /users, all users in the Users table are returned.
When you send a GET request to /users/:id, the user with the specified ID is returned, or a 404 error is returned if the user does not exist.

Update Operation

To update an existing record, you can use the update() method.

File: `app.js` (continued)

				
					app.put('/users/:id', async (req, res) => {
  try {
    const [updated] = await User.update(req.body, {
      where: { id: req.params.id }
    });
    if (updated) {
      const updatedUser = await User.findOne({ where: { id: req.params.id } });
      res.json(updatedUser);
    } else {
      res.status(404).json({ error: 'User not found' });
    }
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

Explanation:

User.update(): Updates the user record with the specified id using the data provided in the request body.
[updated]: The number of records that were updated.

Output:

When you send a PUT request to /users/:id with the updated data, the specified user record is updated, and the updated user is returned in the response.

Delete Operation

To delete a record from the database, you can use the destroy() method.

File: `app.js` (continued)

				
					app.delete('/users/:id', async (req, res) => {
  try {
    const deleted = await User.destroy({
      where: { id: req.params.id }
    });
    if (deleted) {
      res.json({ message: 'User deleted' });
    } else {
      res.status(404).json({ error: 'User not found' });
    }
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

Explanation:

User.destroy(): Deletes the user record with the specified id.

Output:

When you send a DELETE request to /users/:id, the specified user record is deleted, and a confirmation message is returned in the response.

Associations in Sequelize

Defining Associations

Sequelize allows you to define associations (relationships) between different models, such as one-to-many, many-to-many, and one-to-one relationships.

Example: Let’s create a Post model that has a belongsTo relationship with the User model.

File: `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' });
    }
  }
  Post.init({
    title: DataTypes.STRING,
    content: DataTypes.TEXT,
    userId: DataTypes.INTEGER
  }, {
    sequelize,
    modelName: 'Post',
  });
  return Post;
};

Explanation:

Post.belongsTo(models.User, { foreignKey: ‘userId’ }): Defines a belongsTo relationship between Post and User. Each post belongs to a single user.

Using Associations

You can use associations to retrieve related data. For example, you can fetch a user along with their posts.

File: `app.js` (continued)

				
					app.get('/users/:id/posts', async (req, res) => {
  try {
    const user = await User.findOne({
      where: { id: req.params.id },
      include: Post
    });
    if (user) {
      res.json(user);
    } else {
      res.status(404).json({ error: 'User not found' });
    }
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

Explanation:

include: Post: Includes the related posts when retrieving a user.

Output:

When you send a GET request to /users/:id/posts, the user and their associated posts are returned.

Sequelize is a powerful ORM that simplifies database interactions in Express.js applications. By using Sequelize, developers can focus more on the application logic rather than writing and managing complex SQL queries. Happy coding !❤️

Sequelize ORM with Express.js

What is Sequelize?

Why Use Sequelize?

Setting Up Sequelize with Express.js

Installing Dependencies

Installation:

Initializing Sequelize

Initialize Sequelize:

Configuring the Database

File: config/config.json

Defining Models

What are Models in Sequelize?

Creating a Model

Create a User Model:

File: models/user.js

Explanation:

Running Migrations

Run Migration:

Performing CRUD Operations

Create Operation

File: app.js

Explanation:

Output:

Read Operation

File: app.js (continued)

Explanation:

Output:

Update Operation

File: app.js (continued)

Explanation:

Output:

Delete Operation

File: app.js (continued)

Explanation:

Output:

Associations in Sequelize

Defining Associations

File: models/post.js

Explanation:

Using Associations

File: app.js (continued)

Explanation:

Output:

Table of Contents

Explore

Popular Tutorials

Contact here

File: `config/config.json`

File: `models/user.js`

File: `app.js`

File: `app.js` (continued)

File: `app.js` (continued)

File: `app.js` (continued)

File: `models/post.js`

File: `app.js` (continued)