본문으로 건너뛰기
Previous
Next
Sequelize ORM Complete Guide for Node.js | PostgreSQL

Sequelize ORM Complete Guide for Node.js | PostgreSQL

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: '[email protected]',
        age: 25
    });
    await user.save();
    const user2 = await User.create({
        name: 'Bob',
        email: '[email protected]',
        age: 30
    });
}
async function readUsers() {
    const users = await User.find();
    const adults = await User.find({ age: { $gte: 18 } });
    const user = await User.findOne({ email: '[email protected]' });
    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: '[email protected]' });
    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: '[email protected]',
    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

  • [Node.js authentication and security](/en/blog/nodejs-series-07-auth/
  • [Node.js testing](/en/blog/nodejs-series-08-testing/
  • Node.js deployment

Resources



자주 묻는 질문 (FAQ)

Q. 이 내용을 실무에서 언제 쓰나요?

A. Sequelize ORM with Node.js: models, associations, PostgreSQL and MySQL, connection pools, transactions, migrations, and … 실무에서는 위 본문의 예제와 선택 가이드를 참고해 적용하면 됩니다.

Q. 선행으로 읽으면 좋은 글은?

A. 각 글 하단의 이전 글 또는 관련 글 링크를 따라가면 순서대로 배울 수 있습니다. C++ 시리즈 목차에서 전체 흐름을 확인할 수 있습니다.

Q. 더 깊이 공부하려면?

A. cppreference와 해당 라이브러리 공식 문서를 참고하세요. 글 말미의 참고 자료 링크도 활용하면 좋습니다.


같이 보면 좋은 글 (내부 링크)

이 주제와 연결되는 다른 글입니다.


이 글에서 다루는 키워드 (관련 검색어)

Node.js, Sequelize, ORM, PostgreSQL, MySQL, Database, Express 등으로 검색하시면 이 글이 도움이 됩니다.