Node.js MySQL Limit

🚀 Node.js MySQL – LIMIT Clause

The LIMIT clause is used to restrict the number of rows returned by a query.

It’s especially useful for pagination or fetching top N records.

✅ 1. Install mysql2

npm install mysql2

✅ 2. Connect to MySQL Database

const mysql = require("mysql2");

const connection = mysql.createConnection({
host: "localhost",
user: "root",
password: "",
database: "mydb"
});

✅ 3. Fetch Limited Number of Rows

const sql = "SELECT * FROM users LIMIT 5";

connection.query(sql, (err, rows) => {
if (err) throw err;
console.log(rows);
});

Output Example: Returns only 5 rows from the users table.

✅ 4. Fetch Rows with OFFSET (Pagination)

// Fetch 5 rows starting from the 6th row (OFFSET = 5)
const sql = "SELECT * FROM users LIMIT 5 OFFSET 5";

connection.query(sql, (err, rows) => {
if (err) throw err;
console.log(rows);
});

Shortcut Syntax:

SELECT * FROM users LIMIT 5, 5;

  • First 5 = OFFSET

  • Second 5 = number of rows to fetch

✅ 5. Combine LIMIT with ORDER BY

const sql = "SELECT * FROM users ORDER BY created_at DESC LIMIT 3";

connection.query(sql, (err, rows) => {
if (err) throw err;
console.log(rows);
});

  • Fetch latest 3 users based on created_at.

✅ 6. Using Async/Await

const mysql = require("mysql2/promise");

async function getLimitedUsers(limit, offset) {
const connection = await mysql.createConnection({
host: "localhost",
user: "root",
password: "",
database: "mydb"
});

const [rows] = await connection.execute("SELECT * FROM users LIMIT ? OFFSET ?", [limit, offset]);
console.log(rows);

await connection.end();
}

getLimitedUsers(5, 5); // Fetch 5 rows starting from 6th row

✅ 7. Tips & Best Practices

  • Combine LIMIT with ORDER BY to fetch top or latest records.

  • Use prepared statements (?) to safely pass dynamic limit and offset.

  • Use pagination logic for displaying data on pages:

const page = 2;
const limit = 5;
const offset = (page - 1) * limit;

  • Avoid using LIMIT without ORDER BY if you need consistent results.

You may also like...