Relational Databases with Express and Sequelize

Hands-on course: building a complete RESTful API (bookstore) with Express, Sequelize ORM, and a SQLite database.

Table of Contents

1. Course Overview
2. Application Architecture
3. Module 1 — Working with Sequelize Models
   • 3.1 Set up and Configure Sequelize with SQLite
   • 3.2 Creating a Model
   • 3.3 CRUD Operations with Author Model
   • 3.4 Validating Model Data
4. Module 2 — Working with Model Relationships and Queries
   • 4.1 Establishing Relationships Between Models
   • 4.2 Using Relationships to Retrieve Associated Data
   • 4.3 Searching and Filtering with WHERE and Op
5. Reference Diagrams
   • 5.1 ORM Architecture
   • 5.2 CRUD Flow
   • 5.3 Relationships Between Models
   • 5.4 Lifecycle — sync and seed
6. Quick Reference Tables
   • 6.1 Sequelize DataTypes
   • 6.2 CRUD Methods
   • 6.3 Available Associations
   • 6.4 Query Operators (Op)
7. Complete Code Snippets
8. Best Practices and Common Pitfalls

1. Course Overview

This course uses Express, Sequelize (ORM), and SQLite to build a functional bookstore API. The goal is to understand how an ORM abstracts SQL queries and allows interaction with a relational database in pure JavaScript.

Technology Stack

Project Structure

sequelize-bookstore/
├── app.js          ← Express server + global error handler
├── db.js           ← Sequelize / SQLite connection
├── models.js       ← Model definitions + associations
├── appRouter.js    ← Express routes (CRUD)
├── seedDatabase.js ← Initial database population
├── package.json
└── database.sqlite ← Auto-generated DB file

package.json

{
  "name": "sequelize-bookstore",
  "type": "module",
  "version": "1.0.0",
  "scripts": {
    "dev": "node --watch app.js"
  },
  "dependencies": {
    "express": "^4.21.1",
    "sequelize": "^6.37.5",
    "sqlite3": "^5.1.7"
  }
}

"type": "module" enables import/export ES Modules syntax in Node.js.
node --watch automatically restarts the server on every file change.

2. Application Architecture

graph TB
    Client["Client (REST / RapidAPI)"]
    Express["Express Server\n(app.js)"]
    Router["Express Router\n(appRouter.js)"]
    Sequelize["Sequelize ORM\n(models.js)"]
    DB["SQLite Database\n(database.sqlite)"]

    Client -->|HTTP Request| Express
    Express -->|Routes /api/*| Router
    Router -->|Model.create / findAll / ...| Sequelize
    Sequelize -->|Auto-generated SQL| DB
    DB -->|Result| Sequelize
    Sequelize -->|JavaScript Object| Router
    Router -->|JSON Response| Client

    style Client fill:#4A90D9,color:#fff
    style Express fill:#68A063,color:#fff
    style Router fill:#68A063,color:#fff
    style Sequelize fill:#F5A623,color:#fff
    style DB fill:#7B68EE,color:#fff

3. Module 1 — Working with Sequelize Models

3.1 Set up and Configure Sequelize with SQLite

db.js — Database Connection

import { Sequelize } from 'sequelize';

const sequelize = new Sequelize({
  dialect: 'sqlite',
  storage: './database.sqlite'  // SQLite file created automatically
});

(async () => {
  try {
    await sequelize.authenticate();
    console.log('Connection has been established successfully.');
  } catch (error) {
    console.error('Unable to connect to the database:', error);
  }
})();

export default sequelize;

Key points:

• dialect: 'sqlite' — Sequelize also supports postgres, mysql, mssql, mariadb
• storage — path to the SQLite file (created if absent)
• sequelize.authenticate() — verifies the connection is operational

app.js — Server Startup

import express from 'express';
import appRouter from './appRouter.js';
import sequelize from './db.js';
import { Sequelize } from 'sequelize';

const app = express();
app.use(express.json());          // parse incoming JSON body
app.use('/api', appRouter);       // prefix all routes with /api

// ── Global Error Handler ──────────────────────────────────────
app.use((err, req, res, next) => {
  console.error(err.stack);

  if (err instanceof Sequelize.ValidationError) {
    return res.status(400).json({
      error: {
        message: "Validation error",
        details: err.errors.map(e => e.message)
      }
    });
  }

  if (err instanceof Sequelize.UniqueConstraintError) {
    return res.status(400).json({
      error: {
        message: "A record with this unique value already exists.",
        details: err.errors.map(e => e.message)
      }
    });
  }

  if (err.status === 404) {
    return res.status(404).json({
      error: { message: "Resource not found", details: err.message || null }
    });
  }

  res.status(err.status || 500).json({
    error: {
      message: err.message || 'Internal Server Error',
      details: err.details || null
    }
  });
});

// ── sync() then listen() ──────────────────────────────────────
sequelize.sync({ force: false }).then(() => {
  app.listen(3000, () => console.log('Server is running on port 3000'));
}).catch(error => console.error("Unable to start the server:", error));

sequelize.sync() — important options:

3.2 Creating a Model

A Sequelize model represents a table in the database. It defines:

• The columns (fields) and their data type
• Constraints (allowNull, unique, etc.)
• Business validations

import { DataTypes } from 'sequelize';
import sequelize from './db.js';

const Author = sequelize.define('Author', {
  name: {
    type: DataTypes.STRING,
    allowNull: false,
    validate: {
      notEmpty: { msg: "Name must not be empty" }
    }
  },
  email: {
    type: DataTypes.STRING,
    allowNull: false,
    unique: { msg: "This email address already exists." },
    validate: {
      isEmail: { msg: "Invalid email address." },
      notEmpty: { msg: "Email must not be blank." }
    }
  }
}, {
  timestamps: false   // disables automatic createdAt / updatedAt
});

export { Author };

What Sequelize automatically generates:

• An id column (INTEGER PRIMARY KEY AUTOINCREMENT)
• createdAt and updatedAt columns (if timestamps: true)
• The corresponding SQL table during sequelize.sync()

3.3 CRUD Operations with Author Model

appRouter.js — Complete CRUD Routes

import express from 'express';
import { Author } from './models.js';

const router = express.Router();

// ── CREATE ────────────────────────────────────────────────────
router.post('/authors', async (req, res, next) => {
  try {
    const newAuthor = await Author.create(req.body);
    res.status(201).json(newAuthor);
  } catch (error) {
    next(error);
  }
});

// ── READ ALL ──────────────────────────────────────────────────
router.get('/authors', async (req, res, next) => {
  try {
    const authors = await Author.findAll();
    res.status(200).json(authors);
  } catch (error) {
    next(error);
  }
});

// ── READ ONE ──────────────────────────────────────────────────
router.get('/authors/:id', async (req, res, next) => {
  try {
    const author = await Author.findByPk(req.params.id);
    if (!author) {
      return res.status(404).json({ error: 'Author not found' });
    }
    res.status(200).json(author);
  } catch (error) {
    next(error);
  }
});

// ── UPDATE ────────────────────────────────────────────────────
router.put('/authors/:id', async (req, res, next) => {
  try {
    const author = await Author.findByPk(req.params.id);
    if (!author) {
      return res.status(404).json({ error: 'Author not found' });
    }
    const { name, email } = req.body;
    await author.update({ name, email });
    res.status(200).json(author);
  } catch (error) {
    next(error);
  }
});

// ── DELETE ────────────────────────────────────────────────────
router.delete('/authors/:id', async (req, res, next) => {
  try {
    const author = await Author.findByPk(req.params.id);
    if (!author) {
      return res.status(404).json({ error: 'Author not found' });
    }
    await author.destroy();
    res.status(204).send();
  } catch (error) {
    next(error);
  }
});

export default router;

Flow of a POST /api/authors request:

1. The client sends { "name": "Jane", "email": "jane@example.com" } as JSON
2. express.json() parses the body and places it in req.body
3. Author.create(req.body) inserts the row in the database and returns the created object
4. Express responds with 201 Created + the author as JSON
5. In case of validation error, Sequelize throws ValidationError → caught by next(error) → global error handler → 400 Bad Request

3.4 Validating Model Data

Sequelize offers two levels of control:

Most commonly used built-in validators:

validate: {
  notEmpty: true,              // forbids empty strings ""
  isEmail: true,               // validates email format
  isInt: true,                 // validates an integer
  isFloat: true,               // validates a decimal number
  isUrl: true,                 // validates a URL
  len: [2, 100],               // length between 2 and 100 characters
  min: 0,                      // minimum value (for numbers)
  max: 9999,                   // maximum value
  isIn: [['admin', 'user']],   // value in a list
  // Custom message:
  notEmpty: { msg: "Field cannot be empty" }
}

Handling in the error handler:

if (err instanceof Sequelize.ValidationError) {
  return res.status(400).json({
    error: {
      message: "Validation error",
      details: err.errors.map(e => e.message)
      // e.g.: ["Name must not be empty", "Invalid email address."]
    }
  });
}

4. Module 2 — Working with Model Relationships and Queries

4.1 Establishing Relationships Between Models

Relational databases organize data in separate, linked tables. Sequelize provides three types of associations.

The Three Types of Relationships

models.js — All Models and Associations

import { DataTypes } from 'sequelize';
import sequelize from './db.js';

// ── Author ────────────────────────────────────────────────────
const Author = sequelize.define('Author', {
  name: {
    type: DataTypes.STRING,
    allowNull: false,
    validate: { notEmpty: { msg: "Name must not be empty" } }
  },
  email: {
    type: DataTypes.STRING,
    allowNull: false,
    unique: { msg: "This email address already exists." },
    validate: {
      isEmail: { msg: "Invalid email address." },
      notEmpty: { msg: "Email must not be blank." }
    }
  }
}, { timestamps: false });

// ── Profile ───────────────────────────────────────────────────
const Profile = sequelize.define('Profile', {
  photo: {
    type: DataTypes.STRING,   // URL or file path
    allowNull: true
  },
  biography: {
    type: DataTypes.TEXT,     // long text
    allowNull: true
  }
}, { timestamps: false });

// ── Book ──────────────────────────────────────────────────────
const Book = sequelize.define('Book', {
  title: {
    type: DataTypes.STRING,
    allowNull: false,
    validate: { notEmpty: { msg: "Title must not be empty." } }
  },
  publicationYear: {
    type: DataTypes.INTEGER,
    allowNull: true,
    validate: { isInt: { msg: "Publication year must be a valid integer." } }
  }
}, { timestamps: false });

// ── Shelf ─────────────────────────────────────────────────────
const Shelf = sequelize.define('Shelf', {
  name: {
    type: DataTypes.STRING,
    allowNull: false,
    validate: { notEmpty: { msg: "Shelf name must not be empty." } }
  }
}, { timestamps: false });

// ═══════════════════════════════════════════════════════════════
// ASSOCIATIONS
// ═══════════════════════════════════════════════════════════════

// One-to-One: Author → Profile
Author.hasOne(Profile, {
  foreignKey: 'authorId',
  as: 'profile',
  onDelete: 'CASCADE'     // deletes the profile if the author is deleted
});
Profile.belongsTo(Author, {
  foreignKey: 'authorId',
  as: 'profile'
});

// One-to-Many: Author → Books
Author.hasMany(Book, {
  foreignKey: 'authorId',
  as: 'books',
  onDelete: 'CASCADE'
});
Book.belongsTo(Author, {
  foreignKey: 'authorId',
  as: 'author'
});

// Many-to-Many: Books ↔ Shelves (BookShelves junction table)
Book.belongsToMany(Shelf, {
  through: 'BookShelves',   // junction table name
  as: 'shelves',
  foreignKey: 'bookId'
});
Shelf.belongsToMany(Book, {
  through: 'BookShelves',
  as: 'books',
  foreignKey: 'shelfId'
});

export { Author, Profile, Book, Shelf };

What Sequelize automatically generates in the database:

• An authorId column in the Profiles and Books tables (foreign key)
• A BookShelves table with bookId and shelfId columns

4.2 Using Relationships to Retrieve Associated Data

The include Option — Eager Loading

// GET /api/authors — returns each author with their profile AND their books
router.get('/authors', async (req, res, next) => {
  try {
    const authors = await Author.findAll({
      include: [
        { model: Profile, as: 'profile' },
        { model: Book,    as: 'books'   }
      ]
    });
    res.status(200).json(authors);
  } catch (error) {
    next(error);
  }
});

Produced JSON response:

[
  {
    "id": 1,
    "name": "John Doe",
    "email": "john@example.com",
    "profile": {
      "id": 1,
      "photo": "https://...",
      "biography": "...",
      "authorId": 1
    },
    "books": [
      { "id": 1, "title": "The Great Adventure", "publicationYear": 2015, "authorId": 1 },
      { "id": 2, "title": "Mystery of the Lost City", "publicationYear": 2010, "authorId": 1 }
    ]
  }
]

seedDatabase.js — Populating the Database

import sequelize from './db.js';
import { Author, Book, Shelf } from './models.js';

const seedDatabase = async () => {
  try {
    // force: true — drops and recreates all tables
    await sequelize.sync({ force: true });

    // Bulk creation with bulkCreate
    const authors = await Author.bulkCreate([
      { name: 'John Doe',       email: 'john@example.com'  },
      { name: 'Jane Smith',     email: 'jane@example.com'  },
      { name: 'Emily Johnson',  email: 'emily@example.com' }
    ]);

    const books = await Book.bulkCreate([
      { title: 'The Great Adventure',       publicationYear: 2015, authorId: authors[0].id },
      { title: 'Mystery of the Lost City',  publicationYear: 2010, authorId: authors[0].id },
      { title: 'Adventures in Coding',      publicationYear: 2020, authorId: authors[1].id },
      { title: 'The Last Frontier',         publicationYear: 2005, authorId: authors[1].id },
      { title: 'Exploring the Cosmos',      publicationYear: 2022, authorId: authors[2].id },
      { title: 'Ancient Civilizations',     publicationYear: 2018, authorId: authors[2].id }
    ]);

    const shelves = await Shelf.bulkCreate([
      { name: 'Fiction'     },
      { name: 'Non-Fiction' },
      { name: 'Adventure'   },
      { name: 'Science'     }
    ]);

    // Association methods auto-generated by belongsToMany
    await books[0].addShelves([shelves[0], shelves[2]]);  // Fiction + Adventure
    await books[1].addShelves([shelves[2]]);              // Adventure
    await books[2].addShelves([shelves[3]]);              // Science
    await books[3].addShelves([shelves[0], shelves[2]]); // Fiction + Adventure
    await books[4].addShelves([shelves[3]]);              // Science
    await books[5].addShelves([shelves[1], shelves[3]]); // Non-Fiction + Science

    process.exit();
  } catch (error) {
    console.error('Error seeding database:', error);
    process.exit(1);
  }
};

seedDatabase();

Run the seed:

node seedDatabase.js

Important: Remove force: true in app.js after seeding the DB, otherwise data is deleted on every server restart.

4.3 Searching and Filtering with WHERE and Op

Simple WHERE Clause

// All books published in 2020
const books = await Book.findAll({
  where: { publicationYear: 2020 }
});

Query Operators (Op)

import { Sequelize } from 'sequelize';
// or: import { Op } from 'sequelize';

// Partial search in the title (LIKE '%adventure%')
const books = await Book.findAll({
  where: {
    title: { [Sequelize.Op.like]: '%adventure%' }
    // or with Op imported directly: { [Op.like]: '%adventure%' }
  }
});

GET /books Route with Optional Filters

router.get('/books', async (req, res, next) => {
  try {
    const { title, shelf } = req.query;
    // Example call: GET /api/books?title=adventure&shelf=Fiction

    const books = await Book.findAll({
      // WHERE on title (if parameter present)
      where: title
        ? { title: { [Sequelize.Op.like]: `%${title}%` } }
        : undefined,

      include: [
        {
          model: Shelf,
          as: 'shelves',
          attributes: ['name'],            // return only the shelf name
          through: { attributes: [] },     // hide junction table columns
          // WHERE on the relationship (filter by shelf if parameter present)
          where: shelf ? { name: shelf } : undefined
        }
      ]
    });

    res.status(200).json(books);
  } catch (error) {
    next(error);
  }
});

5. Reference Diagrams

5.1 ORM Architecture

graph LR
    subgraph "Node.js Application"
        JS["JavaScript Code\nAuthor.findAll()\nBook.create()"]
        SEQ["Sequelize ORM"]
    end
    subgraph "Database"
        SQL["Generated SQL Queries\nSELECT * FROM Authors\nINSERT INTO Books..."]
        DB[("SQLite\ndatabase.sqlite")]
    end

    JS -->|"method call"| SEQ
    SEQ -->|"translates to SQL"| SQL
    SQL --> DB
    DB -->|"raw result"| SEQ
    SEQ -->|"JavaScript object"| JS

    style JS fill:#68A063,color:#fff
    style SEQ fill:#F5A623,color:#fff
    style SQL fill:#999,color:#fff
    style DB fill:#7B68EE,color:#fff

5.2 CRUD Flow

sequenceDiagram
    participant C as Client
    participant E as Express Router
    participant S as Sequelize Model
    participant D as SQLite DB

    C->>E: POST /api/authors { name, email }
    E->>S: Author.create(req.body)
    S->>D: INSERT INTO Authors (name, email) VALUES (...)
    D-->>S: { id: 1, name: "Jane", email: "jane@..." }
    S-->>E: Author instance
    E-->>C: 201 Created + JSON

    C->>E: GET /api/authors
    E->>S: Author.findAll({ include: [...] })
    S->>D: SELECT * FROM Authors LEFT JOIN ...
    D-->>S: Rows[]
    S-->>E: Author[] with associations
    E-->>C: 200 OK + JSON[]

    C->>E: PUT /api/authors/1 { name: "Updated" }
    E->>S: author.update({ name })
    S->>D: UPDATE Authors SET name=... WHERE id=1
    D-->>S: OK
    S-->>E: Updated Author instance
    E-->>C: 200 OK + JSON

    C->>E: DELETE /api/authors/1
    E->>S: author.destroy()
    S->>D: DELETE FROM Authors WHERE id=1
    D-->>S: OK
    S-->>E: -
    E-->>C: 204 No Content

5.3 Relationships Between Models

erDiagram
    AUTHOR {
        int id PK
        string name
        string email
    }
    PROFILE {
        int id PK
        string photo
        text biography
        int authorId FK
    }
    BOOK {
        int id PK
        string title
        int publicationYear
        int authorId FK
    }
    SHELF {
        int id PK
        string name
    }
    BOOKSHELVES {
        int bookId FK
        int shelfId FK
    }

    AUTHOR ||--o| PROFILE : "hasOne / belongsTo"
    AUTHOR ||--o{ BOOK : "hasMany / belongsTo"
    BOOK }o--o{ SHELF : "belongsToMany (through BookShelves)"
    BOOK ||--o{ BOOKSHELVES : ""
    SHELF ||--o{ BOOKSHELVES : ""

5.4 Lifecycle — sync and seed

flowchart TD
    A["npm run dev\nnode --watch app.js"] --> B["sequelize.authenticate()\nConnection test"]
    B --> C{Connection OK?}
    C -->|No| D["Console error\nShutdown"]
    C -->|Yes| E["sequelize.sync()\nCreate tables if absent"]
    E --> F["app.listen(3000)\nServer ready"]

    G["node seedDatabase.js"] --> H["sequelize.sync({ force: true })\nDrop + Recreate tables"]
    H --> I["Author.bulkCreate([...])"]
    I --> J["Book.bulkCreate([...])"]
    J --> K["Shelf.bulkCreate([...])"]
    K --> L["books[i].addShelves([...])\nMany-to-Many relationships"]
    L --> M["process.exit()"]

    style A fill:#68A063,color:#fff
    style G fill:#4A90D9,color:#fff
    style D fill:#D9534F,color:#fff
    style F fill:#5CB85C,color:#fff
    style M fill:#5CB85C,color:#fff

6. Quick Reference Tables

6.1 Sequelize DataTypes

6.2 CRUD Methods

6.3 Available Associations

Common association options:

Author.hasMany(Book, {
  foreignKey: 'authorId',    // explicit FK name
  as: 'books',               // alias for include: [{ as: 'books' }]
  onDelete: 'CASCADE',       // behavior on delete
  onUpdate: 'CASCADE'        // behavior on update
});

6.4 Query Operators (Op)

import { Op } from 'sequelize';
// or via instance: Sequelize.Op

7. Complete Code Snippets

Advanced Query with include, where and Op

import { Sequelize } from 'sequelize';
const { Op } = Sequelize;

// Books whose title contains "adventure" AND which are on the "Fiction" shelf
const books = await Book.findAll({
  where: {
    title: { [Op.like]: '%adventure%' },
    publicationYear: { [Op.gte]: 2010 }
  },
  include: [
    {
      model: Author,
      as: 'author',
      attributes: ['name', 'email']  // select only certain fields
    },
    {
      model: Shelf,
      as: 'shelves',
      attributes: ['name'],
      through: { attributes: [] },   // hide junction table columns
      where: { name: 'Fiction' }     // filter on the relationship
    }
  ],
  order: [['publicationYear', 'DESC']],  // sorting
  limit: 10,                              // pagination
  offset: 0
});

Using findOrCreate

const [author, created] = await Author.findOrCreate({
  where: { email: 'jane@example.com' },
  defaults: { name: 'Jane Smith' }  // values if creating
});

if (created) {
  console.log('New author created:', author.toJSON());
} else {
  console.log('Existing author found:', author.toJSON());
}

Sequelize Transactions

import sequelize from './db.js';

const t = await sequelize.transaction();
try {
  const author = await Author.create(
    { name: 'New Author', email: 'new@example.com' },
    { transaction: t }
  );
  const book = await Book.create(
    { title: 'New Book', authorId: author.id },
    { transaction: t }
  );
  await t.commit();   // commits both operations
} catch (error) {
  await t.rollback(); // cancels everything if an error occurs
  throw error;
}

Query with count

// Number of books per author
const authors = await Author.findAll({
  include: [
    {
      model: Book,
      as: 'books',
      attributes: []  // don't return book fields
    }
  ],
  attributes: {
    include: [
      [sequelize.fn('COUNT', sequelize.col('books.id')), 'bookCount']
    ]
  },
  group: ['Author.id']
});

Raw SQL Query

// When Sequelize isn't enough, use a direct SQL query
const [results] = await sequelize.query(
  'SELECT * FROM Authors WHERE name LIKE :search',
  {
    replacements: { search: '%John%' },
    type: sequelize.QueryTypes.SELECT
  }
);

Dynamic Association Methods (Many-to-Many)

// Methods available on a Book instance after belongsToMany(Shelf)
await book.getShelves();                     // returns associated shelves
await book.addShelf(shelf);                  // adds a shelf
await book.addShelves([shelf1, shelf2]);     // adds multiple shelves
await book.setShelf([shelf1, shelf2]);       // replaces existing shelves
await book.removeShelf(shelf);              // removes a shelf
await book.hasShelves(shelf);               // checks the association
await book.countShelves();                  // counts the shelves

8. Best Practices and Common Pitfalls

Do

• Always use try/catch + next(error) in async routes to propagate errors to the global handler
• Centralize error handling in a dedicated middleware at the bottom of app.js
• Use as in all associations to avoid ambiguities in include
• Never use force: true in production — it deletes all data
• Use { through: { attributes: [] } } in Many-to-Many include to avoid exposing the junction table
• Validate data on the Sequelize side rather than manually in routes

Common Pitfalls

Difference Between allowNull and validate.notEmpty

// Scenario A: allowNull: false ONLY
// → Accepts: ""  (empty string)
// → Rejects: null

// Scenario B: allowNull: false + validate: { notEmpty: true }
// → Rejects: null
// → Rejects: ""
// → Accepts: "Jane"

name: {
  type: DataTypes.STRING,
  allowNull: false,            // SQL constraint (database level)
  validate: {
    notEmpty: true             // Sequelize validation (before the DB)
  }
}

Recommended Structure for Scale-up

src/
├── config/
│   └── db.js
├── models/
│   ├── Author.js
│   ├── Book.js
│   ├── Shelf.js
│   └── index.js     ← imports and configures all associations
├── routes/
│   ├── authors.js
│   └── books.js
├── middleware/
│   └── errorHandler.js
└── app.js

For larger projects, the Sequelize CLI (sequelize-cli) offers a formal structure with commands to generate models, migrations (schema evolution), and seeders (test data) automatically.

Search Terms

relational · databases · express · sequelize · node.js · apis · microservices · backend · full-stack · web · query · relationships · crud · model · models · between · architecture · associations · data · database · include · methods · operators · pitfalls