MongoDB or PostgreSQL?

MongoDB fits flexible schemas and fast iteration; PostgreSQL fits complex SQL, strong constraints, and analytics-heavy workloads.

ORMs boost productivity but add overhead; use raw SQL for hot paths or complex queries when needed.

Why use a connection pool?

Opening a new DB connection per request is expensive; pools reuse connections and improve throughput.

Node.js Database Integration: MongoDB, PostgreSQL, and MySQL

2026년 3월 29일 · 24분 읽기 · 수정 2026년 3월 29일 Intermediate Tutorial

이 글의 핵심

Hands-on Node.js persistence: Mongoose schemas and populate, pg Pool with transactions, Sequelize models, mysql2 pools, Express REST patterns, query tuning, connection pooling, and Sequelize migrations.

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

Database	Strengths	Typical use
PostgreSQL	Powerful SQL, ACID	Complex queries, finance
MySQL	Fast, ubiquitous	Web apps, CMS
MongoDB	Flexible schema	Prototypes, document APIs
Redis	Very low latency	Cache, 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

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)

// 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

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

Comparison

	MongoDB	PostgreSQL	MySQL
Model	Document	Relational	Relational
Schema	Flexible	Strict	Strict
Joins	`populate`	`JOIN`	`JOIN`
Scaling	Horizontal-friendly	Often vertical	Often vertical

ORM vs raw SQL

	ORM	Raw SQL
Productivity	High	Lower
Performance tuning	Some overhead	Full control
Complex queries	Can be awkward	Natural

Next steps

Resources

Python databases
C++ database integration
C++ query optimization
C++ database query optimization
C++ MongoDB

이 글의 핵심

Introduction

Database families

Choosing a database

1. MongoDB (Mongoose)

Install

Connect

Schema

CRUD

Relationships

2. PostgreSQL (pg, Sequelize)

Install

Raw queries with pg

Sequelize

3. MySQL

4. REST API examples

MongoDB + Express

PostgreSQL + Express (illustrative)

5. Query optimization

Indexes

Fixing N+1

Projection

Pagination helpers

6. Connection pools

Pool events (pg)

7. Migrations (Sequelize)

8. Example: blog API (MongoDB)

9. Common pitfalls

Connection leaks

SQL injection

Missing transactions

10. Production tips

Environment-specific config

Retry MongoDB connection

Graceful shutdown

Summary

Takeaways

Comparison

ORM vs raw SQL

Next steps

Resources

Related posts

Raw queries with `pg`