#7 Connecting to a Database in Node.js

#7 Connecting to a Database in Node.js

In modern web development, databases are essential for storing, retrieving, and managing data. This chapter will introduce you to the basics of databases, how to connect to both SQL and NoSQL databases using Node.js, and how to perform CRUD operations.

Introduction to Databases (SQL vs NoSQL)

Databases can be broadly categorized into two types: SQL (Structured Query Language) and NoSQL (Not Only SQL).

SQL Databases:

  • Structured Data: SQL databases use a structured schema with tables, rows, and columns. Examples include MySQL, PostgreSQL, and SQLite.
  • ACID Compliance: SQL databases are known for their ACID (Atomicity, Consistency, Isolation, Durability) properties, ensuring reliable transactions.
  • Relational: SQL databases are relational, meaning data is stored in related tables.

NoSQL Databases:

  • Flexible Schema: NoSQL databases have a flexible schema, allowing for unstructured or semi-structured data. Examples include MongoDB, Cassandra, and Couchbase.
  • Scalability: NoSQL databases are designed to scale horizontally, making them ideal for large-scale distributed systems.
  • Document-Oriented: Many NoSQL databases are document-oriented, storing data in JSON-like documents.

Connecting to MongoDB using Mongoose

MongoDB is a popular NoSQL database that stores data in flexible, JSON-like documents. Mongoose is an Object Data Modeling (ODM) library for MongoDB and Node.js, which simplifies data validation, casting, and business logic.

Step 1: Install MongoDB and Mongoose

npm install mongoose

Step 2: Connect to MongoDB

const mongoose = require('mongoose');

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

This code connects to a local MongoDB instance running on the default port 27017.

Step 3: Define a Mongoose Model

const Schema = mongoose.Schema;

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

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

Here, we define a simple schema for a User model with fields for name, email, and age.

Performing CRUD Operations with MongoDB

CRUD stands for Create, Read, Update, and Delete, which are the basic operations you can perform on a database.

Create (Insert) a New Document:

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

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

This code creates a new user and saves it to the database.

Read (Find) Documents:

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

You can retrieve all users or apply filters to find specific documents.

Update a Document:

User.findOneAndUpdate({ email: 'john.doe@example.com' }, { age: 31 }).then(() => {
    console.log('User updated');
}).catch(err => {
    console.error('Error updating user', err);
});

This code updates the age of the user with the specified email.

Delete a Document:

User.findOneAndDelete({ email: 'john.doe@example.com' }).then(() => {
    console.log('User deleted');
}).catch(err => {
    console.error('Error deleting user', err);
});

This code deletes the user with the specified email.

Introduction to SQL Databases with PostgreSQL

PostgreSQL is a powerful, open-source SQL database system that is widely used for relational data.

Step 1: Install PostgreSQL and pg

npm install pg

Step 2: Connect to PostgreSQL

const { Client } = require('pg');

const client = new Client({
    user: 'yourusername',
    host: 'localhost',
    database: 'mydatabase',
    password: 'yourpassword',
    port: 5432
});

client.connect().then(() => {
    console.log('Connected to PostgreSQL');
}).catch(err => {
    console.error('Connection error', err.stack);
});

This code connects to a PostgreSQL database running on the default port 5432.

Step 3: Performing SQL Queries

// Create a table
client.query('CREATE TABLE users (id SERIAL PRIMARY KEY, name VARCHAR(100), email VARCHAR(100))').then(() => {
    console.log('Table created');
}).catch(err => {
    console.error('Error creating table', err.stack);
});

// Insert a record
client.query('INSERT INTO users (name, email) VALUES ($1, $2)', ['Jane Doe', 'jane.doe@example.com']).then(() => {
    console.log('User inserted');
}).catch(err => {
    console.error('Error inserting user', err.stack);
});

// Select records
client.query('SELECT * FROM users').then(res => {
    console.log('Users:', res.rows);
}).catch(err => {
    console.error('Error fetching users', err.stack);
});

// Update a record
client.query('UPDATE users SET name = $1 WHERE email = $2', ['Jane Smith', 'jane.doe@example.com']).then(() => {
    console.log('User updated');
}).catch(err => {
    console.error('Error updating user', err.stack);
});

// Delete a record
client.query('DELETE FROM users WHERE email = $1', ['jane.doe@example.com']).then(() => {
    console.log('User deleted');
}).catch(err => {
    console.error('Error deleting user', err.stack);
});

These examples show how to create tables, insert records, select records, update records, and delete records in a PostgreSQL database.

Conclusion

Understanding how to connect to databases and perform CRUD operations is a fundamental skill in web development. Whether you’re using a NoSQL database like MongoDB with Mongoose or an SQL database like PostgreSQL, Node.js provides powerful tools and libraries to make database interactions straightforward and efficient. By mastering these concepts, you can build robust, data-driven applications that scale effectively.

Tags

#Nodejs, #Database, #MongoDB, #Mongoose, #PostgreSQL, #SQL, #NoSQL, #CRUD, #WebDevelopment, #BackendDevelopment, #JavaScript, #ExpressJS

Tags: , , , ,

Leave a Reply