Database Queries and Filtering in Node.js
Learn how to retrieve, filter, sort, and limit database records using SQL queries in Node.js applications.
When an application stores thousands of records, retrieving every record is not always useful. We often need specific information, such as products within a price range, employees from a particular department, or the newest records in a database.
SQL provides clauses such as WHERE, ORDER BY, LIMIT, and OFFSET to retrieve the required data. In this tutorial, we will use Node.js, Express.js, mysql2, and MySQL to understand these concepts.
1. What Are Database Queries?
A database query is a request to retrieve or manipulate information stored in a database. A SELECT query retrieves records from a table.
SELECT * FROM products;
This query retrieves all columns and all rows from the
products table.
2. Select Specific Columns
Instead of retrieving every column, we can select only the columns required by the application.
SELECT id, name, price
FROM products;
This query returns only the product ID, name, and price. Selecting the necessary columns can reduce unnecessary data transfer.
| Clause | Purpose |
|---|---|
SELECT |
Specifies the columns to retrieve. |
FROM |
Specifies the table to query. |
WHERE |
Filters records based on a condition. |
ORDER BY |
Sorts the returned records. |
LIMIT |
Restricts the number of returned records. |
OFFSET |
Skips a specified number of records. |
3. Filter Records Using WHERE
The WHERE clause retrieves only the records that match a specified condition. For example, we can find products whose price is greater than or equal to 1000.
SELECT id, name, price
FROM products
WHERE price >= 1000;
We can also filter records using a specific product ID.
SELECT id, name, price
FROM products
WHERE id = 1;
Common WHERE Operators
| Operator | Meaning | Example |
|---|---|---|
= |
Equal to | price = 1000 |
> |
Greater than | price > 1000 |
< |
Less than | price < 1000 |
>= |
Greater than or equal to | price >= 1000 |
<= |
Less than or equal to | price <= 5000 |
AND |
Both conditions must be true | price >= 1000 AND price <= 5000 |
OR |
At least one condition must be true | price < 1000 OR price > 5000 |
LIKE |
Matches a text pattern | name LIKE 'Key%' |
4. Sort Records Using ORDER BY
ORDER BY sorts records in ascending or descending order. ASC means ascending, while DESC means descending.
A. Ascending Order
SELECT id, name, price
FROM products
ORDER BY price ASC;
The products are sorted from the lowest price to the highest price.
B. Descending Order
SELECT id, name, price
FROM products
ORDER BY price DESC;
The products are sorted from the highest price to the lowest price.
5. Limit the Number of Records
LIMIT specifies the maximum number of records returned by a query. It is useful when an application needs only a small number of records.
SELECT id, name, price
FROM products
ORDER BY id
LIMIT 5;
This query returns up to five products, ordered by ID.
6. Pagination Using LIMIT and OFFSET
Pagination divides a large result set into smaller pages. For example, a product listing might display ten products per page.
SELECT id, name, price
FROM products
ORDER BY id
LIMIT 10 OFFSET 0;
The query retrieves the first ten products. To retrieve the next ten, change the offset to 10.
SELECT id, name, price
FROM products
ORDER BY id
LIMIT 10 OFFSET 10;
Pagination Formula
The page requested by the client
(Page Number - 1) × Page Size
LIMIT Page Size OFFSET Calculated Offset
For page 3 with 10 records per page, the offset is
(3 - 1) × 10 = 20.
7. Connect Database Queries to Node.js
Now let's create an Express endpoint that filters products by minimum and maximum price. It also supports sorting and pagination.
This example assumes that the app and MySQL
connection pool named pool have already been created,
as explained in the previous CRUD tutorial.
app.get("/api/products/search", async (req, res) => {
try {
const minPrice = req.query.minPrice === undefined
? 0
: Number(req.query.minPrice);
const maxPrice = req.query.maxPrice === undefined
? 1000000
: Number(req.query.maxPrice);
const page = req.query.page === undefined
? 1
: Number(req.query.page);
const pageSize = req.query.pageSize === undefined
? 10
: Number(req.query.pageSize);
const sort = req.query.sort === undefined
? "asc"
: req.query.sort.toLowerCase();
if (
!Number.isFinite(minPrice) ||
!Number.isFinite(maxPrice) ||
minPrice < 0 ||
maxPrice < minPrice
) {
return res.status(400).json({
message: "Invalid price range."
});
}
if (
!Number.isSafeInteger(page) ||
page < 1 ||
!Number.isSafeInteger(pageSize) ||
pageSize < 1 ||
pageSize > 100
) {
return res.status(400).json({
message: "Invalid page or page size."
});
}
if (!["asc", "desc"].includes(sort)) {
return res.status(400).json({
message: "Sort must be asc or desc."
});
}
const offset = (page - 1) * pageSize;
if (!Number.isSafeInteger(offset)) {
return res.status(400).json({
message: "Requested page is too large."
});
}
const orderDirection = sort === "desc" ? "DESC" : "ASC";
const [products] = await pool.execute(
`SELECT id, name, price
FROM products
WHERE price >= ? AND price <= ?
ORDER BY price ${orderDirection}, id ASC
LIMIT ? OFFSET ?`,
[minPrice, maxPrice, pageSize, offset]
);
res.status(200).json({
page,
pageSize,
minPrice,
maxPrice,
products
});
} catch (error) {
console.error(error);
res.status(500).json({
message: "Unable to search products."
});
}
});
Notice that the sort direction is chosen from a predefined allowlist. SQL placeholders are used for the filter values, page size, and offset.
8. Test the API
Start the Node.js server and send a GET request from Postman or another API client.
http://localhost:3000/api/products/search?minPrice=500&maxPrice=5000&page=1&pageSize=10&sort=asc
Example response:
{
"page": 1,
"pageSize": 10,
"minPrice": 500,
"maxPrice": 5000,
"products": [
{
"id": 1,
"name": "Keyboard",
"price": "1200.00"
}
]
}
This is an illustrative response. The actual records depend on the data stored in your MySQL database. Depending on the mysql2 configuration, DECIMAL values may be returned as strings.
9. Use Parameterized Queries
When query values come from a user, use parameterized queries instead of concatenating input into SQL statements.
Unsafe Example
// Do not build SQL using untrusted input.
const sql =
"SELECT * FROM products WHERE name = '" +
req.query.name +
"'";
Safer Example
const [products] = await pool.execute(
"SELECT id, name, price FROM products WHERE name = ?",
[req.query.name]
);
Parameterized queries help prevent SQL injection by keeping supplied values separate from the SQL statement.
10. Database Query Flow
Price, sort, and pagination parameters
Validate query parameters
WHERE, ORDER BY, LIMIT, OFFSET
Find and return matching records
Send filtered records to the client
Important Points to Remember
- SELECT retrieves records from a database table.
- WHERE filters records according to specified conditions.
- ORDER BY sorts records in ascending or descending order.
- LIMIT restricts the number of records returned.
- OFFSET skips records and is useful for pagination.
- Validate query parameters before executing database queries.
- Use parameterized queries for user-supplied values.
- Allowlist dynamic SQL identifiers and sort directions.
- Use a stable ORDER BY when implementing pagination.
- Handle database errors and return appropriate HTTP status codes.
Summary
Database queries help applications retrieve exactly the information they need. We learned how to select specific columns, filter records with WHERE, sort with ORDER BY, and implement pagination with LIMIT and OFFSET.
We also connected these concepts to an Express.js API using mysql2 and learned why parameterized queries are important for security.