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

const Database = require("better-sqlite3");
const db = new Database("app.db");

// Create a table
db.exec(`
  CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    email TEXT UNIQUE NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
  )
`);

// Insert
const insert = db.prepare("INSERT INTO users (name, email) VALUES (?, ?)");
const result = insert.run("Alice", "alice@example.com");
console.log("Inserted ID:", result.lastInsertRowid);

// Select all
const users = db.prepare("SELECT * FROM users").all();
console.log(users);

// Select one
const user = db.prepare("SELECT * FROM users WHERE id = ?").get(1);
console.log(user);

// Update
db.prepare("UPDATE users SET name = ? WHERE id = ?").run("Alice Updated", 1);

// Delete
db.prepare("DELETE FROM users WHERE id = ?").run(1);

Integrating with Express

Create a data access layer that your routes can use:

// src/db.js
const Database = require("better-sqlite3");
const path = require("path");

const db = new Database(path.join(__dirname, "..", "app.db"));

// Enable WAL mode for better concurrent read performance
db.pragma("journal_mode = WAL");

module.exports = db;

// 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;

MongoDB with Mongoose

For NoSQL, MongoDB with Mongoose is a popular combination:

npm install mongoose

Connecting

// src/db.js
const mongoose = require("mongoose");

async function connectDB() {
  try {
    await mongoose.connect(process.env.MONGODB_URI || "mongodb://localhost:27017/myapp");
    console.log("Connected to MongoDB");
  } catch (error) {
    console.error("MongoDB connection error:", error);
    process.exit(1);
  }
}

module.exports = connectDB;

Defining a Schema

// src/models/User.js
const mongoose = require("mongoose");

const userSchema = new mongoose.Schema(
  {
    name: { type: String, required: true, trim: true },
    email: { type: String, required: true, unique: true, lowercase: true },
    role: { type: String, enum: ["user", "admin"], default: "user" },
    isActive: { type: Boolean, default: true },
  },
  { timestamps: true }
);

module.exports = mongoose.model("User", userSchema);

CRUD with Mongoose

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

// Create
const user = await User.create({ name: "Alice", email: "alice@example.com" });

// Read
const allUsers = await User.find({ isActive: true }).sort({ name: 1 });
const oneUser = await User.findById(userId);
const byEmail = await User.findOne({ email: "alice@example.com" });

// Update
const updated = await User.findByIdAndUpdate(
  userId,
  { name: "Alice Updated" },
  { new: true, runValidators: true }
);

// Delete
await User.findByIdAndDelete(userId);

Database Transactions

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

// SQLite transaction
const createUserWithProfile = db.transaction((userData, profileData) => {
  const userResult = db
    .prepare("INSERT INTO users (name, email) VALUES (?, ?)")
    .run(userData.name, userData.email);

  db.prepare("INSERT INTO profiles (user_id, bio) VALUES (?, ?)").run(
    userResult.lastInsertRowid,
    profileData.bio
  );

  return userResult.lastInsertRowid;
});

// Either both inserts succeed or neither does
const userId = createUserWithProfile(
  { name: "Bob", email: "bob@example.com" },
  { bio: "Hello world" }
);

Database Migrations

As your schema evolves, use migrations to track changes:

// migrations/001-create-users.js
module.exports = {
  up(db) {
    db.exec(`
      CREATE TABLE users (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        email TEXT UNIQUE NOT NULL,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP
      )
    `);
  },
  down(db) {
    db.exec("DROP TABLE IF EXISTS users");
  },
};

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

Practical Exercise

Build a complete bookstore API with database integration:

// Initialize the books table
db.exec(`
  CREATE TABLE IF NOT EXISTS books (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT NOT NULL,
    author TEXT NOT NULL,
    isbn TEXT UNIQUE,
    price REAL NOT NULL,
    stock INTEGER DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
  )
`);

// Seed some data
const seed = db.transaction(() => {
  const insert = db.prepare(
    "INSERT OR IGNORE INTO books (title, author, isbn, price, stock) VALUES (?, ?, ?, ?, ?)"
  );
  insert.run("The Pragmatic Programmer", "David Thomas", "978-0135957059", 49.99, 10);
  insert.run("Clean Code", "Robert C. Martin", "978-0132350884", 39.99, 15);
  insert.run("JavaScript: The Good Parts", "Douglas Crockford", "978-0596517748", 29.99, 8);
});

seed();

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.