Node.js Intermediate

Node.js Database Integration: MongoDB and MySQL

CodingerWeb
CodingerWeb
22 views 80 min read
Share: Twitter Facebook LinkedIn

Table of Contents

Database Integration in Node.js

Node.js applications often need to store and retrieve data from databases. This lesson covers working with both NoSQL (MongoDB) and SQL (MySQL) databases.

Working with MongoDB

MongoDB is a popular NoSQL database that stores data in flexible, JSON-like documents.

Installing MongoDB Driver

# Install MongoDB driver
npm install mongodb

# Or install Mongoose (ODM for MongoDB)
npm install mongoose

Connecting to MongoDB with Native Driver

const { MongoClient } = require("mongodb");

const url = "mongodb://localhost:27017";
const dbName = "myapp";

async function connectToMongoDB() {
    try {
        const client = new MongoClient(url);
        await client.connect();
        console.log("Connected to MongoDB");
        
        const db = client.db(dbName);
        
        // Insert a document
        const users = db.collection("users");
        const result = await users.insertOne({
            name: "John Doe",
            email: "john@example.com",
            age: 30,
            createdAt: new Date()
        });
        
        console.log("User inserted with ID:", result.insertedId);
        
        // Find documents
        const allUsers = await users.find({}).toArray();
        console.log("All users:", allUsers);
        
        // Update a document
        await users.updateOne(
            { email: "john@example.com" },
            { $set: { age: 31 } }
        );
        
        // Delete a document
        await users.deleteOne({ email: "john@example.com" });
        
        await client.close();
    } catch (err) {
        console.error("MongoDB error:", err);
    }
}

connectToMongoDB();

Using Mongoose ODM

const mongoose = require("mongoose");

// Connect to MongoDB
mongoose.connect("mongodb://localhost:27017/myapp");

// Define a schema
const userSchema = new mongoose.Schema({
    name: {
        type: String,
        required: true,
        trim: true
    },
    email: {
        type: String,
        required: true,
        unique: true,
        lowercase: true
    },
    age: {
        type: Number,
        min: 0,
        max: 120
    },
    isActive: {
        type: Boolean,
        default: true
    },
    createdAt: {
        type: Date,
        default: Date.now
    }
});

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

// CRUD operations with Mongoose
async function mongooseOperations() {
    try {
        // Create a user
        const newUser = new User({
            name: "Jane Smith",
            email: "jane@example.com",
            age: 28
        });
        
        await newUser.save();
        console.log("User created:", newUser);
        
        // Find users
        const users = await User.find({ isActive: true });
        console.log("Active users:", users);
        
        // Find one user
        const user = await User.findOne({ email: "jane@example.com" });
        console.log("Found user:", user);
        
        // Update user
        const updatedUser = await User.findByIdAndUpdate(
            user._id,
            { age: 29 },
            { new: true }
        );
        console.log("Updated user:", updatedUser);
        
        // Delete user
        await User.findByIdAndDelete(user._id);
        console.log("User deleted");
        
    } catch (err) {
        console.error("Mongoose error:", err);
    }
}

mongooseOperations();

Working with MySQL

MySQL is a popular relational database management system.

Installing MySQL Driver

# Install MySQL driver
npm install mysql2

# Or install Sequelize (ORM for SQL databases)
npm install sequelize mysql2

Connecting to MySQL

const mysql = require("mysql2/promise");

async function connectToMySQL() {
    try {
        // Create connection
        const connection = await mysql.createConnection({
            host: "localhost",
            user: "root",
            password: "password",
            database: "myapp"
        });
        
        console.log("Connected to MySQL");
        
        // Create table
        await connection.execute(`
            CREATE TABLE IF NOT EXISTS users (
                id INT AUTO_INCREMENT PRIMARY KEY,
                name VARCHAR(100) NOT NULL,
                email VARCHAR(100) UNIQUE NOT NULL,
                age INT,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        `);
        
        // Insert data
        const [result] = await connection.execute(
            "INSERT INTO users (name, email, age) VALUES (?, ?, ?)",
            ["John Doe", "john@example.com", 30]
        );
        
        console.log("User inserted with ID:", result.insertId);
        
        // Select data
        const [rows] = await connection.execute(
            "SELECT * FROM users WHERE age > ?",
            [25]
        );
        
        console.log("Users over 25:", rows);
        
        // Update data
        await connection.execute(
            "UPDATE users SET age = ? WHERE email = ?",
            [31, "john@example.com"]
        );
        
        // Delete data
        await connection.execute(
            "DELETE FROM users WHERE email = ?",
            ["john@example.com"]
        );
        
        await connection.end();
    } catch (err) {
        console.error("MySQL error:", err);
    }
}

connectToMySQL();

Using Connection Pool

const mysql = require("mysql2/promise");

// Create connection pool
const pool = mysql.createPool({
    host: "localhost",
    user: "root",
    password: "password",
    database: "myapp",
    waitForConnections: true,
    connectionLimit: 10,
    queueLimit: 0
});

class UserService {
    static async createUser(userData) {
        try {
            const { name, email, age } = userData;
            const [result] = await pool.execute(
                "INSERT INTO users (name, email, age) VALUES (?, ?, ?)",
                [name, email, age]
            );
            
            return { id: result.insertId, name, email, age };
        } catch (err) {
            throw new Error(`Error creating user: ${err.message}`);
        }
    }
    
    static async getUserById(id) {
        try {
            const [rows] = await pool.execute(
                "SELECT * FROM users WHERE id = ?",
                [id]
            );
            
            return rows[0] || null;
        } catch (err) {
            throw new Error(`Error fetching user: ${err.message}`);
        }
    }
    
    static async getAllUsers() {
        try {
            const [rows] = await pool.execute("SELECT * FROM users");
            return rows;
        } catch (err) {
            throw new Error(`Error fetching users: ${err.message}`);
        }
    }
    
    static async updateUser(id, userData) {
        try {
            const { name, email, age } = userData;
            await pool.execute(
                "UPDATE users SET name = ?, email = ?, age = ? WHERE id = ?",
                [name, email, age, id]
            );
            
            return await this.getUserById(id);
        } catch (err) {
            throw new Error(`Error updating user: ${err.message}`);
        }
    }
    
    static async deleteUser(id) {
        try {
            const [result] = await pool.execute(
                "DELETE FROM users WHERE id = ?",
                [id]
            );
            
            return result.affectedRows > 0;
        } catch (err) {
            throw new Error(`Error deleting user: ${err.message}`);
        }
    }
}

// Usage example
async function demonstrateUserService() {
    try {
        // Create user
        const newUser = await UserService.createUser({
            name: "Alice Johnson",
            email: "alice@example.com",
            age: 25
        });
        console.log("Created user:", newUser);
        
        // Get all users
        const users = await UserService.getAllUsers();
        console.log("All users:", users);
        
        // Update user
        const updatedUser = await UserService.updateUser(newUser.id, {
            name: "Alice Smith",
            email: "alice.smith@example.com",
            age: 26
        });
        console.log("Updated user:", updatedUser);
        
        // Delete user
        const deleted = await UserService.deleteUser(newUser.id);
        console.log("User deleted:", deleted);
        
    } catch (err) {
        console.error("Service error:", err.message);
    }
}

demonstrateUserService();

Express.js with Database Integration

const express = require("express");
const mysql = require("mysql2/promise");

const app = express();
app.use(express.json());

// Database connection pool
const pool = mysql.createPool({
    host: "localhost",
    user: "root",
    password: "password",
    database: "myapp",
    waitForConnections: true,
    connectionLimit: 10,
    queueLimit: 0
});

// Routes
app.get("/api/users", async (req, res) => {
    try {
        const [rows] = await pool.execute("SELECT * FROM users");
        res.json({ users: rows });
    } catch (err) {
        res.status(500).json({ error: err.message });
    }
});

app.get("/api/users/:id", async (req, res) => {
    try {
        const [rows] = await pool.execute(
            "SELECT * FROM users WHERE id = ?",
            [req.params.id]
        );
        
        if (rows.length === 0) {
            return res.status(404).json({ error: "User not found" });
        }
        
        res.json({ user: rows[0] });
    } catch (err) {
        res.status(500).json({ error: err.message });
    }
});

app.post("/api/users", async (req, res) => {
    try {
        const { name, email, age } = req.body;
        
        if (!name || !email) {
            return res.status(400).json({
                error: "Name and email are required"
            });
        }
        
        const [result] = await pool.execute(
            "INSERT INTO users (name, email, age) VALUES (?, ?, ?)",
            [name, email, age]
        );
        
        const newUser = {
            id: result.insertId,
            name,
            email,
            age
        };
        
        res.status(201).json({ user: newUser });
    } catch (err) {
        if (err.code === "ER_DUP_ENTRY") {
            res.status(400).json({ error: "Email already exists" });
        } else {
            res.status(500).json({ error: err.message });
        }
    }
});

app.listen(3000, () => {
    console.log("Server with database running on http://localhost:3000");
});

Exercise

Create a blog application with database integration that includes:

  1. User registration and authentication
  2. CRUD operations for blog posts
  3. Comments system
  4. Database connection pooling
  5. Error handling and validation

What's Next?

In the next lesson, we'll explore authentication and authorization in Node.js applications.

Previous Express.js Framework: Building Web Applications with Ease
Next Node.js Authentication and Authorization: Securing Your Applications