MySQL with Sequelize

Sequelize is a promise-based Node.js ORM (Object-Relational Mapping) that makes it easier to work with relational databases such as MySQL. It abstracts the complexities of database interactions and provides a clean API to perform CRUD (Create, Read, Update, Delete) operations, manage associations, and run queries. This chapter will guide you through the process of integrating MySQL with Sequelize in a Node.js application, starting from the basics and moving to more advanced topics.

Setting Up the Environment

Before we start using Sequelize, we need to set up our environment. Ensure you have Node.js and MySQL installed on your machine.

Installing Node.js

You can download Node.js from the official website.

Installing MySQL

You can download MySQL from the official website.

Setting Up a New Node.js Project

1.Create a new directory for your project and navigate into it.

				
					mkdir sequelize-mysql-example
cd sequelize-mysql-example

2.Initialize a new Node.js project.

				
					npm init -y

3.Install necessary packages.

				
					npm install express mysql2 sequelize

Creating a MySQL Database

Open your MySQL client and create a new database.

				
					CREATE DATABASE sequelize_example;

Installing and Configuring Sequelize

Sequelize requires two main packages: sequelize and mysql2.

Installing Packages

We have already installed the required packages in the previous section. If you haven’t, run:

				
					npm install sequelize mysql2

Configuring Sequelize

Create a new file config/database.js to set up the Sequelize configuration.

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

// Create a new instance of Sequelize
const sequelize = new Sequelize('sequelize_example', 'root', 'password', {
    host: 'localhost',
    dialect: 'mysql'
});

module.exports = sequelize;

Replace 'root' and 'password' with your MySQL username and password.

Defining Models

Creating a User Model

Create a new directory models and add a file User.js.

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

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

module.exports = User;

Syncing the Model with the Database

To sync our model with the database, we need to create a script. Create a file sync.js.

				
					const sequelize = require('./config/database');
const User = require('./models/User');

sequelize.sync({ force: true }).then(() => {
    console.log('Database & tables created!');
});

Run the script to create the table in the database.

				
					node sync.js

CRUD Operations

Create

Let’s add a new user to the database.

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

async function createUser() {
    try {
        const user = await User.create({
            name: 'John Doe',
            email: 'john.doe@example.com'
        });
        console.log(user.toJSON());
    } catch (error) {
        console.error('Error creating user:', error);
    }
}

createUser();

Read

Retrieve all users from the database.

				
					async function getAllUsers() {
    try {
        const users = await User.findAll();
        console.log(users.map(user => user.toJSON()));
    } catch (error) {
        console.error('Error fetching users:', error);
    }
}

getAllUsers();

Update

Update a user’s email.

				
					async function updateUser() {
    try {
        const user = await User.findByPk(1);
        user.email = 'john.new@example.com';
        await user.save();
        console.log('User updated:', user.toJSON());
    } catch (error) {
        console.error('Error updating user:', error);
    }
}

updateUser();

Delete

Delete a user from the database.

				
					async function deleteUser() {
    try {
        const user = await User.findByPk(1);
        await user.destroy();
        console.log('User deleted');
    } catch (error) {
        console.error('Error deleting user:', error);
    }
}

deleteUser();

Associations

Sequelize supports various types of associations.

One-to-One

Let’s add a Profile model and associate it with the User model.

Create a new file models/Profile.js.

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

const Profile = sequelize.define('Profile', {
    id: {
        type: DataTypes.INTEGER,
        primaryKey: true,
        autoIncrement: true
    },
    bio: {
        type: DataTypes.STRING,
        allowNull: false
    },
    userId: {
        type: DataTypes.INTEGER,
        references: {
            model: 'users',
            key: 'id'
        }
    }
}, {
    tableName: 'profiles',
    timestamps: false
});

module.exports = Profile;

Update models/User.js to define the association.

				
					const Profile = require('./Profile');

User.hasOne(Profile, {
    foreignKey: 'userId',
    as: 'profile'
});
Profile.belongsTo(User, {
    foreignKey: 'userId',
    as: 'user'
});

Sync the models.

				
					const Profile = require('./models/Profile');

sequelize.sync({ force: true }).then(() => {
    console.log('Database & tables created!');
});

Querying with Sequelize

Sequelize provides a powerful query API.

Basic Queries

Fetch users with a specific condition.

				
					async function findUserByEmail(email) {
    try {
        const user = await User.findOne({ where: { email } });
        console.log(user.toJSON());
    } catch (error) {
        console.error('Error finding user:', error);
    }
}

findUserByEmail('john.doe@example.com');

Advanced Queries

Fetch users with their profiles.

				
					async function findUsersWithProfiles() {
    try {
        const users = await User.findAll({
            include: [{
                model: Profile,
                as: 'profile'
            }]
        });
        console.log(users.map(user => user.toJSON()));
    } catch (error) {
        console.error('Error fetching users with profiles:', error);
    }
}

findUsersWithProfiles();

Validations and Constraints

Sequelize allows you to define validations and constraints at the model level.

Adding Validations

Update models/User.js to add validations.

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

Adding Constraints

Add unique and foreign key constraints.

				
					const Profile = sequelize.define('Profile', {
    id: {
        type: DataTypes.INTEGER,
        primaryKey: true,
        autoIncrement: true
    },
    bio: {
        type: DataTypes.STRING,
        allowNull: false
    },
    userId: {
        type: DataTypes.INTEGER,
        references: {
            model: 'users',
            key: 'id'
        },
        onDelete: 'CASCADE'
    }
}, {
    tableName: 'profiles',
    timestamps: false
});

Hooks

Hooks are lifecycle events in Sequelize models.

Adding Hooks

Update models/User.js to add hooks.

				
					const User = sequelize.define('User', {
    id: {
        type: DataTypes.INTEGER,
        primaryKey: true,
        autoIncrement: true
    },
    name: {
        type: DataTypes.STRING,
        allowNull: false
    },
    email: {
        type: DataTypes.STRING,
        allowNull: false,
        unique: true
    }
}, {
    tableName: 'users',
    timestamps: false,
    hooks: {
        beforeCreate: (user, options) => {
            user.name = user.name.toUpperCase();
        }
    }
});

Transactions

Transactions ensure the integrity of your database operations.

Using Transactions

Perform operations within a transaction.

				
					const { Transaction } = require('sequelize');

async function createUserWithProfile() {
    const transaction = await sequelize.transaction();

    try {
        const user = await User.create({
            name: 'Jane Doe',
            email: 'jane.doe@example.com'
        }, { transaction });

        const profile = await Profile.create({
            bio: 'Hello, I am Jane!',
            userId: user.id
        }, { transaction });

        await transaction.commit();
        console.log('User and profile created successfully.');
    } catch (error) {
        await transaction.rollback();
        console.error('Error creating user and profile:', error);
    }
}

createUserWithProfile();

Migration and Seeding

Migrations are used to keep track of changes to your database schema over time. Sequelize provides a powerful CLI for managing migrations and seeders.

Setting Up Sequelize CLI

Install Sequelize CLI globally.

				
					npm install -g sequelize-cli

Initialize Sequelize CLI in your project.

				
					npx sequelize-cli init

This will create the following folders and files:

config/config.json: Database configuration.
migrations/: Folder for migration files.
seeders/: Folder for seeder files.
models/: Folder for models.

Creating a Migration

Create a new migration to add a Profile table.

				
					npx sequelize-cli migration:generate --name create-profile-table

This command creates a file in the migrations folder. Update the file to define the table schema.

				
					'use strict';

module.exports = {
    up: async (queryInterface, Sequelize) => {
        await queryInterface.createTable('profiles', {
            id: {
                type: Sequelize.INTEGER,
                primaryKey: true,
                autoIncrement: true
            },
            bio: {
                type: Sequelize.STRING,
                allowNull: false
            },
            userId: {
                type: Sequelize.INTEGER,
                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('profiles');
    }
};

Running Migrations

Run the migrations to update the database schema.

				
					npx sequelize-cli db:migrate

Creating a Seeder

Seeders allow you to populate your database with initial data.

Create a new seeder to add a user.

				
					npx sequelize-cli seed:generate --name add-users

Update the seeder file.

				
					'use strict';

module.exports = {
    up: async (queryInterface, Sequelize) => {
        await queryInterface.bulkInsert('users', [{
            name: 'Alice',
            email: 'alice@example.com',
            createdAt: new Date(),
            updatedAt: new Date()
        }], {});
    },

    down: async (queryInterface, Sequelize) => {
        await queryInterface.bulkDelete('users', null, {});
    }
};

Running Seeders

Run the seeders to populate the database.

				
					npx sequelize-cli db:seed:all

Sequelize is a powerful ORM for Node.js that simplifies working with relational databases such as MySQL. In this chapter, we covered the basics of setting up a Node.js project with Sequelize, defining models, performing CRUD operations, managing associations, querying data, adding validations and constraints, utilizing hooks, handling transactions, and using migrations and seeders. With this knowledge, you can build robust applications with complex database interactions.Happy coding !❤️