Database Integration

MySQL with Node.js

MySQL is one of the most popular relational databases. Use the mysql2 package to connect to MySQL from Node.js.

// Install: npm install mysql2

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

async function connectMySQL() {
  const connection = await mysql.createConnection({
    host: process.env.DB_HOST || 'localhost',
    user: process.env.DB_USER || 'root',
    password: process.env.DB_PASSWORD || '',
    database: 'node_tutorial'
  });

  // 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,
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    )
  `);

  // Insert
  const [result] = await connection.execute(
    'INSERT INTO users (name, email) VALUES (?, ?)',
    ['Alice', 'alice@example.com']
  );
  console.log('Inserted ID:', result.insertId);

  // Query
  const [rows] = await connection.execute('SELECT * FROM users WHERE id = ?', [1]);
  console.log('User:', rows[0]);

  // Update
  await connection.execute('UPDATE users SET name = ? WHERE id = ?', ['Alice Updated', 1]);

  // Delete
  await connection.execute('DELETE FROM users WHERE id = ?', [1]);

  await connection.end();
}

connectMySQL().catch(console.error);

MongoDB with Node.js

MongoDB is a popular NoSQL database. Use the official mongodb driver or the mongoose ODM.

Using Mongoose (ODM)

// Install: npm install mongoose

const mongoose = require('mongoose');

// Connect
mongoose.connect('mongodb://localhost:27017/node_tutorial');

// Define schema
const userSchema = new mongoose.Schema({
  name: { type: String, required: true },
  email: { type: String, required: true, unique: true },
  age: { type: Number, min: 0 },
  address: {
    street: String,
    city: String
  },
  createdAt: { type: Date, default: Date.now }
});

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

// CRUD operations
async function crudExample() {
  // Create
  const user = await User.create({
    name: 'Bob',
    email: 'bob@example.com',
    age: 25
  });

  // Read
  const found = await User.findOne({ email: 'bob@example.com' });
  console.log(found);

  // Update
  await User.updateOne({ _id: user._id }, { $set: { age: 26 } });

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

MongoDB Aggregations

// Aggregation pipeline
const results = await User.aggregate([
  { $match: { age: { $gte: 18 } } },
  { $group: { _id: '$address.city', count: { $sum: 1 }, avgAge: { $avg: '$age' } } },
  { $sort: { count: -1 } },
  { $limit: 10 }
]);

Connection Pooling

Always use connection pooling in production to reuse database connections:

// MySQL pool
const pool = mysql.createPool({
  host: 'localhost',
  user: 'root',
  database: 'node_tutorial',
  waitForConnections: true,
  connectionLimit: 10,
  queueLimit: 0
});

// Use pool
const [rows] = await pool.execute('SELECT * FROM users');

Choosing Between SQL and NoSQL