Node.js MySQL LIMIT

In this post, we will discuss how to select only particular top rows from an MySQL Table with LIMIT Clause in XAMPP Server using Node.js Script. It is important to install mysql package in node.js.

Command to install the mysql package:

CopiedCopy Code
npm install mysql
				

LIMIT Clause

LIMIT Clause in MySQL is used to return particular number of rows from the top of the resulted rows. It is used with SELECT Clause and WHERE Clause.

Steps:

Now let's see steps

  1. First start your XAMPP Server (Both Apache and MySQL).
  2. Open Notepad or any text-editor and write the Node.js script
  3. In that script, first we have to load the mysql package using the below syntax
  4. var mysql_package = require('mysql');
  5. Create the connection using the server,username and password.
  6. CopiedCopy Code
    
    var connection_data = mysql_package.createConnection({
      host: "localhost",
      user: "root",
      password: "",
      database:"database_name"
    });
    
  7. Write the sql query to select particular number of records from the top of the table using LIMIT Clause. It will take two parameters. The first parameter is the SQL Query and the second parameter will handle the result.
  8. CopiedCopy Code
    
    connection_data.connect(function(error) {
    connection_data.query("SELECT column/s FROM table_name LIMIT number", 
    function (error, result) {
        console.log(result);
      });
    });
    

    Here, number represent an integer that return rows from top.

  9. Now type the following command in your command prompt to run the script.
  10. node file_name.js

It will return the records one by one in the following format:

CopiedCopy Code

[
  RowDataPacket {
    column1: value,
    column2: value,
    .....,
	.....
},

 RowDataPacket {
    column1: value,
    column2: value,
    .....,
	.....
},
.....
.....

Consider the village table with the following records:

alt=

LIMIT Example 1:-

Let's select only the top 3 rows from the village table.

CopiedCopy Code

// Load the mysql package
var mysql_package = require('mysql');
// Create the connection using the server,username and password.
//In my scenario - server is the localhost,
//username is root,
//password is empty.
//database is facility
var connection_data = mysql_package.createConnection({
  host: "localhost",
  user: "root",
  password: "",
  database:"facility"
});
connection_data.connect(function(error) {
// Write SQL query to select top 3 rows rom the village table.
  connection_data.query("SELECT * FROM village LIMIT 3", 
  function (error, result) {
 
//Display the records one by one
    console.log(result);
  });
});
Output:
CopiedCopy Code

[
  RowDataPacket {
    village_name: 'Kakumanu',
    distcict: 'Guntur',
    people: 110
  },
  RowDataPacket {
    village_name: 'delhi',
    distcict: 'delhi',
    people: 30
  },
  RowDataPacket {
    village_name: 'patna',
    distcict: 'patna',
    people: 100
  }
]

So we can see that only top 3 records from the table were returned.

LIMIT Example 2:-

Let's select only one record from the rows with greater than 50 in people column.

CopiedCopy Code

// Load the mysql package
var mysql_package = require('mysql');
// Create the connection using the server,username and password.
//In my scenario - server is the localhost,
//username is root,
//password is empty.
//database is facility
var connection_data = mysql_package.createConnection({
  host: "localhost",
  user: "root",
  password: "",
  database:"facility"
});
connection_data.connect(function(error) {
//Query to select first row from the village table with people greater than 50.
  connection_data.query("SELECT * FROM village WHERE people > 50 LIMIT 1", 
  function (error, result) {
 
//Display the records one by one
    console.log(result);
  });
});
Output:
CopiedCopy Code

[
  RowDataPacket {
    village_name: 'Kakumanu',
    distcict: 'Guntur',
    people: 110
  }
]

So we can see that only first row is returned with record greater than 50 from people column.

Summary

So we seen how to apply LIMIT wirh SELECT & WHERE clauses to return top records in MySQL XAMPP Server using Node.js with two examples. Make sure that you have to install mysql package and xampp server.