Sequelize ORM with MySQL Setup: Complete Guide for Node.js

#sequelize #mysql #node #database

When I first started building Node.js applications, I was writing raw SQL queries everywhere. It worked, but maintaining those queries as the application grew became a nightmare. Then I discovered Sequelize, and it completely changed how I interact with databases. Instead of writing complex JOIN statements, I could use simple JavaScript methods that felt natural.

Sequelize is an ORM (Object-Relational Mapping) library that acts as a bridge between your Node.js code and your MySQL database. It handles all the SQL generation for you, provides type safety, and makes database operations much more maintainable. But setting it up correctly—especially for production—requires understanding connection pooling, the singleton pattern, and proper error handling.

What is Sequelize ORM?

Sequelize is a popular ORM (Object-Relational Mapping) for Node.js that provides:

Type-safe database operations - JavaScript methods instead of raw SQL
Connection pooling - Efficient database connection management
Migrations - Version control for your database schema
Associations - Easy relationship handling (hasMany, belongsTo, etc.)
Transactions - Support for complex operations
Multi-database support - MySQL, PostgreSQL, SQLite, and more

Installation

First, let's install Sequelize and MySQL driver:

npm install sequelize mysql2
npm install dotenv

Database Connection with Singleton Pattern

Creating a singleton database connection ensures you only have one database connection instance, which is crucial for avoiding connection leaks and managing resources efficiently.

const { Sequelize } = require("sequelize");
require("dotenv").config();

class Database {
  constructor() {
    if (Database.instance) {
      return Database.instance;
    }

    this.sequelize = new Sequelize(
      process.env.DB_NAME || "inventory_management",
      process.env.DB_USER || "root",
      process.env.DB_PASSWORD || "",
      {
        host: process.env.DB_HOST || "localhost",
        port: process.env.DB_PORT || 3306,
        dialect: "mysql",

        // Connection Pool Configuration
        pool: {
          max: 10,              // Maximum connections
          min: 0,               // Minimum connections
          acquire: 30000,       // Max time to get connection
          idle: 10000,          // Max time connection can be idle
        },

        // Logging
        logging: process.env.NODE_ENV === "production" ? false : console.log,

        // Retry Configuration
        retry: {
          max: 3,
          match: [
            Sequelize.ConnectionError,
            Sequelize.ConnectionTimedOutError,
          ],
        },

        // Query timeout
        dialectOptions: {
          connectTimeout: 60000,
          decimalNumbers: true,
        },

        // Timezone
        timezone: "+00:00",

        // Model defaults
        define: {
          timestamps: true,
          underscored: false,
          freezeTableName: true,
          charset: "utf8mb4",
          collate: "utf8mb4_unicode_ci",
        },
      }
    );

    Database.instance = this;
  }

  async testConnection() {
    try {
      await this.sequelize.authenticate();
      console.log("✅ Database connection established successfully.");
      return true;
    } catch (error) {
      console.error("❌ Unable to connect to the database:", error);
      return false;
    }
  }

  async closeConnection() {
    try {
      await this.sequelize.close();
      console.log("Database connection closed.");
    } catch (error) {
      console.error("Error closing database connection:", error);
    }
  }
}

const database = new Database();
module.exports = database;