Database Integration

  Almost all modern web applications need to interact with a database to store, manage, and retrieve information.

   Node.js, thanks to its library ecosystem and asynchronous nature, is an excellent choice for building the backend that communicates with various databases, whether SQL or NoSQL. In this lesson, we will explore how to integrate Node.js with different types of databases using popular libraries like Mongoose for MongoDB and Sequelize for relational databases.

Database Types and Node.js

Node.js can interact with a wide variety of database management systems (DBMS). The two main types are:

• SQL Databases (Relational):

  Store data in tables with rows and columns, and relationships between them are explicitly defined. They use SQL (Structured Query Language) for data management. Popular examples include PostgreSQL, MySQL, SQL Server, and SQLite.

  For Node.js, ORMs (Object-Relational Mappers) like Sequelize or TypeORM are often used, which allow interacting with the database using JavaScript objects, eliminating the need to write raw SQL.

• NoSQL Databases (Non-Relational):

  Offer flexible and scalable data models for large volumes of data and high availability. They are divided into several subtypes: document (MongoDB), key-value (Redis), wide-column (Cassandra), and graph (Neo4j).

  For Node.js, ODMs (Object-Document Mappers) like Mongoose (for MongoDB) or native drivers are commonly used to interact with these databases.

Integration with MongoDB using Mongoose (NoSQL)

MongoDB is a very popular NoSQL document database, and Mongoose is a Node.js library that provides a schema-based solution for modeling your application data, making interaction with MongoDB much simpler and more robust.

Step 1: Install Mongoose and MongoDB Driver (if you don't have a local instance, you can use MongoDB Atlas)

npm install mongoose

Step 2: Connect to MongoDB and Define a Schema/Model

// db.js (or directly in your app.js/server.js)
const mongoose = require('mongoose');

// Define the connection URI to your MongoDB database
const DB_URI = 'mongodb://localhost:27017/mydatabase'; // Or your MongoDB Atlas URI

const connectDB = async () => {
  try {
    await mongoose.connect(DB_URI, {
      useNewUrlParser: true,
      useUnifiedTopology: true,
      // useCreateIndex: true, // Deprecated in Mongoose 6.x
      // useFindAndModify: false, // Deprecated in Mongoose 6.x
    });
    console.log('MongoDB Connected...');
  } catch (err) {
    console.error(err.message);
    // Exit process with failure
    process.exit(1);
  }
};

module.exports = connectDB;

// models/User.js
const mongoose = require('mongoose');

const UserSchema = new mongoose.Schema({
  name: {
    type: String,
    required: true,
    trim: true,
  },
  email: {
    type: String,
    required: true,
    unique: true,
    trim: true,
    lowercase: true,
  },
  age: {
    type: Number,
    min: 18,
  },
  createdAt: {
    type: Date,
    default: Date.now,
  },
});

module.exports = mongoose.model('User', UserSchema);

Step 3: Use the Model in your Express.js Application

// app.js or server.js
const express = require('express');
const connectDB = require('./db'); // Import the connection function
const User = require('./models/User'); // Import the user model

const app = express();
const PORT = process.env.PORT || 3000;

// Connect to the database
connectDB();

// Middleware to parse JSON
app.use(express.json());

// Route to create a new user
app.post('/api/users', async (req, res) => {
  try {
    const { name, email, age } = req.body;
    const newUser = new User({ name, email, age });
    await newUser.save();
    res.status(201).json(newUser);
  } catch (err) {
    console.error(err.message);
    res.status(500).send('Server Error');
  }
});

// Route to get all users
app.get('/api/users', async (req, res) => {
  try {
    const users = await User.find();
    res.json(users);
  } catch (err) {
    console.error(err.message);
    res.status(500).send('Server Error');
  }
});

// Start the server
app.listen(PORT, () => {
  console.log(`Server listening on http://localhost:${PORT}`);
});

Integration with PostgreSQL/MySQL using Sequelize (SQL)

Sequelize is a popular ORM (Object-Relational Mapper) for Node.js that simplifies interaction with relational databases like PostgreSQL, MySQL, SQLite, and SQL Server. It allows you to work with your database tables as JavaScript objects.

Step 1: Install Sequelize and your database driver (e.g., `pg` for PostgreSQL, `mysql2` for MySQL)

npm install sequelize pg // For PostgreSQL
# npm install sequelize mysql2 // For MySQL

Step 2: Connect to the Database and Define a Model

// config/database.js
const { Sequelize, DataTypes } = require('sequelize');

const sequelize = new Sequelize('mydatabase', 'myuser', 'mypassword', {
  host: 'localhost',
  dialect: 'postgres', // or 'mysql'
  logging: false, // Disable SQL logging in console
});

const connectDB = async () => {
  try {
    await sequelize.authenticate();
    console.log('Database connection established successfully.');
    // Synchronize models with the database (creates tables if they don't exist)
    await sequelize.sync({ alter: true }); // 'alter: true' will modify existing tables
    console.log('Models synchronized with the database.');
  } catch (error) {
    console.error('Could not connect to the database:', error);
    process.exit(1);
  }
};

module.exports = { sequelize, connectDB };

// models/Product.js
const { DataTypes } = require('sequelize');
const { sequelize } = require('../config/database');

const Product = sequelize.define('Product', {
  id: {
    type: DataTypes.INTEGER,
    autoIncrement: true,
    primaryKey: true,
  },
  name: {
    type: DataTypes.STRING,
    allowNull: false,
  },
  price: {
    type: DataTypes.FLOAT,
    allowNull: false,
  },
  description: {
    type: DataTypes.TEXT,
    allowNull: true,
  },
}, {
  // Model options
  timestamps: true, // Adds createdAt and updatedAt automatically
});

module.exports = Product;

Step 3: Use the Model in your Express.js Application

// app.js or server.js
const express = require('express');
const { connectDB } = require('./config/database'); // Import the connection
const Product = require('./models/Product'); // Import the product model

const app = express();
const PORT = process.env.PORT || 3000;

// Connect to the database
connectDB();

// Middleware to parse JSON
app.use(express.json());

// Route to create a new product
app.post('/api/products', async (req, res) => {
  try {
    const { name, price, description } = req.body;
    const newProduct = await Product.create({ name, price, description });
    res.status(201).json(newProduct);
  } catch (err) {
    console.error(err.message);
    res.status(500).send('Server error creating product');
  }
});

// Route to get all products
app.get('/api/products', async (req, res) => {
  try {
    const products = await Product.findAll();
    res.json(products);
  } catch (err) {
    console.error(err.message);
    res.status(500).send('Server error getting products');
  }
});

// Start the server
app.listen(PORT, () => {
  console.log(`Server listening on http://localhost:${PORT}`);
});
```

  The choice between a SQL or NoSQL database, and the ORM/ODM to use, will depend on your project's specific requirements. Both approaches offer powerful tools for managing your data in Node.js efficiently and scalably.