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.
📖 Want the complete guide with more examples and advanced patterns? Check out the full article on my blog for an in-depth tutorial with additional code examples, troubleshooting tips, and real-world use cases.
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("✅ 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;
}
}
getSequelize() {
return this.sequelize;
}
}
const database = new Database();
module.exports = database;
Why Singleton Pattern?
The singleton pattern ensures:
- Single connection instance - Prevents multiple database connections
- Resource efficiency - Reuses the same connection pool
- Consistent state - All parts of your app use the same connection
- Easier testing - Mock the singleton instance for tests
Creating Models
Defining models with Sequelize is straightforward. Here's an example Product model:
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 provides a clean, maintainable interface:
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 with database credentials:
DB_HOST=localhost
DB_PORT=3306
DB_NAME=inventory_management
DB_USER=root
DB_PASSWORD=your_mysql_password
NODE_ENV=development
Important: Never commit your .env file to version control. Add it to .gitignore.
Testing the Connection
Test your database connection before starting your server:
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();
Connection Pooling Explained
Connection pooling is crucial for production applications:
-
max: 10- Maximum number of connections in the pool -
min: 0- Minimum number of connections to maintain -
acquire: 30000- Maximum time (ms) to wait for a connection -
idle: 10000- Maximum time (ms) a connection can be idle before being released
Why Connection Pooling?
- Performance - Reuses existing connections instead of creating new ones
- Resource management - Limits the number of database connections
- Scalability - Handles concurrent requests efficiently
- Stability - Prevents connection exhaustion
Common Operations
Creating Records
// Create a single record
const product = await Product.create({
name: "Laptop",
categoryId: 1,
price: 999.99,
stock: 10,
});
// Create multiple records
const products = await Product.bulkCreate([
{ name: "Laptop", price: 999.99, stock: 10 },
{ name: "Mouse", price: 29.99, stock: 50 },
]);
Querying Records
// Find all records
const products = await Product.findAll();
// Find with conditions
const products = await Product.findAll({
where: {
stock: {
[Op.gt]: 0 // Greater than 0
}
},
order: [["price", "DESC"]],
limit: 10,
});
// Find one record
const product = await Product.findOne({
where: { id: 1 },
include: [{ model: Category, as: "category" }],
});
// Find by primary key
const product = await Product.findByPk(1);
Updating Records
// Update a single record
const product = await Product.findByPk(1);
await product.update({ price: 899.99 });
// Update multiple records
await Product.update(
{ stock: 0 },
{ where: { price: { [Op.lt]: 10 } } }
);
Deleting Records
// Delete a single record
const product = await Product.findByPk(1);
await product.destroy();
// Delete multiple records
await Product.destroy({
where: { stock: 0 }
});
Best Practices
- Use singleton pattern - Ensure only one database connection instance
- Configure connection pooling - Set appropriate pool sizes for your workload
- Always use environment variables - Never hardcode database credentials
- Implement proper error handling - Catch and handle database errors gracefully
- Use transactions - For operations that must succeed or fail together
- Set appropriate timeouts - Prevent hanging connections
- Close connections gracefully - On application shutdown
- Use migrations - Version control your database schema
- Index frequently queried fields - Improve query performance
- Monitor connection pool - Track connection usage in production
Error Handling Example
async function safeDatabaseOperation() {
try {
const result = await Product.findAll();
return { success: true, data: result };
} catch (error) {
console.error("Database error:", error);
// Handle specific errors
if (error.name === "SequelizeConnectionError") {
return { success: false, message: "Database connection failed" };
}
return { success: false, message: "Database operation failed" };
}
}
Production Considerations
Connection Pool Sizing
For production, adjust pool settings based on your workload:
pool: {
max: 20, // Increase for high-traffic apps
min: 5, // Keep minimum connections alive
acquire: 30000,
idle: 10000,
}
Logging
Disable SQL logging in production for better performance:
logging: process.env.NODE_ENV === "production" ? false : console.log,
Retry Logic
Implement retry logic for transient failures:
retry: {
max: 3,
match: [
Sequelize.ConnectionError,
Sequelize.ConnectionTimedOutError,
],
}
Resources and Further Reading
- 📚 Full Sequelize ORM Setup Guide - Complete tutorial with advanced examples, troubleshooting, and best practices
- Sequelize Associations Guide - Learn how to define and use Sequelize associations
- Sequelize Documentation - Official Sequelize documentation
- Sequelize API Reference - Complete API reference
- Express.js REST API Setup - Learn how to integrate Sequelize with Express.js
- JWT Authentication Guide - Add authentication to your Sequelize-powered API
- Multer File Upload Guide - Handle file uploads with Sequelize
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.
Key Takeaways:
- Sequelize simplifies database operations with JavaScript methods
- Singleton pattern prevents connection leaks and resource issues
- Connection pooling improves performance and scalability
- Model classes provide clean, maintainable interfaces
- Proper error handling ensures robust applications
- Environment variables keep credentials secure
- Production configuration requires careful pool sizing and logging
Whether you're building a simple CRUD app or a complex system with multiple relationships, Sequelize handles it all elegantly. Start with this setup, then gradually explore associations, migrations, and advanced features as your application grows.
What's your experience with Sequelize? Share your tips and tricks in the comments below! 🚀
💡 Looking for more details? This is a condensed version of my comprehensive guide. Read the full article on my blog for additional examples, advanced patterns, troubleshooting tips, and more in-depth explanations.
If you found this guide helpful, consider checking out my other articles on Node.js development and database best practices.
Top comments (0)