Sequelize ORM with MySQL Setup: Complete Guide

Sequelize is a popular ORM (Object-Relational Mapping) for Node.js that provides a clean interface for database operations. In this guide, we'll set up Sequelize with MySQL using a singleton pattern for connection management and production-ready configuration.

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.