SELECT Queries in Sequelize

Basic SELECT Queries

Imagine you're a librarian with a digital catalog. Just as you might want to look up all books or find a specific one, Sequelize provides different methods to retrieve data:

Finding All Records

// SQL: SELECT * FROM Users;
const allUsers = await User.findAll();

// SQL: SELECT * FROM Books WHERE genre = 'Fiction';
const fictionBooks = await Book.findAll({
    where: {
        genre: 'Fiction'
    }
});
                

Finding Specific Columns

// SQL: SELECT firstName, lastName FROM Users;
const userNames = await User.findAll({
    attributes: ['firstName', 'lastName']
});
                

Advanced Selection Patterns

Like a chef selecting specific ingredients for a recipe, you can be precise about what data you want:

Column Aliases

// SQL: SELECT firstName AS name, age AS userAge FROM Users;
const users = await User.findAll({
    attributes: [
        ['firstName', 'name'],
        ['age', 'userAge']
    ]
});
                

Calculated Fields

// SQL: SELECT id, firstName, lastName, 
//    CONCAT(firstName, ' ', lastName) AS fullName 
//    FROM Users;
const users = await User.findAll({
    attributes: [
        'id',
        'firstName',
        'lastName',
        [
            sequelize.fn(
                'CONCAT',
                sequelize.col('firstName'),
                ' ',
                sequelize.col('lastName')
            ),
            'fullName'
        ]
    ]
});
                

Finding Single Records

Sometimes you need to find just one specific record, like locating a particular book in a library:

Find by Primary Key

// SQL: SELECT * FROM Users WHERE id = 1;
const user = await User.findByPk(1);
                

Find First Match

// SQL: SELECT * FROM Users 
//    WHERE email = 'john@example.com' 
//    LIMIT 1;
const user = await User.findOne({
    where: {
        email: 'john@example.com'
    }
});
                

Real-World Examples

E-commerce Product Search

// Search products with filters
const searchProducts = async (query, filters) => {
    return await Product.findAll({
        attributes: [
            'id',
            'name',
            'price',
            [sequelize.fn('ROUND', sequelize.col('rating')), 'avgRating']
        ],
        where: {
            name: {
                [Op.iLike]: `%${query}%`
            },
            price: {
                [Op.between]: [filters.minPrice, filters.maxPrice]
            },
            category: filters.categories
        },
        order: [
            ['price', filters.sortPrice || 'ASC']
        ],
        limit: filters.limit || 20,
        offset: filters.offset || 0
    });
};
                

Social Media Feed

// Get user's feed with post details
const getUserFeed = async (userId) => {
    return await Post.findAll({
        attributes: [
            'id',
            'content',
            'createdAt',
            [
                sequelize.fn('COUNT', sequelize.col('Likes.id')),
                'likeCount'
            ]
        ],
        include: [{
            model: User,
            attributes: ['username', 'avatar']
        }, {
            model: Like,
            attributes: []
        }],
        where: {
            userId: {
                [Op.in]: sequelize.literal(`
                    (SELECT followingId 
                     FROM Followers 
                     WHERE followerId = ${userId})
                `)
            }
        },
        group: ['Post.id', 'User.id'],
        order: [['createdAt', 'DESC']],
        limit: 20
    });
};
                

Advanced Selection Techniques

Excluding Fields

// Select all fields except password
const users = await User.findAll({
    attributes: {
        exclude: ['password']
    }
});
                

Conditional Selection

// Select different fields based on user role
const getUsers = async (userRole) => {
    const attributes = userRole === 'admin' 
        ? ['id', 'email', 'role', 'lastLogin']
        : ['id', 'username'];
        
    return await User.findAll({ attributes });
};
                

Best Practices

• Select Only What You Need: Like picking ingredients for a recipe, only select the columns you'll actually use
• Use Proper Indexing: Ensure frequently queried columns are indexed
• Pagination: Always implement pagination for large datasets
• Error Handling: Implement proper error handling for database queries
• Security: Always sanitize user input before using in queries

Common Patterns and Use Cases

Pagination Implementation

const getPaginatedResults = async (page = 1, limit = 10) => {
    const offset = (page - 1) * limit;
    
    const { count, rows } = await Model.findAndCountAll({
        limit,
        offset,
        order: [['createdAt', 'DESC']]
    });
    
    return {
        data: rows,
        totalPages: Math.ceil(count / limit),
        currentPage: page
    };
};
                

Search with Multiple Conditions

const searchUsers = async (searchParams) => {
    const where = {};
    
    if (searchParams.name) {
        where.name = {
            [Op.iLike]: `%${searchParams.name}%`
        };
    }
    
    if (searchParams.age) {
        where.age = {
            [Op.gte]: searchParams.age
        };
    }
    
    return await User.findAll({ where });
};
                

Troubleshooting Tips

• Use logging to see the generated SQL queries
• Check query performance with EXPLAIN
• Monitor query execution time
• Watch for N+1 query problems

Further Topics to Explore

• Subqueries in Sequelize
• Raw queries when needed
• Query optimization techniques
• Complex joins and relationships