Chapter 13: Sequelize ORM

Talk to databases using JavaScript instead of SQL — Sequelize translates your code into database queries automatically.

What is an ORM?

Database Setup

Models

DataTypes & Validations

Migrations

Quiz

What is an ORM?

Imagine you want to talk to someone who speaks a different language. You could learn their language (SQL), or you could use a translator (ORM). An ORM (Object-Relational Mapping) lets you talk to the database using JavaScript instead of writing raw SQL queries. Sequelize is that translator for Node.js.

Here's the key idea: a database table = a JavaScript class (model)

Think of a database as an Excel spreadsheet:

Database = the entire Excel file
Table = one sheet/tab (e.g., "Users", "Tasks")
Column = a header in that sheet (name, email, age)
Row = one entry/record in that sheet
Model = a cookie cutter that defines the shape of each row

SQL vs Sequelize

Comparison — SQL vs Sequelize

// ===== RAW SQL =====
// You'd have to write this by hand:

SELECT * FROM Users;
SELECT * FROM Users WHERE id = 42;
INSERT INTO Users (name, email) VALUES ('Rahul', 'rahul@example.com');
UPDATE Users SET email = 'new@email.com' WHERE id = 42;
DELETE FROM Users WHERE id = 42;


// ===== SEQUELIZE (JavaScript) =====
// Same operations, but in JavaScript:

await User.findAll();                              // Get all users
await User.findByPk(42);                           // Find by ID
await User.create({ name: 'Rahul', email: 'rahul@example.com' });
await User.update({ email: 'new@email.com' }, { where: { id: 42 } });
await User.destroy({ where: { id: 42 } });

// Benefits of Sequelize:
// 1. Write JavaScript, not SQL
// 2. Works with PostgreSQL, MySQL, SQLite (same code!)
// 3. Prevents SQL injection attacks automatically
// 4. Validates data before saving
// 5. Handles relationships between tables

Database Setup

Step 1: Install Packages

Terminal — Install Sequelize

# Install Sequelize and the database driver
npm install sequelize pg pg-hstore

# pg = PostgreSQL driver
# pg-hstore = handles PostgreSQL hstore data type

# For MySQL, use: npm install sequelize mysql2
# For SQLite, use: npm install sequelize sqlite3

Step 2: Create Database Connection

config/database.js — Database Connection

const { Sequelize } = require('sequelize');
require('dotenv').config();

// Create connection using environment variables
const sequelize = new Sequelize(
  process.env.DB_NAME,     // database name
  process.env.DB_USER,     // username
  process.env.DB_PASSWORD, // password
  {
    host: process.env.DB_HOST || 'localhost',
    dialect: 'postgres',    // or 'mysql', 'sqlite'
    port: process.env.DB_PORT || 5432,
    logging: false,         // Set to console.log to see SQL queries
  }
);

// Test the connection
async function testConnection() {
  try {
    await sequelize.authenticate();
    console.log('Database connected successfully!');
  } catch (err) {
    console.error('Database connection failed:', err.message);
  }
}

module.exports = { sequelize, testConnection };

Step 3: Environment Variables

.env — Environment Variables

DB_NAME=task_manager
DB_USER=postgres
DB_PASSWORD=your_password_here
DB_HOST=localhost
DB_PORT=5432
PORT=5000

NEVER commit your .env file! It contains passwords and secrets. Always add .env to your .gitignore file. Share a .env.example file instead (with fake values).

Models

A model is like a cookie cutter. It defines the shape of your data — what fields a user should have, what type each field is, and what rules they must follow. Every user you create is a "cookie" made from this cutter.

Defining a User Model

models/User.js — User Model

const { DataTypes } = require('sequelize');
const { sequelize } = require('../config/database');

const User = sequelize.define('User', {
  // id is created automatically by Sequelize!

  name: {
    type: DataTypes.STRING,      // Text (VARCHAR)
    allowNull: false,            // Required field
    validate: {
      notEmpty: true,            // Can't be empty string
      len: [2, 100],             // Between 2 and 100 characters
    },
  },

  email: {
    type: DataTypes.STRING,
    allowNull: false,
    unique: true,                // No two users can have same email
    validate: {
      isEmail: true,             // Must be valid email format
    },
  },

  password: {
    type: DataTypes.STRING,
    allowNull: false,
    validate: {
      len: [6, 255],             // At least 6 characters
    },
  },

  role: {
    type: DataTypes.ENUM('user', 'admin'),
    defaultValue: 'user',        // If not specified, defaults to 'user'
  },

}, {
  tableName: 'users',           // Explicit table name (lowercase, plural)
  timestamps: true,             // Adds createdAt and updatedAt automatically
});

module.exports = User;

Task Model with Associations

models/Task.js — Task Model with Association

const { DataTypes } = require('sequelize');
const { sequelize } = require('../config/database');

const Task = sequelize.define('Task', {
  title: {
    type: DataTypes.STRING,
    allowNull: false,
    validate: { notEmpty: true },
  },

  description: {
    type: DataTypes.TEXT,        // Longer text (no length limit)
    allowNull: true,             // Optional field
  },

  priority: {
    type: DataTypes.ENUM('low', 'medium', 'high'),
    defaultValue: 'medium',
  },

  completed: {
    type: DataTypes.BOOLEAN,
    defaultValue: false,
  },

  dueDate: {
    type: DataTypes.DATEONLY,     // Just date, no time
    allowNull: true,
  },

  // userId will be added automatically by the association below
}, {
  tableName: 'tasks',
  timestamps: true,
});

module.exports = Task;


// ===== In models/index.js — Define Relationships =====
const User = require('./User');
const Task = require('./Task');

// One User has many Tasks
User.hasMany(Task, { foreignKey: 'userId', as: 'tasks' });
// Each Task belongs to one User
Task.belongsTo(User, { foreignKey: 'userId', as: 'user' });

Associations explained: User.hasMany(Task) means one user can have multiple tasks. Task.belongsTo(User) means each task is owned by exactly one user. Sequelize automatically adds a userId column to the tasks table.

DataTypes & Validations

Common DataTypes

Sequelize Type	SQL Equivalent	What It Stores	Example
`STRING`	VARCHAR(255)	Short text	Name, email
`STRING(1000)`	VARCHAR(1000)	Custom length text	Bio, address
`TEXT`	TEXT	Unlimited text	Blog post, description
`INTEGER`	INTEGER	Whole numbers	Age, quantity
`FLOAT`	FLOAT	Decimal numbers	Price, rating
`BOOLEAN`	BOOLEAN	true/false	isActive, completed
`DATE`	TIMESTAMP	Date + time	createdAt
`DATEONLY`	DATE	Just date	Birthday, dueDate
`ENUM`	ENUM	One of set values	'low', 'medium', 'high'
`JSON`	JSON	JSON data	Settings, metadata

Built-in Validations

Sequelize — Validation Examples

const Product = sequelize.define('Product', {
  name: {
    type: DataTypes.STRING,
    allowNull: false,
    validate: {
      notEmpty: true,                    // Can't be ''
      len: [2, 200],                     // 2-200 characters
    },
  },
  email: {
    type: DataTypes.STRING,
    validate: {
      isEmail: true,                     // Must be valid email
    },
  },
  price: {
    type: DataTypes.FLOAT,
    validate: {
      isFloat: true,                     // Must be a number
      min: 0,                            // Can't be negative
      max: 99999,                        // Maximum value
    },
  },
  website: {
    type: DataTypes.STRING,
    validate: {
      isUrl: true,                       // Must be valid URL
    },
  },
  status: {
    type: DataTypes.ENUM('draft', 'published', 'archived'),
    defaultValue: 'draft',
  },
});

// If validation fails, Sequelize throws a ValidationError
// Your error handler should catch it and return a 400 status

Migrations

Migrations are like a version history for your database. Just like Git tracks changes to your code, migrations track changes to your database structure. Need to add a new column? Create a migration. Need to rename a table? Migration. If something goes wrong, you can roll back to a previous version.

Setting Up Sequelize CLI

Terminal — Sequelize CLI Commands

# Install the CLI tool
npm install --save-dev sequelize-cli

# Initialize Sequelize (creates config, models, migrations, seeders folders)
npx sequelize-cli init

# Create a migration
npx sequelize-cli migration:generate --name create-users-table

# Run all pending migrations
npx sequelize-cli db:migrate

# Undo the last migration
npx sequelize-cli db:migrate:undo

# Undo all migrations
npx sequelize-cli db:migrate:undo:all

Migration File Example

migrations/20260327-create-users-table.js

'use strict';

module.exports = {
  // UP = what to do when running the migration
  async up(queryInterface, Sequelize) {
    await queryInterface.createTable('users', {
      id: {
        type: Sequelize.INTEGER,
        primaryKey: true,
        autoIncrement: true,
        allowNull: false,
      },
      name: {
        type: Sequelize.STRING,
        allowNull: false,
      },
      email: {
        type: Sequelize.STRING,
        allowNull: false,
        unique: true,
      },
      password: {
        type: Sequelize.STRING,
        allowNull: false,
      },
      role: {
        type: Sequelize.ENUM('user', 'admin'),
        defaultValue: 'user',
      },
      createdAt: {
        type: Sequelize.DATE,
        allowNull: false,
      },
      updatedAt: {
        type: Sequelize.DATE,
        allowNull: false,
      },
    });
  },

  // DOWN = how to undo this migration (rollback)
  async down(queryInterface, Sequelize) {
    await queryInterface.dropTable('users');
  },
};

Never edit a migration that's already been run! If you need to change the database structure, create a NEW migration. Old migrations are history — like Git commits, you don't go back and edit them.

📝 Chapter 13 Quiz

1. What does ORM stand for?

Online Resource Manager

Object Request Model

Object-Relational Mapping

Ordered Record Method

2. Which DataType would you use for a user's name?

DataTypes.STRING

DataTypes.TEXT

DataTypes.INTEGER

DataTypes.BOOLEAN

3. What are migrations used for?

Moving data between servers

Tracking and applying database structure changes

Migrating from JavaScript to TypeScript

Backing up the database

4. What does unique: true do on a model field?

Makes the field required

Encrypts the field value

Makes the field read-only

Prevents duplicate values in that column

5. What does User.hasMany(Task) mean?

One user can have multiple tasks

Users and tasks are the same table

Tasks can have multiple users

Users are stored in the tasks table

← Chapter 12: Express.js Chapter 14: REST API with Sequelize →