Working with Databases in Node.js

In modern web development, interacting with databases is essential for storing and retrieving application data. Node.js offers multiple ways to connect to databases, both SQL and NoSQL. This module will introduce you to working with databases in Node.js, covering both relational (SQL) and non-relational (NoSQL) databases. We will walk you through how to connect, query, and manage databases like MongoDB and MySQL.

Table of Contents

1. Introduction to Databases in Node.js
2. Setting Up MongoDB with Node.js
3. Setting Up MySQL with Node.js
4. Basic CRUD Operations with MongoDB
5. Basic CRUD Operations with MySQL
6. Using Object-Relational Mappers (ORMs)
7. Using MongoDB with Mongoose
8. Connecting to Remote Databases
9. Conclusion

1. Introduction to Databases in Node.js

Node.js provides several libraries and frameworks to work with databases. Since Node.js is designed for asynchronous, event-driven applications, many libraries designed to work with databases are built around these principles, making it easy to perform non-blocking I/O operations.

The two main types of databases that Node.js applications typically interact with are:

• SQL (Relational Databases): These databases, such as MySQL and PostgreSQL, store data in structured tables and use SQL queries to interact with the data.
• NoSQL (Non-relational Databases): These databases, such as MongoDB, store data in formats like JSON or BSON and are more flexible when it comes to handling unstructured data.

In this module, we’ll first focus on MongoDB (a NoSQL database) and MySQL (a SQL database) to understand how to interact with these databases in Node.js.

2. Setting Up MongoDB with Node.js

MongoDB is a popular NoSQL database that stores data in a JSON-like format called BSON (Binary JSON). To interact with MongoDB in Node.js, we use the MongoDB Node.js Driver or the more popular Mongoose library.

Step 1: Installing MongoDB Driver and Mongoose

First, we need to install the MongoDB Node.js Driver and Mongoose:

bashCopyEditnpm install mongodb mongoose --save

Step 2: Connecting to MongoDB

Here’s an example of connecting to a local MongoDB instance using Mongoose:

const mongoose = require('mongoose');

mongoose.connect('mongodb://localhost:27017/mydb', {
  useNewUrlParser: true,
  useUnifiedTopology: true
})
.then(() => console.log('Connected to MongoDB'))
.catch(err => console.log('Error connecting to MongoDB:', err));

3. Setting Up MySQL with Node.js

MySQL is one of the most popular relational databases. It uses SQL (Structured Query Language) to interact with data, making it ideal for structured data storage and retrieval.

To interact with MySQL in Node.js, we use the mysql2 package.

Step 1: Installing MySQL2

Install the mysql2 package using npm:

npm install mysql2 --save

Step 2: Connecting to MySQL

Here’s an example of connecting to a MySQL database:

const mysql = require('mysql2');

const connection = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  password: 'password',
  database: 'mydb'
});

connection.connect(err => {
  if (err) {
    console.error('Error connecting to MySQL:', err);
    return;
  }
  console.log('Connected to MySQL');
});

4. Basic CRUD Operations with MongoDB

Now that we have connected to MongoDB, let’s perform basic CRUD (Create, Read, Update, Delete) operations.

Creating a Document:

const mongoose = require('mongoose');

// Define a Schema
const userSchema = new mongoose.Schema({
  name: String,
  age: Number,
  email: String
});

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

// Create a new user
const newUser = new User({
  name: 'John Doe',
  age: 30,
  email: '[email protected]'
});

newUser.save()
  .then(user => console.log('User created:', user))
  .catch(err => console.log('Error creating user:', err));

Reading Documents:

User.find()
  .then(users => console.log('Users:', users))
  .catch(err => console.log('Error reading users:', err));

5. Basic CRUD Operations with MySQL

Next, let’s perform basic CRUD operations with MySQL.

Creating a Record:

const query = 'INSERT INTO users (name, age, email) VALUES (?, ?, ?)';
const values = ['John Doe', 30, '[email protected]'];

connection.execute(query, values, (err, results) => {
  if (err) {
    console.error('Error inserting data:', err);
    return;
  }
  console.log('User created with ID:', results.insertId);
});

Reading Records:

const query = 'SELECT * FROM users';

connection.execute(query, (err, results) => {
  if (err) {
    console.error('Error fetching data:', err);
    return;
  }
  console.log('Users:', results);
});

6. Using Object-Relational Mappers (ORMs)

ORMs help simplify database interactions by allowing you to use JavaScript objects to represent database tables. For MySQL, Sequelize is a popular ORM, while Mongoose is commonly used for MongoDB.

Sequelize (MySQL ORM):

npm install sequelize sequelize-cli mysql2 --save

Example:

const { Sequelize, DataTypes } = require('sequelize');
const sequelize = new Sequelize('mysql://root:password@localhost:3306/mydb');

const User = sequelize.define('User', {
  name: {
    type: DataTypes.STRING,
    allowNull: false
  },
  age: {
    type: DataTypes.INTEGER,
    allowNull: false
  },
  email: {
    type: DataTypes.STRING,
    allowNull: false
  }
});

sequelize.sync()
  .then(() => User.create({ name: 'John Doe', age: 30, email: '[email protected]' }))
  .then(user => console.log('User created:', user))
  .catch(err => console.log('Error:', err));

7. Using MongoDB with Mongoose

Mongoose simplifies working with MongoDB by providing a schema-based solution to model your data. In addition to basic CRUD, Mongoose offers built-in query methods, validation, and middleware.

8. Connecting to Remote Databases

Whether you’re working with MongoDB or MySQL, you’ll often need to connect to a remote database instead of a local instance. The connection string will include the hostname, port, and credentials necessary for accessing the remote database.

For MongoDB (using MongoDB Atlas):

mongoose.connect('mongodb+srv://<username>:<password>@cluster0.mongodb.net/mydb', {
  useNewUrlParser: true,
  useUnifiedTopology: true
})
.then(() => console.log('Connected to remote MongoDB'))
.catch(err => console.log('Error connecting:', err));

For MySQL (using a remote server):

const connection = mysql.createConnection({
  host: 'remote-server.com',
  user: 'root',
  password: 'password',
  database: 'mydb'
});

9. Conclusion

In this module, we explored how to interact with both SQL (MySQL) and NoSQL (MongoDB) databases in a Node.js application. We covered basic CRUD operations, setting up databases locally and remotely, and using ORM tools like Sequelize and Mongoose.