How to query databases in Node.js
Łukasz Holeczek
Follow Łukasz Holeczek on GitHub
Connect with Łukasz Holeczek on LinkedIn
Follow Łukasz Holeczek on X (Twitter)
Querying databases in Node.js efficiently requires understanding both raw SQL approaches and ORM abstractions to handle data retrieval, filtering, and aggregation operations effectively. As the creator of CoreUI with extensive Node.js experience since 2014, I’ve implemented database query systems in numerous enterprise applications for optimal performance and maintainable data access patterns. The most effective approach involves using a combination of ORM queries for complex relationships and raw SQL for performance-critical operations with proper parameterization. This method provides flexibility in query optimization while maintaining security through prepared statements and efficient connection management.
Use ORM query builders and raw SQL queries with proper parameterization for efficient and secure database operations in Node.js applications.
const { Sequelize, DataTypes, Op } = require('sequelize')
const { Pool } = require('pg')
// Database setup with Sequelize ORM
const sequelize = new Sequelize(process.env.DATABASE_URL, {
logging: false,
pool: {
max: 10,
min: 0,
acquire: 30000,
idle: 10000
}
})
// Raw SQL connection pool
const pool = new Pool({
connectionString: process.env.DATABASE_URL,
max: 10,
idleTimeoutMillis: 30000
})
// Models
const User = sequelize.define('User', {
email: DataTypes.STRING,
name: DataTypes.STRING,
age: DataTypes.INTEGER,
isActive: DataTypes.BOOLEAN
})
const Post = sequelize.define('Post', {
title: DataTypes.STRING,
content: DataTypes.TEXT,
published: DataTypes.BOOLEAN,
viewCount: DataTypes.INTEGER,
publishedAt: DataTypes.DATE
})
User.hasMany(Post, { foreignKey: 'authorId' })
Post.belongsTo(User, { foreignKey: 'authorId', as: 'author' })
// ORM Query Examples
class DatabaseQueries {
// Basic CRUD operations
static async findUserById(id) {
return await User.findByPk(id, {
include: [{
model: Post,
where: { published: true },
required: false
}]
})
}
static async createUser(userData) {
return await User.create(userData)
}
static async updateUser(id, updates) {
const [updatedCount, updatedUsers] = await User.update(updates, {
where: { id },
returning: true
})
return updatedUsers[0]
}
// Complex queries with filters
static async findUsersWithFilters(filters = {}) {
const whereConditions = {}
if (filters.name) {
whereConditions.name = {
[Op.iLike]: `%${filters.name}%`
}
}
if (filters.minAge) {
whereConditions.age = {
[Op.gte]: filters.minAge
}
}
if (filters.isActive !== undefined) {
whereConditions.isActive = filters.isActive
}
return await User.findAndCountAll({
where: whereConditions,
include: [{
model: Post,
attributes: ['id', 'title', 'publishedAt'],
where: { published: true },
required: false
}],
order: [['createdAt', 'DESC']],
limit: filters.limit || 10,
offset: filters.offset || 0
})
}
// Aggregation queries
static async getUserStatistics() {
return await User.findAll({
attributes: [
'isActive',
[sequelize.fn('COUNT', sequelize.col('id')), 'userCount'],
[sequelize.fn('AVG', sequelize.col('age')), 'averageAge']
],
include: [{
model: Post,
attributes: [
[sequelize.fn('COUNT', sequelize.col('Posts.id')), 'postCount']
]
}],
group: ['User.isActive', 'User.id']
})
}
// Raw SQL queries for complex operations
static async getPopularPosts(limit = 10) {
const query = `
SELECT
p.id,
p.title,
p."viewCount",
p."publishedAt",
u.name as "authorName",
COUNT(c.id) as "commentCount"
FROM "Posts" p
INNER JOIN "Users" u ON p."authorId" = u.id
LEFT JOIN "Comments" c ON p.id = c."postId"
WHERE p.published = true
GROUP BY p.id, u.name
ORDER BY p."viewCount" DESC, "commentCount" DESC
LIMIT $1
`
const result = await sequelize.query(query, {
bind: [limit],
type: sequelize.QueryTypes.SELECT
})
return result
}
// Advanced search with full-text search
static async searchPosts(searchTerm, options = {}) {
const query = `
SELECT
p.id,
p.title,
p.content,
p."publishedAt",
u.name as "authorName",
ts_rank(
to_tsvector('english', p.title || ' ' || p.content),
plainto_tsquery('english', $1)
) as rank
FROM "Posts" p
INNER JOIN "Users" u ON p."authorId" = u.id
WHERE p.published = true
AND to_tsvector('english', p.title || ' ' || p.content)
@@ plainto_tsquery('english', $1)
ORDER BY rank DESC, p."publishedAt" DESC
LIMIT $2 OFFSET $3
`
const client = await pool.connect()
try {
const result = await client.query(query, [
searchTerm,
options.limit || 10,
options.offset || 0
])
return result.rows
} finally {
client.release()
}
}
// Transaction example
static async createUserWithPosts(userData, postsData) {
const transaction = await sequelize.transaction()
try {
const user = await User.create(userData, { transaction })
const posts = await Promise.all(
postsData.map(postData =>
Post.create({
...postData,
authorId: user.id
}, { transaction })
)
)
await transaction.commit()
return { user, posts }
} catch (error) {
await transaction.rollback()
throw error
}
}
// Pagination helper
static async getPaginatedUsers(page = 1, pageSize = 10, filters = {}) {
const offset = (page - 1) * pageSize
const { count, rows } = await this.findUsersWithFilters({
...filters,
limit: pageSize,
offset
})
return {
data: rows,
pagination: {
currentPage: page,
totalPages: Math.ceil(count / pageSize),
totalItems: count,
hasNextPage: page * pageSize < count,
hasPrevPage: page > 1
}
}
}
}
module.exports = DatabaseQueries
This code demonstrates comprehensive database querying approaches in Node.js using both Sequelize ORM for complex relationships and raw SQL for performance-critical operations. The implementation includes CRUD operations, filtering, aggregations, full-text search, transactions, and pagination with proper parameterization to prevent SQL injection. The query patterns provide flexibility for different use cases while maintaining security and performance optimization.
Best Practice Note:
This is the database query architecture we use in CoreUI backend services for scalable data access with optimal performance and security. Always use parameterized queries, implement proper connection pooling, and consider query performance monitoring for production applications with high traffic volumes.
