Sequelize ORM Complete Guide for Node.js | PostgreSQL

Sequelize ORM Complete Guide for Node.js | PostgreSQL

· 24분 읽기 · 수정 Intermediate Tutorial
Sequelize ORM Complete Guide for Node.js | PostgreSQL

이 글의 핵심

Sequelize ORM with Node.js: models, associations, PostgreSQL and MySQL, connection pools, transactions, migrations, and Express REST patterns—plus raw SQL when you need it.

Introduction

Database families

SQL (relational):

  • ✅ PostgreSQL: Rich features, standard SQL
  • ✅ MySQL: Widely deployed, mature ecosystem
  • ✅ SQLite: File-based, great for small apps NoSQL:
  • ✅ MongoDB: Document model, flexible schema
  • ✅ Redis: In-memory, caching and sessions
  • ✅ Cassandra: Wide-column, distributed writes

Choosing a database

DatabaseStrengthsTypical use
PostgreSQLPowerful SQL, ACIDComplex queries, finance
MySQLFast, ubiquitousWeb apps, CMS
MongoDBFlexible schemaPrototypes, document APIs
RedisVery low latencyCache, rate limits, sessions

1. MongoDB (Mongoose)

Install

npm install mongodb
npm install mongoose

Connect

// db.js
const mongoose = require('mongoose');
const connectDB = async () => {
    try {
        await mongoose.connect('mongodb://localhost:27017/mydb', {
            useNewUrlParser: true,
            useUnifiedTopology: true
        });
        console.log('MongoDB connected');
    } catch (err) {
        console.error('MongoDB connection failed:', err.message);
        process.exit(1);
    }
};
module.exports = connectDB;

Schema

// models/User.js
// 변수 선언 및 초기화
const mongoose = require('mongoose');
const userSchema = new mongoose.Schema({
    name: {
        type: String,
        required: [true, 'Name is required'],
        trim: true,
        minlength: 2,
        maxlength: 50
    },
    email: {
        type: String,
        required: true,
        unique: true,
        lowercase: true,
        match: [/^\S+@\S+\.\S+$/, 'Please enter a valid email']
    },
    age: {
        type: Number,
        min: 0,
        max: 150
    },
    role: {
        type: String,
        enum: ['user', 'admin'],
        default: 'user'
    },
    isActive: {
        type: Boolean,
        default: true
    },
    createdAt: {
        type: Date,
        default: Date.now
    }
});
userSchema.index({ email: 1 });
userSchema.virtual('info').get(function() {
    return `${this.name} (${this.email})`;
});
userSchema.methods.greet = function() {
    return `Hello, ${this.name}!`;
};
userSchema.statics.findByEmail = function(email) {
    return this.findOne({ email });
};
userSchema.pre('save', function(next) {
    console.log('Before save:', this.name);
    next();
});
userSchema.post('save', function(doc) {
    console.log('After save:', doc.name);
});
module.exports = mongoose.model('User', userSchema);

CRUD

const User = require('./models/User');
async function createUser() {
    const user = new User({
        name: 'Alice',
        email: 'alice@example.com',
        age: 25
    });
    await user.save();
    const user2 = await User.create({
        name: 'Bob',
        email: 'bob@example.com',
        age: 30
    });
}
async function readUsers() {
    const users = await User.find();
    const adults = await User.find({ age: { $gte: 18 } });
    const user = await User.findOne({ email: 'alice@example.com' });
    const userById = await User.findById('507f1f77bcf86cd799439011');
    const names = await User.find().select('name email -_id');
    const sorted = await User.find().sort({ age: -1 });
    const page = 1;
    const limit = 10;
    const paginated = await User.find()
        .skip((page - 1) * limit)
        .limit(limit);
    return users;
}
async function updateUser(id) {
    await User.findByIdAndUpdate(id, { age: 26 }, { new: true, runValidators: true });
    const user2 = await User.findById(id);
    user2.age = 26;
    await user2.save();
    await User.updateMany({ age: { $lt: 18 } }, { isActive: false });
}
async function deleteUser(id) {
    await User.findByIdAndDelete(id);
    await User.deleteOne({ email: 'alice@example.com' });
    await User.deleteMany({ isActive: false });
}

Relationships

// models/Post.js
const postSchema = new mongoose.Schema({
    title: { type: String, required: true },
    content: { type: String, required: true },
    author: {
        type: mongoose.Schema.Types.ObjectId,
        ref: 'User',
        required: true
    },
    tags: [String],
    createdAt: { type: Date, default: Date.now }
});
const Post = mongoose.model('Post', postSchema);
const post = await Post.create({
    title: 'First post',
    content: 'Body',
    author: userId
});
const posts = await Post.find().populate('author');
const posts2 = await Post.find().populate('author', 'name email');

2. PostgreSQL (pg, Sequelize)

Install

npm install pg
npm install sequelize

Raw queries with pg

const { Pool } = require('pg');
const pool = new Pool({
    host: 'localhost',
    port: 5432,
    database: 'mydb',
    user: 'postgres',
    password: 'password',
    max: 20,
    idleTimeoutMillis: 30000,
    connectionTimeoutMillis: 2000
});
module.exports = pool;
const pool = require('./db');
async function getUsers() {
    const result = await pool.query('SELECT * FROM users');
    return result.rows;
}
async function createUser(name, email) {
    const query = 'INSERT INTO users (name, email) VALUES ($1, $2) RETURNING *';
    const result = await pool.query(query, [name, email]);
    return result.rows[0];
}
async function transferMoney(fromId, toId, amount) {
    const client = await pool.connect();
    try {
        await client.query('BEGIN');
        await client.query(
            'UPDATE accounts SET balance = balance - $1 WHERE id = $2',
            [amount, fromId]
        );
        await client.query(
            'UPDATE accounts SET balance = balance + $1 WHERE id = $2',
            [amount, toId]
        );
        await client.query('COMMIT');
        console.log('Transfer succeeded');
    } catch (err) {
        await client.query('ROLLBACK');
        console.error('Transfer failed:', err.message);
        throw err;
    } finally {
        client.release();
    }
}

Sequelize

const { Sequelize } = require('sequelize');
const sequelize = new Sequelize('mydb', 'postgres', 'password', {
    host: 'localhost',
    dialect: 'postgres',
    logging: false,
    pool: { max: 5, min: 0, acquire: 30000, idle: 10000 }
});
module.exports = sequelize;
// 변수 선언 및 초기화
const { DataTypes } = require('sequelize');
const sequelize = require('../db');
const { Op } = require('sequelize');
const User = sequelize.define('User', {
    id: {
        type: DataTypes.INTEGER,
        primaryKey: true,
        autoIncrement: true
    },
    name: {
        type: DataTypes.STRING(100),
        allowNull: false,
        validate: { len: [2, 50] }
    },
    email: {
        type: DataTypes.STRING(255),
        allowNull: false,
        unique: true,
        validate: { isEmail: true }
    },
    age: {
        type: DataTypes.INTEGER,
        validate: { min: 0, max: 150 }
    },
    role: {
        type: DataTypes.ENUM('user', 'admin'),
        defaultValue: 'user'
    }
}, {
    tableName: 'users',
    timestamps: true
});
module.exports = User;
const { Op } = require('sequelize');
const User = require('./models/User');
const user = await User.create({
    name: 'Alice',
    email: 'alice@example.com',
    age: 25
});
const users = await User.findAll();
const one = await User.findByPk(1);
const filtered = await User.findAll({
    where: { age: { [Op.gte]: 18 } },
    order: [['createdAt', 'DESC']],
    limit: 10,
    offset: 0
});
await User.update({ age: 26 }, { where: { id: 1 } });
await User.destroy({ where: { id: 1 } });

3. MySQL

npm install mysql2
const mysql = require('mysql2/promise');
const pool = mysql.createPool({
    host: 'localhost',
    user: 'root',
    password: 'password',
    database: 'mydb',
    waitForConnections: true,
    connectionLimit: 10,
    queueLimit: 0
});
module.exports = pool;
const pool = require('./db');
async function getUsers() {
    const [rows] = await pool.query('SELECT * FROM users');
    return rows;
}
async function transferMoney(fromId, toId, amount) {
    const connection = await pool.getConnection();
    try {
        await connection.beginTransaction();
        await connection.query(
            'UPDATE accounts SET balance = balance - ? WHERE id = ?',
            [amount, fromId]
        );
        await connection.query(
            'UPDATE accounts SET balance = balance + ? WHERE id = ?',
            [amount, toId]
        );
        await connection.commit();
        console.log('Transfer succeeded');
    } catch (err) {
        await connection.rollback();
        console.error('Transfer failed:', err.message);
        throw err;
    } finally {
        connection.release();
    }
}

4. REST API examples

MongoDB + Express

const express = require('express');
const mongoose = require('mongoose');
const User = require('./models/User');
const app = express();
app.use(express.json());
mongoose.connect('mongodb://localhost:27017/mydb')
    .then(() => console.log('MongoDB connected'))
    .catch(err => console.error('Connection failed:', err));
app.get('/api/users', async (req, res) => {
    try {
        const { page = 1, limit = 10, sort = '-createdAt' } = req.query;
        const users = await User.find()
            .sort(sort)
            .skip((page - 1) * limit)
            .limit(parseInt(limit, 10));
        const total = await User.countDocuments();
        res.json({
            users,
            pagination: {
                page: parseInt(page, 10),
                limit: parseInt(limit, 10),
                total,
                pages: Math.ceil(total / limit)
            }
        });
    } catch (err) {
        res.status(500).json({ error: err.message });
    }
});
app.get('/api/users/:id', async (req, res) => {
    try {
        const user = await User.findById(req.params.id);
        if (!user) {
            return res.status(404).json({ error: 'User not found' });
        }
        res.json(user);
    } catch (err) {
        res.status(500).json({ error: err.message });
    }
});
app.post('/api/users', async (req, res) => {
    try {
        const user = await User.create(req.body);
        res.status(201).json(user);
    } catch (err) {
        if (err.name === 'ValidationError') {
            return res.status(400).json({ error: err.message });
        }
        res.status(500).json({ error: err.message });
    }
});
app.put('/api/users/:id', async (req, res) => {
    try {
        const user = await User.findByIdAndUpdate(
            req.params.id,
            req.body,
            { new: true, runValidators: true }
        );
        if (!user) {
            return res.status(404).json({ error: 'User not found' });
        }
        res.json(user);
    } catch (err) {
        res.status(500).json({ error: err.message });
    }
});
app.delete('/api/users/:id', async (req, res) => {
    try {
        const user = await User.findByIdAndDelete(req.params.id);
        if (!user) {
            return res.status(404).json({ error: 'User not found' });
        }
        res.status(204).send();
    } catch (err) {
        res.status(500).json({ error: err.message });
    }
});
app.listen(3000, () => {
    console.log('Server listening on http://localhost:3000');
});

PostgreSQL + Express (illustrative)

const express = require('express');
const pool = require('./db');
const app = express();
app.use(express.json());
app.get('/api/users', async (req, res) => {
    try {
        const { page = 1, limit = 10 } = req.query;
        const offset = (page - 1) * limit;
        const [users] = await pool.query(
            'SELECT * FROM users ORDER BY created_at DESC LIMIT ? OFFSET ?',
            [parseInt(limit, 10), offset]
        );
        const [[{ total }]] = await pool.query('SELECT COUNT(*) as total FROM users');
        res.json({
            users,
            pagination: {
                page: parseInt(page, 10),
                limit: parseInt(limit, 10),
                total,
                pages: Math.ceil(total / limit)
            }
        });
    } catch (err) {
        res.status(500).json({ error: err.message });
    }
});
app.post('/api/users', async (req, res) => {
    try {
        const { name, email, age } = req.body;
        const [result] = await pool.query(
            'INSERT INTO users (name, email, age) VALUES (?, ?, ?)',
            [name, email, age]
        );
        const [users] = await pool.query('SELECT * FROM users WHERE id = ?', [result.insertId]);
        res.status(201).json(users[0]);
    } catch (err) {
        if (err.code === 'ER_DUP_ENTRY') {
            return res.status(400).json({ error: 'Email already exists' });
        }
        res.status(500).json({ error: err.message });
    }
});
app.listen(3000);

5. Query optimization

Indexes

userSchema.index({ email: 1 });
userSchema.index({ name: 1, age: -1 });
userSchema.index({ email: 1 }, { unique: true });
await pool.query('CREATE INDEX idx_email ON users(email)');
await pool.query('CREATE INDEX idx_name_age ON users(name, age)');
await pool.query('CREATE UNIQUE INDEX idx_email_unique ON users(email)');

Fixing N+1

// Bad: N+1 queries
const posts = await Post.find();
for (const post of posts) {
    const author = await User.findById(post.author);
    console.log(author.name);
}
// Good: populate
const posts = await Post.find().populate('author');
for (const post of posts) {
    console.log(post.author.name);
}

Projection

const users = await User.find().select('name email');
const [usersPg] = await pool.query('SELECT name, email FROM users');

Pagination helpers

async function paginateUsersMongo(page = 1, limit = 10) {
    const skip = (page - 1) * limit;
    const [users, total] = await Promise.all([
        User.find().skip(skip).limit(limit),
        User.countDocuments()
    ]);
    return {
        users,
        page,
        limit,
        total,
        pages: Math.ceil(total / limit)
    };
}
async function paginateUsersSql(page = 1, limit = 10) {
    const offset = (page - 1) * limit;
    const [users] = await pool.query(
        'SELECT * FROM users LIMIT ? OFFSET ?',
        [limit, offset]
    );
    const [[{ total }]] = await pool.query('SELECT COUNT(*) as total FROM users');
    return {
        users,
        page,
        limit,
        total,
        pages: Math.ceil(total / limit)
    };
}

6. Connection pools

The following example demonstrates the concept in javascript:

mongoose.connect('mongodb://localhost:27017/mydb', {
    maxPoolSize: 10,
    minPoolSize: 2,
    maxIdleTimeMS: 30000
});
const { Pool } = require('pg');
const pool = new Pool({
    max: 20,
    min: 5,
    idleTimeoutMillis: 30000,
    connectionTimeoutMillis: 2000
});
const mysql = require('mysql2/promise');
mysql.createPool({
    connectionLimit: 10,
    queueLimit: 0,
    waitForConnections: true
});

Pool events (pg)

pool.on('connect', () => console.log('New client connected'));
pool.on('acquire', () => console.log('Client acquired'));
pool.on('release', () => console.log('Client released'));

7. Migrations (Sequelize)

npm install --save-dev sequelize-cli
npx sequelize-cli init
npx sequelize-cli migration:generate --name create-users-table
// migrations/20260329-create-users-table.js
// 실행 예제
module.exports = {
    up: async (queryInterface, Sequelize) => {
        await queryInterface.createTable('users', {
            id: {
                type: Sequelize.INTEGER,
                primaryKey: true,
                autoIncrement: true
            },
            name: {
                type: Sequelize.STRING(100),
                allowNull: false
            },
            email: {
                type: Sequelize.STRING(255),
                allowNull: false,
                unique: true
            },
            age: {
                type: Sequelize.INTEGER
            },
            created_at: {
                type: Sequelize.DATE,
                defaultValue: Sequelize.literal('CURRENT_TIMESTAMP')
            },
            updated_at: {
                type: Sequelize.DATE,
                defaultValue: Sequelize.literal('CURRENT_TIMESTAMP')
            }
        });
        await queryInterface.addIndex('users', ['email']);
    },
    down: async (queryInterface) => {
        await queryInterface.dropTable('users');
    }
};
npx sequelize-cli db:migrate
npx sequelize-cli db:migrate:undo
npx sequelize-cli db:migrate:undo:all

8. Example: blog API (MongoDB)

The following example demonstrates the concept in javascript:

// models/Post.js
const mongoose = require('mongoose');
const postSchema = new mongoose.Schema({
    title: {
        type: String,
        required: true,
        trim: true,
        minlength: 1,
        maxlength: 200
    },
    content: { type: String, required: true },
    author: {
        type: mongoose.Schema.Types.ObjectId,
        ref: 'User',
        required: true
    },
    tags: [String],
    published: { type: Boolean, default: false },
    views: { type: Number, default: 0 }
}, { timestamps: true });
postSchema.index({ title: 'text', content: 'text' });
postSchema.index({ author: 1, createdAt: -1 });
postSchema.virtual('url').get(function() {
    return `/posts/${this._id}`;
});
module.exports = mongoose.model('Post', postSchema);
// routes/posts.js
const express = require('express');
const router = express.Router();
const Post = require('../models/Post');
router.get('/', async (req, res) => {
    try {
        const { page = 1, limit = 10, tag, author } = req.query;
        const query = {};
        if (tag) query.tags = tag;
        if (author) query.author = author;
        const posts = await Post.find(query)
            .populate('author', 'name email')
            .sort({ createdAt: -1 })
            .skip((page - 1) * limit)
            .limit(parseInt(limit, 10));
        const total = await Post.countDocuments(query);
        res.json({
            posts,
            pagination: {
                page: parseInt(page, 10),
                limit: parseInt(limit, 10),
                total,
                pages: Math.ceil(total / limit)
            }
        });
    } catch (err) {
        res.status(500).json({ error: err.message });
    }
});
router.get('/search', async (req, res) => {
    try {
        const { q } = req.query;
        if (!q) {
            return res.status(400).json({ error: 'Search query required' });
        }
        const posts = await Post.find(
            { $text: { $search: q } },
            { score: { $meta: 'textScore' } }
        )
            .sort({ score: { $meta: 'textScore' } })
            .populate('author', 'name');
        res.json({ posts, count: posts.length });
    } catch (err) {
        res.status(500).json({ error: err.message });
    }
});
router.post('/', async (req, res) => {
    try {
        const post = await Post.create({
            ...req.body,
            author: req.user.id
        });
        await post.populate('author', 'name email');
        res.status(201).json(post);
    } catch (err) {
        if (err.name === 'ValidationError') {
            return res.status(400).json({ error: err.message });
        }
        res.status(500).json({ error: err.message });
    }
});
router.post('/:id/view', async (req, res) => {
    try {
        const post = await Post.findByIdAndUpdate(
            req.params.id,
            { $inc: { views: 1 } },
            { new: true }
        );
        if (!post) {
            return res.status(404).json({ error: 'Post not found' });
        }
        res.json({ views: post.views });
    } catch (err) {
        res.status(500).json({ error: err.message });
    }
});
module.exports = router;

9. Common pitfalls

Connection leaks

async function bad() {
    const connection = await pool.getConnection();
    const [rows] = await connection.query('SELECT * FROM users');
    return rows;
}
async function good() {
    const connection = await pool.getConnection();
    try {
        const [rows] = await connection.query('SELECT * FROM users');
        return rows;
    } finally {
        connection.release();
    }
}

SQL injection

async function vulnerable(email) {
    const query = `SELECT * FROM users WHERE email = '${email}'`;
    const [rows] = await pool.query(query);
    return rows;
}
async function safe(email) {
    const [rows] = await pool.query(
        'SELECT * FROM users WHERE email = ?',
        [email]
    );
    return rows;
}

Missing transactions

async function badTransfer(fromId, toId, amount) {
    await pool.query('UPDATE accounts SET balance = balance - ? WHERE id = ?', [amount, fromId]);
    await pool.query('UPDATE accounts SET balance = balance + ? WHERE id = ?', [amount, toId]);
}
async function goodTransfer(fromId, toId, amount) {
    const connection = await pool.getConnection();
    try {
        await connection.beginTransaction();
        await connection.query('UPDATE accounts SET balance = balance - ? WHERE id = ?', [amount, fromId]);
        await connection.query('UPDATE accounts SET balance = balance + ? WHERE id = ?', [amount, toId]);
        await connection.commit();
    } catch (err) {
        await connection.rollback();
        throw err;
    } finally {
        connection.release();
    }
}

10. Production tips

Environment-specific config

module.exports = {
    development: {
        mongodb: 'mongodb://localhost:27017/mydb-dev',
        postgres: {
            host: 'localhost',
            database: 'mydb_dev',
            user: 'postgres',
            password: 'password'
        }
    },
    production: {
        mongodb: process.env.MONGODB_URI,
        postgres: {
            host: process.env.DB_HOST,
            database: process.env.DB_NAME,
            user: process.env.DB_USER,
            password: process.env.DB_PASSWORD,
            ssl: true
        }
    }
};

Retry MongoDB connection

async function connectWithRetry(maxRetries = 5) {
    for (let i = 0; i < maxRetries; i++) {
        try {
            await mongoose.connect('mongodb://localhost:27017/mydb');
            console.log('MongoDB connected');
            return;
        } catch (err) {
            console.error(`Connection failed (${i + 1}/${maxRetries}):`, err.message);
            if (i === maxRetries - 1) throw err;
            const delay = Math.pow(2, i) * 1000;
            console.log(`Retrying in ${delay}ms...`);
            await new Promise(resolve => setTimeout(resolve, delay));
        }
    }
}

Graceful shutdown

const mongoose = require('mongoose');
async function gracefulShutdown() {
    console.log('Shutting down...');
    try {
        await mongoose.connection.close();
        console.log('MongoDB closed');
        await pool.end();
        console.log('PostgreSQL pool closed');
        process.exit(0);
    } catch (err) {
        console.error('Shutdown error:', err.message);
        process.exit(1);
    }
}
process.on('SIGTERM', gracefulShutdown);
process.on('SIGINT', gracefulShutdown);

Summary

Takeaways

  1. MongoDB: Documents + Mongoose ODM
  2. PostgreSQL: pg driver and/or Sequelize
  3. MySQL: mysql2 with pools
  4. Pools: reuse connections
  5. Indexes: match your filter/sort fields
  6. Transactions: keep related updates consistent

Comparison

MongoDBPostgreSQLMySQL
ModelDocumentRelationalRelational
SchemaFlexibleStrictStrict
JoinspopulateJOINJOIN
ScalingHorizontal-friendlyOften verticalOften vertical

ORM vs raw SQL

ORMRaw SQL
ProductivityHighLower
Performance tuningSome overheadFull control
Complex queriesCan be awkwardNatural

Next steps

Resources

Frequently Asked Questions (FAQ)

Q. When would I use this in practice?

A. Sequelize ORM with Node.js: models, associations, PostgreSQL and MySQL, connection pools, transactions, migrations, and …

Q. What should I read before this?

A. Follow the previous article or related articles links at the bottom of each post to learn in sequence.

Q. Where can I study this more deeply?

A. Check cppreference and the relevant library’s official documentation. The reference links at the end of the article are also worth using.

Other articles related to this topic.

Keywords Covered in This Article (Related Search Terms)

This article covers Node.js, Sequelize, ORM, PostgreSQL, MySQL, Database, Express.