Part 10 – Database Integration

Databases are essential for persistent data storage in Node.js applications. This guide covers MongoDB (NoSQL) and PostgreSQL (SQL) integration with best practices for production applications.

1. Database Options in Node.js

NoSQL Databases

• MongoDB (Document store)
• Redis (Key-value store)
• Firebase (Real-time database)

SQL Databases

• PostgreSQL (Advanced features)
• MySQL (Popular choice)
• SQLite (Lightweight)

2. MongoDB with Mongoose

Setup and Connection

const mongoose = require('mongoose');

// Connection URI (store in environment variables)
const dbURI = 'mongodb://localhost:27017/mydatabase';

// Connect with options
mongoose.connect(dbURI, {
    useNewUrlParser: true,
    useUnifiedTopology: true,
    useCreateIndex: true
})
.then(() => console.log('MongoDB connected'))
.catch(err => console.error('Connection error:', err));

// Connection events
mongoose.connection.on('connected', () => {});
mongoose.connection.on('error', err => {});
mongoose.connection.on('disconnected', () => {});

Defining Schemas and Models

const { Schema } = mongoose;

const userSchema = new Schema({
    username: { type: String, required: true, unique: true },
    email: { type: String, required: true, unique: true },
    password: { type: String, required: true },
    createdAt: { type: Date, default: Date.now },
    isAdmin: { type: Boolean, default: false }
});

// Add methods
userSchema.methods.verifyPassword = async function(password) {
    return await bcrypt.compare(password, this.password);
};

// Create model
const User = mongoose.model('User', userSchema);

3. CRUD Operations with Mongoose

Create

// Create single document
const newUser = await User.create({
    username: 'johndoe',
    email: 'john@example.com',
    password: hashedPassword
});

// Create multiple
await User.insertMany([
    { username: 'user1', email: 'user1@test.com' },
    { username: 'user2', email: 'user2@test.com' }
]);

Read

// Find all
const users = await User.find();

// Find with conditions
const admins = await User.find({ isAdmin: true });

// Find one
const user = await User.findOne({ email: 'test@test.com' });

// Find by ID
const user = await User.findById(userId);

Update

// Update one
await User.updateOne(
    { _id: userId },
    { $set: { isAdmin: true } }
);

// Find and update
const updatedUser = await User.findOneAndUpdate(
    { email: 'test@test.com' },
    { $inc: { loginCount: 1 } },
    { new: true } // Return updated document
);

Delete

// Delete one
await User.deleteOne({ _id: userId });

// Delete multiple
await User.deleteMany({ isActive: false });

4. PostgreSQL with Sequelize

Setup and Connection

const { Sequelize } = require('sequelize');

// Connection setup
const sequelize = new Sequelize('database', 'user', 'password', {
    host: 'localhost',
    dialect: 'postgres',
    pool: {
        max: 5,
        min: 0,
        acquire: 30000,
        idle: 10000
    },
    logging: false // Disable SQL logging in production
});

// Test connection
try {
    await sequelize.authenticate();
    console.log('Connection established');
} catch (err) {
    console.error('Unable to connect:', err);
}

Defining Models

const User = sequelize.define('User', {
    username: {
        type: DataTypes.STRING,
        allowNull: false,
        unique: true
    },
    email: {
        type: DataTypes.STRING,
        allowNull: false,
        unique: true,
        validate: {
            isEmail: true
        }
    },
    password: {
        type: DataTypes.STRING,
        allowNull: false
    }
}, {
    // Model options
    timestamps: true
});

5. Database Best Practices

Connection Management

• Use connection pooling
• Handle disconnections gracefully
• Implement retry logic for cloud databases

Performance Optimization

• Create proper indexes
• Use projections to limit returned data
• Implement pagination for large datasets
• Consider caching with Redis

Security Considerations

• Always use environment variables for credentials
• Sanitize all user inputs
• Implement proper role-based access
• Regularly backup your database

6. Transaction Management

MongoDB Transactions

const session = await mongoose.startSession();
session.startTransaction();

try {
    await Order.create([orderDetails], { session });
    await User.updateOne(
        { _id: userId },
        { $push: { orders: orderId } },
        { session }
    );

    await session.commitTransaction();
} catch (err) {
    await session.abortTransaction();
    throw err;
} finally {
    session.endSession();
}

Sequelize Transactions

const transaction = await sequelize.transaction();

try {
    const user = await User.create({
        username: 'newuser',
        email: 'new@example.com'
    }, { transaction });

    await Profile.create({
        userId: user.id,
        bio: 'New user bio'
    }, { transaction });

    await transaction.commit();
} catch (err) {
    await transaction.rollback();
    throw err;
}

Next: Authentication in Node.js →

Database Cheat Sheet

• MongoDB: Great for flexible schemas and scalability
• PostgreSQL: Ideal for complex queries and transactions
• Connection Pooling: Essential for production apps
• ORM/ODM: Mongoose for MongoDB, Sequelize for SQL
• Migrations: Track schema changes over time