Configure next.js project with Sequelize and MySQL

Today we are going to set up Sequelize with MySQL database in the Next.js project.  I think it will be fun because every new tech is more fun if you are a new tech enthusiast.

Let’s know the technologies description which we are going to set up.

• Next.js
  • The React Framework for Production.
  • Next.js gives you the best developer experience with all the features you need for production: hybrid static & server rendering, TypeScript support, smart bundling, route pre-fetching, and more.

• Sequelize
  • Sequelize is a promise-based Node.js ORM tool for Postgres, MySQL, MariaDB, SQLite, Microsoft SQL Server, Amazon Redshift, and Snowflake’s Data Cloud. It features solid transaction support, relations, eager and lazy loading, read replication, and more.
  • Sequelize follows Semantic Versioning and supports Node v10 and above.

• MySQL
  • MySQL is a relational database management system (RDBMS) developed by Oracle that is based on structured query language (SQL). A database is a structured collection of data. It may be anything from a simple shopping list to a picture gallery or a place to hold vast amounts of information in a corporate network.

Before starting this project we have to install MySQL and database tools like PHPMyAdmin, if you are using Windows then you need to WAMP/XAMPP and if you are using Mac then you have to install MAMP, and personally, I am using MAMP.

Let’s create a project:

Run this command

npx create-next-app@latest Then run

npm install sequelize sequelize-cli env-cmd mysql2

Then create a file called .env in the root folder and update like below

DB_USERNAME=username 
DB_PASSWORD=password
DB_NAME=database_development
DB_HOSTNAME=127.0.0.1
DB_PORT=port

.sequelizerc

And update like below code

const path = require("path");
module.exports = {
    config: path.resolve("database/config", "config.mjs"),
    "models-path": path.resolve("database", "models"),
    "migrations-path": path.resolve("database", "migrations"),
};

and now create a folder in the root of the project such as databases

into the database folder create several folders like below

• /config
• /models
• /migrations

and into the /config folder create a file called config.mjs and add codebase like below

export default {
    // For development environment
    development: {
        username: process.env.DB_USERNAME,
        password: process.env.DB_PASSWORD,
        database: process.env.DB_NAME_DEVELOPMENT,
        host: process.env.DB_HOSTNAME,
        port: process.env.DB_PORT,
        dialect: "mysql",
    },
    // For production environment
    production: {
        username: process.env.DB_USERNAME,
        password: process.env.DB_PASSWORD_PROD,
        database: process.env.DB_NAME_PRODUCTION,
        host: process.env.DB_HOSTNAME,
        port: process.env.DB_PORT_PROD,
        logging: false,
        dialect: "mysql",
    },
};

Now and finally update the package.json file for scripts like below

"scripts": {
    "dev": "next dev",
    "build": "next build",
    "start": "next start",
    "lint": "next lint",
    "model:generate": "env-cmd sequelize-cli model:generate",
    "migrate": "env-cmd sequelize-cli db:migrate",
    "migrate:rollback": "env-cmd sequelize-cli db:migrate:undo",
    "migrate:rollback:all": "env-cmd sequelize-cli db:migrate:undo:all"
},

And now run commands into the terminal for generating models like if we want to user the model then the command will be like this

yarn model:generate --name User --attributes first_name:string,last_name:string,email:string

now check the migrations and models folder and if everything was alright then you should see a file something like this 20220807061714-create-user.js and user.js and now update files like below

first migration file

module.exports = {
    async up(queryInterface, Sequelize) {
        await queryInterface.createTable("users", {
            id: {
                allowNull: false,
                autoIncrement: true,
                primaryKey: true,
                type: Sequelize.INTEGER,
            },
            first_name: {
                type: Sequelize.STRING,
                allowNull: false,
            },
            last_name: {
                type: Sequelize.STRING,
                allowNull: false,
            },
            email: {
                type: Sequelize.STRING,
                allowNull: false,
            },
            created_at: {
                allowNull: false,
                type: Sequelize.DATE,
                defaultValue: Sequelize.literal("CURRENT_TIMESTAMP"),
            },
            updated_at: {
                allowNull: false,
                type: Sequelize.DATE,
                defaultValue: Sequelize.literal("CURRENT_TIMESTAMP"),
            },
        });
    },
    async down(queryInterface) {
        await queryInterface.dropTable("users");
    },
};

and the model

import { Model, DataTypes } from "sequelize";
import connection from "../connection";

const initUser = (sequelize, Types) => {
    class User extends Model {
        /**
         * Helper method for defining associations.
         * This method is not a part of Sequelize lifecycle.
         * The `models/index` file will call this method automatically.
         */
        // static associate(models) {
        // 	// define association here
        // }
    }
    User.init(
        {
            id: {
                allowNull: false, 
                autoIncrement: true, 
                primaryKey: true, 
                type: Sequelize.INTEGER,
            },
            first_name: Types.STRING,
            last_name: Types.STRING,
            email: Types.STRING,
        },
        {
            sequelize,
            modelName: "User",
            tableName: "users",
            createdAt: "created_at",
            updatedAt: "updated_at",
        }
    );
    return User;
};

export default initUser(connection, DataTypes);

and run the migration like yarn migrate

Now you can check the phpmyadmin if the users table exists.

If any confusion let me know in the comment section, and I will be there.

Happy coding 🙂