Elysia Tools

Explora herramientas, ejemplos, explicaciones visuales y guías temáticas en un solo espacio claro.

Categorías populares

Temas

Idioma
ZHESPTRUFRDEEN

Elysia Tools

Navegación

Bases de datos

Ejemplos de Sequelize

ORM Node.js Sequelize con definiciones de modelos, asociaciones, migraciones, consultas y operaciones de base de datos

Ejemplos

Entradas de esta colección

CódigojavascriptSimple

Definición de Modelo Sequelize

Definición de modelos de base de datos, relaciones y esquemas con Sequelize

javascript
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
}
CódigojavascriptSimple

Operaciones CRUD

Operaciones CRUD completas, consultas y manipulación de datos con Sequelize

javascript
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
}
CódigotextIntermedio

Migraciones Sequelize

Migraciones de base de datos, seeding y control de versiones para cambios de esquema

text
// 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
CódigojavascriptComplejo

Características Avanzadas de Sequelize

Características avanzadas de Sequelize incluyendo transacciones, scopes, hooks y optimización de rendimiento

javascript
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
}

Herramientas

Herramientas que suelen usarse con este ejemplo

Relacionado

Colecciones de ejemplos relacionadas

Relacionado

Temas relacionados

Tema

Revisión e integración de un componente de sorteos autoalojado

Revisa la configuración, las respuestas de ejemplo, la legibilidad y el diseño adaptable antes de entregar los archivos y las pruebas de integración del sorteo.

Tema

Modelos farmacocineticos docentes y revision de resultados

Explora formulas farmacocineticas, supuestos de modelo y coherencia de resultados para docencia o QA, sin dar dosis ni consejo clinico.

Tema

Herramientas de cifrado clásico y moderno

Practica cifrados seguros para educación o CTF comparando transformaciones clásicas reversibles con cifrado moderno y sus límites de seguridad.

Tema

Revisión de una lección Markdown interactiva y entrega sin conexión

Revisa contenido y configuración, ensaya el HTML exportado sin conexión y separa archivos del público de fuentes privadas.

Inicio
Herramientas3211
Ejemplos593
Visualizaciones471
Visores de archivos103
Temas239
Etiquetas333
Imágenes, audio y video657

Herramientas de procesamiento, conversión y optimización de imágenes, audio y video

Matemáticas, fechas y finanzas647

Calculadoras, herramientas numéricas, lógica de fechas, estadística y finanzas

Diseño y color298

Herramientas de color, maquetación, gráficos, estilo visual y apoyo al diseño

Texto y redacción209

Herramientas de redacción, limpieza, formato, extracción y análisis de texto

Desarrollo y Web193

Utilidades para desarrolladores, red, depuración web y automatización

Conversión y codificación175

Herramientas de conversión de formatos, archivos, unidades, archivos comprimidos y codificación

Lanzamiento de un cuestionario de captación y entrega de contactos
Textos conmemorativos: revisión familiar y entrega para impresión
Hábitos privados: copia, restauración y revisión antes de compartir
De artículo a borradores de X y LinkedIn: revisión factual y entrega
Revisión de una lección Markdown interactiva y entrega sin conexión

© 2026 Elysia Tools. Todos los derechos reservados.

DocumentaciónPreciosAcerca deTérminos de servicioPolítica de privacidadCookiesContacto

Development

Visualizador de Plan de Ejecución SQL

Pega la salida de EXPLAIN / EXPLAIN ANALYZE (PostgreSQL/MySQL/SQLite) para renderizar un árbol de costos, marcar divergencias entre filas estimadas y reales, y sugerir índices

Format Conversion

Emisor de INSERT SQL desde CSV

Analiza CSV/Excel-CSV/TSV (detección automática, RFC 4180) y genera lotes INSERT para MySQL/PostgreSQL/SQLite/SQL Server con inferencia de tipos y ON CONFLICT/IGNORE

Development

Diff de Schema de Base de Datos

Compara dos schemas y genera un borrador de migracion

Development

Puente de dialectos SQL

Traduce DDL, DML, funciones y tipos entre MySQL / PostgreSQL / SQLite / BigQuery / Snowflake / Redshift, señalando lo no soportado

Security

Detector de Inyección SQL

Detecta patrones de ataque de inyección SQL comunes en cadenas de entrada

Development

Formateador y Minificador de SQL

Embellece o minifica SQL con control total del dialecto (PostgreSQL, MySQL, SQLite, SQL Server/T-SQL, BigQuery, Snowflake, etc.), mayúsculas de palabras clave/identificadores/funciones, estilo de sangría (estándar, tabular izquierda/derecha) y líneas entre sentencias. Produce un resultado resaltado y listo para copiar con estadísticas de tamaño y sentencias.

Development

Generador de Respaldo de Base de Datos

Generar scripts de respaldo SQL para tablas

Development

Generador de Migración de Base de Datos

Generar scripts de migración SQL para cambios de esquema

Bases de datos

Ejemplos Avanzados de PostgreSQL

Ejemplos avanzados de base de datos PostgreSQL incluyendo estrategias de optimización, consultas complejas, indexación y ajuste de rendimiento

Formatos de datos

Ejemplos de Scripts SQL

Ejemplos de consultas SQL y scripts de base de datos para varios escenarios y casos de uso

Bases de datos

Ejemplos de Prisma

Toolkit de base de datos moderno y ORM de Prisma con definiciones de esquema, migraciones, relaciones y acceso a base de datos tipo seguro

Python

Ejemplos de Base de Datos Web Python

Ejemplos de base de datos Web Python incluyendo conexión SQLite, consultas SQL y manejo de transacciones

Bases de datos

MySQL Database Ejemplos

Scripts de base de datos MySQL de varios niveles de complejidad, desde tablas simples hasta sistemas ERP empresariales

Bases de datos

Ejemplos de MongoDB

Ejemplos completos de la base de datos NoSQL MongoDB que incluyen operaciones CRUD, el framework de agregación, indexación y estrategias de escalado