Sequelize Samples
Sequelize Node.js ORM with model definitions, associations, migrations, queries, and database operations
Key Facts
- Category
- Database
- Items
- 4
- Format Families
- sql, text
Sample Overview
Sequelize Node.js ORM with model definitions, associations, migrations, queries, and database operations This sample set belongs to Database and can be used to test related workflows inside Elysia Tools.
💻 Sequelize Model Definition javascript
🟢 simple
Database models, relationships, and schema definitions with Sequelize
const { Sequelize, DataTypes, Model, Op } = require('sequelize')
// Initialize Sequelize connection
const sequelize = new Sequelize({
dialect: 'postgres',
host: 'localhost',
port: 5432,
database: 'myapp',
username: 'postgres',
password: 'password',
logging: console.log,
pool: {
max: 5,
min: 0,
acquire: 30000,
idle: 10000
}
})
// User Model
class User extends Model {}
User.init({
id: {
type: DataTypes.INTEGER,
primaryKey: true,
autoIncrement: true
},
email: {
type: DataTypes.STRING,
allowNull: false,
unique: true,
validate: {
isEmail: true
}
},
username: {
type: DataTypes.STRING,
allowNull: true,
unique: true,
validate: {
len: [3, 30]
}
},
password: {
type: DataTypes.STRING,
allowNull: false,
validate: {
len: [6, 100]
}
},
firstName: {
type: DataTypes.STRING,
allowNull: true
},
lastName: {
type: DataTypes.STRING,
allowNull: true
},
isActive: {
type: DataTypes.BOOLEAN,
defaultValue: true
},
role: {
type: DataTypes.ENUM('USER', 'ADMIN', 'MODERATOR', 'AUTHOR'),
defaultValue: 'USER'
},
lastLoginAt: {
type: DataTypes.DATE,
allowNull: true
},
profilePicture: {
type: DataTypes.STRING,
allowNull: true
}
}, {
sequelize,
modelName: 'User',
tableName: 'users',
timestamps: true,
indexes: [
{
unique: true,
fields: ['email']
},
{
fields: ['username']
},
{
fields: ['isActive']
}
]
})
// Profile Model
class Profile extends Model {}
Profile.init({
id: {
type: DataTypes.INTEGER,
primaryKey: true,
autoIncrement: true
},
userId: {
type: DataTypes.INTEGER,
allowNull: false,
unique: true,
references: {
model: User,
key: 'id'
}
},
bio: {
type: DataTypes.TEXT,
allowNull: true
},
avatar: {
type: DataTypes.STRING,
allowNull: true
},
website: {
type: DataTypes.STRING,
allowNull: true,
validate: {
isUrl: true
}
},
birthDate: {
type: DataTypes.DATEONLY,
allowNull: true
},
phone: {
type: DataTypes.STRING,
allowNull: true
},
address: {
type: DataTypes.JSON,
allowNull: true
}
}, {
sequelize,
modelName: 'Profile',
tableName: 'profiles'
})
// Post Model
class Post extends Model {}
Post.init({
id: {
type: DataTypes.INTEGER,
primaryKey: true,
autoIncrement: true
},
title: {
type: DataTypes.STRING,
allowNull: false,
validate: {
len: [1, 200]
}
},
slug: {
type: DataTypes.STRING,
allowNull: false,
unique: true
},
content: {
type: DataTypes.TEXT,
allowNull: false
},
excerpt: {
type: DataTypes.STRING,
allowNull: true
},
publishedAt: {
type: DataTypes.DATE,
allowNull: true
},
status: {
type: DataTypes.ENUM('DRAFT', 'PUBLISHED', 'ARCHIVED'),
defaultValue: 'DRAFT'
},
featuredImage: {
type: DataTypes.STRING,
allowNull: true
},
tags: {
type: DataTypes.JSON,
allowNull: true,
defaultValue: []
},
viewCount: {
type: DataTypes.INTEGER,
defaultValue: 0
},
metadata: {
type: DataTypes.JSON,
allowNull: true
}
}, {
sequelize,
modelName: 'Post',
tableName: 'posts',
indexes: [
{
fields: ['authorId']
},
{
fields: ['status']
},
{
fields: ['publishedAt']
},
{
fields: ['slug']
}
]
})
// Comment Model
class Comment extends Model {}
Comment.init({
id: {
type: DataTypes.INTEGER,
primaryKey: true,
autoIncrement: true
},
content: {
type: DataTypes.TEXT,
allowNull: false,
validate: {
len: [1, 1000]
}
},
postId: {
type: DataTypes.INTEGER,
allowNull: false,
references: {
model: Post,
key: 'id'
}
},
authorId: {
type: DataTypes.INTEGER,
allowNull: false,
references: {
model: User,
key: 'id'
}
},
parentId: {
type: DataTypes.INTEGER,
allowNull: true,
references: {
model: 'comments',
key: 'id'
}
},
isApproved: {
type: DataTypes.BOOLEAN,
defaultValue: false
}
}, {
sequelize,
modelName: 'Comment',
tableName: 'comments',
indexes: [
{
fields: ['postId']
},
{
fields: ['authorId']
},
{
fields: ['parentId']
}
]
})
// Category Model
class Category extends Model {}
Category.init({
id: {
type: DataTypes.INTEGER,
primaryKey: true,
autoIncrement: true
},
name: {
type: DataTypes.STRING,
allowNull: false,
unique: true
},
description: {
type: DataTypes.TEXT,
allowNull: true
},
slug: {
type: DataTypes.STRING,
allowNull: false,
unique: true
},
color: {
type: DataTypes.STRING,
allowNull: true
},
icon: {
type: DataTypes.STRING,
allowNull: true
}
}, {
sequelize,
modelName: 'Category',
tableName: 'categories'
})
// Define Associations (Relationships)
User.hasOne(Profile, {
foreignKey: 'userId',
as: 'profile'
})
Profile.belongsTo(User, {
foreignKey: 'userId',
as: 'user'
})
User.hasMany(Post, {
foreignKey: 'authorId',
as: 'posts'
})
Post.belongsTo(User, {
foreignKey: 'authorId',
as: 'author'
})
User.hasMany(Comment, {
foreignKey: 'authorId',
as: 'comments'
})
Comment.belongsTo(User, {
foreignKey: 'authorId',
as: 'author'
})
Post.hasMany(Comment, {
foreignKey: 'postId',
as: 'comments'
})
Comment.belongsTo(Post, {
foreignKey: 'postId',
as: 'post'
})
// Self-referencing relationship for nested comments
Comment.hasMany(Comment, {
foreignKey: 'parentId',
as: 'replies'
})
Comment.belongsTo(Comment, {
foreignKey: 'parentId',
as: 'parent'
})
// Many-to-Many: Posts and Categories
const PostCategory = sequelize.define('PostCategory', {
postId: {
type: DataTypes.INTEGER,
references: {
model: Post,
key: 'id'
}
},
categoryId: {
type: DataTypes.INTEGER,
references: {
model: Category,
key: 'id'
}
}
}, {
tableName: 'post_categories',
timestamps: false
})
Post.belongsToMany(Category, {
through: PostCategory,
foreignKey: 'postId',
otherKey: 'categoryId',
as: 'categories'
})
Category.belongsToMany(Post, {
through: PostCategory,
foreignKey: 'categoryId',
otherKey: 'postId',
as: 'posts'
})
// Hooks and Instance Methods
User.beforeCreate(async (user) => {
// Hash password before creating user
const bcrypt = require('bcrypt')
user.password = await bcrypt.hash(user.password, 10)
})
User.prototype.comparePassword = async function(password) {
const bcrypt = require('bcrypt')
return await bcrypt.compare(password, this.password)
}
User.prototype.toJSON = function() {
const values = Object.assign({}, this.get())
delete values.password
return values
}
Post.beforeSave(async (post) => {
// Generate excerpt if not provided
if (!post.excerpt && post.content) {
post.excerpt = post.content.substring(0, 150) + '...'
}
// Generate slug if not provided
if (!post.slug && post.title) {
post.slug = post.title.toLowerCase()
.replace(/[^a-z0-9]+/g, '-')
.replace(/(^-|-$)/g, '')
}
})
module.exports = {
sequelize,
User,
Profile,
Post,
Comment,
Category,
PostCategory
}💻 CRUD Operations javascript
🟢 simple
Complete CRUD operations, queries, and data manipulation with Sequelize
const { User, Profile, Post, Comment } = require('./models')
// CREATE Operations
async function createUser(userData) {
try {
const user = await User.create({
email: userData.email,
username: userData.username,
password: userData.password,
firstName: userData.firstName,
lastName: userData.lastName,
role: userData.role || 'USER'
})
return user
} catch (error) {
console.error('Error creating user:', error)
throw error
}
}
// Create user with profile
async function createUserWithProfile(userData) {
const user = await User.create(userData, {
include: [{
association: User.Profile,
as: 'profile'
}]
})
return user
}
// Create multiple users
async function createMultipleUsers(usersData) {
const users = await User.bulkCreate(usersData, {
validate: true,
individualHooks: true
})
return users
}
// READ Operations
async function getUserById(id) {
const user = await User.findByPk(id, {
include: [
{
model: Profile,
as: 'profile'
},
{
model: Post,
as: 'posts',
include: [
{
model: Comment,
as: 'comments'
}
]
}
]
})
return user
}
async function getUserByEmail(email) {
const user = await User.findOne({
where: { email },
attributes: ['id', 'email', 'username', 'firstName', 'lastName', 'role', 'createdAt']
})
return user
}
async function getAllUsers(options = {}) {
const {
page = 1,
limit = 10,
search,
role,
sortBy = 'createdAt',
sortOrder = 'DESC'
} = options
const offset = (page - 1) * limit
const where = {
[Op.and]: []
}
if (search) {
where[Op.and].push({
[Op.or]: [
{ username: { [Op.like]: `%${search}%` } },
{ email: { [Op.like]: `%${search}%` } },
{ firstName: { [Op.like]: `%${search}%` } },
{ lastName: { [Op.like]: `%${search}%` } }
]
})
}
if (role) {
where[Op.and].push({ role })
}
where[Op.and].push({ isActive: true })
const { count, rows } = await User.findAndCountAll({
where,
offset,
limit,
include: [
{
model: Profile,
as: 'profile',
attributes: ['bio', 'avatar', 'website']
}
],
order: [[sortBy, sortOrder.toUpperCase()]]
})
return {
users: rows,
pagination: {
page,
limit,
total: count,
pages: Math.ceil(count / limit)
}
}
}
// UPDATE Operations
async function updateUser(id, updateData) {
const user = await User.update(updateData, {
where: { id },
returning: true,
include: [{
model: Profile,
as: 'profile'
}]
})
return user[1][0] // Return the updated user
}
async function updateOrCreateProfile(userId, profileData) {
const [profile, created] = await Profile.findOrCreate({
where: { userId },
defaults: {
userId,
...profileData
}
})
if (!created) {
await profile.update(profileData)
}
return profile
}
// DELETE Operations
async function deleteUser(id) {
// Soft delete
const user = await User.update(
{ isActive: false },
{ where: { id } }
)
return user
}
async function hardDeleteUser(id) {
const user = await User.destroy({
where: { id }
})
return user
}
// Complex Queries
async function getUserWithPostStats(userId) {
const user = await User.findByPk(userId, {
attributes: [
'id', 'email', 'username', 'firstName', 'lastName', 'role'
],
include: [
{
model: Post,
as: 'posts',
attributes: [
'id', 'title', 'publishedAt', 'viewCount', 'status'
],
include: [
{
model: Comment,
as: 'comments',
attributes: ['id'],
required: false
}
]
}
]
})
// Add post counts
const userWithStats = user.toJSON()
userWithStats.stats = {
totalPosts: user.posts.length,
publishedPosts: user.posts.filter(p => p.status === 'PUBLISHED').length,
totalComments: user.posts.reduce((sum, post) => sum + post.comments.length, 0),
totalViews: user.posts.reduce((sum, post) => sum + post.viewCount, 0)
}
return userWithStats
}
// Advanced Search
async function searchPosts(query) {
const posts = await Post.findAll({
where: {
[Op.and]: [
{
[Op.or]: [
{ title: { [Op.like]: `%${query}%` } },
{ content: { [Op.like]: `%${query}%` } },
{ excerpt: { [Op.like]: `%${query}%` } }
]
},
{ status: 'PUBLISHED' },
{ publishedAt: { [Op.not]: null } }
]
},
include: [
{
model: User,
as: 'author',
attributes: ['id', 'username', 'firstName', 'lastName']
},
{
model: Comment,
as: 'comments',
attributes: ['id'],
required: false
}
],
order: [['publishedAt', 'DESC']],
limit: 20
})
return posts
}
// Aggregation Queries
async function getPostAnalytics() {
const analytics = await Post.findAll({
attributes: [
[sequelize.fn('COUNT', sequelize.col('id')), 'totalPosts'],
[sequelize.fn('COUNT', sequelize.col('id')), 'publishedPosts'],
[sequelize.fn('AVG', sequelize.col('viewCount')), 'avgViews'],
[sequelize.fn('SUM', sequelize.col('viewCount')), 'totalViews']
],
where: {
[Op.or]: [
{ status: 'PUBLISHED' },
{ status: 'DRAFT' }
]
},
raw: true
})
return analytics[0]
}
// Raw SQL Queries
async function getUsersWithMostPosts() {
const users = await sequelize.query(`
SELECT
u.id,
u.username,
u.email,
COUNT(p.id) as post_count,
COUNT(c.id) as comment_count
FROM users u
LEFT JOIN posts p ON u.id = p.author_id
LEFT JOIN comments c ON u.id = c.author_id
WHERE u.is_active = true
GROUP BY u.id, u.username, u.email
HAVING COUNT(p.id) > 0
ORDER BY post_count DESC
LIMIT 10
`, {
type: Sequelize.QueryTypes.SELECT
})
return users
}
// Transaction Operations
async function transferPostOwnership(postId, newAuthorId) {
const result = await sequelize.transaction(async (t) => {
const post = await Post.findByPk(postId, {
include: [{
model: User,
as: 'author'
}],
transaction: t
})
if (!post) {
throw new Error('Post not found')
}
const updatedPost = await post.update({
authorId: newAuthorId
}, { transaction: t })
return {
previousAuthor: post.author,
updatedPost
}
})
return result
}
// Bulk Operations
async function archiveOldPosts(daysOld = 365) {
const cutoffDate = new Date()
cutoffDate.setDate(cutoffDate.getDate() - daysOld)
const [affectedCount] = await Post.update(
{
status: 'ARCHIVED',
archivedAt: new Date()
},
{
where: {
publishedAt: {
[Op.lt]: cutoffDate
},
status: 'PUBLISHED'
}
}
)
return affectedCount
}
module.exports = {
createUser,
createUserWithProfile,
createMultipleUsers,
getUserById,
getUserByEmail,
getAllUsers,
updateUser,
updateOrCreateProfile,
deleteUser,
hardDeleteUser,
getUserWithPostStats,
searchPosts,
getPostAnalytics,
getUsersWithMostPosts,
transferPostOwnership,
archiveOldPosts
}💻 Sequelize Migrations text
🟡 intermediate
Database migrations, seeding, and version control for schema changes
// Migration Example: 20231201000001-create-users.js
module.exports = {
up: async (queryInterface, Sequelize) => {
await queryInterface.createTable('users', {
id: {
type: Sequelize.INTEGER,
primaryKey: true,
autoIncrement: true
},
email: {
type: Sequelize.STRING,
allowNull: false,
unique: true
},
username: {
type: Sequelize.STRING,
allowNull: true,
unique: true
},
password: {
type: Sequelize.STRING,
allowNull: false
},
firstName: {
type: Sequelize.STRING,
allowNull: true
},
lastName: {
type: Sequelize.STRING,
allowNull: true
},
isActive: {
type: Sequelize.BOOLEAN,
defaultValue: true
},
role: {
type: Sequelize.ENUM('USER', 'ADMIN', 'MODERATOR', 'AUTHOR'),
defaultValue: 'USER'
},
lastLoginAt: {
type: Sequelize.DATE,
allowNull: true
},
createdAt: {
type: Sequelize.DATE,
allowNull: false,
defaultValue: Sequelize.literal('CURRENT_TIMESTAMP')
},
updatedAt: {
type: Sequelize.DATE,
allowNull: false,
defaultValue: Sequelize.literal('CURRENT_TIMESTAMP')
}
})
// Create indexes
await queryInterface.addIndex('users', ['email'], { unique: true })
await queryInterface.addIndex('users', ['username'])
await queryInterface.addIndex('users', ['isActive'])
},
down: async (queryInterface, Sequelize) => {
await queryInterface.dropTable('users')
}
}
// Migration Example: 20231201000002-create-profiles.js
module.exports = {
up: async (queryInterface, Sequelize) => {
await queryInterface.createTable('profiles', {
id: {
type: Sequelize.INTEGER,
primaryKey: true,
autoIncrement: true
},
userId: {
type: Sequelize.INTEGER,
allowNull: false,
unique: true,
references: {
model: 'users',
key: 'id'
},
onUpdate: 'CASCADE',
onDelete: 'CASCADE'
},
bio: {
type: Sequelize.TEXT,
allowNull: true
},
avatar: {
type: Sequelize.STRING,
allowNull: true
},
website: {
type: Sequelize.STRING,
allowNull: true
},
birthDate: {
type: Sequelize.DATEONLY,
allowNull: true
},
phone: {
type: Sequelize.STRING,
allowNull: true
},
address: {
type: Sequelize.JSON,
allowNull: true
},
createdAt: {
type: Sequelize.DATE,
allowNull: false,
defaultValue: Sequelize.literal('CURRENT_TIMESTAMP')
},
updatedAt: {
type: Sequelize.DATE,
allowNull: false,
defaultValue: Sequelize.literal('CURRENT_TIMESTAMP')
}
})
await queryInterface.addIndex('profiles', ['userId'], { unique: true })
},
down: async (queryInterface, Sequelize) => {
await queryInterface.dropTable('profiles')
}
}
// Migration Example: 20231201000003-add-user-profile-field.js
module.exports = {
up: async (queryInterface, Sequelize) => {
await queryInterface.addColumn('users', 'profilePicture', {
type: Sequelize.STRING,
allowNull: true
})
},
down: async (queryInterface, Sequelize) => {
await queryInterface.removeColumn('users', 'profilePicture')
}
}
// Seeders Example: 20231201000001-users-seeder.js
module.exports = {
up: async (queryInterface, Sequelize) => {
const bcrypt = require('bcrypt')
const users = [
{
email: '[email protected]',
username: 'admin',
password: await bcrypt.hash('admin123', 10),
firstName: 'Admin',
lastName: 'User',
role: 'ADMIN',
isActive: true,
createdAt: new Date(),
updatedAt: new Date()
},
{
email: '[email protected]',
username: 'john_doe',
password: await bcrypt.hash('password123', 10),
firstName: 'John',
lastName: 'Doe',
role: 'USER',
isActive: true,
createdAt: new Date(),
updatedAt: new Date()
},
{
email: '[email protected]',
username: 'jane_smith',
password: await bcrypt.hash('password123', 10),
firstName: 'Jane',
lastName: 'Smith',
role: 'AUTHOR',
isActive: true,
createdAt: new Date(),
updatedAt: new Date()
}
]
await queryInterface.bulkInsert('users', users)
},
down: async (queryInterface, Sequelize) => {
await queryInterface.bulkDelete('users', {
email: {
[Sequelize.Op.in]: [
'[email protected]',
'[email protected]',
'[email protected]'
]
}
})
}
}
// Database Configuration
const { Sequelize } = require('sequelize')
const sequelize = new Sequelize({
dialect: 'postgres',
host: process.env.DB_HOST || 'localhost',
port: process.env.DB_PORT || 5432,
database: process.env.DB_NAME || 'myapp',
username: process.env.DB_USER || 'postgres',
password: process.env.DB_PASSWORD || 'password',
logging: process.env.NODE_ENV === 'development' ? console.log : false,
pool: {
max: parseInt(process.env.DB_POOL_MAX) || 5,
min: parseInt(process.env.DB_POOL_MIN) || 0,
acquire: parseInt(process.env.DB_POOL_ACQUIRE) || 30000,
idle: parseInt(process.env.DB_POOL_IDLE) || 10000
}
})
module.exports = sequelize💻 Sequelize Advanced Features javascript
🔴 complex
Advanced Sequelize features including transactions, scopes, hooks, and performance optimization
const { Sequelize, DataTypes, Model, Op } = require('sequelize')
// 1. Advanced Scopes
class User extends Model {
// Instance methods
getFullName() {
return `${this.firstName} ${this.lastName}`
}
async getRecentPosts(limit = 5) {
return await this.getPosts({
where: {
status: 'PUBLISHED',
publishedAt: {
[Op.not]: null
}
},
order: [['publishedAt', 'DESC']],
limit
})
}
async deactivate() {
return await this.update({ isActive: false })
}
}
User.init({
// ... fields
}, {
sequelize,
modelName: 'User',
// Scopes for reusable queries
scopes: {
active: {
where: { isActive: true }
},
admins: {
where: { role: 'ADMIN' }
},
recent: (days = 30) => ({
where: {
createdAt: {
[Op.gte]: new Date(Date.now() - days * 24 * 60 * 60 * 1000)
}
}
}),
withProfile: {
include: [{
model: Profile,
as: 'profile'
}]
},
withStats: {
attributes: {
include: [
[
sequelize.literal(`(
SELECT COUNT(*)
FROM posts
WHERE posts.author_id = User.id
AND posts.status = 'PUBLISHED'
)`),
'publishedPostCount'
],
[
sequelize.literal(`(
SELECT COUNT(*)
FROM comments
WHERE comments.author_id = User.id
)`),
'commentCount'
]
]
}
}
}
})
// Using scopes
async function getActiveAdminsWithStats() {
const users = await User.scope([
'active',
'admins',
'withProfile',
'withStats'
]).findAll()
return users
}
async function getRecentUsers(days = 7) {
return await User.scope('recent', days).findAll({
order: [['createdAt', 'DESC']]
})
}
// 2. Advanced Hooks and Middleware
User.addHook('beforeCreate', async (user) => {
console.log('Creating user:', user.email)
// Additional validation
if (user.email && user.email.endsWith('@spam.com')) {
throw new Error('Spam emails not allowed')
}
})
User.addHook('afterCreate', async (user) => {
// Send welcome email
await sendWelcomeEmail(user.email, user.getFullName())
})
User.addHook('beforeUpdate', async (user) => {
if (user.changed('password')) {
// Rehash password if changed
const bcrypt = require('bcrypt')
user.password = await bcrypt.hash(user.password, 10)
}
})
User.addHook('afterDestroy', async (user) => {
// Soft delete related data
await Post.update(
{ authorId: null },
{ where: { authorId: user.id } }
)
})
// 3. Advanced Transactions
async function complexTransaction() {
const result = await sequelize.transaction({
isolationLevel: Sequelize.Transaction.ISOLATION_LEVELS.SERIALIZABLE,
type: Sequelize.Transaction.TYPES.DEFERRED
}, async (t) => {
// Create user
const user = await User.create({
email: '[email protected]',
password: 'password123'
}, { transaction: t })
// Create profile
const profile = await Profile.create({
userId: user.id,
bio: 'New user bio'
}, { transaction: t })
// Create initial post
const post = await Post.create({
title: 'Welcome Post',
content: 'My first post',
authorId: user.id,
status: 'PUBLISHED'
}, { transaction: t })
// If any operation fails, all changes are rolled back
return { user, profile, post }
})
return result
}
// Parallel transactions
async function processBatchUsers(userDataArray) {
const promises = userDataArray.map(userData =>
sequelize.transaction(async (t) => {
const user = await User.create(userData, { transaction: t })
await Profile.create({
userId: user.id,
bio: `Bio for ${user.email}`
}, { transaction: t })
return user
})
)
const results = await Promise.all(promises)
return results
}
// 4. Performance Optimization
// Eager loading with nested associations
async function getPostsWithFullDetails(postIds) {
const posts = await Post.findAll({
where: {
id: { [Op.in]: postIds }
},
include: [
{
model: User,
as: 'author',
attributes: ['id', 'username', 'firstName', 'lastName'],
include: [{
model: Profile,
as: 'profile',
attributes: ['avatar', 'bio']
}]
},
{
model: Comment,
as: 'comments',
include: [{
model: User,
as: 'author',
attributes: ['username', 'firstName']
}],
where: { isApproved: true },
required: false
},
{
model: Category,
as: 'categories',
attributes: ['id', 'name', 'slug']
}
],
order: [
['publishedAt', 'DESC'],
[{ model: Comment, as: 'comments' }, 'createdAt', 'ASC']
]
})
return posts
}
// Query optimization with limit and pagination
async function getOptimizedUserList(options = {}) {
const { page = 1, limit = 20, search, sortBy = 'createdAt' } = options
const offset = (page - 1) * limit
const where = {
isActive: true
}
if (search) {
where[Op.or] = [
{ username: { [Op.iLike]: `%${search}%` } },
{ email: { [Op.iLike]: `%${search}%` } },
[sequelize.fn('CONCAT', sequelize.col('firstName'), ' ', sequelize.col('lastName')),
{ [Op.iLike]: `%${search}%` }]
]
}
const { count, rows } = await User.findAndCountAll({
where,
offset,
limit,
attributes: ['id', 'username', 'email', 'firstName', 'lastName', 'role', 'createdAt'],
order: [[sortBy, 'DESC']],
include: [
{
model: Profile,
as: 'profile',
attributes: ['avatar'],
required: false
}
]
})
return {
users: rows,
pagination: {
page,
limit,
total: count,
pages: Math.ceil(count / limit)
}
}
}
// 5. Raw Query Optimization
async function getDashboardStats() {
const stats = await sequelize.query(`
SELECT
(SELECT COUNT(*) FROM users WHERE is_active = true) as total_users,
(SELECT COUNT(*) FROM posts WHERE status = 'PUBLISHED') as published_posts,
(SELECT COUNT(*) FROM comments WHERE is_approved = true) as approved_comments,
(SELECT COUNT(*) FROM categories) as total_categories,
(SELECT AVG(view_count) FROM posts WHERE status = 'PUBLISHED') as avg_post_views,
(SELECT
COUNT(CASE WHEN status = 'PUBLISHED' THEN 1 END) * 100.0 / COUNT(*)
FROM posts
WHERE published_at >= NOW() - INTERVAL '30 days'
) as recent_publish_rate
`, {
type: Sequelize.QueryTypes.SELECT,
plain: true
})
return stats
}
// 6. Caching Layer
class CacheService {
constructor(ttl = 300) { // 5 minutes default TTL
this.cache = new Map()
this.ttl = ttl * 1000
}
set(key, value) {
this.cache.set(key, {
value,
timestamp: Date.now()
})
}
get(key) {
const item = this.cache.get(key)
if (!item) return null
if (Date.now() - item.timestamp > this.ttl) {
this.cache.delete(key)
return null
}
return item.value
}
has(key) {
return this.get(key) !== null
}
delete(key) {
return this.cache.delete(key)
}
}
const cache = new CacheService()
async function getCachedUserWithPosts(userId) {
const cacheKey = `user_with_posts_${userId}`
let cached = cache.get(cacheKey)
if (cached) return cached
const user = await User.findByPk(userId, {
include: [
{
model: Post,
as: 'posts',
where: { status: 'PUBLISHED' },
required: false,
order: [['publishedAt', 'DESC']]
}
]
})
cache.set(cacheKey, user)
return user
}
// 7. Connection Pool Management
async function getConnectionPoolStats() {
const pool = sequelize.connectionManager.pool
return {
total: pool.total,
all: pool.all,
waiting: pool.waiting,
idle: pool.idle,
used: pool.used,
max: pool.max,
min: pool.min
}
}
// 8. Error Handling and Validation
class ValidationError extends Error {
constructor(message, field) {
super(message)
this.name = 'ValidationError'
this.field = field
}
}
async function createUserWithValidation(userData) {
try {
// Custom validation
if (userData.email && userData.email.endsWith('@blocked.com')) {
throw new ValidationError('Email domain is blocked', 'email')
}
if (userData.username && userData.username.length < 3) {
throw new ValidationError('Username must be at least 3 characters', 'username')
}
const user = await User.create(userData)
return user
} catch (error) {
if (error.name === 'SequelizeUniqueConstraintError') {
const field = error.errors[0]?.path
throw new ValidationError(`${field} already exists`, field)
}
if (error.name === 'SequelizeValidationError') {
const field = error.errors[0]?.path
const message = error.errors[0]?.message
throw new ValidationError(message, field)
}
throw error
}
}
module.exports = {
User,
getActiveAdminsWithStats,
getRecentUsers,
complexTransaction,
processBatchUsers,
getPostsWithFullDetails,
getOptimizedUserList,
getDashboardStats,
getCachedUserWithPosts,
getConnectionPoolStats,
createUserWithValidation,
ValidationError,
cache
}