[Roadmap_Node] 9_Working with Databases

—
Node

Table of content

Introduction

Here’s a breakdown of working with databases in Node.js:

1. Choosing a Database:

2. Database Drivers:

3. Connecting to the Database:

4. CRUD Operations (Create, Read, Update, Delete):

5. Promises or Async/Await:

Example (Using mysql driver for a simple SELECT query):

const mysql = require("mysql");

const connection = mysql.createConnection({
  host: "localhost",
  user: "your_username",
  password: "your_password",
  database: "your_database",
});

connection.connect((err) => {
  if (err) {
    console.error("Error connecting to database:", err);
  } else {
    console.log("Connected to database!");

    const query = "SELECT * FROM users";
    connection.query(query, (err, results) => {
      if (err) {
        console.error("Error fetching data:", err);
      } else {
        console.log("Fetched user data:", results);
      }
    });

    connection.end(); // Close the connection after the query
  }
});

Important Considerations:

By understanding these concepts and following best practices, you can effectively work with databases in your Node.js applications!

Connecting to Databases (MongoDB, MySQL)

Node.js provides a powerful platform for building web applications, and interacting with databases is a crucial aspect of many applications. Here’s a breakdown of how to connect to popular databases like MongoDB and MySQL in Node.js:

1. Choosing the Right Database:

2. Database Drivers:

Node.js itself doesn’t have built-in database access. You’ll need to use a database driver specific to your chosen database type. Here are popular options:

3. Installation:

Use npm or yarn to install the required driver package for your chosen database:

npm install mongoose  # For MongoDB (Mongoose)
npm install mysql2    # For MySQL

4. Connection Setup:

MongoDB (Mongoose):

const mongoose = require("mongoose");

const uri = "mongodb://localhost:27017/your_database_name"; // Replace with your connection details

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

MySQL (mysql2):

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

const connection = mysql.createPool({
  host: "localhost",
  user: "your_username",
  password: "your_password",
  database: "your_database_name",
});

connection
  .getConnection()
  .then((conn) => {
    console.log("Connected to MySQL!");
    // Use the connection object (`conn`) for queries
  })
  .catch((err) => console.error("Error connecting to MySQL:", err));

5. Performing CRUD Operations:

MongoDB (Mongoose):

Use Mongoose’s schema and model features to define your data structure and perform CRUD operations:

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

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

// Create a new user
User.create({ name: "John Doe", email: "john.doe@example.com" })
  .then((user) => console.log("User created:", user))
  .catch((err) => console.error("Error creating user:", err));

// Find all users
User.find()
  .then((users) => console.log("Users:", users))
  .catch((err) => console.error("Error fetching users:", err));

MySQL (mysql2):

Use prepared statements (recommended for security) or string interpolation (with caution) to execute queries:

connection
  .query("SELECT * FROM users")
  .then(([rows]) => console.log("Users:", rows))
  .catch((err) => console.error("Error fetching users:", err));

// Insert a new user (prepared statement example)
const newUser = { name: "Jane Doe", email: "jane.doe@example.com" };
connection
  .query("INSERT INTO users SET ?", newUser)
  .then(() => console.log("User inserted!"))
  .catch((err) => console.error("Error inserting user:", err));

Important Considerations:

Remember: These are basic examples. Real-world applications will likely involve more complex queries, data manipulation, and error handling practices.

Mongoose (for MongoDB)

Mongoose is a popular Object Document Mapper (ODM) library for Node.js that simplifies interacting with MongoDB databases. It provides a layer of abstraction over the native MongoDB driver, allowing you to work with data using JavaScript objects instead of raw queries. Here’s a breakdown of Mongoose in Node.js:

Benefits of Using Mongoose:

Core Concepts in Mongoose:

  1. Schema: A blueprint that defines the structure of your data documents in MongoDB. You specify properties (fields) and their data types.

  2. Model: A Mongoose model is essentially a compiled version of your schema that provides functions for CRUD (Create, Read, Update, Delete) operations on your data.

  3. CRUD Operations: Mongoose provides intuitive methods for performing CRUD operations on your data using the model:

    • create() - Insert a new document into the collection.
    • find() - Retrieve documents based on criteria (queries).
    • findById() - Find a document by its unique ID.
    • update() or updateOne() - Update an existing document.
    • deleteOne() or deleteMany() - Delete documents.

Example (Simple Mongoose Usage):

const mongoose = require("mongoose");

// Define a schema for User documents
const userSchema = new mongoose.Schema({
  name: String,
  email: String,
});

// Compile the schema into a model
const User = mongoose.model("User", userSchema);

// Create a new user
const newUser = new User({ name: "John Doe", email: "john.doe@example.com" });
newUser
  .save()
  .then((user) => console.log("User created:", user))
  .catch((err) => console.error("Error creating user:", err));

// Find all users
User.find()
  .then((users) => console.log("Users:", users))
  .catch((err) => console.error("Error fetching users:", err));

Additional Features:

Mongoose is a powerful tool that streamlines working with MongoDB in Node.js applications. It offers a productive and robust way to manage your data and interact with your MongoDB database.

Here are some additional points to consider:

Sequelize (for MySQL)

Sequelize is a popular Object-Relational Mapper (ORM) library for Node.js that simplifies interacting with relational databases like MySQL. It acts as a bridge between your Node.js application and your MySQL database, allowing you to define data models and perform CRUD (Create, Read, Update, Delete) operations using JavaScript objects and functions.

Benefits of Using Sequelize:

Core Concepts in Sequelize:

  1. Model: A Sequelize model represents a database table. You define the model’s structure by specifying properties (columns) and their data types.

  2. Instance: An instance of a model represents a single row of data in the corresponding database table.

  3. Associations: Define relationships between your models using Sequelize’s association methods (e.g., hasOne, belongsTo, belongsToMany). This allows you to model complex data relationships.

  4. CRUD Operations: Sequelize provides methods for performing CRUD operations on your data using the model:

    • create() - Insert a new row into the table.
    • findAll() or findOne() - Retrieve rows based on criteria (queries).
    • update() or save() - Update an existing row.
    • destroy() - Delete a row.

Example (Simple Sequelize Usage):

const Sequelize = require("sequelize");

// Connect to MySQL database
const sequelize = new Sequelize(
  "your_database_name",
  "your_username",
  "your_password",
  {
    dialect: "mysql",
    host: "localhost",
  }
);

// Define a model for the 'users' table
const User = sequelize.define("User", {
  name: Sequelize.STRING,
  email: Sequelize.STRING,
});

// Create the table in the database (if it doesn't exist)
sequelize
  .sync()
  .then(() => console.log("Models synced successfully!"))
  .catch((err) => console.error("Error syncing models:", err));

// Create a new user
User.create({ name: "John Doe", email: "john.doe@example.com" })
  .then((user) => console.log("User created:", user))
  .catch((err) => console.error("Error creating user:", err));

// Find all users
User.findAll()
  .then((users) => console.log("Users:", users))
  .catch((err) => console.error("Error fetching users:", err));

Additional Features:

Sequelize is a powerful tool for working with MySQL databases in Node.js applications. It offers a structured and efficient way to manage your data and interact with your MySQL database.

Here are some additional things to consider:

Differences between ORM and ODM

Both Object-Relational Mapper (ORM) and Object Document Mapper (ODM) are tools that bridge the gap between your programming language objects and database storage. They provide a way to interact with databases using a more object-oriented approach, simplifying data access and manipulation in your application. However, they differ in their approach based on the underlying database structure:

1. Relational vs. Document Databases:

2. ORM vs. ODM: Key Differences:

Here’s a table summarizing the key differences between ORMs and ODMs:

FeatureORMODM
Database TypeRelational databases (tables, rows, columns)Document databases (flexible JSON-like documents)
Data ModelingModels represent database tables and their relationshipsModels represent document structures
Data SchemaMore rigid schema enforcementFlexible schema, data can vary within a collection
SQL GenerationAutomatic SQL generation based on model operationsNo direct SQL generation, uses database-specific queries
RelationshipsExplicitly defined using association methods (e.g., one-to-many)Implicitly defined within documents (e.g., references)

3. Choosing Between ORM and ODM:

Here’s an analogy:

In conclusion, both ORMs and ODMs are valuable tools for working with databases in your Node.js applications. The best choice depends on the type of database you’re using and the specific needs of your project.

Database Migrations and Seeders

Database Migrations and Seeders in Node.js

In Node.js applications that interact with databases, managing schema changes and initial data population can be crucial tasks. Here’s an overview of database migrations and seeders in Node.js:

1. Database Migrations:

Benefits of Migrations:

2. Sequelize Migrations (Example):

Sequelize, a popular ORM for Node.js with relational databases, provides built-in support for migrations. Here’s a simplified example:

3. Database Seeders:

Benefits of Seeders:

4. Sequelize Seeders (Example):

Sequelize also provides support for seeders. Here’s a simplified example:

5. Important Considerations:

In conclusion, database migrations and seeders are essential tools for managing schema changes and initial data population in your Node.js applications. They promote better code organization, maintainability, and a smoother development workflow.

Database migration and seeding example

Example: Migrations and Seeders with Sequelize

Here’s an example using Sequelize to demonstrate both migrations and seeders:

1. Project Setup:

npm init -y
npm install sequelize

2. Database Connection (config.js):

Create a config.js file to store your database connection details:

module.exports = {
  development: {
    username: "your_username",
    password: "your_password",
    database: "your_database_name",
    dialect: "mysql", // Replace with your dialect (e.g., 'mysql', 'postgres')
    host: "localhost",
  },
  test: {
    username: "your_test_username",
    password: "your_test_password",
    database: "your_test_database_name",
    dialect: "mysql",
    host: "localhost",
  },
};

3. User Model (models/user.js):

Create a user.js file to define your User model:

const DataTypes = require("sequelize");
const sequelize = require("../config").development; // Replace with your environment

const User = sequelize.define("User", {
  name: DataTypes.STRING,
  email: DataTypes.STRING,
});

module.exports = User;

4. Migration Example (migrations/20240409-add-user-email.js):

Create a migration file named 20240409-add-user-email.js (following a date-based naming convention):

const DataTypes = require("sequelize");
const sequelize = require("../config").development; // Replace with your environment

module.exports = {
  up: async (queryInterface, DataTypes) => {
    await queryInterface.addColumn("Users", "email", {
      type: DataTypes.STRING,
      allowNull: false,
    });
  },
  down: async (queryInterface, DataTypes) => {
    await queryInterface.removeColumn("Users", "email");
  },
};

This migration adds an email column to the Users table. The up function executes the migration, and the down function allows you to revert the changes if needed.

5. Seeder Example (seeders/users.js):

Create a seeder file named users.js:

const User = require("../models/user");

const users = [
  { name: "John Doe", email: "john.doe@example.com" },
  { name: "Jane Doe", email: "jane.doe@example.com" },
];

const seedUsers = async () => {
  for (const user of users) {
    await User.create(user);
  }
};

seedUsers()
  .then(() => console.log("Users seeded successfully!"))
  .catch((err) => console.error("Error seeding users:", err));

module.exports = seedUsers;

This seeder defines some sample users data and then loops through it, creating each user in the database using the User.create method.

6. Running Migrations and Seeders:

Remember:

This example demonstrates a basic implementation of migrations and seeders using Sequelize. You can extend this concept to manage more complex schema changes and data seeding needs in your Node.js applications.

Conclusion

Today we learned about DB, but only at a superficial level which is to complement what we learned in previous posts, while MongoDB as a non-relational DB is easier to digest, SQL or relational DB is a whole other roadmap in itself we need to tackle another day. For now we can rely on ORM to translate our requirements to the relational DB SQL as instructions.

See you on the next post.

Sincerely,

Eng. Adrian Beria