Working with Databases — Node.js & Express | Sabaoon Academy

Most web applications need to store data persistently. In this lesson, you will learn how to connect an Express application to both SQL and NoSQL databases and perform create, read, update, and delete (CRUD) operations.

Choosing a Database

Type	Examples	Best For
SQL	PostgreSQL, MySQL	Structured data, complex queries, relationships
NoSQL	MongoDB, Redis	Flexible schemas, rapid prototyping
In-memory	Redis, SQLite	Caching, sessions, quick prototypes

For production applications, PostgreSQL is an excellent default choice. MongoDB is popular for projects where schema flexibility matters.

SQLite for Development

SQLite is a file-based database perfect for development and prototyping. Install the better-sqlite3 driver:

npm install better-sqlite3

Ctrl+Enter

HTML

CSS

Preview

Integrating with Express

Create a data access layer that your routes can use:

Ctrl+Enter

HTML

CSS

Preview

Ctrl+Enter

HTML

CSS

// src/routes/users.js
const express = require("express");
const router = express.Router();
const db = require("../db");

// GET /api/users
router.get("/", (req, res) => {
const { limit = 20, offset = 0 } = req.query;
const users = db
  .prepare("SELECT * FROM users LIMIT ? OFFSET ?")
  .all(parseInt(limit), parseInt(offset));

const total = db.prepare("SELECT COUNT(*) as count FROM users").get();

res.json({
  users,
  total: total.count,
  limit: parseInt(limit),
  offset: parseInt(offset),
});
});

// GET /api/users/:id
router.get("/:id", (req, res) => {
const user = db
  .prepare("SELECT * FROM users WHERE id = ?")
  .get(req.params.id);

if (!user) {
  return res.status(404).json({ error: "User not found" });
}

res.json(user);
});

// POST /api/users
router.post("/", (req, res) => {
const { name, email } = req.body;

if (!name || !email) {
  return res.status(400).json({ error: "Name and email are required" });
}

try {
  const result = db
    .prepare("INSERT INTO users (name, email) VALUES (?, ?)")
    .run(name, email);

const user = db
    .prepare("SELECT * FROM users WHERE id = ?")
    .get(result.lastInsertRowid);

res.status(201).json(user);
} catch (error) {
  if (error.code === "SQLITE_CONSTRAINT_UNIQUE") {
    return res.status(409).json({ error: "Email already exists" });
  }
  throw error;
}
});

// PUT /api/users/:id
router.put("/:id", (req, res) => {
const { name, email } = req.body;
const result = db
  .prepare("UPDATE users SET name = ?, email = ? WHERE id = ?")
  .run(name, email, req.params.id);

if (result.changes === 0) {
  return res.status(404).json({ error: "User not found" });
}

const user = db
  .prepare("SELECT * FROM users WHERE id = ?")
  .get(req.params.id);

res.json(user);
});

// DELETE /api/users/:id
router.delete("/:id", (req, res) => {
const result = db
  .prepare("DELETE FROM users WHERE id = ?")
  .run(req.params.id);

if (result.changes === 0) {
  return res.status(404).json({ error: "User not found" });
}

res.status(204).end();
});

module.exports = router;

Preview

MongoDB with Mongoose

For NoSQL, MongoDB with Mongoose is a popular combination:

npm install mongoose

Connecting

Ctrl+Enter

HTML

CSS

Preview

Defining a Schema

Ctrl+Enter

HTML

CSS

Preview

CRUD with Mongoose

Ctrl+Enter

HTML

CSS

Preview

Database Transactions

Transactions ensure that multiple operations either all succeed or all fail:

Ctrl+Enter

HTML

CSS

Preview

Database Migrations

As your schema evolves, use migrations to track changes:

Ctrl+Enter

HTML

CSS

Preview

For production, use a migration tool like knex, prisma migrate, or sequelize-cli.

Practical Exercise

Build a complete bookstore API with database integration:

Ctrl+Enter

HTML

CSS

Preview

Key Takeaways

Choose SQL for structured data with relationships; choose NoSQL for flexible schemas.
SQLite is excellent for development and prototyping — no server setup required.
Always validate input before writing to the database and handle constraint errors gracefully.
Use transactions when multiple operations must succeed or fail together.
Separate your database logic from your route handlers for cleaner, testable code.