Sequelize ORM Setup for Web Application

Sequelize ORM Setup for Web Application

Sequelize is a mature ORM for Node.js, supporting PostgreSQL, MySQL, MariaDB, SQLite, and MSSQL. We install version 6.x: it brought a complete transition to promises and improved typescript types compared to the fifth branch.

npm install sequelize pg pg-hstore
# or for MySQL
npm install sequelize mysql2

Connection Initialization

Connection is best organized as a singleton shared between application modules. Create src/db/sequelize.ts:

import { Sequelize } from 'sequelize';

const sequelize = new Sequelize(process.env.DATABASE_URL!, {
  dialect: 'postgres',
  dialectOptions: {
    ssl: process.env.NODE_ENV === 'production'
      ? { require: true, rejectUnauthorized: false }
      : false,
  },
  pool: {
    max: 10,
    min: 2,
    acquire: 30000,
    idle: 10000,
  },
  logging: process.env.NODE_ENV !== 'production' ? console.log : false,
  define: {
    underscored: true,
    timestamps: true,
  },
});

export default sequelize;

The underscored: true parameter automatically converts camelCase field names to snake_case columns. This is important: without it, Sequelize will create createdAt instead of created_at.

Model Definition

Sequelize 6 supports two model declaration styles — class-based and object. Class-based is preferable for TypeScript:

import {
  Model, DataTypes, InferAttributes,
  InferCreationAttributes, CreationOptional,
} from 'sequelize';
import sequelize from '../db/sequelize';

class User extends Model<
  InferAttributes<User>,
  InferCreationAttributes<User>
> {
  declare id: CreationOptional<number>;
  declare email: string;
  declare passwordHash: string;
  declare role: 'admin' | 'editor' | 'viewer';
  declare createdAt: CreationOptional<Date>;
  declare updatedAt: CreationOptional<Date>;
}

User.init({
  id: {
    type: DataTypes.INTEGER,
    primaryKey: true,
    autoIncrement: true,
  },
  email: {
    type: DataTypes.STRING(320),
    allowNull: false,
    unique: true,
    validate: { isEmail: true },
  },
  passwordHash: {
    type: DataTypes.STRING(255),
    allowNull: false,
  },
  role: {
    type: DataTypes.ENUM('admin', 'editor', 'viewer'),
    defaultValue: 'viewer',
  },
}, {
  sequelize,
  tableName: 'users',
  modelName: 'User',
});

export default User;

Associations

Declare relations after defining all models, in a separate file src/db/associations.ts:

import User from '../models/User';
import Post from '../models/Post';
import Comment from '../models/Comment';
import Tag from '../models/Tag';

User.hasMany(Post, { foreignKey: 'authorId', as: 'posts' });
Post.belongsTo(User, { foreignKey: 'authorId', as: 'author' });

Post.hasMany(Comment, { foreignKey: 'postId', as: 'comments' });
Comment.belongsTo(Post, { foreignKey: 'postId', as: 'post' });

Post.belongsToMany(Tag, {
  through: 'post_tags',
  foreignKey: 'postId',
  otherKey: 'tagId',
  as: 'tags',
});
Tag.belongsToMany(Post, {
  through: 'post_tags',
  foreignKey: 'tagId',
  otherKey: 'postId',
  as: 'posts',
});

Call the function from this file once at application startup, before any database requests.

Queries with Eager Loading

A common mistake is loading related data with N+1 queries. In Sequelize, use include:

const posts = await Post.findAll({
  where: { status: 'published' },
  include: [
    {
      model: User,
      as: 'author',
      attributes: ['id', 'email'],
    },
    {
      model: Tag,
      as: 'tags',
      through: { attributes: [] }, // hide junction table fields
    },
  ],
  order: [['createdAt', 'DESC']],
  limit: 20,
  offset: 0,
});

Transactions

For operations affecting multiple tables, always use transactions:

import sequelize from '../db/sequelize';

async function createPostWithTags(
  data: { title: string; body: string; tagIds: number[] },
  authorId: number,
) {
  return sequelize.transaction(async (t) => {
    const post = await Post.create(
      { title: data.title, body: data.body, authorId, status: 'draft' },
      { transaction: t },
    );

    if (data.tagIds.length > 0) {
      await post.setTags(data.tagIds, { transaction: t });
    }

    return post;
  });
}

On exception inside the callback, the transaction rolls back automatically.

Migrations via sequelize-cli

For managing database schema in CI/CD, use sequelize-cli:

npm install --save-dev sequelize-cli
npx sequelize-cli init

Create .sequelizerc config:

const path = require('path');
module.exports = {
  config: path.resolve('src/db', 'config.json'),
  'models-path': path.resolve('src', 'models'),
  'seeders-path': path.resolve('src/db', 'seeders'),
  'migrations-path': path.resolve('src/db', 'migrations'),
};

Create a migration:

npx sequelize-cli migration:generate --name create-users

// src/db/migrations/20240315120000-create-users.js
'use strict';

module.exports = {
  up: async (queryInterface, Sequelize) => {
    await queryInterface.createTable('users', {
      id: {
        type: Sequelize.INTEGER,
        primaryKey: true,
        autoIncrement: true,
      },
      email: {
        type: Sequelize.STRING(320),
        allowNull: false,
        unique: true,
      },
      password_hash: {
        type: Sequelize.STRING(255),
        allowNull: false,
      },
      role: {
        type: Sequelize.ENUM('admin', 'editor', 'viewer'),
        defaultValue: 'viewer',
      },
      created_at: { type: Sequelize.DATE, allowNull: false },
      updated_at: { type: Sequelize.DATE, allowNull: false },
    });

    await queryInterface.addIndex('users', ['email']);
  },

  down: async (queryInterface) => {
    await queryInterface.dropTable('users');
  },
};

Hooks and Validation

Sequelize supports lifecycle hooks. For example, hashing password before saving:

import bcrypt from 'bcrypt';

User.addHook('beforeCreate', async (user: User) => {
  if (user.passwordHash) {
    user.passwordHash = await bcrypt.hash(user.passwordHash, 12);
  }
});

User.addHook('beforeUpdate', async (user: User) => {
  if (user.changed('passwordHash')) {
    user.passwordHash = await bcrypt.hash(user.passwordHash, 12);
  }
});

Timeline and Scope

Setting up Sequelize for a new project from scratch: 1–2 days. Includes database connection, basic set of models, associations, migrations, seed data, and connection tests. If the project already has a database and needs reverse engineering (generating models from existing schema) — add 1 more day for sequelize-auto and manual type refinement.