Working with Databases in Node.js

• SQL Databases: Structured Query Language (SQL) databases, like MySQL, PostgreSQL, and SQLite, are relational databases where data is stored in tables with predefined schemas. These databases use SQL for querying and managing data, and they enforce relationships between data through foreign keys.
• NoSQL Databases: NoSQL databases, like MongoDB, CouchDB, and Cassandra, are non-relational and generally schema-less. They store data in various formats, such as key-value pairs, documents (JSON-like), or graphs. NoSQL databases are designed to handle large volumes of unstructured or semi-structured data and can scale horizontally.

Mongoose is a popular ODM (Object Data Modeling) library for MongoDB and Node.js. It provides a schema-based solution to model your application data, making it easier to work with MongoDB.

Step 1: Install Mongoose

First, install Mongoose in your Node.js project:

npm install mongoose

Step 2: Connect to MongoDB

You can connect to a MongoDB database using Mongoose by providing the connection string.

const mongoose = require('mongoose');

// Replace with your MongoDB connection string
const mongoURI = 'mongodb://localhost:27017/mydatabase';

mongoose.connect(mongoURI, { useNewUrlParser: true, useUnifiedTopology: true })
  .then(() => {
    console.log('Connected to MongoDB');
  })
  .catch((err) => {
    console.error('Error connecting to MongoDB:', err);
  });

Explanation:

• mongoose.connect(): Establishes a connection to the MongoDB server.
• useNewUrlParser and useUnifiedTopology: Options to opt into MongoDB driver’s new connection management engine.

CRUD stands for Create, Read, Update, and Delete—fundamental operations for interacting with databases.

Step 1: Define a Mongoose Schema and Model

A schema defines the structure of the documents within a collection, and a model provides an interface to the database for creating, querying, updating, and deleting records.

const mongoose = require('mongoose');

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

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

Step 2: Create a Document (C in CRUD)

You can create a new document by instantiating a Mongoose model and saving it.

const newUser = new User({
  name: 'John Doe',
  email: 'johndoe@example.com',
  age: 30,
});

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

Step 3: Read Documents (R in CRUD)

You can retrieve documents from the database using methods like find, findOne, etc.

// Find all users
User.find()
  .then((users) => {
    console.log('All users:', users);
  })
  .catch((err) => {
    console.error('Error retrieving users:', err);
  });

// Find a user by ID
User.findById('some-user-id')
  .then((user) => {
    if (user) {
      console.log('User found:', user);
    } else {
      console.log('User not found');
    }
  })
  .catch((err) => {
    console.error('Error finding user:', err);
  });

Step 4: Update a Document (U in CRUD)

You can update a document using methods like updateOne, findByIdAndUpdate, etc.

User.findByIdAndUpdate('some-user-id', { age: 31 }, { new: true })
.then((user) => {
  if (user) {
    console.log('User updated:', user);
  } else {
    console.log('User not found');
  }
})
.catch((err) => {
  console.error('Error updating user:', err);
});

Step 5: Delete a Document (D in CRUD)

You can delete a document using methods like deleteOne, findByIdAndDelete, etc.

User.findByIdAndDelete('some-user-id')
.then((user) => {
  if (user) {
    console.log('User deleted:', user);
  } else {
    console.log('User not found');
  }
})
.catch((err) => {
  console.error('Error deleting user:', err);
});

SQL Databases (e.g., MySQL, PostgreSQL) are commonly used relational databases that structure data in tables with rows and columns. To interact with SQL databases in Node.js, you can use libraries like Knex.js or Sequelize.

Using Knex.js

Knex.js is a SQL query builder for Node.js that supports various databases, including MySQL, PostgreSQL, and SQLite.

Step 1: Install Knex.js and a Database Driver

For example, to use Knex.js with MySQL:

npm install knex mysql

Step 2: Configure Knex.js

Set up a Knex.js instance with your database configuration:

const knex = require('knex')({
  client: 'mysql',
  connection: {
    host: '127.0.0.1',
    user: 'your-username',
    password: 'your-password',
    database: 'your-database'
  }
});

Step 3: Perform CRUD Operations with Knex.js

// Create a new record
knex('users').insert({ name: 'Jane Doe', email: 'janedoe@example.com', age: 25 })
  .then(() => console.log('User created'))
  .catch(err => console.error('Error:', err));

// Read records
knex('users').select('*')
  .then(users => console.log('Users:', users))
  .catch(err => console.error('Error:', err));

// Update a record
knex('users').where({ id: 1 }).update({ age: 26 })
  .then(() => console.log('User updated'))
  .catch(err => console.error('Error:', err));

// Delete a record
knex('users').where({ id: 1 }).del()
  .then(() => console.log('User deleted'))
  .catch(err => console.error('Error:', err));

Using Sequelize

Sequelize is a powerful ORM (Object-Relational Mapping) library for Node.js that supports MySQL, PostgreSQL, SQLite, and more.

Step 1: Install Sequelize and a Database Driver

For example, to use Sequelize with MySQL:

npm install sequelize mysql2

Step 2: Set Up Sequelize

Configure Sequelize with your database connection:

const { Sequelize, DataTypes } = require('sequelize');
const sequelize = new Sequelize('your-database', 'your-username', 'your-password', {
  host: 'localhost',
  dialect: 'mysql'
});

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

Step 3: Sync the Model with the Database

Synchronize the model with the database (create the table if it doesn’t exist):

sequelize.sync()
.then(() => console.log('Database & tables created!'))
.catch(err => console.error('Error:', err));

Step 4: Perform CRUD Operations with Sequelize

// Create a new user
User.create({ name: 'Jane Doe', email: 'janedoe@example.com', age: 25 })
  .then(user => console.log('User created:', user))
  .catch(err => console.error('Error:', err));

// Read users
User.findAll()
  .then(users => console.log('Users:', users))
  .catch(err => console.error('Error:', err));

// Update a user
User.update({ age: 26 }, { where: { id: 1 } })
  .then(() => console.log('User updated'))
  .catch(err => console.error('Error:', err));

// Delete a user
User.destroy({ where: { id: 1 } })
  .then(() => console.log('User deleted'))
  .catch(err => console.error('Error:', err));

Working with databases in Node.js is essential for building dynamic, data-driven applications. Whether you’re using NoSQL databases like MongoDB with Mongoose or SQL databases like MySQL with Knex.js or Sequelize, understanding how to connect, perform CRUD operations, and manage your data is crucial. Mongoose provides a schema-based approach to MongoDB, while Knex.js and Sequelize offer robust tools for interacting with SQL databases. Mastering these tools will enable you to build powerful and scalable applications in Node.js.