09 - Database Integration in APIs

• nodejs
• by Sn0wAlice

Introduction

Integrating databases into APIs allows us to store, retrieve, and manipulate data efficiently. In this chapter, we will cover how to connect Express.js APIs to both SQL (MySQL, PostgreSQL) and NoSQL (MongoDB) databases.

---

1. Choosing a Database

Relational Databases (SQL): MySQL, PostgreSQL, SQLite.

Non-Relational Databases (NoSQL): MongoDB, Firebase, Redis.

---

2. Connecting an API to MySQL with Sequelize

Install dependencies: npm install sequelize mysql2

Create a connection:

const { Sequelize, DataTypes } = require("sequelize");
const sequelize = new Sequelize("testdb", "root", "password", { dialect: "mysql" });

sequelize.authenticate().then(() => console.log("Connected to MySQL"));

Define a model:

const User = sequelize.define("User", {
    name: { type: DataTypes.STRING, allowNull: false },
    email: { type: DataTypes.STRING, unique: true }
});

Sync database and create tables:

sequelize.sync().then(() => console.log("Database synced"));

Create, read, update, and delete (CRUD) operations:

// Create User
app.post("/users", async (req, res) => {
    const user = await User.create(req.body);
    res.json(user);
});

// Get Users
app.get("/users", async (req, res) => {
    const users = await User.findAll();
    res.json(users);
});

---

3. Connecting an API to MongoDB with Mongoose

Install dependencies: npm install mongoose

Create a connection:

const mongoose = require("mongoose");

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

Define a schema and model:

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

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

Perform CRUD operations:

// Create User
app.post("/users", async (req, res) => {
    const user = new User(req.body);
    await user.save();
    res.json(user);
});

// Get Users
app.get("/users", async (req, res) => {
    const users = await User.find();
    res.json(users);
});

---

4. Best Practices for Database Integration

• Use environment variables for database credentials.
• Validate user input before inserting into the database.
• Use indexes to optimize query performance.
• Handle database connection errors gracefully.
• Implement pagination for large data retrieval.

---

Exercises

• Connect an Express API to a MySQL database and perform CRUD operations.
• Connect an Express API to a MongoDB database and perform CRUD operations.
• Implement pagination for a /users route.
• Handle validation errors when inserting user data.
• Secure database credentials using environment variables.

---

Conclusion

This chapter covered integrating databases into Express.js APIs using Sequelize for SQL and Mongoose for NoSQL. In the next chapter, we will explore deploying Node.js applications.

---