Ultimate Sequelize ORM Guide: Node.js & MySQL Setup

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.

In this guide, I'll walk you through setting up Sequelize with MySQL using patterns I've refined over multiple production applications. We'll cover connection management (crucial for avoiding connection leaks), the singleton pattern (so you don't accidentally create multiple database connections), and configuration that works well in both development and production environments.

Installation

npm install sequelize mysql2
npm install dotenv

Database Connection with Singleton Pattern

Creating a singleton database connection class:

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("✅ MySQL Database connected successfully");
      return true;
    } catch (error) {
      console.error("❌ Database connection failed:", error.message);
      return false;
    }
  }

  async closeConnection() {
    try {
      await this.sequelize.close();
      console.log("✅ Database connection closed");
      return true;
    } catch (error) {
      console.error("❌ Error closing connection:", error.message);
      return false;
    }
  }
}

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

Creating Models

Defining a Product model with associations:

const { DataTypes } = require("sequelize");
const database = require("./database");
const { Category } = require("./index");

const Product = database.getSequelize().define("Product", {
  id: {
    type: DataTypes.INTEGER,
    primaryKey: true,
    autoIncrement: true,
  },
  name: {
    type: DataTypes.STRING,
    allowNull: false,
  },
  categoryId: {
    type: DataTypes.INTEGER,
    allowNull: false,
    references: {
      model: Category,
      key: "id",
    },
  },
  sku: {
    type: DataTypes.STRING,
    allowNull: true,
  },
  description: {
    type: DataTypes.TEXT,
    allowNull: true,
  },
  price: {
    type: DataTypes.DECIMAL(10, 2),
    allowNull: false,
    defaultValue: 0,
  },
  cost: {
    type: DataTypes.DECIMAL(10, 2),
    allowNull: false,
    defaultValue: 0,
  },
  stock: {
    type: DataTypes.INTEGER,
    allowNull: false,
    defaultValue: 0,
  },
  minStock: {
    type: DataTypes.INTEGER,
    allowNull: false,
    defaultValue: 0,
  },
  unit: {
    type: DataTypes.STRING,
    allowNull: false,
    defaultValue: "pcs",
  },
  barcode: {
    type: DataTypes.STRING,
    allowNull: true,
  },
}, {
  tableName: "products",
  timestamps: true,
});

// Associations
Product.belongsTo(Category, { foreignKey: "categoryId", as: "category" });
Category.hasMany(Product, { foreignKey: "categoryId", as: "products" });

module.exports = Product;

Model Class for Clean Interface

Creating a model class that wraps Sequelize operations:

const { Product, Category } = require("./index");

class ProductModel {
  async getAll() {
    try {
      const products = await Product.findAll({
        include: [{
          model: Category,
          as: "category",
          attributes: ["id", "name"],
        }],
        order: [["createdAt", "DESC"]],
      });
      
      return products.map(product => {
        const data = product.toJSON();
        return {
          ...data,
          categoryName: data.category ? data.category.name : "Unknown",
        };
      });
    } catch (error) {
      console.error("Error getting all products:", error);
      return [];
    }
  }

  async getById(id) {
    try {
      const product = await Product.findByPk(id, {
        include: [{
          model: Category,
          as: "category",
          attributes: ["id", "name"],
        }],
      });
      
      if (!product) {
        return { success: false, message: "Product not found" };
      }
      
      const data = product.toJSON();
      return {
        ...data,
        categoryName: data.category ? data.category.name : "Unknown",
      };
    } catch (error) {
      console.error("Error getting product by ID:", error);
      return { success: false, message: "Error reading product" };
    }
  }

  async create(productData) {
    try {
      return await Product.create(productData);
    } catch (error) {
      console.error("Error creating product:", error);
      return { success: false, message: "Error creating product", error: error.message };
    }
  }

  async update(id, productData) {
    try {
      const product = await Product.findByPk(id);
      if (!product) {
        return { success: false, message: "Product not found" };
      }
      await product.update(productData);
      return product;
    } catch (error) {
      console.error("Error updating product:", error);
      return { success: false, message: "Error updating product" };
    }
  }

  async delete(id) {
    try {
      const product = await Product.findByPk(id);
      if (!product) {
        return { success: false, message: "Product not found" };
      }
      const productData = product.toJSON();
      await product.destroy();
      return productData;
    } catch (error) {
      console.error("Error deleting product:", error);
      return { success: false, message: "Error deleting product" };
    }
  }
}

module.exports = new ProductModel();

Environment Variables

Configure your .env file:

DB_HOST=localhost
DB_PORT=3306
DB_NAME=inventory_management
DB_USER=root
DB_PASSWORD=your_mysql_password
NODE_ENV=development

Testing the Connection

const database = require("./config/database");

async function startServer() {
  const dbConnected = await database.testConnection();
  
  if (!dbConnected) {
    console.error("Failed to connect to database");
    process.exit(1);
  }

  // Start your Express server
  app.listen(3000, () => {
    console.log("Server running on port 3000");
  });
}

startServer();

Best Practices

Use singleton pattern to ensure only one database connection instance
Configure connection pooling for better performance
Always use environment variables for database credentials
Implement proper error handling in model methods
Use transactions for complex operations
Set appropriate timeouts and retry logic
Close connections gracefully on application shutdown

Conclusion

Sequelize ORM provides a powerful, type-safe interface for MySQL operations in Node.js. Using a singleton pattern ensures efficient connection management, while connection pooling improves performance. This setup is production-ready and scalable for inventory management systems and other data-heavy applications.

Sequelize ORM with MySQL Setup – Complete Guide

Installation

Database Connection with Singleton Pattern

Creating Models

Model Class for Clean Interface

Environment Variables

Testing the Connection

Best Practices

Conclusion

Related Articles

Sequelize Associations and Relationships: Complete Guide

Express.js REST API Setup: Complete Guide with Error Handling

JWT Authentication in Express.js and Node.js: Complete Guide

Multer File Upload in Express.js: Complete Guide with Examples